Post reply

How to avoid use of index in query?

  • July 16, 2006 at 9:47 pm

    #61291

    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.

     

     

     

     

  • July 17, 2006 at 1:03 am

    #649461

    Take look at "hints" (more specifically "table hints") in BOL in the "Index" tab. "Hints" is there as a separate keyword.

    Hints let you "instruct" SQL Server on how to to something. Although it is generally recommended to let SQL Server to decide, you might have a case fot it.

    ***

  • July 17, 2006 at 1:13 am

    #649463

    Dear Newbie,

    Thanks for your useful feedback. I don't have any exprience in sql server admin. I am Oracle DBA. So please tell me details. I Lookup the Table Hint but I don't understand properly.

    so Please help.

     

    Regards,

    Prathamesh.

     

     

     

  • July 17, 2006 at 2:29 pm

    #649676

    After you table name add

    WITH (INDEX(0))

     

    This will force a table scan or clustered index scna (if you have one) which is what you are trying to accomplish.

    You might also try using Query Analyzer to show the Execution Plan and try to determine what specifically is causing the performance difference. I personnally would try this first.

  • July 18, 2006 at 2:50 am

    #649729

    The index are used for fast retrieval of data,isn't.. then how it doesn't work for your query.. can u put a sample of ur code

  • July 18, 2006 at 9:14 am

    #649818

    The way you structure your query can cause SQL to not be able to use an index.  Post your query and you'll get better help.  Also, this thread shouldn't be in the SSC Suggestions forum, as it's a question that would be better asked in SQL 7/2000 General.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

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

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