Question

I m having a performance issue with a select statement I m executing.

Here it is:

SELECT     Material.*
FROM       Material 
INNER JOIN LineInfo ON Material.LineInfoCtr = LineInfo.ctr 
INNER JOIN Order_Header ON LineInfo.Order_HeaderCtr = Order_Header.ctr
WHERE   (Order_Header.jobNum =  ttest ) 
    AND (Order_Header.revision_number = 0) 
    AND (LineInfo.lineNum = 46)

The statement is taking 5-10 seconds to execute depending on server load.

Some table stats:

- Material has 2,030,xxx records.
- Lineinfo has 190,xxx records
- Order_Header has 2,5xx records.

My statement is returning a total of 18 rows containing about 20-25 fields of data. Returning a single field or all of them makes no difference. Is this performance typical? Is there something I could do to improve it?

I ve tried using a sub select to retrieve the foreign key, the IN clause and I found one post where a fella said using a left outer join helped him. For me, they all yield the same 5 to 10 seconds of execution time.

This is MS SQL server 2005 accessed through MS SQL management studio. Times are the elapsed time in query analyzer.

Any ideas?

Answer 1

The first thing you should do is analyze the query plan, to see what indexes (if any) SQL Server is using.

You can probably benefit from some covering indexes in this query, since you only use columns in Lineinfo and Order_Header for the join and the query restriction (the WHERE clause).

Answer 2

I do not see anything special in your query so, if indexes are correct, it should perform much more faster than that,, the number of rows is not very high.

Do you have indexes on the table involved in the query and have you tried to use the "display execution plan" option of the Query Analyzer. Basically you need to run the query, loop at the execution plan and add indexes so that you do not see any full table scan operation.

If you run from SQL Management studio then you have the option to tune automatically the query adding indexes but I would suggest trying optimize on your own to better understand what you re doing.

Regards Massimo

Answer 3

It won t affect performance, but don t write a query such as "SELECT * FROM X". Eschew the star notation and spell out the individual columns. The code that calls this will still work that way, even if the schema is changed by adding a column.

Indexes are key here, as others have already said.

The order of the WHERE clauses can help. Execute the one that eliminates the greatest number of rows from consideration first.

Answer 4

Taking all suggestions and rolling them together I was able to setup some indexes and now it s taking less than a second to execute. Honestly, it s almost immediate.

My problem was that by clicking on the table properties I saw that the primary key was indexed and I mistakenly thought that s what everyone had been talking about. I looked at the execution plan and ran the tuning assistant and putting the two together, I realized that you could index the foreign keys too. That is now done and things are exceptionally snappy.

Thanks for the help, and sorry for such a newb question.

友情链接