I have a table with the following columns.
Customer Id, SSN and rest of the columns.
I have two queries, one with WHERE criteria as CustomerId and other with
WHERE criteria as SSN.
I have created two Index on the above table. One index uses CustomerId as
the indexed column and other index uses SSN as the Index column.
So when I run the queries how to find out that SQL Server used the
appropriate index. Is it possible to specify the index while running the
query through the application?
Thanks,
AkshayRun your query by enabling Execution plan in Query Analyzer
Thanks,
RK
"Akshay Sen" wrote:
> I have a table with the following columns.
> Customer Id, SSN and rest of the columns.
> I have two queries, one with WHERE criteria as CustomerId and other with
> WHERE criteria as SSN.
> I have created two Index on the above table. One index uses CustomerId as
> the indexed column and other index uses SSN as the Index column.
> So when I run the queries how to find out that SQL Server used the
> appropriate index. Is it possible to specify the index while running the
> query through the application?
> Thanks,
> Akshay
>
>
>
>|||and as for the second one: yes, you can specify which index to be used, via
a hint:
select ... from table with(index(index_name))
but you should better leave to sql server to determine the execution plan,
and the indexes used. sql server's reasoning is based on number of factors,
like column and index statistics, which change over time and which your
application doesn't know about. even worse, the execution plan that was
optimal once, might become less than optimal as tha data in tables change.
dean
"Ram Kumar Koditala" <RamKumarKoditala@.discussions.microsoft.com> wrote in
message news:CDC6F3B0-4377-4801-BDD2-A696BF76B791@.microsoft.com...
> Run your query by enabling Execution plan in Query Analyzer
> Thanks,
> RK
> "Akshay Sen" wrote:
>
as
Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
Friday, March 9, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
Subscribe to:
Posts (Atom)