how to avoid use of index in query???

• prathamesh patil-349282

  SSC Journeyman

  Points: 89

  More actions

  July 17, 2006 at 12:58 am

  #173470

   

  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.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  July 17, 2006 at 2:14 pm

  #649673

  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!

• Jeff Gray

  SSChampion

  Points: 10667

  More actions

  July 18, 2006 at 6:42 am

  #649763

  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