This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi there,
New to Lightspeed, but long time developer with a lot of database experience. We are re-writing an application which is driven primarily by stored procedures. In fact, the only thing traditionally we've exposed to the application is stored procedures for all activities. Generally, we find this a good pattern, since a stored procedure can be optimized real-time without a code release, embedded linq to sql cannot. It also creates a layer of obfuscation between the app and the database. But as we are going to Lightspeed, I want to find out: how fast are the more basic queries to a table? Standard inserts, updates, deletes and simple selects, I am assuming they are as fast as any stored procedure call, especially if the db is well indexed and running on a good db server. Just want to gauge the best way to proceed. Our developers will be abstracted from this portion of the code to a large extent (they will be talking to services), but I want to make sure my senior devs aren't digging themselves into performance holes. Cheers in advance
|
|
|
|
|
Sorry, meant a layer of abstraction, not obfuscation. |
|
|
|
|
Its a bit of a loaded question (and one which people can get pretty heated discussing!) but you will find that most ad-hoc queries will execute with comparable performance to an equivalent stored procedure - some may be slightly faster, some may be slightly slower. The best thing as always is to benchmark any workloads which are of interest so you can actually make a proper comparison (e.g. grab a set of procs from the existing system you intend to convert, benchmark them, update them to run under LS, profile the SQL that is executed and then benchmark that). Another thing to remember with "basic" queries is that generally the database execution cost is pretty minimal compared to other factors such as moving the resulting data back over a network etc. We advocate the use of stored procs for more complicated queries, or queries which are of a nature where performance is key and you want to remain in control of the generated query accordingly so we have mechanisms built into LightSpeed to support this.=
Jeremy |
|
|
|
|
Jeremy,
Thanks for that. Yeah, it is a slightly loaded question. I can see both sides of the coin on this debate. The ease for a developer of writing code to the db rather than mussing with stored procedures is pretty attractive and fits nicely with the rest of the coding paradigm (I can unit test my C# code, including my linq to sql calls; I can't do that with a stored procedure,etc). However, having worked with some big-time database experts, I know the necessity to let the db do what it does best, aka set calculation and retrieval. Your suggested approach sounds good. I will investigate a bit more and return with follow up questions, I'm sure.
Chris |
|
|
|
