Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Question about indexes
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

16 Posts

Posted - 03/01/2007 :  23:15:14  Show Profile  Reply with Quote

I have a question regarding indexes. In short I have no idea what to index, I have read up on clustered and non clustered but still its a bit mind boggling.

I have a query which is returning all the results from 4 different tables and 1 view (sq HGI Results), The main table Outbound Sample has left joins to all the other tables by [sample number] and the query takes about 15 secs to run returning 9800 records, it is also joined to the customer table by customer name and destination.

SELECT [Outbound Samples].[Start Date], [Outbound Samples].[Finish Date], [Outbound Samples].[Scheduled Date], [Outbound Samples].[Sample Number], [Outbound Samples].[Stockpile Number], [Outbound Samples].[Lot Number], [Outbound Samples].[Sample Tonnes], [Outbound Samples].[Delivered Tonnes], [Outbound Samples].Customer, [Outbound Samples].Destination, [Outbound Samples].[Train Number], [Outbound Samples].[Sequence No], [Sample Results].[Customer Number], [Sample Results].[ACIRL File Number], [Sample Results].Moisture, [Sample Results].SE, [Sample Results].[Dry SE], [Sample Results].Sulphur, [Sample Results].[Dry Sulphur], [Sample Results].Ash, [Sample Results].[Dry Ash], [Sample Results].DAF, [Sample Results].[Sample Mass], [Sample Results].[Date Received], [WP Sample Results].[Sulphur 26], [WP Sample Results].[Sulphur 27], [WP Sample Results].[Ash 27], [WP Sample Results].[SE 27], [Outbound Samples].[Primary Cuts], [Outbound Samples].[Secondary Cuts], [Sample Results].Secure, [sq HGI Results].Result, [Outbound Samples].Excluded, Customer.[Delivery Method]
FROM ((([Outbound Samples] LEFT JOIN [Sample Results] ON [Outbound Samples].[Sample Number] = [Sample Results].[Sample Number]) LEFT JOIN [WP Sample Results] ON [Outbound Samples].[Sample Number] = [WP Sample Results].[Sample Number]) LEFT JOIN [sq HGI Results] ON [Outbound Samples].[Sample Number] = [sq HGI Results].[Sample Number]) LEFT JOIN Customer ON ([Outbound Samples].Destination = Customer.Destination) AND ([Outbound Samples].Customer = Customer.[Customer Name])

I was wondering

1. What fields would I create an index on and what kind off index to speed up the above query (I notice indexes are already created for sample number as this is the primary key).

2. Because I want to select all the records and not use a WHERE clause can I still use indexes effectively.

3. If Im using a linked Access interface will indexes still benefit as i understand linked tables are still processed by the Jet engine.


Flowing Fount of Yak Knowledge

2886 Posts

Posted - 03/01/2007 :  23:42:46  Show Profile  Visit jezemine's Homepage  Reply with Quote
you might check out some of the articles on (there are many more than just these two):

and of course don't forget BOL.

also, learn how to indent code for readability. otherwise nobody will read it. :)

Edited by - jezemine on 03/01/2007 23:59:55
Go to Top of Page

Constraint Violating Yak Guru

323 Posts

Posted - 03/05/2007 :  12:55:16  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
Charles, if you save this query as a script file. You can use the Database Tuning Advisor tool in SSMS and if you start the analysis, it will give you the idea what to index and what statistics to create. It even gives you a script which will create your indexes ready to use. It has really helped me a lot.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000