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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-04 : 15:15:09
|
| Manish Rawat writes "I have a master table with approximately 2000 rows.Very often in my web app and a Service i need to bring all the 2000 rows in specific order by to VB code from Database.Select statement is like "Select * from <TableName> order by col1,col2,col3"To make this select faster i made clustered index on col1,col2,col3 but still it takes 500 to 1000 milli seconds to Select as seen in profiler.What else i can do to make this select faster from database." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-04 : 15:36:31
|
| The problem is not the SELECT speed, but your ASP code speed. I'm guessing you're using a Do While Not rs.EOF...rs.Movenext...Loop construct to write each row of your recordset to your web page, correct? If so, you should read this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11019 |
 |
|
|
manishrawat
Starting Member
9 Posts |
Posted - 2001-12-06 : 18:37:12
|
| I am calling this with client side cursor on when bringing the recordset.So Do while loop should not make difference.More over the 500 to 1000 ms time to select data is shown in SQL Profiler |
|
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2001-12-06 : 18:58:48
|
| It may not be the bottle neck in your case, but a client side cursor can make a difference b/c the batch size is often small enough that you're making multiple round trips to the server to get all the records, rather than the getrows or getstring methods, which grab the whole RS and dump it into a structure on the client w/o all the round trips. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2001-12-06 : 19:11:12
|
| An "Order by" is the most expensive thing SQL Server does.Remeber that relations (tables) are by definition NOT sorted.No matter how many indexes you create this will still be expensive Look at the query plan for just how muchDavidMIt gets windy at a thousand feet...."Tutorial D" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-06 : 19:28:14
|
quote:
I am calling this with client side cursor on when bringing the recordset.So Do while loop should not make difference.More over the 500 to 1000 ms time to select data is shown in SQL Profiler
It doesn't matter what type of cursor you use, a Do...While loop with multiple response.write's and rs.Movenext WILL WILL WILL be slower that GetString/GetRows for large data sets.To add to that, network latency could also be a factor. You didn't mention how long it takes for the data to draw on screen, but I assume it's more than 1000 milliseconds (if it only took 1 second to draw 2,000 rows on the screen I can't imagine you'd find that slow!)If it does take longer than 1 second to draw the screen, and the query execution is at most 1 second, then there must be another factor that's causing the slowdown. Either network latency, ASP response generation, or HTML rendering. In any case, even if you got query execution time down to nothing, it won't affect any of these other processes. |
|
|
|
|
|
|
|
|
How to make Select fast