SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Optimize


Query Optimize

Author
Message
Trouble Shooter
Trouble Shooter
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 74
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
Attachments
Select Query.sqlplan (48 views, 7.00 KB)
dmoldovan
dmoldovan
Say Hey Kid
Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)

Group: General Forum Members
Points: 676 Visits: 1469
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87265 Visits: 45272
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


SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 836
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



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search