Post reply

can anyone help in rebuildin this query..??

  • March 20, 2007 at 5:38 am

    #175657

    Hi all,

    I hav this query which is not using index..

    select * from contactEnquiries where Enquiry_id=@id or @id=''

    Here if the @id value is passed '' it will show all the rows..

    but its not using index even if i set @id value other than ''

    can anyone help me rebuild this query to get better performance..??

    Thanks in advance...

  • March 20, 2007 at 6:21 am

    #695888

    if the contactEnquiries table is small, the optimizer will always do a table scan regardless, and ignore the index;That means if the table is less than 8MB in size, it will always table scan... I seem to remember hearing a rule of thumb  that can translate to between 100 and 400 records or so, depending on how many columns the table has, influences whether an index is used or not.

    also, is this part of a stored procedure? remember a stored proc stores it's query plan with itself, so if it thinks tha the @id will result in all records when it was compiled, that's the plan it will use, regardless of the value actually passed.

    the OR statement is what needs to be changed. either get the calling procedure to decide BEFORE you call SQL server whether it needs all records or just one, and use two separate statements...

    select * from contactEnquiries where Enquiry_id=@id

    select * from contactEnquiries

    don't try to do both queries in the same procedure...you should create 2 procedures, and call either or based on logic at the application level,  or call a master procedure, which then calls one of the two sub procedures.

     

    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!

  • March 20, 2007 at 6:25 am

    #695891

    Thank u so much...

  • March 20, 2007 at 6:25 am

    #695890

    In this topic you tell use there is a present index for the 100,000 records returned?

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=352569

     

    N 56°04'39.16"
    E 12°55'05.25"

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

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