Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
charles101
Starting Member
16 Posts |
 Posted - 2007-03-01 : 23:15:14
|
| 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 wondering1. 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
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-01 : 23:42:46
|
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.asphttp://www.sql-server-performance.com/gv_clustered_indexes.aspand of course don't forget BOL. also, learn how to indent code for readability. otherwise nobody will read it. :)
www.elsasoft.org |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-05 : 12:55:16
|
| 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. |
|
|
|
|
|
|
|
|
Question about indexes