Clarification on Dynamic SQL

Hi, Dave Kosenko and Jonathan Leffler have given two contradictory answers regarding the performance of Dynamic SQL, but on further examination, it turns out that we have been using two somewhat different definitions of the term Dynamic SQL. When you establish that there are two different scenarios which can both be called 'Dynamic SQL', and that Jonathan was referring to Scenario 1 and Dave to Scenario 2, the disagreement is simply about 'What is Dynamic SQL' rather than the conclusions. Scenario 1: All prepared statements are dynamic SQL. This simplistic definition includes both the statements where the contents of the prepared string is known in advance and the statements where nothing is known about the statements before the PREPARE is executed. Scenario 2: Nothing is known about the SQL statement before it is prepared. This definition excludes the cases where the details of the statement are known before it is prepared. In this case, the statement must be DESCRIBED in order to find out what type of statement it is, and to find out about the output parameters of a SELECT statement or the input parameters of an INSERT statement. If you were dealing with completely arbitrary SQL statements, as in an ISQL or DB-Access command interpreter, then Scenario 2 applies and Dave's comments about the time taken to handle the memory allocation, etc, definitely apply. On the other hand, if you convert static SQL to dynamic SQL, you typically know the variables etc involved, and you do not need to do any dynamic memory allocation. Jonathan still regards this as dynamic SQL, but Dave doesn't. For example, consider how you might convert the following SELECT: EXEC SQL SELECT a, b, c INTO :a, :b, :c FROM SomeWhere WHERE d = :x; The dynamic SQL version looks like (error checking omitted for brevity): EXEC SQL PREPARE p_name FROM "SELECT a, b, c FROM SomeWhere WHERE d = ?"; EXEC SQL DECLARE c_name CURSOR FOR p_name; EXEC SQL OPEN c_name USING :x; EXEC SQL FETCH c_name INTO :a, :b, :c; EXEC SQL CLOSE c_name; This sequence, with no memory allocation and no analysis using DESCRIBE, is very similar to what happens with the static SQL statement. If you wrap the PREPARE and DECLARE inside an if statement so that it is only executed once, then the code is as efficent as, if not more efficient than, the static SQL it replaces: { static int done = 0; if (done == 0) { EXEC SQL PREPARE p_name FROM "SELECT a, b, c FROM SomeWhere WHERE d = ?"; EXEC SQL DECLARE c_name CURSOR FOR p_name; done = 1; } } EXEC SQL OPEN c_name USING :x; EXEC SQL FETCH c_name INTO :a, :b, :c; EXEC SQL CLOSE c_name; The dynamic SQL version of the code makes three trips into the SQLI library, which is less efficient than one trip (if only because there are multiple calls to locate the cursor in 5.00 and above), but the underlying code must be very similar. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== }From: "David L. Kosenko" <davek@informix.com> }Date: Fri, 19 Jul 1996 01:25:23 -0400 }X-Informix-List-Id: <news.26311> } }Jonathan Leffler wrote: }> }> (1) How do you propose to call dynamic SQL from C? }> (2) ESQL/C is a thin layer which translates SQL code into C. }> }> Unless you are going to get fancy and use internal knowledge of how the }> ESQL/C functions work (and thereby jettison any chance of your code being }> portable to later versions of Informix ESQL/C, let alone anybody else's }> database), you are not going to notice any difference between the two }> types of code. } }I'm afraid I have to mildly disagree with my esteemed and learned }colleague here. While it certainly depends on how one uses the various }statements, the most common approach to non dynamic esql/c is along }the lines of: } }declare vars }prepare statement from string (may be omitted) }declare cursor for prepared statement (could substitute string here) }open/fetch/close } }On the other hand, dynamic esql/c would look like: } }declare structs }get query string }prepare query string }describe prepared string into structs }malloc space for vars in structs }declare cursor for prepared statement }open/fetch/close } }Now I'll grant that in a small application w/o a lot of repetition of this }activity, the difference will wind up being noise. But in larger apps, }and apps where this procedure is repeated over and over, the time spent in }the additional steps can add up. This is especially true in client/server }environments where the lan/wan response time is less than stellar. } }Generally speaking, dynamic esql/c has its place, and gets the job done }well in those cases. If it is not necessary to get the job done, you are }likely to find a *slim* performance gain in going with "standard" (i.e. }non-dynamic) esql/c. } }Also keep in mind that my personal experience is colored strongly by a }great deal of benchmark work, where shaving every millisecond is a }desirable goal. From an typical end-user's perspective, the possible 1-2 }seconds gained by coding one way vs. the other is likely to be }undetectable. In those cases, the "best" approach is arguably the one }with which the programmer is most comfortable. =========================================================================== Date: Thu Jul 18 09:30:00 1996 From: johnl@informix.com (Jonathan Leffler) To: joana@hpvcpja.vcd.hp.com, johnl@informix.com Subject: Re: Dynamic SQL vs. ESQL/C Hi, }Date: Thu, 18 Jul 96 09:11:26 -0700 }From: Joan Armstrong <joana@hpvcpja.vcd.hp.com> } }In message <199607181555.IAA07648@anubis.informix.com> you write: }>(1) How do you propose to call dynamic SQL from C? } } ESQL/C. } }>(2) ESQL/C is a thin layer which translates SQL code into C. } } Yes. I did not express my original question well. The two } options I am looking at are: } 1. ESQL/C in which the sql is known at compile time and hard } coded. } 2. ESQL/C in which the sql is constructed and 'prepare'd at runtime. Aah... The correct magic incantation mentions 'static SQL' as well as 'dynamic SQL', and if you'd done that, all would have been clear on pass 1. } I am not going to call the ESQL/C library functions directly or } anything fancy/stupid like that! Phew! } I expected option 1 to be faster but that is not what my initial } testing is showing. Why? If you look at the code which is generated, you'll see that even static SQL statements are prepared -- the difference is that they are prepared automatically (and prepared every time they are used), whereas with dynamic SQL statements, they can be prepared once and used many times. So, with Informix ESQL/C, you will often get better performance from carefully coded dynamic SQL than from static SQL. This contrasts with systems such as DB2 which actually pre-compile the SQL statements. In those environments, what is left in your code is a@@