June 6, 2012 at 8:40 am
Does anyone know why this is? In the past, I have done a full index rebuild, updateusage, and sp_updatestats about once a week or two.
Now, we have a query that is constantly slowing down and the database tuning advisor keeps asking me to add new stats everytime it runs on this query:
SELECT VBLA, VBLN, VLFN, USL1, VANR, UDAT, VDAT1, VDAT, VKDL, VLAG, (COALESCE(VMGS,0) - COALESCE(VMGL,0) - COALESCE(VMGD,0) - COALESCE(VALLOC,0)) AS OPENQTY,
VPSHP, UTXV, VCSTS, VPLF, VBNR, VPLT, VLOE, VERA, USL1, VMGD, VALLOC
FROM TXUYUF01, TXUYUV00
WHERE VBNR = UBNR AND VPLT = UPLT AND VBLA = UBLA AND VBLN = UBLN AND VBNR = '001' AND VPLT = '01'
AND (VFIL IS NULL OR VFIL <> 'Y') AND (USL3 IS NULL OR (USL3 <> 'HLD' AND USL3 <> 'PFA' AND USL3 <> 'CSH'))
AND (COALESCE(VCSTS,'') <> 'HLD' AND COALESCE(VCSTS,'') <> 'CSH') AND VBLA > 'KB' AND COALESCE(VKZ1, '') <> 'Y'
AND USL1 IN ( 'CA', 'CB', 'CO', 'PL', 'RA', 'RB', 'WR', 'DO', 'EM', 'FB', 'R5', 'R0', 'SM', 'AC', 'SP', 'SS', 'RX', 'ES',
'YN', 'CT', 'C5', 'RC', 'RD', 'CS', 'RS', 'PA', 'CP', 'FF', 'TR', 'PD', 'GE', 'RE', 'HV', 'WB', 'SC', 'PC', 'BU', 'CH',
'PS', 'MS', 'CE', 'NM', 'CD', 'SE', 'CC', 'JL', 'GS', 'CM', 'BB', 'OA', 'OB', 'OE', 'BC', 'WC', 'CI', 'AZ', 'CG', 'EP',
'EC', 'EB', 'BE', 'C4', 'BT', 'LR', 'BS' ) AND (UDAT >= 20040101) AND (UDAT <= 20120630)
AND VBLN in (SELECT VBLN FROM TXUYUF01 where VBNR = '001' AND VPLT = '01' AND VBLA > 'KB' AND (UDAT >= 20040101)
AND (UDAT <= 20120630) AND COALESCE(VERA, '') <> 'E' AND COALESCE(VKZ1, '') <> 'Y' AND (USL3 IS NULL OR (USL3 <> 'HLD' AND USL3 <> 'PFA' AND USL3 <> 'CSH'))
GROUP BY VBNR, VPLT, VBLA, VBLN HAVING SUM(COALESCE(VMGS,0) - COALESCE(VMGL,0) - COALESCE(VMGD,0) - COALESCE(VALLOC,0)) <= 0)
AND TXUYUF01.VALLOC > COALESCE((SELECT SUM(COALESCE(VALLOC,0)) AS DVALLOC FROM TXUYDR01 WHERE TXUYDR01.VBNR = TXUYUF01.VBNR
AND TXUYDR01.VPLT = TXUYUF01.VPLT AND TXUYDR01.VBLA = TXUYUF01.VBLA AND TXUYDR01.VBLN = TXUYUF01.VBLN AND TXUYDR01.VLFN = TXUYUF01.VLFN
GROUP BY VBLN, VLFN),0)
AND ((VDAT = 20120606 AND VDAT1 = 20120523 AND UDAT = 20120523 AND VBLA = 'SO' AND VBLN = '5003469000' AND VLFN > 30)
OR (VDAT = 20120606 AND VDAT1 = 20120523 AND UDAT = 20120523 AND VBLA = 'SO' AND VBLN > '5003469000')
OR (VDAT = 20120606 AND VDAT1 = 20120523 AND UDAT = 20120523 AND VBLA > 'SO')
OR (VDAT = 20120606 AND VDAT1 = 20120523 AND UDAT > 20120523)
OR (VDAT = 20120606 AND VDAT1 > 20120523)
OR (VDAT > 20120606)) ORDER BY VDAT, VDAT1, UDAT, UBLA, UBLN, VLFN
This is the query in question.
Any ideas?
June 7, 2012 at 6:01 am
Not parsing the entire query, but several issues immediately jump out at me. First, you're not using ANSI standard JOIN syntax:
...FROM TableA AS a
JOIN TableB AS b
ON a.ID = b.ID...
But more importantly, you're using functions on columns in the WHERE clause, such as those COALESCE functions. That will automatically result in index scans and there's nothing you can do about that except to stop using the functions on columns in the WHERE clause.
Beyond that, without the structure, data, execution plans and a lot of time, it's hard to suggest all the possible fixes for this query. The DTA is notorious for it's inability to tune a single query. It's reasonably good at tuning large sets of queries based on very thorough data input from a comprehensive capture all queries running against a given database. But single queries executed once, not so much.
Basically, I'd suggest breaking down & restructuring the query. Also, rebuilding the index is for defragmentation of the index. It's not related to the query performance, but to the I/O performance. It's more likely that your constant rebuilding of the index is benefitting the query because you're updating the statistics with a FULL SCAN over & over. You could save time & try just doing the statistics update manually rather than rebuilding the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply