How to avoid convert function in the where clause???

Question

How to avoid convert function in the where clause???

chandan_jha18

SSChampion

Points: 11330
More actions
June 17, 2011 at 2:06 am

#239443

I am trying to fix the performance of an application. they had several queries where they used functions in the where clause forcing an index scan. I could fix them but there is one I am not able to.
declare @p datetime
set @p=DATEADD(dd, -45, getdate())
select a.ProcessDate, a.BatchNo, b.UserString6
from [jbtms].DetailFile1 a with (nolock) Inner join
[jbtms].DetailFile2 b with (nolock)
on a.detailkey = b.detailkey
and a.dayofdate = b.dayofdate
where b.UserString6 is not null and LTRIM(RTRIM(b.UserString6)) <> ''
and convert(datetime, a.ProcessDate) >@p
The convert function in the last statement is causing the index scan of 2 million+ rows. Can someone please show me some light here.
Thanks
Chandan
June 17, 2011 at 6:52 am

This was removed by the editor as SPAM
June 17, 2011 at 7:29 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 29 total)

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

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

What's the data type of a.ProcessDate?

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

GilaMonster (6/17/2011)
What's the data type of a.ProcessDate?

its a char. i attached the ddl for 2 tables for reference.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

GilaMonster (6/17/2011)
What's the data type of a.ProcessDate?

Here is some sample data from DetailFile1 table for which table alias a is used:

ProcessDateBatchNo

201102050000200000

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Your best bet is to store dates as datetime (or just date) in your database. If you don't have any control over the structure of the database, you can change @p to varchar, and just compare that against the date. This will work because your dates are stored as yyyymmdd.

John

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

John Mitchell-245523 (6/17/2011)
Your best bet is to store dates as datetime (or just date) in your database. If you don't have any control over the structure of the database, you can change @p to varchar, and just compare that against the date. This will work because your dates are stored as yyyymmdd.
John

I tried this too and fortunately both operations became seek but still the execution time is 15-20 seconds which is the original one. We are fetching 200k records here.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 6

GilaMonster (6/17/2011)
What's the data type of a.ProcessDate?

Do you think i can do something else here?

chandan_jha18 SSChampion Points: 11330 More actions · Answer 7

I have tried to modify few things. I tried to minimize the use of function in where clause and also made sure that the processeddate column which is defined as char in the table schema, is compared with char values only.

declare @p char(8);

set @p= convert(char(8),dateadd(dd, -45, getdate()),112)

select a.ProcessDate, a.BatchNo, b.UserString6from [jbtms].DetailFile1 a Inner join

[jbtms].DetailFile2 b

on a.detailkey = b.detailkey

and a.dayofdate = b.dayofdate

where b.UserString6 is not null

and LTRIM(RTRIM(b.UserString6)) <> ''

and a.ProcessDate >@p

option(maxdop 1)

I am attaching the new execution plan. It shows both of them as seek operations but still takes 17-20 seconds to show 183427 rows. Can anyone tell me what can be improved further? any ideas to remove the LTrim function from the where clause. I think this is also making things slow. Please advice.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 8

stewartc-708166 (6/17/2011)
the UserString6 column is defined as VARCHAR(200)
have you triedand b.UserString6 > ''

b.userstring6 is being used twice:

where b.UserString6 is not null

and LTRIM(RTRIM(b.UserString6)) <> ''

which place exactly you are suggesting the change, please clarify

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

He's suggesting replacing both predicates you have with the one he suggested. They are synonymous.

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 10

GilaMonster (6/17/2011)
He's suggesting replacing both predicates you have with the one he suggested. They are synonymous.

ok...i used the following in the where clause:

where b.UserString6 > ''

and a.ProcessDate >@p

but no gain yet. Both tables show seek operations with hash join taking 75 percent of work units in execution plan.

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

Post new exec plans

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 12

Modified query and execution plan:

declare @p char(8);

set @p= convert(char(8),dateadd(dd, -45, getdate()),112)

select a.ProcessDate, a.BatchNo, b.UserString6 from [jbtms].DetailFile1 a Inner join

[jbtms].DetailFile2 b

on a.detailkey = b.detailkey

and a.dayofdate = b.dayofdate

where b.UserString6 is not null

and

b.UserString6 > ''

and a.ProcessDate >@p

option(maxdop 1)