Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Optimize Expand / Collapse
Author
Message
Posted Friday, May 21, 2010 1:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 6, 2012 5:00 AM
Points: 114, 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




  Post Attachments 
Select Query.sqlplan (40 views, 7.98 KB)
Post #925706
Posted Friday, May 21, 2010 4:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:16 PM
Points: 363, Visits: 1,321
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?
Post #925801
Posted Friday, May 21, 2010 4:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 39,980, Visits: 36,344
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 2008, MVP
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

Post #925805
Posted Friday, May 21, 2010 7:35 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #925941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse