Query Too Slow - 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 2000 Forums

Transact-SQL (2000)

Query Too Slow

Author

Topic

mdd1314
Starting Member

3 Posts

Posted - 2004-07-16 : 15:22:14

This query takes about 8 seconds to run, but I really need to cut that time in half, if possible. The "lu_" tables range in size from 3 rows to about 230 rows. The RevExp table has about 2.2 million records, and it has an index with these columns: univ_fiscal_yr, dept_code, catid, org_cd, data_source, and amount.

Is there any way to speed this thing up a little bit?


CREATE PROCEDURE dbo.resp_BreakdownByReportPage
(
	@deptCode varchar(5),
	@orgCode varchar(5) = '%',
	@firstYear int,
	@rptPageID int,
	@dataSource varchar(4) = '%'
)
AS

IF @orgCode = ''
BEGIN
	SET @orgCode = '%'
END

IF @dataSource = ''
BEGIN
	SET @dataSource = '%'
END


SELECT
	grouping(c.name) as SubGrouping,
	grouping(d.name) as GroupGrouping,
	grouping(f.Category) as CatGrouping,
	b.id AS GTotID,
	isnull(b.Name, '') AS GTotName,
	c.id AS SubID,
	isnull(c.Name, '') AS SubName,
	d.id AS GrpID,
	isnull(d.Code, '') AS GrpCode,
	isnull(d.Name, '') AS GrpName,
	f.id AS CatID,
	isnull(f.Category, '') AS CategoryName,
	(SELECT isnull(SUM(amount), 0)
		FROM RevExp
		WHERE catID = f.ID AND univ_fiscal_yr = @firstYear AND dept_code = @deptCode 
			AND org_cd LIKE @orgCode AND data_source LIKE @dataSource
	) AS Year1,
	(SELECT isnull(SUM(amount), 0)
		FROM RevExp
		WHERE catID = f.ID AND univ_fiscal_yr = @firstYear + 1 AND dept_code = @deptCode 
			AND org_cd LIKE @orgCode AND data_source LIKE @dataSource
	) AS Year2,
	(SELECT isnull(SUM(amount), 0)
		FROM RevExp
		WHERE catID = f.ID AND univ_fiscal_yr = @firstYear + 2 AND dept_code = @deptCode 
			AND org_cd LIKE @orgCode AND data_source LIKE @dataSource
	) AS Year3,
	(SELECT isnull(SUM(amount), 0)
		FROM RevExp
		WHERE catID = f.ID AND univ_fiscal_yr = @firstYear + 3 AND dept_code = @deptCode 
			AND org_cd LIKE @orgCode AND data_source LIKE @dataSource
	) AS Year4,
	(SELECT isnull(SUM(amount), 0)
		FROM RevExp
		WHERE catID = f.ID AND univ_fiscal_yr = @firstYear + 4 AND dept_code = @deptCode 
			AND org_cd LIKE @orgCode AND data_source LIKE @dataSource
	) AS Year5,
	f.lineNumber as LineNumber
FROM
	lu_GTotCodes b INNER JOIN
	lu_SubCodes c ON b.ID = c.GTotID INNER JOIN
	lu_GroupCodes d ON c.ID = d.SubID INNER JOIN
	lu_EntityCodes e ON d.ID = e.GrpID INNER JOIN
	lu_LineNumbers f ON e.ID = f.EntityID
WHERE
	b.rptPageID = 
	CASE
		WHEN @rptPageID > 4 THEN b.rptPageID
		ELSE @rptPageID
	END AND
	f.RptID = 
	CASE
		WHEN @rptPageID > 4 THEN @rptPageID
		ELSE f.RptID
	END AND
	EXISTS (SELECT TOP 1 catid FROM RevExp g WHERE f.id = g.catid AND g.dept_code = @deptCode AND g.org_cd LIKE @orgCode)
GROUP BY
	b.id, b.name, c.id, c.name, d.id, d.code, d.name, f.id, f.lineNumber, f.category WITH ROLLUP
HAVING
	grouping(f.category) = 0 OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=0) OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=1 AND grouping(d.code)=1 AND grouping(d.id)=1 AND grouping(c.name)=0) OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=1 AND grouping(d.code)=1 AND grouping(d.id)=1 AND grouping(c.name)=1 AND grouping(c.id)=1 AND grouping(b.name)=0)
ORDER BY
	isnull(b.id, 999999), isnull(c.id, 999999), isnull(d.id, 999999), isnull(f.LineNumber, 999999)
GO

mdd1314
Starting Member

3 Posts

Posted - 2004-07-16 : 15:33:15

Here are the showplan results if that helps any...

 declare @deptCode varchar(5)  declare @orgCode varchar(5)  declare @firstYear int  declare @rptPageID int  declare @dataSource varchar(4)  set @deptcode = 'surg'
 set @orgcode = 'surc'
 set @firstyear = 2002
 set @rptpageid = 2
 set @dataSource = 'iu'
 exec resp_breakdownbyreportpage @deptcode, @orgcode, @firstyear, @rptpageid, @datasource
  CREATE PROCEDURE dbo.resp_BreakdownByReportPage  (   @deptCode varchar(5),   @orgCode varchar(5) = '%',   @firstYear int,   @rptPageID int,   @dataSource varchar(4) = '%'  )  AS    IF @orgCode = ''
     BEGIN   SET @orgCode = '%'
   END    IF @dataSource = ''
     BEGIN   SET @dataSource = '%'
   END      SELECT   grouping(c.name) as SubGrouping,   grouping(d.name) as GroupGrouping,   grouping(f.Category) as CatGrouping,   b.id AS GTotID,   isnull(b.Name, '') AS GTotName,   c.id AS SubID,   isnull(c.Name, '') AS SubName,   d.id AS GrpID,   isnul
       |--Sort(ORDER BY:([Expr1045] ASC, [Expr1046] ASC, [Expr1047] ASC, [Expr1048] ASC))
            |--Compute Scalar(DEFINE:([Expr1015]=isnull([b].[Name], ''), [Expr1016]=isnull([c].[Name], ''), [Expr1017]=isnull([d].[Code], ''), [Expr1018]=isnull([d].[Name], ''), [Expr1019]=isnull([f].[Category], ''), [Expr1024]=isnull([Expr1022], 0.00), [E
                 |--Nested Loops(Left Outer Join, WHERE:([RevExp].[catid]=[f].[ID]))
                      |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f].[ID]))
                      |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f].[ID]))
                      |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f].[ID]))
                      |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f].[ID]))
                      |    |    |    |    |--Filter(WHERE:((([Expr1007]=0 OR (([Expr1007]=1 AND [Expr1008]=1) AND [Expr1009]=0)) OR ((((([Expr1007]=1 AND [Expr1008]=1) AND [Expr1009]=1) AND [Expr1010]=1) AND [Expr1011]=1) AND [Expr1012]=0)) OR ((((((([Expr
                      |    |    |    |    |    |--Compute Scalar(DEFINE:([Expr1007]=If (([Grp1006]&1)<>0) then 1 else 0, [Expr1008]=If (([Grp1006]&4)<>0) then 1 else 0, [Expr1009]=If (([Grp1006]&8)<>0) then 1 else 0, [Expr1010]=If (([Grp1006]&16)<>0) then 
                      |    |    |    |    |         |--Stream Aggregate(GROUP BY:([b].[ID], [b].[Name], [c].[ID], [c].[Name], [d].[ID], [d].[Code], [d].[Name], [f].[ID], [f].[LineNumber], [f].[Category]))
                      |    |    |    |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[GTotID]))
                      |    |    |    |    |                   |--Sort(ORDER BY:([c].[GTotID] ASC, [c].[ID] ASC, [d].[ID] ASC, [f].[ID] ASC))
                      |    |    |    |    |                   |    |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[SubID]))
                      |    |    |    |    |                   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[GrpID]))
                      |    |    |    |    |                   |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([f].[EntityID]))
                      |    |    |    |    |                   |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([g].[catid]))
                      |    |    |    |    |                   |         |    |    |    |--Hash Match(Aggregate, HASH:([g].[catid]))
                      |    |    |    |    |                   |         |    |    |    |    |--Index Scan(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9] AS [g]),  WHERE:([g].[dept_code]=[@deptCode] AND like([g].[org_cd], [@orgCode], NULL)))
                      |    |    |    |    |                   |         |    |    |    |--Clustered Index Seek(OBJECT:([RevExp2].[dbo].[lu_LineNumbers].[PK_lu_LineNumbers_1] AS [f]), SEEK:([f].[ID]=[g].[catid]),  WHERE:([f].[RptID]=If ([@rptPageID]>4) then
                      |    |    |    |    |                   |         |    |    |--Clustered Index Seek(OBJECT:([RevExp2].[dbo].[lu_EntityCodes].[PK_lu_EntityCodes_1] AS [e]), SEEK:([e].[ID]=[f].[EntityID]) ORDERED FORWARD)
                      |    |    |    |    |                   |         |    |--Clustered Index Seek(OBJECT:([RevExp2].[dbo].[lu_GroupCodes].[PK_lu_GroupCodes_1] AS [d]), SEEK:([d].[ID]=[e].[GrpID]) ORDERED FORWARD)
                      |    |    |    |    |                   |         |--Clustered Index Seek(OBJECT:([RevExp2].[dbo].[lu_SubCodes].[PK_lu_SubCodes_1] AS [c]), SEEK:([c].[ID]=[d].[SubID]) ORDERED FORWARD)
                      |    |    |    |    |                   |--Clustered Index Seek(OBJECT:([RevExp2].[dbo].[lu_GTotCodes].[PK_lu_GTotCodes_1] AS [b]), SEEK:([b].[ID]=[c].[GTotID]),  WHERE:([b].[RptPageID]=If ([@rptPageID]>4) then [b].[RptPageID] else [@
                      |    |    |    |    |--Hash Match(Cache, HASH:([f].[ID], LikeRangeStart([@orgCode], NULL), LikeRangeEnd([@orgCode], NULL), LikeRangeInfo([@orgCode], NULL), LikeRangeStart([@dataSource], NULL), LikeRangeEnd([@dataSource], NULL), LikeRa
                      |    |    |    |         |--Compute Scalar(DEFINE:([Expr1022]=If ([Expr1211]=0) then NULL else [Expr1212]))
                      |    |    |    |              |--Stream Aggregate(DEFINE:([Expr1211]=COUNT_BIG([RevExp].[amount]), [Expr1212]=SUM([RevExp].[amount])))
                      |    |    |    |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1208], [Expr1209], LikeRangeInfo([@orgCode], NULL)))
                      |    |    |    |                        |--Compute Scalar(DEFINE:([Expr1208]=Convert(LikeRangeStart([@orgCode], NULL)), [Expr1209]=Convert(LikeRangeEnd([@orgCode], NULL)), [Expr1083]=LikeRangeInfo([@orgCode], NULL)))
                      |    |    |    |                        |    |--Constant Scan
                      |    |    |    |                        |--Index Seek(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9]), SEEK:([RevExp].[univ_fiscal_yr]=[@firstYear] AND [RevExp].[dept_code]=[@deptCode] AND [RevExp].[catid]=[f].[ID] AND [RevExp].[org_cd] >
                      |    |    |    |--Hash Match(Cache, HASH:([f].[ID], [@firstYear]+1, LikeRangeStart([@orgCode], NULL), LikeRangeEnd([@orgCode], NULL), LikeRangeInfo([@orgCode], NULL), LikeRangeStart([@dataSource], NULL), LikeRangeEnd([@dataSource], NU
                      |    |    |         |--Compute Scalar(DEFINE:([Expr1027]=If ([Expr1216]=0) then NULL else [Expr1217]))
                      |    |    |              |--Stream Aggregate(DEFINE:([Expr1216]=COUNT_BIG([RevExp].[amount]), [Expr1217]=SUM([RevExp].[amount])))
                      |    |    |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1213], [Expr1214], LikeRangeInfo([@orgCode], NULL)))
                      |    |    |                        |--Compute Scalar(DEFINE:([Expr1213]=Convert(LikeRangeStart([@orgCode], NULL)), [Expr1214]=Convert(LikeRangeEnd([@orgCode], NULL)), [Expr1096]=LikeRangeInfo([@orgCode], NULL)))
                      |    |    |                        |    |--Constant Scan
                      |    |    |                        |--Index Seek(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9]), SEEK:([RevExp].[univ_fiscal_yr]=[@firstYear]+1 AND [RevExp].[dept_code]=[@deptCode] AND [RevExp].[catid]=[f].[ID] AND [RevExp].[org_cd] > [E
                      |    |    |--Hash Match(Cache, HASH:([f].[ID], [@firstYear]+2, LikeRangeStart([@orgCode], NULL), LikeRangeEnd([@orgCode], NULL), LikeRangeInfo([@orgCode], NULL), LikeRangeStart([@dataSource], NULL), LikeRangeEnd([@dataSource], NULL), 
                      |    |         |--Compute Scalar(DEFINE:([Expr1032]=If ([Expr1221]=0) then NULL else [Expr1222]))
                      |    |              |--Stream Aggregate(DEFINE:([Expr1221]=COUNT_BIG([RevExp].[amount]), [Expr1222]=SUM([RevExp].[amount])))
                      |    |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1218], [Expr1219], LikeRangeInfo([@orgCode], NULL)))
                      |    |                        |--Compute Scalar(DEFINE:([Expr1218]=Convert(LikeRangeStart([@orgCode], NULL)), [Expr1219]=Convert(LikeRangeEnd([@orgCode], NULL)), [Expr1109]=LikeRangeInfo([@orgCode], NULL)))
                      |    |                        |    |--Constant Scan
                      |    |                        |--Index Seek(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9]), SEEK:([RevExp].[univ_fiscal_yr]=[@firstYear]+2 AND [RevExp].[dept_code]=[@deptCode] AND [RevExp].[catid]=[f].[ID] AND [RevExp].[org_cd] > [Expr12
                      |    |--Hash Match(Cache, HASH:([f].[ID], [@firstYear]+3, LikeRangeStart([@orgCode], NULL), LikeRangeEnd([@orgCode], NULL), LikeRangeInfo([@orgCode], NULL), LikeRangeStart([@dataSource], NULL), LikeRangeEnd([@dataSource], NULL), LikeR
                      |         |--Compute Scalar(DEFINE:([Expr1037]=If ([Expr1226]=0) then NULL else [Expr1227]))
                      |              |--Stream Aggregate(DEFINE:([Expr1226]=COUNT_BIG([RevExp].[amount]), [Expr1227]=SUM([RevExp].[amount])))
                      |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1223], [Expr1224], LikeRangeInfo([@orgCode], NULL)))
                      |                        |--Compute Scalar(DEFINE:([Expr1223]=Convert(LikeRangeStart([@orgCode], NULL)), [Expr1224]=Convert(LikeRangeEnd([@orgCode], NULL)), [Expr1122]=LikeRangeInfo([@orgCode], NULL)))
                      |                        |    |--Constant Scan
                      |                        |--Index Seek(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9]), SEEK:([RevExp].[univ_fiscal_yr]=[@firstYear]+3 AND [RevExp].[dept_code]=[@deptCode] AND [RevExp].[catid]=[f].[ID] AND [RevExp].[org_cd] > [Expr1223] A
                      |--Compute Scalar(DEFINE:([Expr1042]=If ([Expr1228]=0) then NULL else [Expr1229]))
                           |--Stream Aggregate(GROUP BY:([RevExp].[catid]) DEFINE:([Expr1228]=COUNT_BIG([RevExp].[amount]), [Expr1229]=SUM([RevExp].[amount])))
                                |--Index Seek(OBJECT:([RevExp2].[dbo].[RevExp].[RevExp9]), SEEK:([RevExp].[univ_fiscal_yr]=[@firstYear]+4 AND [RevExp].[dept_code]=[@deptCode]),  WHERE:(like([RevExp].[org_cd], [@orgCode], NULL) AND like([RevExp].[data_sourc

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 16:12:01

remove all of those correllated subquries that return Year1-Year5 and add this to your FROM clause:


INNER JOIN
(
 select cat_id,
   case when univ_fiscal_yr = @firstYear then amount else 0 end as Year1,
   case when univ_fiscal_yr = @firstYear+1 then amount else 0 end as Year2,
   case when univ_fiscal_yr = @firstYear+2 then amount else 0 end as Year3,
   case when univ_fiscal_yr = @firstYear+3 then amount else 0 end as Year4,
   case when univ_fiscal_yr = @firstYear+4 then amount else 0 end as Year5
 from
   RevExp
 where
   dept_code = @deptCode AND 
   org_cd LIKE @orgCode AND 
   data_source LIKE @dataSource AND
   univ_fiscal_yr between @firstYear and @firstYear+4
 group by 
   cat_id
) YearTotals
ON
  F.ID = YearTotals.Cat_ID

and use that to return Year1-Year5. Then, remove this part of your WHERE Clause:

EXISTS (SELECT TOP 1 catid FROM RevExp g WHERE f.id = g.catid AND g.dept_code = @deptCode AND g.org_cd LIKE @orgCode)

because the inner join above will take care of that.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 16:25:42

also try changing your WHERE clause from:


	b.rptPageID = 
	CASE
		WHEN @rptPageID > 4 THEN b.rptPageID
		ELSE @rptPageID
	END AND
	f.RptID = 
	CASE
		WHEN @rptPageID > 4 THEN @rptPageID
		ELSE f.RptID
	END AND

to

((b.rptPageID = @rptPageID) or (@rptPageID > 4)) AND
((f.RptID = @rptPageID) or (@rptPAgeID <=4))

they should be logically equivalent; not sure if it'll help performance.

- Jeff

mdd1314
Starting Member

3 Posts

Posted - 2004-07-16 : 18:05:25

OK, I added the INNER JOIN to the FROM clause and removed the EXISTS statement from the WHERE clause. Now, depending on the criteria I send to the query, it takes 7 to 40+ seconds to run. However, it does calculate the correct totals in the "ROLLUP" rows. With my previous query, my asp.net page had to calculate the amounts in those rows.

But I still need to get the query to run in under 5 seconds or so. Removing the CASE statements from the WHERE clause didn't make a noticable difference. Here is what my query looks like now:


declare @deptCode varchar(5)
declare @orgCode varchar(5)
declare @firstYear int
declare @rptPageID int
declare @dataSource varchar(4)
set @deptcode = 'surg'
set @orgcode = 'surc'
set @firstyear = 2002
set @rptpageid = 2
set @dataSource = 'pp'

IF @orgCode = ''
BEGIN
	SET @orgCode = '%'
END

IF @dataSource = ''
BEGIN
	SET @dataSource = '%'
END

SELECT
	b.id AS GTotID,
	isnull(b.Name, '') AS GTotName,
	c.id AS SubID,
	isnull(c.Name, '') AS SubName,
	d.id AS GrpID,
	isnull(d.Code, '') AS GrpCode,
	isnull(d.Name, '') AS GrpName,
	f.id AS CatID,
	isnull(f.Category, '') AS CategoryName,
	f.lineNumber as LineNumber,
	sum(isnull(g.year1, 0)) AS Year1,
	sum(isnull(g.year2, 0)) AS Year2,
	sum(isnull(g.year3, 0)) AS Year3,
	sum(isnull(g.year4, 0)) AS Year4,
	sum(isnull(g.year5, 0)) AS Year5
FROM
	lu_GTotCodes b INNER JOIN
	lu_SubCodes c ON b.ID = c.GTotID INNER JOIN
	lu_GroupCodes d ON c.ID = d.SubID INNER JOIN
	lu_EntityCodes e ON d.ID = e.GrpID INNER JOIN
	lu_LineNumbers f ON e.ID = f.EntityID
	INNER JOIN
	 (select id,catid,
	   case when univ_fiscal_yr = @firstYear then amount else 0 end as Year1,
	   case when univ_fiscal_yr = @firstYear+1 then amount else 0 end as Year2,
	   case when univ_fiscal_yr = @firstYear+2 then amount else 0 end as Year3,
	   case when univ_fiscal_yr = @firstYear+3 then amount else 0 end as Year4,
	   case when univ_fiscal_yr = @firstYear+4 then amount else 0 end as Year5
	 from
	   RevExp
	 where
	   dept_code = @deptCode AND 
	   org_cd LIKE @orgCode AND 
	   data_source LIKE @dataSource AND
	   univ_fiscal_yr between @firstYear and @firstYear+4
	 group by 
	   id, catid, univ_fiscal_yr, amount) g ON F.ID = g.CatID
WHERE
((b.rptPageID = @rptPageID) or (@rptPageID > 4)) AND
((f.RptID = @rptPageID) or (@rptPAgeID <=4))
GROUP BY
	b.id, b.name, c.id, c.name, d.id, d.code, d.name, f.id, f.lineNumber, f.category WITH ROLLUP
HAVING
	grouping(f.category) = 0 OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=0) OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=1 AND grouping(d.code)=1 AND grouping(d.id)=1 AND grouping(c.name)=0) OR
	(grouping(f.category)=1 AND grouping(f.id)=1 AND grouping(d.name)=1 AND grouping(d.code)=1 AND grouping(d.id)=1 AND grouping(c.name)=1 AND grouping(c.id)=1 AND grouping(b.name)=0)

ORDER BY
	isnull(b.id, 999999), isnull(c.id, 999999), isnull(d.id, 999999), isnull(f.LineNumber, 999999)

Thanks for your help so far. Let me know if there is any other information I can provide that might be useful. It is still early in the development process, so I'm open to changing the DB structure, if necessary. I just found out that this RevExp table might have 50 million records in it when it gets into production, so anything that will speed up this query would be great.

Thanks,

-Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources