charindex or like condition

Question

Post reply

charindex or like condition

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
January 22, 2010 at 3:59 am

#137599

declare @date1 datetime,@date2 datetime;
set @date1='01/21/2010';
set @date2='01/22/2010';
select distinct count(*) as Form3and4,br.br_districtid from
[192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br
INNER JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr
ON bvr.vr_beneficiaryid=br.br_beneficiaryId
where
charindex( 'F3',vr_formsfilled)>0 OR charindex( 'F4',vr_formsfilled)>0
and convert(varchar,vr_visitDate,101) between @date1 and @date2
group by br_districtid
here if i replace charindex with like can you tell whether my query will correct or not.and also iam
executing the query using linked server
Malleswarareddy
I.T.Analyst
MCITP(70-451)

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

declare @date1 datetime, @date2 datetime;

set @date1 = '01/21/2010';

set @date2 = '01/22/2010';

SELECT

DISTINCT count(*) AS Form3and4,

br.br_districtid

FROM [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br

INNER JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr

ON bvr.vr_beneficiaryid = br.br_beneficiaryId

WHERE charindex('F3', vr_formsfilled) > 0

OR charindex('F4', vr_formsfilled) > 0

AND convert(varchar, vr_visitDate, 101) BETWEEN @date1 AND @date2

GROUP BY br.br_districtid

-- here if i replace charindex with like can you tell whether my query will correct or not.and also iam

-- executing the query using linked server

-- No. Correct the mistakes in your query first.

-- SELECT DISTINCT count(*) is meaningless in this context, it won't do anything.

-- There is an OR and an AND in your WHERE clause. If this query returns the rows you want, it's by accident, not by design.

-- You probably want your WHERE clause to look like this:

WHERE (charindex('F3', vr_formsfilled) > 0

OR charindex('F4', vr_formsfilled) > 0)

AND convert(varchar, vr_visitDate, 101) BETWEEN @date1 AND @date2

-- Why are you converting vr_visitDate, which appears to be a date column, into a varchar

-- before comparing it to your start and end dates, which are datetime? Why not manipulate

-- @date1 AND @date2 so that they can be compared directly to vr_visitDate?

-- Questions:

-- Where in the column vr_formsfilled are the strings F3 and F4 likely to be located?

-- Right at the beginning? Right at the end? Somewhere in the middle?

^{“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

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

malleswarareddy_m (1/22/2010)
charindex( 'F3',vr_formsfilled)>0 OR charindex( 'F4',vr_formsfilled)>0
and convert(varchar,vr_visitDate,101) between @date1 and @date2

That is never going to perform well. i do hope fast response time is not a requirement here.

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

Hi chri,

i checked my query and executedb in read only server 192.168.3.201.

it will executed with in 90 secs in liked server.but in reporting server it executing only 5-15 secs.

The query is shown below.It will executed the same result but will executed faster than char index.

declare @date1 datetime,@date2 datetime;

set @date1='01/20/2010';

set @date2='01/20/2010';

with F3F4count(F3Count,Districtid) as

(

select (select distinct count(distinct br_beneficiaryid) as F3Count from

[192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr

INNER JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br

ON bvr.vr_beneficiaryid=br.br_beneficiaryId where vr_formsfilled like '%f4%'

and convert(varchar,vr_visitDate,101) between @date1 and @date2 and

br.br_DistrictId = districtId ),Districtid

from [192.168.3.201].MASTERDATA.HIHLMain.District

group by districtid

union

select (select distinct count(distinct br_beneficiaryid) as F3Count from

[192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr

inner JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br

ON bvr.vr_beneficiaryid=br.br_beneficiaryId where vr_formsfilled like '%f3%'

and convert(varchar,vr_visitDate,101) between @date1 and @date2 and

br.br_DistrictId = districtId ),Districtid

from [192.168.3.201].MASTERDATA.HIHLMain.District

group by districtid

)

select Districtid as District,sum(F3count) as [F3 F4 Count] from F3F4count

group by districtid

like the same query the second query is also taking 10 mins in linked server where the same query executed in reporting server is 10 to 20 secs only.

can you increase this query performance.Appreciated.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

It looks like, based on 4-part naming, that you are running this across a linked server. You may get some better performance by using OPENQUERY/OPENROWSET or creating and SP on the linked server. This offloads all the processing to the linked server.

You can improve performance by removing the conversion of the date column to a varchar as it not only causes at best an index scan but it also then does an implicit convert right back to datetime. For example if you run this code:

CREATE TABLE #test (date_col DATETIME PRIMARY KEY)

DECLARE @date1 DATETIME,

@date2 DATETIME

SELECT

@date1 = DATEADD(DAY, -100, GETDATE()),

@date2 = DATEADD(DAY, -60, GETDATE())

INSERT INTO #test

(

date_col

)

SELECT TOP 365

DATEADD(DAY, -(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), GETDATE())

FROM

sys.all_columns AS AC

SELECT

*

FROM

#test

WHERE

CONVERT(varchar, date_col, 101) BETWEEN @date1 AND @date2

SELECT

*

FROM

#test

WHERE

date_col BETWEEN @date1 AND @date2

DROP TABLE #test

If you look at the execution plans for the 2 selects from #test you see that the query with the conversion does a clustered index scan and has a predicate of

CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[tempdb].[dbo].[#test].[date_col],101),0)>=[@date1] AND

CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[tempdb].[dbo].[#test].[date_col],101),0)<=[@date2]

while the query without the conversion does a clustered index seek and the predicate is:

Start: [tempdb].[dbo].[#test].date_col >= Scalar Operator([@date1]),

End: [tempdb].[dbo].[#test].date_col <= Scalar Operator([@date2])

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

This is worse than the original query and the suggestions which have been made have not been implemented.

Are you able to create a view or stored procedure on the server 192.168.3.201?

Edit: derogatory comment references the OP's second posted query, not Jack's excellent suggestions.

^{“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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 6

yaa,

what you told is correct. i permissions and iam able to call RPC. which will executed as same time.

and also removed the date conversion. thanks alot

Malleswarareddy
I.T.Analyst
MCITP(70-451)

_watching Mr or Mrs. 500 Points: 530 More actions · Answer 7

If anyone was interested in speed comparisons, it seems CHARINDEX is a lot faster than LIKE and others. The benchmarks can be found here.

It's quite interesting because charindex seems to have wiped out everything else.

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

_watching (10/7/2015)
If anyone was interested in speed comparisons, it seems CHARINDEX is a lot faster than LIKE and others. The benchmarks can be found here.
It's quite interesting because charindex seems to have wiped out everything else.

It's quite a comprehensive test. A note about SARGable LIKE queries within the article would improve it - or a link to the same statement elsewhere in the blog. If you're Dave Lozinski, then your blog post suggestion that temp tables always get written to disk (and table variables don't) requires an update[/url].

^{“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