how to avoid use of index in query???

  •  

    Dear All,

    One of my query takes lots of time to execute. I rebuild the index using DBCC_REINDEX command but it takes 1 Minute less to execute. I used index tunning wizard but it shows 0% implement. When I dropped the Indexes on table and execute the query then it takes only 30seconds to execute. So I want to avoid use of index in that query. Is any syntax to avoid or skip indexes.

    Please some one guide me.

    Thanks in advance,

    Prathamesh.

  • post the query; most likely we can help tune it ; most likely the WHERE clause can be tuned to use SARG-able arguments, which will then use the proper indexes in your query...as soon as an argument is not SARG-able, it probably uses table scans, which slow down your query, and when in actual use, the query plan used when you test is not valid with the other parameters which get passed in during later use.

    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!

  • Hello,

     

    What Lowell said.  

    For now, and to directly answer the question, you can use the hint "WITH (INDEX =0)" like so:

     

    Select fruit, animal from things WITH (INDEX = 0)

    where color = 'Red'

     

    Will force a scan of the 'things' clustered index or table.

    It would still be nice for you to post what you have so we can see what is going on.  We like this kind of stuff, because it helps us all.

    Thanks,

    jg

     

     

     

     

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

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