Post reply

Specifing Index to be used in From clause

  • November 18, 2003 at 10:54 am

    #151978

    Howdy, I am trying to use the From...With to specify a certain index I would like the query to use. I can't seem to get the proper syntax and am getting a variety of errors. What is the proper syntax? Also, can something like this really help performance? How much?

    Thanks

  • November 18, 2003 at 11:06 am

    #482485

    -- Use the specifically named INDEX.

    USE pubs

    SELECT au_lname, au_fname, phone

    FROM authors WITH (INDEX(aunmind))

    WHERE au_lname = 'Smith'

    See BOL: Select > Examples.

    Why would you want to force it? Does the optimiser not use on?

    If not, it could be because optimiser decided that it's to expensive to use the index.

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • November 19, 2003 at 12:18 am

    #482545

    The query optimizer makes its decision based on probability. And can therefore sometime make wrong decisions.

    To see if and how the hint can improve performance run your queries with

    STATISTICS IO

    STATISTICS TIME

    SHOWPLAN

    and compare the results.

    Don't get used to use query hint, because what might seem to be appropriate now, might hurt performance when your underlying data changes significantly. The term 'hint' is in this case some kind of understatement. It forces SQL Server to follow your hint. FWIW.

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    Sorry for the editing!

    Edited by - Frank kalis on 11/19/2003 12:19:36 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 19, 2003 at 1:13 am

    #482560

    Also, before you go down the 'query hint' route, check that your index statistics are up to date, and do an 'UPDATE STATISTICS' if not. There's a good chance that SQL Server isn't using the index you want because it has out of date statistics.

    You can check when the statistics were last updated by running a DBCC SHOW_STATISTICS(tablename,indexname).

    Having said all that, the SQL Server optimiser still makes the wrong decision occasionally...

  • November 24, 2003 at 6:30 am

    #483326

    Thanks, I'll try all of that.

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

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