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