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 |
|
whamo
Starting Member
10 Posts |
 Posted - 2003-02-04 : 11:21:59
|
| Hello all,I have a several web applications that call many, many, stored procedures throughout the pages. They are called one at a time as needed for that point in the page. To date I am happy with the speed and resulting datasets. However, I am now leaning towrd a newer feature (for me) of making one trip to the DB for all my Data sets. That is either, one USP at the beginning of my page with Mulitple selects and whatever else I need in that page. OR using the same number of USP's I use now only just call one USP at the top of my page and have the others, as exec's after that one.Would either of these be faster than the other? Obviously using several selects as one USP would cut down on USP's but seems a little messy. I'm leaning toward the second option of exec all my needed USP's in one call from the page.Is there a point of futlity here? I'm looking for the benefit of the fewer nmber of trips to the DB, but If I load up one USP as sevseral, will that kill the benefit?I'm looking to see if you guys know of any known Pitfalls or red flags for this stuff before I change my protocals for DB tripsDB is SQLSVR 2000Web Interface is ColdFusion 5Thanks for any helpEdited by - whamo on 02/04/2003 11:43:36 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-04 : 12:06:38
|
| We built something similar to this. The Java development team decided to get all the data "up front". Several Pluses and minuses. Any time the data chanegs, you won't know unless you requery everything, initial load time will be slower, updates become problematic...you'll have to check to see if an update occurred while you're holding all of the data..and so on and so on...The upside is the app has everything in memory and should "POP".Never did like that implementation though.Good LuckBrett8-) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-04 : 12:46:09
|
Your second option sounds better to me too. If you're talking about say 5 sproc's and you set up code like this:Dim rs1, rs2, rs3, rs4, rs5Set rs1=Server.CreateObject("ADODB.Recordset")Set rs2=Server.CreateObject("ADODB.Recordset")Set rs3=Server.CreateObject("ADODB.Recordset")Set rs4=Server.CreateObject("ADODB.Recordset")Set rs5=Server.CreateObject("ADODB.Recordset")connObj.Open "connection string to connect to SQL Server"rs1.Open "EXECUTE sp1", connObjrs2.Open "EXECUTE sp2", connObjrs3.Open "EXECUTE sp3", connObjrs4.Open "EXECUTE sp4", connObjrs5.Open "EXECUTE sp5", connObjrs1.ActiveConnection=Nothingrs2.ActiveConnection=Nothingrs3.ActiveConnection=Nothingrs4.ActiveConnection=Nothingrs5.ActiveConnection=NothingconnObj.Closeset connObj=Nothing... 'Process each recordset when neededThat way you grab all the data up front, but each procedure is represented by a separate recordset. Trying to combine them into one recordset and using NextRecordset to move between them *may* be a little faster, but will be much less intuitive to program (and you can't go back to a recordset once you move off of it) Each sproc should return only one set of results, and each recordset should only run one sproc.Personally, whenever I open a database connection, every line of code between the Open and Close is related to that connection. Everything after the Close is dedicated to building the page and/or processing the records returned. If updates, deletes or inserts have to happen, they are all done with sproc's (never with ADO AddNew or Update) and are also done first thing, before any other page processing occurs. Like your parents tell you when you're a kid (or older): DON'T KEEP THE REFRIGERATOR DOOR OPEN ALL THE TIME!  As long as you open the recordsets, grab the data, and disconnect them as quickly as possible, you should do fine. |
|
|
|
|
|
|
|
|
Performance Question