Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
roykauf
Starting Member
36 Posts |
 Posted - 2008-11-16 : 04:25:56
|
| Hi all,I recently installed SQL 2008 enterprise evaluation edition.I noticed that a simple query like : 'select top 10 * from table' takes 13 sec than 3 sec in SQL 2000. Why is it so? and what to do to accelerate it? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-16 : 05:09:27
|
| have you analysed the execution plans in sql 2000 & 2008 and spotted any differences? |
 |
|
|
roykauf
Starting Member
36 Posts |
Posted - 2008-11-16 : 09:44:10
|
| I checked and saw that there are differeneces in the query time between 2000 and 2008,I tried all things possible: Update statistics, rebuild for indexes |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-16 : 11:54:47
|
| Does you tables in both the dbs have similar indexes? |
|
|
|
defiantiger
Starting Member
5 Posts |
Posted - 2008-11-20 : 21:06:01
|
| Do you really need to use the '*' in the query? I know it shouldn't make a difference between 2005/2008, but you're never really going to benefit from any indexing running queries with the '*'. Try using the column names and run the query on both databases. The nice thing about SSMS 2008 is that if it queries a table without an index, it will alert you to this in the execution plan. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-20 : 21:36:22
|
| select top 10 * from tableThis won't use any indexes and shouldn't really be affected by anything.Why would this take 3 seconds previously? It just has to take the first 10 rows it comes to and return them.Unless you have a really slow network or really big rows it should be less than the time able to be displayed - i.e. 0.Sounds like it has to start something up before it can execute. Maybe has to start something in sql server (can you still stop databases if they aren't being used?). Maybe the conectio takestime to initiate.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
SQL 2008 performence & query time