![]() With sprocs you can get them back as output parameters, which is considerably faster. Ability to use output parameters - if you send inline SQL that returns one row, you can only get back a recordset.This is particularly performant for small queries run frequently. Caching query plan - the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse.With sprocs, you can execute SQL in batches, which is also more efficient. Reduce network traffic - you have to send the SQL statement across the network.Eventually (if it's not here already) ad hoc SQL performance will become indistinguishable (on average!) from stored procedure performance, so any sort of massive stored procedure use ** solely for "performance reasons"** sure sounds like premature optimization to me.Īnyway, I think if you avoid the edge cases and have fairly vanilla SQL, you won't notice a difference between ad hoc and stored procedures. Sure, you can place every single silly teeny SQL statement inside a stored proc, but just know that the programmers working on optimizers are very smart and are continually looking for ways to improve performance. Moore's law continues to march on and DBMS optimizers, with every release, get more sophisticated. Some do this implicitly or must be enabled with a command setting (SQL server:, Oracle: ). Most DBMS have the ability to "abstract" ad hoc SQL into parameterized versions - thus more or less negating the difference between the two. In the past, these were the "worst" performers (on average) since the DBMS had no way of pre-optimizing the queries as in the parameterized/stored proc method. Just open a console window to your DBMS and type in a SQL statement. These provide a more generalized version of a query that modern-day optimizers can use to cache (and re-use) the query execution plan, resulting in much of the performance benefit of stored procedures. Kind of a cross between stored procedures and ad hoc SQL, they are embedded SQL statements in a host language that uses "parameters" for query values, e.g.: SELECT. Of course, no discussion of Stored Procedure optimization would be complete without a discussion of parameterized and ad hoc SQL. Unless your app has shared memory access to DBMS data, the edge will always be to stored procs. Performing server-side transforms of data can potentially be faster if you have sufficient memory on the DBMS and the data you need is in memory of the server. in your code (everyone's favorite WTF!)Ī) Modern 1Gb (and 10Gb and up!) Ethernet really make this negligible.ī) Depends on how saturated your network is - why shove several megabytes of data back and forth for no good reason? Analogous to doing a JOIN in the DBMS vs. This saves shuffling large amounts of data off to the client only to have it send a trickle of info back (or none at all!). ![]() Occasionally the query plan that is generated is sub-optimal.Ī) If you are running the same SQL over and over - and the SQL adds up to many KB of code - replacing that with a simple "exec foobar" can really add up.ī) Stored procs can be used to move procedural code into the DBMS. WARNING Cached query plans are not a performance panacea. ![]() There are some caveats and cases in which this isn't the case, so you'll need to test on your target DBMS.Īlso, more and more DBMS allow you to provide optimizer path plans (abstract query plans) to significantly reduce optimization time (for both ad hoc and stored procedure SQL!!). Most DBMS' (the latest editions) will cache the query plans for INDIVIDUAL SQL statements, greatly reducing the performance differential between stored procs and ad hoc SQL. Stored procedures will, generally, store this in memory so you can avoid this overhead. It is not unknown for very complicated SQL to have distinct, measurable (I've seen a complicated query take 10+ seconds just to generate a plan, before we tweaked the DBMS) latencies due to the optimizer trying to figure out the "near best" execution plan. If you have many JOINs the permutations can grow quite unmanageable (modern optimizers have limits and cut-offs for performance reasons). Not very noticeable at all on the modern CPU, but if you are sending a single SQL statement that is VERY large eleventy-billion times a second, the parsing overhead can add up. interpreted code, except on a very micro level. Pre-parsed SQL - similar benefits to compiled vs. The historical performance benefit of stored procs have generally been from the following (in no particular order): So, I have no particular axe to grind in this (sometimes) holy war. I've been a Sybase ASE, MySQL, and SQL Server DBA on-and off since for almost a decade (along with application development in C, PHP, PL/SQL, C#.NET, and Ruby). ![]() NOTE that this is a general look at stored procedures not regulated to a specific
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |