Query Execution Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Query Execution Performance

Query Execution Performance

Rate Topic

Display Mode

Topic Options

Author

Message

Lokeshkumar

Posted Monday, August 16, 2010 9:16 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 1:07 AM
Points: 88, Visits: 39

If a Select Statement takes more than 5seconds to Execute.

Steps needs to be followed to reduce the time interval or execution time

To execute the statement in 2 seconds

Simple Select to a Single Table not multiple tables.

Select * from Table1

Post #970120

GilaMonster

Posted Monday, August 16, 2010 11:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 38,062, Visits: 30,356

I'm not sure I follow you completely.

As for your example query - select * from table - there's no way to optimise that. It's asking for all rows of the table, hence SQL has to scan the entire table and return all the rows, all the columns. Are all rows really necessary.

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 #970152

Lokeshkumar

Posted Tuesday, August 17, 2010 2:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 1:07 AM
Points: 88, Visits: 39

Is their any Steps to be followed for changing the Server Settings
Or any changes need to be made to the table.

Post #970194

GilaMonster

Posted Tuesday, August 17, 2010 2:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 38,062, Visits: 30,356

If you're requesting all the rows, all the columns from a table there is no practical way to optimise the query. It may take some time if the table is large. How many rows are in that table? Why are you selecting every single column, every single row from that table? Do you really need all of that?

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 #970198

LutzM

Posted Tuesday, August 17, 2010 3:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

Based on the very limited information provided so far: reduce the number of rows in the table or in your select statement (the former by archiving data and the latter by using a where clause with appropriate covering index).
If none of the above is an option please provide a more detailed information (how many rows, how many columns, total size of the table, is the time measure using SSMS on the SQL Server or is there a network in between, maybe even a frontend that might be the cause for the delay).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #970219

Nadrek

Posted Wednesday, August 18, 2010 8:11 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:08 PM
Points: 679, Visits: 2,038

1) Select fewer columns, and use a well tuned covering index
2) Select fewer rows, and use a well tuned (covering) index
3) Make sure that table has a clustered index, and rebuild the index
4) Defrag the storage at the OS level and the SAN level
5) Tune the fillfactor of the clustered index to make sure the table takes up as few pages as possible when this query is likely to run
6) Revisit the business requirement
7) Buy a new set of disks, short-stroke them, and use them solely for this table
8) Buy a new set of flash SSD's and put this table on them
9) Buy more RAM so this table is likely to stay in the buffers
10) Buy a new set of DRAM or SRAM SSD's and put this table on them
11) Buy a faster machine, faster IO, faster network, faster storage, faster everything.

Post #971155

Eugene Elutin

Posted Wednesday, August 18, 2010 9:18 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 AM
Points: 2,596, Visits: 4,506

Use Microsoft Time and you will get 2 sec or whatever you like without much of hassle BigGrin

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #971218

« Prev Topic | Next Topic »

Permissions