Querying table with varchar datatype column

Question

Querying table with varchar datatype column

sacheen.nemade

Newbie

Points: 2
More actions
April 17, 2009 at 1:24 am

#202314

Dear All,
I am having agentrankandrecruitment table with chain column with varchar(2000) size.
I am filtering agentid from this table and for each agentid I gets need to find count from same table so i make inner join to same table.
Filtered agents are around 15000 and for each agent i make further inner join to find count.
But it is very time consuming as it is using like statement on varchar column.
select agentid,
'SR' = (select count(1) from agentrankandrecruitment arr2
where
arr2.asondate = '30/11/2008'
and arr2.rankid = 40
and arr2.chain like '%-' + convert(varchar(50),arr1.agentid) + '.%' and arr2.branchid = 2
)
from agentrankandrecruitment arr1
where
arr1.asondate = '30/11/2008'
and arr1.chain like '%-' + convert(varchar(50),150000) + '.%' and arr1.branchid = 2
can u suggest ways to optimize table and query.
Index can not be built on column bcaz of size.

Viewing 4 posts - 1 through 4 (of 4 total)

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

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

Please can you provide a script for table arr2, and some sample data. The link below will show you how to provide sample data in a format which is convenient.

Is there any structure to the chain column? You use - and . with your LIKE operator.

Cheers

ChrisM

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

sacheen.nemade Newbie Points: 2 More actions · Answer 2

Hi

Agentid - Integer

Chain - Varchar(2000)

Chain , it contains all sub-agents(Agentid) list seprated by '-'

For eg

agentid 100

chain would be 100-101-102

Chain gets longer as new subagents are added.

Thanks and regards,

sachin N

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

sacheen.nemade (4/17/2009)
Hi
Agentid - Integer
Chain - Varchar(2000)
Chain , it contains all sub-agents(Agentid) list seprated by '-'
For eg
agentid 100
chain would be 100-101-102
Chain gets longer as new subagents are added.
Thanks and regards,
sachin N

Please can you provide a script for table agentrankandrecruitment including any indexes, and some sample data in a readily-consumable format.

Are you sure subagents are separated only by '-'?

'%-' + convert(varchar(50),arr1.agentid) + '.%'

Cheers

ChrisM

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