SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

charles101
Starting Member

16 Posts

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

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.


thanks



jezemine
Flowing Fount of Yak Knowledge

USA
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 sql-server-performance.com (there are many more than just these two):

http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.com/gv_clustered_indexes.asp

and of course don't forget BOL.

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


www.elsasoft.org

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

pareshmotiwala
Constraint Violating Yak Guru

USA
322 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000