CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

Improving performance on an alphanumeric text search query

I have table where millions of records are there I'm just posting sample data. Actually I'm looking to get only Endorsement data by using LIKE or LEFT but there is no difference between them in Execution time. IS there any fine way to get data in less time while dealing with Alphanumeric Data. I have 4.4M records in table. Suggest me 

declare @t table (val varchar(50))

insert into @t(val)values 
('0-1AB11BC11yerw123Endorsement'),
('0-1AB114578Endorsement'),
('0-1BC11BC11yerw122553Endorsement'),
('0-1AB11BC11yerw123newBusiness'),
('0-1AB114578newBusiness'),
('0-1BC11BC11yerw122553newBusiness'),
('0-1AB11BC11yerw123Renewal'),
('0-1AB114578Renewal'),
('0-1BC11BC11yerw122553Renewal')



SELECT   * FROM @t  where RIGHT(val,11) = 'Endorsement'

 SELECT  * FROM @t  where val like  '%Endorsement%'

Upvotes: 1

Views: 183

Answers (3)

N.Dinesh.Reddy
N.Dinesh.Reddy

Reputation: 632

CREATE  TABLE tbl 
(val varchar(50));

insert into tbl(val)values 
('0-1AB11BC11yerw123Endorsement'),
('0-1AB114578Endorsement'),
('0-1BC11BC11yerw122553Endorsement'),
('0-1AB11BC11yerw123newBusiness'),
('0-1AB114578newBusiness'),
('0-1BC11BC11yerw122553newBusiness'),
('0-1AB11BC11yerw123Renewal'),
('0-1AB114578Renewal'),
('0-1BC11BC11yerw122553Renewal');


CREATE CLUSTERED INDEX inx
ON dbo.tbl(val) 


 SELECT  * FROM tbl  where val like  '%Endorsement';
--LIKE '%Endorsement' will give better performance it will utilize the index well efficiently than RIGHT(val,ll)

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Imagine you'd have to find names in a telephone book that end with a certain string. All you could do is read every single name and compare. It doesn't help you at all to see where the names with A, B, C, etc. start, because you are not interested in the initial characters of the names but only in the last characters instead. Well, the only thing you could do to speed this up is ask some friends to help you and each person scans a range of pages only. In a DBMS it is the same. The DBMS performs a full table scan and does this parallelized if possible.

If however you had a telephone book listing the words backwards, so you'd see which words end with A, B, C, etc., that sure would help. In SQL Server: Create a computed column on the reverse string:

alter table t add reverse_val as reverse(val);

And add an index:

create index idx_reverse_val on t(reverse_val);

Then query the string with LIKE. The DBMS should notice that it can use the index for speeding up the search process.

select * from t where reverse_val like reverse('Endorsement') + '%';

Having said this, it seems strange that you are interested in the end of your strings at all. In a good database you store atomic information, e.g. you would not store a person's name and birthdate in the same column ('John Miller 12.12.2000'), but in separate columns instead. Sure, it does happen that you store names and want to look for names starting with, ending with, containing substrings, but this is a rare thing after all. Check your column and think about whether its content should be separate columns instead. If you had the string ('Endorsement', 'Renewal', etc.) in a separate column, this would really speed up the lookup, because all you'd have to do is ask where val = 'Endorsement' and with an index on that column this is a super-simple task for the DBMS.

Upvotes: 3

t1t1an0
t1t1an0

Reputation: 281

try charindex or patindex:

SELECT *
FROM @t t
WHERE CHARINDEX('endorsement', t.val) > 0


SELECT *
FROM @t t
WHERE PATINDEX('%endorsement%', t.val) > 0

Upvotes: 1

Related Questions