{"id":4947,"date":"2012-06-18T11:16:29","date_gmt":"2012-06-18T09:16:29","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4947"},"modified":"2012-06-18T11:16:29","modified_gmt":"2012-06-18T09:16:29","slug":"on-stored-routines-and-dynamic-statements","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/on-stored-routines-and-dynamic-statements","title":{"rendered":"On stored routines and dynamic statements"},"content":{"rendered":"

I very much enjoyed reading Overloading Procedures<\/a> by Michael McLaughlin: good stuff!<\/p>\n

I’m dealing with similar issues in common_schema\/QueryScript<\/a>, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema\/QueryScript<\/em>, with a major addition to the scripting language to put yet even more power at the hands of the programmer\/DBA using simple, clean syntax.<\/p>\n

Still hush hush, the development of that feature touched at the very same issues described in Michael’s post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a few insights of mine:<\/p>\n

Internal array implementation<\/h4>\n

Like Michael, I started by implementing arrays through tables. That is, create a (temporary, in my case) table, wrap it up with a lot of stored routine code, and simulate an array. This array is not yet provided to the user, but is used internally for QueryScript’s own code.<\/p>\n

Well, disappointment here: during load tests on intense structures, such as a foreach<\/a> loop, where each iteration of the loop requires the creation of an array, I found that the current solution does not hold well on busy servers.<\/p>\n

Seemingly, there’s nothing wrong with the creation of a new table every once in a while — and in particular a temporary table. However, I quickly found out that a busy server thrashes the table cache with such intense rate of creation\/dropping of tables. The competition over the table cache mutex becomes intolerable and hogs not only the script’s execution but the entire server’s.<\/p>\n

There’s also the issue of the type of array values — no going around using textual columns, of course, but — how long? A VARCHAR(32767)<\/strong> should be enough for any reasonable implementation, but — how much memory would that consume? Both MEMORY<\/strong> and standard temporary tables (Percona Server<\/em> has that partially resolved<\/a>) use a fixed row format, which means a 32K text is actually allocated in memory even when your value is ‘x’.<\/p>\n

The next release completely rewrites the internal array implementation. How does it work now?<\/p>\n

By avoiding using tables in the first place. To implement arrays now, I chose to use a well formatted text, such that can be easily parsed and manipulated.<\/p>\n

I chose XML format, for which MySQL provides with a couple functions<\/a>. Far from covering real XML manipulation requirements, but with a little effort and hacking, can provide with a reasonable base for work.<\/p>\n

This also means the entire array resides in a single string, and this can be implemented by a User Defined Variable. So — no tables!<\/p>\n

Moreover, there is now no need for stored procedures, since no dynamic SQL is sued. It’s all functions, which makes for an easier access. And to wrap it up — no need to decide about value limits. What fits in the string is what you get.<\/p>\n

This solution is not perfect, either, of course. There’s more CPU used to parse\/manipulate the XML (not a bug problem on my work since arrays are typically small), allocation\/reallocation of memory (any manipulation assigns a new value to the User Defined Variable), and handling of NULL<\/strong>s is more complex.<\/p>\n

Bottom line: overdoing CREATE TABLE\/DROP TABLE<\/strong>, even for TEMPORARY<\/strong>, can be a pain. Avoid it.<\/p>\n

Dyamic SQL<\/h4>\n

You can’t invoke dynamic SQL from within dynamic SQL. There is a wide variety of additional commands that cannot be invoked by dynamic SQL.<\/p>\n

This calls for some attention. If you want to be able to dynamically invoke server commands issued by the user, you either limit the user’s choice of commands, or you find another way.<\/p>\n

QueryScript<\/em> is a classic “interpreted by an interpreter”<\/em> code. I wish there was a better way, but I use stored routines to interpret QueryScript<\/em> text. So the QueryScript<\/em> interpreter takes care not to over do it. While just reading the script’s text and sending it for dynamic invocation is certainly easier to code, it makes for said limitations. Thanks to Roland<\/a>‘s contributions, common_schema<\/em> parses user’s text, analyses and find intended queries, and — sometimes — simply runs it without dynamic invocation. Think nested if-else statement.<\/p>\n

Still, most user’s code is indeed invoked dynamically.<\/p>\n

Parsing by comma<\/h4>\n

If a user provides a comma delimited string, it is very dangerous to parse it via comma. The text ‘one,two,three,four’<\/strong> seems fine, but what about ‘one,two,”hi, dad”‘<\/strong>?<\/p>\n

So common_schema<\/em> does a lot of such parsing. It takes care to properly analyze quoted text. But more than that: since the parsing of such text is costly, common_schema<\/em> translates such text to a well-formed delimited text, such that there is a known, unique delimiter, not to be found within quotes, such that be splitting using said delimiter we get the definite tokenizing of the text.<\/p>\n

To illustrate how it works: it attempts an unlikely single-character delimiter. Consider ‘\\0’<\/strong>. Is ‘\\0’<\/strong> found in the original text? If not, we can once analyze the proper tokenization, then replace appropriate commas with ‘\\0’<\/strong>. Wait, is ‘\\0’<\/strong> found anywhere in the text? Then we try the even more unlikely delimiter ‘\\0\\b’<\/strong>, then the yet unlikelier ‘\\0\\b\\r’<\/strong>, etc.<\/p>\n

QueryScript<\/h4>\n

QueryScript is a way to write stored SQL code “the way it ought to be”: simple, clean, useful, dynamic, flexible, wicked cool. A lot is going on, and I have enough ideas to last a couple years worth of development. No intention to create a heavyweight, full blown language. On the contrary: the language is intended to be simple, lightweight, and provide with easy to access interface to complex SQL operations. Start the journey here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff! I’m dealing with similar issues in common_schema\/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema\/QueryScript, with a major addition to the scripting language to put yet even more […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,82,76,59],"class_list":["post-4947","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-prepared-statements","tag-queryscript","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1hN","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4947","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4947"}],"version-history":[{"count":23,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4947\/revisions"}],"predecessor-version":[{"id":4970,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4947\/revisions\/4970"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4947"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4947"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4947"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}