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 Execution Performance Expand / Collapse
Author
Message
Posted Monday, August 16, 2010 9:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 31, 2013 4:28 AM
Points: 88, Visits: 43
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
Posted Monday, August 16, 2010 11:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 41,547, Visits: 34,469
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
Posted Tuesday, August 17, 2010 2:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 31, 2013 4:28 AM
Points: 88, Visits: 43
Is their any Steps to be followed for changing the Server Settings
Or any changes need to be made to the table.
Post #970194
Posted Tuesday, August 17, 2010 2:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 41,547, Visits: 34,469
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
Posted Tuesday, August 17, 2010 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,955, Visits: 12,708
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
Posted Wednesday, August 18, 2010 8:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
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
Posted Wednesday, August 18, 2010 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Use Microsoft Time and you will get 2 sec or whatever you like without much of hassle

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(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 »

Add to briefcase

Permissions Expand / Collapse