Query Optimize

  • Hi,

    I have select query which taking too much time to retrieve data from DB.

    How can optimize that query.

    I try full text search tech also.but it doesn`t work for me

    I am attaching excution plan for the query

    Plz. Help

    Thanks in Advance

  • As far as I can see, your query

    select top 100 * from dbo.StockLocationLog where mode='Dispatch'

    - it's a "select all" query

    - if there is any ndex on the "mode" column, it may not be selective enough.

    Can you send the table definition, indexes included?

  • Please post the query, table definitions and index definitions as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • pretty simple query. Not much SQL to tune.

    looks like building a nonclustered index on mode will help this query a great deal.

    Be cautious when adding indices, as they slow down write performance (slightly) and require continued maintenance. Before building an index, make sure you have enough disk space to hold all the data in the mode column, plus the PK column.

    refactoring the query to not be a select * might help too, but then you'd select only the columns you want to use and build a covering index with the first column of mode. That would make the query the fastest, but would cost the most in terms of disk space and write performance.

    ~c

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

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