DaveWentzel.com All Things Data
Bind Variables, Prepared Execution, ad-hoc SQL statements, and Performance...an allegory
And The Developer went to the Oracle and asked, "If programming in Delphi, should I use bind variables? What if I'm programming in Cleveland? Or Visual Basic?"
Tom Kyte, aka the Oracle, pondered the question for 3 days while sitting over a crack on a rock, inhaling noxious volcanic fumes. He then, suddenly, responded. "You will bind never performance will suffer."
Immediately the Oracle fainted from the fumes.
The Developer tried to rustle him to consciousness. He did not understand the Oracle's prophesy. Should the missing comma come BEFORE or AFTER the word "NEVER" ?
The Developer could not wait for the Oracle to awaken, the Kanban card was already in the "blocked" state for too long and The ScrumMaster was angry. What should he do?
At that moment Bill Gates appeared in a burning bush.
The Developer: "Father, are bind variables good?"
"Son, we haven't the concept. The closest thing is prepared execution. As with all things my son, imbibe in moderation, for thy performance cup shall never runneth over."
The burning bush disappeared. Still The Developer was confused.
The Developer came across The Great Google and structured his query thusly, "When sending a repeated SQL statement to my RDBMS, should I use a cacheable, parameterized statement?"
He clicked "I'm Feeling Lucky"...because, well, he was.
Stack Overflow responded immediately with The Commandments:
- "Thou shalt not commit non-parameterized querying." Regardless of platform…prepared execution/bind variables in ad hoc SQL statements are thy friend. On modern RDBMSs every single call should be parameterized. Always. No exception. Ever.
- "Remember to bind, to keep Oracle holy." It is a PITA (pain in thine ass) to return result sets from Oracle stored procedures/packages which is why bind variables are the cornerstone of good performance on that platform.
- "Honor thy SQL Server Stored Procedure." They are generally much better than adhoc parameterized statements for a litany of reasons.
- No need to pollute thine query cache with thy garbage singleton statements.
- Stored procs are easier to change (sql can be recompiled on the fly with any kind of fix…I don't need to monkey with externalized sql embedded in compiled Java.
- I can use lots of different kinds of "hinting" that I can't use in ad hoc sql…changing isolation levels, locking paradigms, plan guides, etc.
- It's just good design. Best to keep data logic at the data tier.
- Decrease the "network effect"…a stored proc call will always be more compact than an equivalent parameterized ad hoc statement. Think a couple bytes don’t matter? Try developing using "the cloud" with SQL Server on EC2 or Azure without using stored procs. The network effect literally kills performance.
- Your friendly neighborhood DBA can't "profile" when you need help. "All I see is that you ran sp_execute 15, 1417. Sorry I can't help you. I can't find a stored procedure named "15,1417" in my database. "
- If you use stored procs you can always "bear false witness against thy DBA." (Seriously. Just say, "I don't know why that stored proc isn't performing, isn't performance the DBA's job?" It works flawlessly).
4. "Thou Shalt not be Chatty, but should remember to be Chunky, and keep it holy." Regardless of platform, if you are going to send the same SQL statement a gazillion times a minute (say because you are too lazy to "batch up" your INSERT statements) then prepared execution isn't going to help much. The performance benefits of a prepared INSERT statement may save a millisecond, but the real killer is the network chattiness, which can be 16, or sometimes even 50, milliseconds per call. Best to be chunky.
<TODO insert picture of chunky 'ol Dave Wentzel here. Until then, here's another "Chunky">
(a little Thursday afternoon blasphemy for you.)