Poor Performance - Nested Views & Complex Joins

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

SQL Server Development (2000)

Poor Performance - Nested Views & Complex Joins

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-27 : 22:29:17

The code below is from a nested view, which I've read is bad. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 22:59:35

You should make this change, because otherwise the DATEDIFF function must be executed against every row in YTD05B and it prevents using any index on Process_Date.


where (DATEDIFF(mm, Process_Date, GETDATE())  <= 15)
to
where Process_Date >= dateadd(mm,-15,GETDATE())

If YTD05B and YTD06B don't have duplicate rows, change the UNION to UNION ALL to eliminate the grouping operation.

The following indicates a problem with the table design if it is necessary to extract part of a column for a join.

ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)

I don't see the point of the following:

null as Shares,
null as Closing_Price,
null as Mktval, 
cast(Null as varchar(20)) as Cusip_#,

You should change these to only return the columns that are actually needed.

SELECT * FROM

CODO ERGO SUM

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-28 : 10:15:37

Thanks. This is exactly what I am looking for. I'm also going to suggest they set a variable to getdate() and reference the variable in the query so getdate does not get invoked for every record retrieved in the query.

What about joining to a SELECT statement? Is this an indication of poor design and how bad would that impact performance?

Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 11:04:07

quote:
Originally posted by DBADave
...I'm also going to suggest they set a variable to getdate() and reference the variable in the query so getdate does not get invoked for every record retrieved in the query...

There is no need for that. SQL Server is smart enough to know it only has to evaluate GETDATE() once.

CODO ERGO SUM

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-28 : 11:14:08

I think my logic about GetDate() is confused with user-defined functions, which use row-by-row processing instead of working as a set-based operation.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources