Code Optimization - 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)

Code Optimization

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-03 : 06:54:05

Morning Good people,

I am having performance issue with following code. Kindly advice how I can optimize the code



;with MyCTE (acid ,MAIN_CLASSIFICATION_USER , SUB_CLASSIFICATION_USER , USER_CLASSIFICATION_DATE,RowVersion)
AS(
select
  acid
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
,ROW_NUMBER() OVER(PARTITION BY acid ORDER BY USER_CLASSIFICATION_DATE desc) RowVersion
from
(
select -- All loans + Classification
  acid
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
DBO.dwv_allclassification
except
select
 acid
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
(
select
ROW_NUMBER() OVER(PARTITION BY acid ORDER BY USER_CLASSIFICATION_DATE desc) RowVersion
, acid
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
DBO.dwv_allclassification
)t
where
RowVersion = 1
and MAIN_CLASSIFICATION_USER = '001'
)t
)
,
MyMiniCTE(acid,MAIN_CLASSIFICATION_USER,SUB_CLASSIFICATION_USER,USER_CLASSIFICATION_DATE,map)
as
(
select
 b.acid
,b.MAIN_CLASSIFICATION_USER
,b.SUB_CLASSIFICATION_USER
,b.USER_CLASSIFICATION_DATE
,case when (b.SUB_CLASSIFICATION_USER > l.SUB_CLASSIFICATION_USER and l.MAIN_CLASSIFICATION_USER = '001')  then 1 else 0 end map
from MyCTE b
LEFT JOIN MyCTE L ON b.acID = L.acID
AND B.RowVersion = L.RowVersion-1

)
,
myctemaped(acid,MAIN_CLASSIFICATION_USER,SUB_CLASSIFICATION_USER,USER_CLASSIFICATION_DATE
,RowVersion)
as
(
select
acid
,MAIN_CLASSIFICATION_USER
,SUB_CLASSIFICATION_USER
,USER_CLASSIFICATION_DATE
,ROW_NUMBER() OVER(PARTITION BY acid ORDER BY USER_CLASSIFICATION_DATE asc) RowVersion
from MyMiniCTE
where map = 1
)
,
FinalCTE(acid,MAIN_CLASSIFICATION_USER,SUB_CLASSIFICATION_USER,USER_CLASSIFICATION_DATE,USER_CLASSIFICATION_END_DATE)
as
(
select
b.acid
,b.MAIN_CLASSIFICATION_USER
,b.SUB_CLASSIFICATION_USER
,b.USER_CLASSIFICATION_DATE
,l.USER_CLASSIFICATION_DATE USER_CLASSIFICATION_END_DATE
from myctemaped b
LEFT JOIN myctemaped L ON b.acID = L.acID
AND B.RowVersion = L.RowVersion-1
)
,
FullCTE
as
(
select
b.acid
,b.MAIN_CLASSIFICATION_USER
,b.SUB_CLASSIFICATION_USER
,b.USER_CLASSIFICATION_DATE
,USER_CLASSIFICATION_END_DATE = (select max(USER_CLASSIFICATION_DATE) from MyMiniCTE y where map=0 and b.acid=y.acid and y.USER_CLASSIFICATION_DATE between b.USER_CLASSIFICATION_DATE and b.USER_CLASSIFICATION_END_DATE  )
from 
FinalCTE b
)

select * from FullCTE

Pls Note:

1. The main view DBO.dwv_allclassification contains more than one million records.

2. It takes an hour plus for the script to finish executing.

Many Thanks

I sign for fame not for shame but all the same, I sign my name.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 07:13:16

As I can see "dwv_allclassification" is the only "real" table in your select statement.
If you could provide table structure, example data and wanted output thenmaybe it is easier for us to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-03 : 08:06:26

Many thanks webfred,

DBO.dwv_allclassification is actually a view
as d structure is

acid varchar(11)
MAIN_CLASSIFICATION_USER varchar(5)
SUB_CLASSIFICATION_USER varchar(5)
USER_CLASSIFICATION_DATE datetime

and sample data


acid	MAIN_CLASSIFICATION_USER	SUB_CLASSIFICATION_USER	USER_CLASSIFICATION_DATE
LG1080724	002	002	2010-02-25 00:00:00.000
LG1080724	002	002	2010-01-29 00:00:00.000
YE1141396	002	004	2010-01-08 00:00:00.000
LG1080724	001	001	2009-04-30 00:00:00.000
LG1080724	002	003	2008-12-31 00:00:00.000
LG1080724	002	002	2008-08-29 00:00:00.000
LG1080724	001	001	2008-07-31 00:00:00.000
LG1080724	002	002	2008-06-30 00:00:00.000
LG1080724	001	001	2008-01-04 00:00:00.000
YE1141396	001	001	2007-02-01 00:00:00.000

expect output

acid MAIN_CLASSIFICATION_USER SUB_CLASSIFICATION_USER USER_CLASSIFICATION_DATE USER_CLASSIFICATION_END_DATE
LG1080724 002 002 2008-06-30 00:00:00.000 2008-07-31 00:00:00.000

LG1080724 002 002 2008-08-29 00:00:00.000 2008-12-31 00:00:00.000

LG1080724 002 002 2010-01-29 00:00:00.000 null

YE1141396 002 004 2010-01-08 00:00:00.000 null

Many Thanks

I sign for fame not for shame but all the same, I sign my name.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 10:53:52

quote:

DBO.dwv_allclassification is actually a view

And the code for the view is.......

If that view is horrible then you'll want to start there.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-03 : 11:38:25

Thanks Webfred

Lol, and the code for the view is




ALTER view 
 [dbo].[dwv_allclassification]
as
WITH MyCTE (acid ,MAIN_CLASSIFICATION_USER , SUB_CLASSIFICATION_USER , USER_CLASSIFICATION_DATE)
AS
(
select

 B2K_ID acid
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
(
select
  B2K_ID
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
dbo.SRC_ACH
union all
select 
  B2K_ID
, MAIN_CLASSIFICATION_USER
, SUB_CLASSIFICATION_USER
, USER_CLASSIFICATION_DATE
from
dbo.SRC_ACD
where
  entity_cre_flg='Y'
AND del_flg='N'
)t 
)


select 
distinct
 b.acid 
, b.MAIN_CLASSIFICATION_USER 
, b.SUB_CLASSIFICATION_USER 
, b.USER_CLASSIFICATION_DATE 
from MyCTE b

dbo.SRC_ACH - > History table (count => 1357647 )
dbo.SRC_ACD - > Current Table (count => 1331155 )

Script runs once in a month, and populates the dbo.SRC_ACH and truncate and insert fresh status in dbo.SRC_ACD.

Many thanks

I sign for fame not for shame but all the same, I sign my name.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 11:55:20

OK -- try running the view code standalone


; WITH MyCTE (
	acid
	, MAIN_CLASSIFICATION_USER
	, SUB_CLASSIFICATION_USER
	, USER_CLASSIFICATION_DATE
	)
AS (
	select
		B2K_ID acid
		, MAIN_CLASSIFICATION_USER
		, SUB_CLASSIFICATION_USER
		, USER_CLASSIFICATION_DATE
	from
		(
		select
			  B2K_ID
			, MAIN_CLASSIFICATION_USER
			, SUB_CLASSIFICATION_USER
			, USER_CLASSIFICATION_DATE
		from
			dbo.SRC_ACH

		union all select 
			  B2K_ID
			, MAIN_CLASSIFICATION_USER
			, SUB_CLASSIFICATION_USER
			, USER_CLASSIFICATION_DATE
		from
			dbo.SRC_ACD
		where
			entity_cre_flg='Y'
			AND del_flg='N'
		)
		t 
)
select distinct
	 b.acid 
	, b.MAIN_CLASSIFICATION_USER 
	, b.SUB_CLASSIFICATION_USER 
	, b.USER_CLASSIFICATION_DATE 
from
	MyCTE b

And take a look at the actual execution plan (cntrl + M) in management studio

Do you get good index usage? Any table scans or key lookups?

Also do you actually need the DISTINCT? generally that's a good indication that the SELECT statement isn't specific enough. (you are having to discard rows that you shouldn't have brought back in the first place).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-03-03 : 12:15:27

Yes!! I had good index usage. even the whole query executed for 02.28 mins. This indicate the problem is in my first code.

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources