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 |
|
brubaker
Starting Member
15 Posts |
 Posted - 2002-07-25 : 22:57:44
|
| Which of the following 4 scenarios is faster for a master/detail page arrangement, where the master returns 5 columns and the detail returns 10 including 1 which may be the size of an email, text message, or news type item:1) One stored procedure that returns all info for a table, but you wouldn't use all of the info on the master page. The query could be cached though and save time on the detail page.2) Two stored procedures, one for the master page and one for the detail page. Only necessary data is returned, but it takes two stored procedures to do it.3) One stored procedure that you pass a variable to which determines whether to return a master page recordset or return a detail page recordset4)It doesn't matterThanks. |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-26 : 00:08:37
|
| IMHO,I would think Number 1 would be the fastest assuming you are using ASP to code the pages. One thing I learned from Robvolk is the longer you leave a connection open, the longer it will take the page to load. If I need to return two blocks, I open the recordset calling the sp with all the info I need in the one sp. I then set the an array equal to the first recordset, move to the next recordset and set an array equal to it, etc., etc. This allows all the information to be stored in arrays so that you can immediately close the connection. Then you can parse the arrays as needed.This closes the connection to the database ASAP, instead of leaving it open to loop through the recordset. I think Robvolk once said (correct me if I am wrong Robvolk) that each time you loop through a record, a call is made to the database.I haven't actually tested the timing differences, but I would suspect because of the reasons above, that number 1 would be the fastest.Jeremy |
 |
|
|
dsdeming
479 Posts |
Posted - 2002-07-26 : 08:18:35
|
| Have you considered one sp which returns 2 result sets, one master and one detail? This replaces your item 2. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-26 : 08:44:23
|
quote:
I think Robvolk once said (correct me if I am wrong Robvolk) that each time you loop through a record, a call is made to the database
Actually, when using a loop on a recordset you're making calls on that recordset object. It won't call the database each time unless you set up a server-side cursor and a low record cache. But it DOES invoke processing overhead for each call to the object.Think about painting a brick wall. If you use a recordset loop, essentially you are pulling each brick from the wall, painting it, and then putting it back. The effort/overhead of removing the brick and putting it back is wasted. If you use GetRows or GetString, you're leaving them in place, and painting them all at once. It's much faster that way. (this is actually a pretty crappy analogy! but it sort of demonstrates the point)As Jeremy said, the longer you leave a connection open the longer the page will process, and therefore load, and the recordset/loop overhead is the reason. However many SPs you use, grab their data into an array (with GetRows) and close the connection as quickly as possible. |
|
|
|
|
|
|
|
|
What's faster in SQL Server 2000?