How to improve search with '%'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to improve search with '%'

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-22 : 11:57:21

A program create claimno as the following way:
date + operator + sequence
For example, for operator = 523 in today's claimno as:
200910225230001, 200910225230002, 200910225230003, ...
I used select statement
"select * from claim where claimno like '20091022523%'"
only take one second.
I used code below will take more than 10 minutes.
"select * from claim where claimno like ((select (convert(varchar(20), getdate(), 112) + @operator + '%')))"
How to improve it?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 12:40:50

is claimno indexed? you might to look into dynamic sql sp_executesql also, I use that all the time. but first things first do you have an index on claimno.
what is that subselect there , you don't need that do the following as a suggestion

DECLARE @claimno nvarchar(50)
DECLARE @sql nvarchar(4000)
DECLARE @paramlist nvarchar(4000)

Select @claimno = convert(varchar(20), getdate(), 112) + @operator + '%'''
SELECT @sql = 'select * from claim where claimno like '''
PRINT @sql

SELECT @paramlist = '@claimno nvarchar(50)'
EXEC sp_executesql @sql, @paramlist

if you have any problems post back the PRINT statment
Try it out , it has helped me

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-22 : 13:38:25

That is strange.. Could it be parameter sniffing? Maybe try putting the entire string in a variable and then just the use the variable? Also, there is no need for the SELECT in the LIKE statement (unless you example is a simplified version of the real thing).

EDIT: Changed my mind. :)

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-22 : 14:18:13

yosiasz, I ran you script but got error.
Error is at ''

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 14:22:49

do this for @sql
SELECT @sql = 'select * from claim where claimno like ''' + @claimno

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 14:26:28

Select @claimno = convert(varchar(20), getdate(), 112) + @operator + '%'''
SELECT @sql = 'select * from claim where claimno like ''' + @claimno
PRINT @sql

SELECT @paramlist = '@claimno nvarchar(50)'
EXEC sp_executesql @sql, @paramlist , @claimno

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-22 : 14:37:14

message:

select * from CLAIM where claimno like '20091022523%'
Msg 8178, Level 16, State 5, Line 0
Prepared statement '(@claimno nvarchar(50))select * from CLAIM where clai' expects parameter @claimno, which was not supplied.

Where is clai coming from?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 14:41:34

please see my previous post

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-22 : 14:42:06

You either put the value of @ClaimNo in the string or you pass it into the stored procedure, but not both.. :)

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-10-22 : 15:03:48

It is working now. Thank you.
I forgot to copy @claimno in
EXEC sp_executesql @sql, @paramlist , @claimno

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 15:06:31

great! ok it works but is it fast and do you have an index on claimno ?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mfemenel
Professor Frink

1421 Posts

Posted - 2009-10-22 : 15:31:43

Given the length of the value you're searching for you may want to conside indexing a computed column with a checksum on the date + operator values. This may perform better than indexing directly on column date + operator + sequence. Lenghty checks against long strings with % frequently don't perform very well. As with everything...test and compare.
http://msdn.microsoft.com/en-us/library/ms189788.aspx

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources