March 1, 2007 at 9:18 pm
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
March 2, 2007 at 2:22 am
Charlie
Try putting that query through the Database Tuning Advisor and see what it suggests.
Generally speaking, you will want indexes on columns used in JOIN clauses, WHERE clauses and ORDER BY clauses. But it's not that simple because it depends on many factors and you also have to balance the benefits of indexes with the overhead of maintaining them. You also need to choose carefully what column(s) to create your clustered index on. You should read in sql-server-performance.com and other places about indexing strategies. And a lot of it just comes from experience.
Yes, your query may still benefit from indexes even though there is no WHERE clause.
I'm sorry but I don't know anything about linking with Access.
John
March 2, 2007 at 3:16 am
Since you're not using a where, you will get limited improvemetn from indexes. I would suggest, as a first pass, put indexes on the foreign key columns. It may not help though.
If you want some more effective suggestions, please post the schema of the tables and, if possible, the execution plan of that query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2007 at 5:59 am
you really have to examine the query plan to see if there are any possible improvements. If you're struggling with indexes then the tuning advisor may just confuse you further.
Queries without where clauses are tricky to tune as scans are often most effective. It all depends if you think you have a problem.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply