charindex or like condition

  • 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)

  • 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

  • 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
  • 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)

  • 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])

  • 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

  • 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)

  • 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.

  • _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

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

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