Post reply

Function To convert large string to small values

  • October 11, 2012 at 11:57 am

    #269178

    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

    #1548300

    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

    #1548305

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 11, 2012 at 12:35 pm

    #1548315

    @sean

    HI,

    yes, i have an index on that column..

  • October 11, 2012 at 12:35 pm

    #1548316

    @lowell,

    Thank you

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

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