Anyway, here is my question. I am investigating of eliminate the SPROCs in my Apps, or keep the SPROCS as simple as CRUD, and move all my business rules to my C#/VB.NET code. Here is very popular scenerio of shopping cart: Everytime the user select a new product,
I save this item in a temporary shopping cart (there's a table called shopping cart.) Then I submitted the shoppingcart. Now I have SPROC doing this job, all I need to is passing in the ShoppingcartID, UserID, and other necessary information, the SPROC did
all the dirty work, include: 1. Create the order in orderheader table, 2. Move detail lines to Order detail table, 3. Get other information from other tables/views, or eveal call other SPs, and update OrderHeader table if necessary. Let's say we don't use
SPROC any more, everything is done in C#/VB.NET code. This will result in at least 5 - 10 round trip, even much more depend on what you do. For me, I also have an auditing table needs to be updated, ..., which will be more than 100 round trips on the worst
case (for every line item, I do a lot of process, basically check real time availabilty in my current login user location, create a cursor to loop through my inventory, get items from other region/location if I don't have it in my current region/location,...,etc)
Yea, it's a lot of work, but SPROC deals with it fine, since there's only one database roundtrip. I am just wondering if everything is done in C#/VB.NET code, will this be a desaster for performance? As to all the O/R mappers, how you do that? Thanks, Calvin
I think the number of trips depends heavily upon your object design and architecture. If you simple move the logic from your sproc's to your business layer then yes performance will be an issue. However, if you design the objects to handle the logic required
of them from the offset then you end up with a much cleaner solution. I have always placed business logic in sprocs and even thought that this was good practice. However, the maintenace issues I have recently faced, plus the simple lack of any source control
support from SQL Server means I am quiclky coming round to dynamic sql. I really dont care if i lose 2/10ths of a second per order, what i do care about is trying to track and fix bugs and that orders placed are correctly entered :)
Encapsulation can be a wonderful thing. I'd have to say that generally in an n-tier architecture it is common practice to place business rules and logic in the middle tier, NOT the database (in the form of sprocs). However, each situation is different. If all
you're doing is a few updates, a delete, and then a select then all that happens on the database server anyway - why introduce a middle tier that will just add more database calls? It is often said that in order to achieve flexibility one of the most common
trade-offs is performance. You need to decide how much flexibility you need. Only then can you decide what to do in classes (VB.NET or C#), what to do in sprocs, and what to do in the UI.
HI Russ, You commented:I really dont care if i lose 2/10ths of a second per order, what i do care about is trying to track and fix bugs and that orders placed are correctly entered
I would imagine that the no-load performance difference is a lot less than that. But the real issue, it would seem to me, is not how fast can you do one transaction, but how many transactions per second the system will support, and that, IMO, is where sprocs
can help. If the SQL server has to do less work on each transaction, it seems logical that it can therefore support more transactions in a given time frame. Of course, if what you mean by "dynamic sql" is using a command and it's parameters collection, but
a text query string instead of a sproc name, then we're probably talking a lot less performance hit for repeated queries, but still... But a sproc is no place for business logic. It should be kept as simple as possible, IMO. If you're trying to use TSQL to
write business rules, and debug them, then I can understand why you would become dissatisfied.
Naturally stored procedures (except for sp's with EXEC's) are more efficient than dynamic SQL but dynamic SQL encapsulated in DLL's are easier to manage in terms of versioning, upgrading, maintanence (moving servers), etc..
dynamic SQL encapsulated in DLL's are easier to manage in terms of versioning, upgrading, maintanence (moving servers), etc.. If I understand what you're saying here then I have to disagree. We use a data project within our solution to manage our database
objects and get them into SourceSafe and it works quite well.
Hey Jim, What WE do is force all changes to the database to go through T-SQL as opposed to opening tables to insert new data, using the UI to add fields or change fields, add new tables, etc. With that policy in mind (which in ITSELF takes 2 or 3 days to get
used to), we have a data project in our solution that is organized by developer (since there are only 4). What that means is that within our data project each developer has their own folder. Within your own folder you can add as many folders as you want to
organize it to best suit your needs. Mine is organized by functional area of the application. Whenever there are new changes, I add a new .SQL file to one of the folders in my main folder, and this .SQL file is the code equivalent of everything that I have
done to get this change working in the development environment, including (as I said) new data, schema changes, new sprocs, sproc changes, etc. So now to get all my database changes out to QA all I have to do is right-click my one file and choose "Run On"
and then choose the QA server. In that one step all of my database changes are applied. Then I deploy the application. Since our whole solution is in SourceSafe, all of the changes that I'm doing are under version control. That way if it takes me 2 months
to write something new or implement different functionality, every single iteration of my database changes exists and I can roll back. I sometimes have multiple .SQL files for a single release if there are a lot of changes. For example, I could have a file
for all of my new sprocs, a file for changed sprocs, table changes, data modifications, etc. etc. In a shop where there are many more developers it might be more prudent to organize your data project's folders by database and then release or by functional
area and then release. The possibilities are endless. Does this help at all?
Yeah in what possible way is it easier to maintain dynamic sqls within a dll? Maybe I misunderstand the meaning, but actually storing SQL in a dll means recompiling the dll any time you have a sql change. I think russnem's approach is sound. You could also
easily externalize your dynamic sqls to a sql folder and work with them in sourcesafe in a similar manner. This makes it easy for DBAs to review your sql, and for you to make a mass change to your sqls, or otherwise change them without massive recompiles.
Hi Russ, Yes, that is interesting. thanks. Managing database changes during a development cycle is, IMO, much harder than managing the source code, but I haven't yet seen a system I really like. To be sure, there are ways that can be made to work, but that's
different than "really liking" them. We're a small team also, and what we have found best is to appoint one of the team as the dba, and all changes have to go through the dba. We have an external "real" dba who handles everything once we're ready to move stuff
outside the development environment, and with all the ponderous multiple redundancy that is the hallmark of huge bank IT departments. In some ways it's a pain to have to deal with another party and their mandated lead times, but I'll say this--they have never
messed up a move on us. Lately we have had a couple of projects involving re-engineering existing applications, and thus, including migrating their database. These have proven to be far more demanding of script management, etc. than stuff we have full control
of from the git-go. It never ceases to amaze me the design errors I find in the schemas in some of these things. I'm working on one now where the fellow has some kind of odd 'round robin' head-swallowing-the-tail set of tables to handle what _seems_ to be
a fairly straight-forward roll-up hierarchy. I'm approaching it with caution, because the app is known for some very convoluted user requirements, so it is hard to tell if the odd schema is the result of a real requirement, or just the original designer being
confused by changing "apparent" user needs.
cklein
Participant
1561 Points
363 Posts
Again, SPROC VS Dynamic SQL, performance wise
Aug 19, 2003 08:12 PM|LINK
http://www.raincoder.com
Equal parts art and science
Email: cguo@raincoder.com
RussUK
Member
462 Points
93 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 20, 2003 09:05 AM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 24, 2003 07:18 AM|LINK
JimRoss [MVP...
Star
10080 Points
2008 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 24, 2003 05:57 PM|LINK
MS MVP ASP.NET [VC++/MFC emeritus]
Old Dog Learns New Tricks
Preferred programming language: cuneiform on clay tablets
ActiveDataOn...
Member
335 Points
67 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 26, 2003 02:22 AM|LINK
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 26, 2003 01:38 PM|LINK
JimRoss [MVP...
Star
10080 Points
2008 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 26, 2003 01:52 PM|LINK
MS MVP ASP.NET [VC++/MFC emeritus]
Old Dog Learns New Tricks
Preferred programming language: cuneiform on clay tablets
russnem
Contributor
7001 Points
1389 Posts
ASPInsiders
MVP
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 26, 2003 06:36 PM|LINK
Stephen Vaki...
Contributor
2540 Points
508 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 26, 2003 09:52 PM|LINK
JimRoss [MVP...
Star
10080 Points
2008 Posts
Re: Again, SPROC VS Dynamic SQL, performance wise
Aug 27, 2003 12:13 PM|LINK
MS MVP ASP.NET [VC++/MFC emeritus]
Old Dog Learns New Tricks
Preferred programming language: cuneiform on clay tablets