how to avoid sub queries and FUNCTIONS in where clause ?

Question

how to avoid sub queries and FUNCTIONS in where clause ?

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
January 22, 2014 at 2:41 am

#287538

HI ,
the below query taking 5 minutes time, i want to rewrite this query ?
query details :
select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore
from pdDataMarketSignal_vw pd (nolock)
inner join address_vw a
on pd.companyId = a.objectId
and a.usercompanyId=0
and a.primaryFlag = 1
where asOfDate > dateadd(yy, -5, GETDATE())
and PDDataItemId = 11
and PointInTimeEndDate is null
and PDModelId in (3050)
and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)
from the above query i want to avoid the following things and re wr
1. avoid sub query in the where clause and modify the query
and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)
2. avoid FUNCTION in where clause
where asOfDate > dateadd(yy, -5, GETDATE())
3.avoid this function in select statment
Convert(numeric(38,10),pd.PDValue)
please help me to rewrite this query to with out effecting the business logic.
Thanks
Bhanu

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Have you confirmed that the subquery, function on a constant and conversion in the select are the causes of the poor performance?

If you haven't, then making the changes you request may well be a waste of time.

Edit: and are your users aware that they may be getting incorrect results due to that nolock hint?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

kbhanu15 (1/22/2014)
HI ,
the below query taking 5 minutes time, i want to rewrite this query ?
query details :
select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore
from pdDataMarketSignal_vw pd (nolock)
inner join address_vw a
on pd.companyId = a.objectId
and a.usercompanyId=0
and a.primaryFlag = 1
where asOfDate > dateadd(yy, -5, GETDATE())
and PDDataItemId = 11
and PointInTimeEndDate is null
and PDModelId in (3050)
and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)
from the above query i want to avoid the following things and re wr
1. avoid sub query in the where clause and modify the query
and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)
2. avoid FUNCTION in where clause
where asOfDate > dateadd(yy, -5, GETDATE())
3.avoid this function in select statment
Convert(numeric(38,10),pd.PDValue)
please help me to rewrite this query to with out effecting the business logic.
Thanks
Bhanu

Have you looked at the execution plan? Can you post the actual execution plan as a .sqlplan file attachment please.

Subqueries in the WHERE clause may enhance performance. They're not necessarily a bad thing.

There's nothing seriously wrong with the function either. Problems arise when there's a function on a column, which (almost) always prevents sql server from using an index on the column.

The function in the SELECT is unlikely to have a measurable effect on performance.

You're joining views to views. This is highly likely to lead to performance issues because you forget - or don't know - how the views are constructed, leading to a) queries which are overcomplicated for the purpose and b)queries of sufficient complexity that the optimiser borks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 3

have you tried this?

DECLARE @5YearsAgo SMALLDATETIME = dateadd(yy, -5, GETDATE())

DECLARE @SetVal INT = (select settingvalue from ApplicationSettings_tbl where appsettingid = 1136)

select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore

from pdDataMarketSignal_vw pd (nolock)

inner join address_vw a

on pd.companyId = a.objectId

and a.usercompanyId=0

and a.primaryFlag = 1

where asOfDate > @5YearsAgo

and PDDataItemId = 11

and PointInTimeEndDate is null

and PDModelId in (3050)

and (@SetVal = 1)

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 4

HI Thanks for your reply but the given query is present in a view

i think it wont work in views?

view details:

create view Screen_RatingsData_ProbabilityOfDefault_vw

as

select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore

from pdDataMarketSignal_vw pd (nolock)

inner join address_vw a

on pd.companyId = a.objectId

and a.usercompanyId=0

and a.primaryFlag = 1

where asOfDate > dateadd(yy, -5, GETDATE())

and PDDataItemId = 11

and PointInTimeEndDate is null

and PDModelId in (3050)

and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

kbhanu15 (1/22/2014)
HI Thanks for your reply but the given query is present in a view
i think it wont work in views?
view details:
create view Screen_RatingsData_ProbabilityOfDefault_vw
as
select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore
from pdDataMarketSignal_vw pd (nolock)
inner join address_vw a
on pd.companyId = a.objectId
and a.usercompanyId=0
and a.primaryFlag = 1
where asOfDate > dateadd(yy, -5, GETDATE())
and PDDataItemId = 11
and PointInTimeEndDate is null
and PDModelId in (3050)
and ((select settingvalue from ApplicationSettings_tbl where appsettingid = 1136) = 1)

ChrisM@Work (1/22/2014)

...
You're joining views to views. This is highly likely to lead to performance issues because you forget - or don't know - how the views are constructed, leading to a) queries which are overcomplicated for the purpose and b)queries of sufficient complexity that the optimiser borks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 6

Your first stop must be the execution plan. You can guess at causes to performance issues, but without the execution plan, they're just guesses. And I totally agree, joining view to view and/or nesting views within views is a sure way to poor performance. You're overwhelming the optimizer as it attempts to simplify your query down to just the tables and columns it actually needs to satisfy your query. Check the execution plan, first operator (presumable a SELECT in this case) for the property 'Reason for early termination' and look for the value 'Timeout'. That will be the indicator that you're nesting of views is killing performance.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning