October 11, 2012 at 11:57 am
Hi I got a table with one column of lenght 500 and i got around 500,000 records.
So from that i'm looking for some record 'abc dfgr hjk kishg lZDjsdf dnuwhcn (88sdnjhoi'. It is taking lot of time to check for that value from all the records..Is there any function by which i can convert this into smaller value like so that it makes data retrival faster
Like
Select * from Table
Where function(column)=function('abc dfgr hjk kishg lZDjsdf dnuwhcn (88sdnjhoi').
I tried to find it out but couldn't. So can some one help me out in this please. Thank you
October 11, 2012 at 12:13 pm
Adding a scalar function to the where is not going to make anything faster, in fact it will most likely make it slower.
Do you have an index on this column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2012 at 12:21 pm
tripri (10/11/2012)
Hi I got a table with one column of lenght 500 and i got around 500,000 records.So from that i'm looking for some record 'abc dfgr hjk kishg lZDjsdf dnuwhcn (88sdnjhoi'. It is taking lot of time to check for that value from all the records..Is there any function by which i can convert this into smaller value like so that it makes data retrival faster
Like
Select * from Table
Where function(column)=function('abc dfgr hjk kishg lZDjsdf dnuwhcn (88sdnjhoi').
I tried to find it out but couldn't. So can some one help me out in this please. Thank you
Where function(column)=function() is what is known as non-sarg-able;
that will not allow you to use any indexing, and will require a table scan
if you add a column that calculates the checksum or binary checksum, and then an index on that checksum, then you could use a function to compare faster:
--ALTER TABLE gmact add desc_Check As CHECKSUM(ACTDESCRIP) PERSISTED
ALTER TABLE gmact add desc_Check As CHECKSUM(CONVERT(VARCHAR(max),ACTDESCRIP)) PERSISTED --if this is a TEXT datatype, you must convert to varchar(max)
CREATE INDEX IX_desc_Check ON GMACT(desc_Check)
SELECT * FROM GMACT WHERE desc_Check = CHECKSUM('abc dfgr hjk kishg lZDjsdf dnuwhcn (88sdnjhoi')
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply