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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-20 : 08:07:57
|
| claire writes "Hi,I'd like to perform sql tuning on the following query:SELECT g.bkjrcode,g.periode, SUM(g.bdr_hfl) * -1 AS Revenue, 'Revenue' As Type FROM dbo.gbkmut g INNER JOIN dbo.Grtbk gk ON gk.reknr = g.reknr WHERE (g.transtype <> 'V') AND (gk.bal_vw = 'W') AND (gk.debcrd = 'C') AND g.verwerknrl <> 0 AND g.res_id BETWEEN 7662 AND 8523 AND g.bkjrcode=2004 AND g.transtype <> 'B' GROUP BY g.bkjrcode, g.periode UNION(SELECT g.bkjrcode,g.periode, SUM(g.bdr_hfl) * -1 AS Cost, 'Cost' As Type FROM dbo.gbkmut g INNER JOIN dbo.Grtbk gk ON gk.reknr = g.reknr WHERE (g.transtype <> 'V') AND (gk.bal_vw = 'W') AND (gk.debcrd = 'D') AND g.verwerknrl <> 0 AND g.res_id BETWEEN 7662 AND 8523 AND g.bkjrcode=2004 AND g.transtype <> 'B' GROUP BY g.bkjrcode, g.periode) ORDER BY g.bkjrcode, g.periodeas the read = 166000 and duration = 6603 are infavourable. How can i further improve on the query? " |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-02-20 : 08:15:11
|
| Well, you don't need a UNION for this:SELECT g.bkjrcode,g.periode, SUM(g.bdr_hfl) * -1 AS Amount, CASE WHEN gk.debcrd = 'C' THEN 'Cost' ELSE 'Revenue') END As Type FROM dbo.gbkmut g INNER JOIN dbo.Grtbk gk ON gk.reknr = g.reknr WHERE g.transtype <> 'V' AND gk.bal_vw = 'W' AND gk.debcrd IN('D','C') AND g.verwerknrl <> 0 AND g.res_id BETWEEN 7662 AND 8523 AND g.bkjrcode=2004 AND g.transtype <> 'B' GROUP BY g.bkjrcode, g.periodeORDER BY g.bkjrcode, g.periodeAlso look at your query plan for any scans, especially table scans. Also look at the row counts for each stage of the plan. Also check for indexes on all of the columns you have in the WHERE clause. If they are indexed, then run UPDATE STATISTICS...WITH FULLSCAN on those tables (see Books Online for more details). And if this still doesn't improve performance, it's possible that there's too much data being processed for this query, and the indexes are not selective enough. You may want to try using index hints, but make sure you test them thoroughly and compare them to the non-hinted query. |
 |
|
|
|
|
|
|
|
Performance tuning