Query Performance and Indexes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Performance and Indexes

Please check the following query… which is used to update the average demand per month. This query is taking 2-3 mins over a 70GB database.
UPDATE #Temp SET Demand =
(SELECT ROUND(SUM(ORDERED)/12 ,2)
FROM Order_Header H with (nolock)
INNER JOIN Order_Detail D with (nolock)
ON H.ORDER_NO = D.ORDER_NO
WHERE
D.ORDER_DATE BETWEEN ’12/23/2004′ AND ’12/23/2005′ AND
D.ORDER_NO like ‘O%’ AND
D.STATUS <> ‘C’ AND
D.CONDITION IN (‘A’,’B’) AND
D.ITEM_ID = #Temp.ITEM_ID AND
D.PartSuffix = #Temp.PartSuffix AND
H.DIVISION = ‘M’
)
–> #Temp table will contains the parts records around 15000. I have to update the demand for each part over a period of 1 year data.
–> Order_Header table is having 5 million records with 15 indexes
–> Order_Detail table is having 10 million records with 25 indexes I have two quesions…
1) Is there any other way to rewrite this query to get better performance?
2) On what fields I have to create index? Can any one tell based on the query?


1) create #temp table for real, said temp_01.
2) replace in procedure using Query Analyzer
3) see execution plan to find out if any indexes are necesary or run Index Tuning Wizard.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
check this tips… Index Optimization tips
•Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
•Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
•Try to create indexes on columns that have integer values rather than character values.
•If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
•If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
•Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
•Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
•If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
•You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.
•You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.
sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’)"
SURYA
Surya, When quoting the tips, show the Link to that site if any where you have read that [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
You need an composite index at least on ItemID and partSuffix columns of Order_Detail table. You need at lest index on Order_No on Order_Header table. To suggest anything more then that I would need to know data distribution. Any column mentioned in the query may be added to the composite index to speed-up the query, but too many and too wide indexes slows down insert/update/delete operations. I guess you can use d.condition between ‘A’ and ‘B’ instead of in (‘a’, ‘b’). That might speed-up the query if proper (composite) index is in place and that index is the part of the most efficient execution plan. It might be more efficient not to use update:
SELECT d.item_ID, d.PartSuffix, ROUND(SUM(ORDERED)/12 ,2)
FROM Order_Header H with (nolock)
INNER JOIN Order_Detail D with (nolock)ON H.ORDER_NO = D.ORDER_NO
WHERE
D.ORDER_DATE BETWEEN ’12/23/2004′ AND ’12/23/2005′ AND
D.ORDER_NO like ‘O%’ AND
D.STATUS <> ‘C’ AND
D.CONDITION IN (‘A’,’B’) AND
H.DIVISION = ‘M’ and
<conditions restricting to specific combinations of item_id, partSuffix>
group by d.itemID, d.PartSuffix
]]>