August 16, 2010 at 9:16 pm
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
August 16, 2010 at 11:31 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 2:38 am
Is their any Steps to be followed for changing the Server Settings
Or any changes need to be made to the table.
August 17, 2010 at 2:43 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 3:40 am
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).
August 18, 2010 at 8:11 am
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.
August 18, 2010 at 9:18 am
Use Microsoft Time and you will get 2 sec or whatever you like without much of hassle 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply