There's an interesting debate in our office and I wanted to see how you all felt. This is for a Web application.
One option for committing changes is to:
The other option is to:
I find in my tests that without memo fields, SPs are actually a little faster. With a test considering some memo fields (10% of fields being 1500 chars), then inline outperforms SPs handily.
But I really suspect that the clone can tend to get somewhat expensive with larger objects. Not to mention the comparision of all the properties to construct the "smart" sql. I suspect SPs are in the end - faster.
I don't want to invoke a large scale war... just a small scale one :). Anyone have any thoughts on this subject??
You could encapsulate the 'expensive' fields into a helper BO. That would require two database hits, but if its uncommon for those memo fields to change, it could be worth it.
Your tests showing SPs to be faster are likely misleading you; Sql Server doesn't handle dynamic sql vs.a proc any differently. The fact that you're creating a different ad-hoc query each time will hurt performance, because an execution plan will need to be created for each different statement. Using inline sql that is the SAME (that is, # fields, ordering, same from / where clause, etc) each time should result in performance the same as a proc.
I think the reason why the SPs are a little faster for updates (w/o large fields) is because of the fact that the constructed updates differ and perhaps don't have an execution plan (compared to an update sproc) - perhaps as various permutations occur (in a 20 field BO, a lot of permutations of field changes can occur) then various execution plans perhaps build up?
Anyways, thanks for the answer.
Chris
Copyright (c) Marimer LLC