May 21, 2010 at 1:05 am
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
May 21, 2010 at 4:21 am
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?
May 21, 2010 at 4:25 am
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
May 21, 2010 at 7:35 am
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
Craig Outcalt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply