Help optimising this query? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help optimising this query?

Author

Topic

weirdbeardmt
Starting Member

4 Posts

Posted - 2008-10-21 : 05:05:36

Hi..

I have this query, which works as well as it should. It is designed to retrieve sales statistics from our CRM db for all just today. The main problem though, is that when the server is busy (i.e., during peak times when our agents are using the CRM app heavily) then it tends to time out a lot. The main flaw in this scheme is that it's always querying the entire sales history table (is obviously with time, the query is going to get slower and slower, so I really need to look in to alternative solutions such as a simple trigger to populate a new table for example). So really I'm just interested to see how SQL experts would go about improving this, if possible!

SELECT Brand_Group, SUM(CASE method WHEN 'TELE' THEN sales ELSE 0 END) AS Phone, SUM(CASE method WHEN 'WEB' THEN sales ELSE 0 END)
AS Web, SUM(CASE method WHEN 'SCAN' THEN sales ELSE 0 END) AS Scan, SUM(CASE method WHEN 'MAIL' THEN sales ELSE 0 END) AS Mail,
SUM(Sales) AS Total
FROM (SELECT dbo.sord.level_1 AS Brand_Group, dbo.sord_defl.method AS Method, 1 AS Sales
FROM dbo.sord_defl INNER JOIN
dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no
WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0) AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))) AS a
WHERE (Brand_Group = Brand_Group)
GROUP BY Brand_Group

So it sorts the orders depending on the method that they were received and then groups them by the appropriate brand (table SORD has the order, table SORD_DEFL has the method)

Here's the stats profile of the query:

2 1 SELECT Brand_Group, SUM(CASE method WHEN 'TELE' THEN sales ELSE 0 END) AS Phone, SUM(CASE method WHEN 'WEB' THEN sales ELSE 0 END) AS Web, SUM(CASE method WHEN 'SCAN' THEN sales ELSE 0 END) AS Scan, SUM(CASE method WHEN 'MAIL' THEN sales ELSE 0 END) AS Mail, SUM(Sales) AS Total FROM (SELECT dbo.sord.level_1 AS Brand_Group, dbo.sord_defl.method AS Method, 1 AS Sales FROM dbo.sord_defl INNER JOIN dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0) AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))) AS a WHERE (Brand_Group = Brand_Group) GROUP BY Brand_Group 6 1 0 NULL NULL NULL NULL 6.6757 NULL NULL NULL 1054.041 NULL NULL SELECT 0 NULL
2 1 |--Parallelism(Gather Streams) 6 2 1 Parallelism Gather Streams NULL NULL 6.6757 0 0.02854153 39 1054.041 [sord].[level_1], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008] NULL PLAN_ROW 1 1
2 8 |--Hash Match(Aggregate, HASH:([sord].[level_1]), RESIDUAL:([sord].[level_1]=[sord].[level_1]) DEFINE:([Expr1004]=SUM(If ([sord_defl].[method]='TELE') then 1 else 0), [Expr1005]=SUM(If ([sord_defl].[method]='WEB') then 1 else 0), [Expr1006]=SUM(If ([sord_defl].[method]='SCAN') then 1 else 0), [Expr1007]=SUM(If ([sord_defl].[method]='MAIL') then 1 else 0), [Expr1008]=SUM(1))) 6 3 2 Hash Match Aggregate HASH:([sord].[level_1]), RESIDUAL:([sord].[level_1]=[sord].[level_1]) [Expr1004]=SUM(If ([sord_defl].[method]='TELE') then 1 else 0), [Expr1005]=SUM(If ([sord_defl].[method]='WEB') then 1 else 0), [Expr1006]=SUM(If ([sord_defl].[method]='SCAN') then 1 else 0), [Expr1007]=SUM(If ([sord_defl].[method]='MAIL') then 1 else 0), [Expr1008]=SUM(1) 6.6757 0 0.3944395 39 1054.012 [sord].[level_1], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008] NULL PLAN_ROW 1 1
1624 8 |--Parallelism(Repartition Streams, PARTITION COLUMNS:([sord].[level_1])) 6 4 3 Parallelism Repartition Streams PARTITION COLUMNS:([sord].[level_1]) NULL 230509.8 0 1.118696 747 1053.618 [sord_defl].[method], [sord].[level_1] NULL PLAN_ROW 1 1
1624 8 |--Nested Loops(Inner Join, OUTER REFERENCES:([sord_defl].[ref_no]) WITH PREFETCH) 6 5 4 Nested Loops Inner Join OUTER REFERENCES:([sord_defl].[ref_no]) WITH PREFETCH NULL 230509.8 0 0.2421036 747 1052.499 [sord_defl].[method], [sord].[level_1] NULL PLAN_ROW 1 1
1624 8 |--Clustered Index Scan(OBJECT:([qlmdat].[dbo].[sord_defl].[sord_defl_pk]), WHERE:(datediff(day, [sord_defl].[dt_created], getdate())=0 AND ((([sord_defl].[method]='SCAN' OR [sord_defl].[method]='MAIL') OR [sord_defl].[method]='WEB') OR [sord_defl].[method]='TELE'))) 6 7 5 Clustered Index Scan Clustered Index Scan OBJECT:([qlmdat].[dbo].[sord_defl].[sord_defl_pk]), WHERE:(datediff(day, [sord_defl].[dt_created], getdate())=0 AND ((([sord_defl].[method]='SCAN' OR [sord_defl].[method]='MAIL') OR [sord_defl].[method]='WEB') OR [sord_defl].[method]='TELE')) [sord_defl].[ref_no], [sord_defl].[dt_created], [sord_defl].[method] 231678 279.6109 4.151203 195 283.7621 [sord_defl].[ref_no], [sord_defl].[dt_created], [sord_defl].[method] NULL PLAN_ROW 1 1
1624 1624 |--Clustered Index Seek(OBJECT:([qlmdat].[dbo].[sord].[sord_pk]), SEEK:([sord].[ref_no]=[sord_defl].[ref_no]), WHERE:([sord].[level_1]=[sord].[level_1]) ORDERED FORWARD) 6 8 5 Clustered Index Seek Clustered Index Seek OBJECT:([qlmdat].[dbo].[sord].[sord_pk]), SEEK:([sord].[ref_no]=[sord_defl].[ref_no]), WHERE:([sord].[level_1]=[sord].[level_1]) ORDERED FORWARD [sord].[level_1] 1 0.003203425 7.9603E-05 562 760.1566 [sord].[level_1] NULL PLAN_ROW 1 231678

I can't do anything to adjust the general database design as it's a 3rd party application.

Any thoughts on where the bottlenecks are? It currently takes between 15-25 seconds to run (on a good day). Both tables have around 15m rows in them.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 05:11:20

whats the purpose of below where condition? it looks like a redundant check

WHERE (Brand_Group = Brand_Group)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-21 : 06:08:32

try with Pivot


SELECT  Brand_Group,
        [TELE] AS 'Phone',
        [Web],
        [Scan],
        [MAIL] AS 'Total'
FROM
        (SELECT dbo.sord.level_1     AS Brand_Group,
                dbo.sord_defl.method AS Method     ,
                COUNT(1)             AS Sales
        FROM    dbo.sord_defl
        INNER JOIN dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no
        WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0)
            AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))
        ) AS a
PIVOT
	( MAX(Sales)
	FOR Method IN ([TELE], [Web], [Scan], [MAIL])
	) AS Pvt
ORDER BY Brand_Group


OR 

SELECT  Brand_Group,
        [TELE] AS 'Phone',
        [Web],
        [Scan],
        [MAIL] AS 'Total'
FROM
        (SELECT dbo.sord.level_1     AS Brand_Group,
                dbo.sord_defl.method AS Method     ,
                1					 AS Sales
        FROM    dbo.sord_defl
        INNER JOIN dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no
        WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0)
            AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))
        ) AS a
PIVOT
	( SUM(Sales)
	FOR Method IN ([TELE], [Web], [Scan], [MAIL])
	) AS Pvt
ORDER BY Brand_Group

weirdbeardmt
Starting Member

4 Posts

Posted - 2008-10-21 : 06:26:35

quote:
Originally posted by visakh16

whats the purpose of below where condition? it looks like a redundant check

WHERE (Brand_Group = Brand_Group)

Because the top most select uses aggregate functions (i.e., sum) you have to have a group by clause...

Thanks Peter - I'll try your suggestion and see what happens!

weirdbeardmt
Starting Member

4 Posts

Posted - 2008-10-21 : 06:34:19

quote:

Originally posted by PeterNeo

try with Pivot


SELECT  Brand_Group,
        [TELE] AS 'Phone',
        [Web],
        [Scan],
        [MAIL] AS 'Total'
FROM
        (SELECT dbo.sord.level_1     AS Brand_Group,
                dbo.sord_defl.method AS Method     ,
                COUNT(1)             AS Sales
        FROM    dbo.sord_defl
        INNER JOIN dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no
        WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0)
            AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))
        ) AS a
PIVOT
	( MAX(Sales)
	FOR Method IN ([TELE], [Web], [Scan], [MAIL])
	) AS Pvt
ORDER BY Brand_Group


OR 

SELECT  Brand_Group,
        [TELE] AS 'Phone',
        [Web],
        [Scan],
        [MAIL] AS 'Total'
FROM
        (SELECT dbo.sord.level_1     AS Brand_Group,
                dbo.sord_defl.method AS Method     ,
                1					 AS Sales
        FROM    dbo.sord_defl
        INNER JOIN dbo.sord ON dbo.sord_defl.ref_no = dbo.sord.ref_no
        WHERE (DATEDIFF(dd, dbo.sord_defl.dt_created, GETDATE()) = 0)
            AND (dbo.sord_defl.method IN ('TELE', 'WEB', 'MAIL', 'SCAN'))
        ) AS a
PIVOT
	( SUM(Sales)
	FOR Method IN ([TELE], [Web], [Scan], [MAIL])
	) AS Pvt
ORDER BY Brand_Group

Hi Peter... sorry, my mistake using this forum - the server is SQL 8 so no pivot :(

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 08:05:17

You should remove functions where ever possible as the query plan cannot work out that in index might be available.
A suggestion for you to try/measure:
- Ensure there is an index on dt_created
- rewrite the date compare function so it is terms of a comparison against an unmodified dt_created function, e.g. if dt_created > (some function that calculates the date rounded as you need - maybe CONVERT??). SQL cannot use an index where there is an expression.
Your redundant checks could be removed, as could the METHOD IN. I dont think that will make a difference because you have it covered in the CASE.

weirdbeardmt
Starting Member

4 Posts

Posted - 2008-10-21 : 09:38:01

quote:
Originally posted by LoztInSpace

You should remove functions where ever possible as the query plan cannot work out that in index might be available.
A suggestion for you to try/measure:
- Ensure there is an index on dt_created
- rewrite the date compare function so it is terms of a comparison against an unmodified dt_created function, e.g. if dt_created > (some function that calculates the date rounded as you need - maybe CONVERT??). SQL cannot use an index where there is an expression.

OK cool - didn't know that. I suppose I could just set a var as today's date and see if dt_created is > than it...?

quote:

Your redundant checks could be removed, as could the METHOD IN. I dont think that will make a difference because you have it covered in the CASE.

Except that the method could be one of about 15 different possibilities, whereas we're only interested in those 4, so the check isn't redundant!?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 09:53:27

If you can do your date thing then give it a go and see what happens. I think it will help if you have an index on the date. Date created is an ideal candidate for a clustered index in cases where you have rows added with a datestamp.

METHOD may or may not be redundant in the where clause depending on the index and selectivity. In practice it is redundant because your sum wont add up anything you dont need. It won't hurt to keep it in and may make for a better plan if the optimiser can't work out the case statement or the selectivity makes it worthwhile (I doubt it).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources