Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

charindex or like condition Expand / Collapse
Author
Message
Posted Friday, January 22, 2010 3:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #851888
Posted Friday, January 22, 2010 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #851955
Posted Friday, January 22, 2010 7:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #852032
Posted Friday, January 22, 2010 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #852053
Posted Friday, January 22, 2010 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 10,191, Visits: 13,115
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #852059
Posted Friday, January 22, 2010 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #852069
Posted Saturday, January 23, 2010 2:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #852500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse