| Author |
Topic |
|
00kevin
Yak Posting Veteran
78 Posts |
 Posted - 2007-06-19 : 13:43:14
|
| I'm just wondering what people think of these two design methods for calling sql stored procedures from an asp.net web app.When returning data.1. Having one call to the database (a stored procedure) that returns many recordsets (datatables) for each item on the form / pagevs.2. Creating DAL objects for each action (many calls to stored procedures for each form /page.Is there anyone with experience with #1 that can tell me if it was a good idea or not.My database admin is strongly against the application making any more then one stored procedure call per page request. I would argue that the time saved execution wise isn't significant enough. The other problem with #1 is that business logic become part of the stored procedure and that can make your application less modular. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-20 : 03:37:28
|
| >> The other problem with #1 is that business logic become part of the stored procedure.That's not a bad thing - almost unnavoidable.The problem with #1 is that you are binding the stored procedure to the page. A stored procedure should represent entity access (not table but they may be the same thing in some instances). What you would do here is have a stored procedure wrapper for the page which then called stored procedures for the business entities (don't be strict about ir as performance may dictate other SPs). You will get the re-use as an SP for another page could call the same entity SPs..In that way 1 and 2 become almost the same thing - you're just moving the individual SP calls from the application into a SP.I would in fact consider having a table which defined the SPs called for each page so that there is a single SP called from a page with a page id (and probably a collection of parameters) then what happens is configurable from that table.Note if you change your mind it is simple to move this back into the application - could even just have a layer which accessed the same table (probably move it into an xml file) so the migration would be trivial.==========================================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. |
 |
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2007-06-20 : 11:32:32
|
| Thanks nr. You outlined a very interesting way to place alot of the code in sql server. I'll have to think about it. In the business layer I would need just one function call per page. Perhaps I could pass an xml document to the stored procedure for the paremeters. Do you think the database admin has a valid point when he says that many SP calls per page by the application is a performance issue? In most cases there will only be 2-3 calls per page to the db and they will all be associated to a transaction created by the application. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-20 : 11:41:51
|
| He has a bit of a point but at the expense of complicating the code.It's a good idea to try not to hold transactions from the application - that'll probably have a bigger impact than a few SP calls. Better to put all transactopn control in an SP (although sometimes it's a lot simpler to run some from the app).Get away from this concept of a business layer. The business rules should be implemented where they fit best - sometimes this will be in an SP sometimes in the client.==========================================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. |
|
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2007-06-21 : 15:47:46
|
| ok thanks. do you think it would be best to pass the parameter call in a text field or should I use an xml document? if it was just text I could parse it on line breaks for each stored procedure call in the table. I guess this works for geting data from the database but it won't work very well for saving data since binary data will be a problem.for examplecreate procedure getPageData( @pageId int, @paramCalls text)as...select @maxSeq=max(seq) from SPCall where pageId = @pageIdwhile(@i < @maxSeq){ -- parse out param from @paramCalls (xml or just text?) ... exec @spName + ' ' + spParam set @i = @i +1} |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-22 : 02:59:32
|
| In general terms I think you should reduce the number of individual SQL calls from the application. Each one requires a round-trip to the SQL Server box, with all the "admin" of setting up, handshaking, and so on. Yes its milliseconds, but it adds up!Sometimes its unavoidable because the application has to make a decision, and based on that call a specific SProc. But where the application logic is linear I think it is better to try to reduce the "traffic" to a single SQL call that returns multiple recordsets."business logic become part of the stored procedure and that can make your application less modular"For me this is A Good Thing!We can debug and test an SProc in isolation. So we can fix a bug, or make a change, and fully regression test the Sproc, and thus change the application with no side effects (OK, we aren't perfect!, but in general it works well)OTOH changing a Web Page brings with it all sorts of possible interactions within the application, and the testing (for us at least) needs to be MUCH more rigorous.So by moving most of our Business Logic to Sprocs we are more easily able to maintain that code.Some planning is required before making the change though! We have version control systems, QA systems, deployment processes and systems, and so. But basically when we change our application now we write SQL Sprocs, not application code.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-22 : 05:16:00
|
| I would start off with text (it might contain pseudo xml but use a common SP (best) or function to parse it and common routines in the app to build it.In that way if you change your mind it just means changing those two objects and not a change to every module. You can also put loggnig in those routines to monitor the parameters passed.==========================================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. |
|
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2007-06-22 : 10:56:20
|
| ok I'm going to try your suggestion. I'm just trying to figureout how I'm going to do the same thing for data going into the database. Often there are pages that have to update many rows and in some cases that means many stored procedure calls. Whatever solution I use it has to support all datatypes including Image / binary params. btw, I am not really a fan of the business logic layer myself. There are many people I know who call it the business illogic layer. It really doesn't work all the time and there are many times that you need to be change (fix) the busiess logic on the fly. I do agree that having it in a stored procedure is ideal. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
best way to call sql stored procedures