Clarification on Dynamic SQL
Jonathan Leffler
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 (jo...@informix.com) #include <disclaimer.h>
===========================================================================
}From: "David L. Kosenko" <da...@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: jo...@informix.com (Jonathan Leffler)
To: jo...@hpvcpja.vcd.hp.com, jo...@informix.com
Subject: Re: Dynamic SQL vs. ESQL/C
Hi,
}Date: Thu, 18 Jul 96 09:11:26 -0700
}From: Joan Armstrong <jo...@hpvcpja.vcd.hp.com>
}
}In message <1996071815...@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
reference to the precompiled statement, which is stored in the database
itself (similar to a stored procedure in some ways, but DB2 at least
actually generates machine code to execute the query, whereas Informix uses
an interpretive scheme). If the pre-determined query plan is still valid,
it is used at run-time; if not, then the statement is automatically
recompiled and the new version is used. Note that a query plan is not
recompiled just because there may be a better query plan now than when the
compilation was done; it is only recompiled because something that was
being relied on (eg an index) has been changed. In such systems, the
static SQL statements have less overhead at runtime and dynamic SQL does
work more slowly.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
PS: If you aren't sure about all this, use a monitor to track the
statements going back and forth between the ESQL/C application and the
Engine and see what is happening...
===========================================================================
}Date: Sat, 20 Jul 1996 01:53:24 -0400
}From: "David L. Kosenko" <da...@informix.com>
}To: Jonathan Leffler <jo...@informix.com>
}Subject: Re: Dynamic SQL vs. ESQL/C
}
}Jonathan Leffler wrote:
}> I hope you've seen my second answer as well as the first one which you
}> quote. For some reason, I didn't see that the questioner was asking about
}> dynamic vs static SQL. I agree with most of what you say, though most of
}> what I said can also apply -- it depends on the context in which the
}> dynamic SQL is used.
}
}Of course. What is curious is that the fellow seems to have found dynamic
}to be performing better for him - if it is in fact a large improvement I'd
}be very curious as to why (and exactly how he is doing the different
}approaches).
}
}> 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.
}
}I think we are each dealing with different assumptions. To my mind, if
}one is using dynamic sql, then one knows nothing about the query in
}question. By dynamic sql, I don't mean just doing a prepare of a string,
}but also doing the necessary describe, alloc, etc. To my mind, a prepare
}is not necessarily dynamic sql, as we often prepare static strings to save
}on parse time (prepare once & use the prapred statement many times).
}
}>[snip]
}
}> This sequence, with no memory allocation and no analysis using DESCRIBE, is
}> very similar to what happens with the static SQL statement, I think you'll
}> agree.
}
}Certainly without the overhead of a descibe and malloc sequence, there is
}no practical different in the two methods, and thus no appreciable
}performance difference should be detectable. Again, I make the implicit
}assumption that dynamic sql == describe/malloc. As with any assumption,
}it has its pitfalls...
===========================================================================
}Date: Sat, 20 Jul 1996 02:18:02 -0400
}From: "David L. Kosenko" <da...@informix.com>
}To: Jonathan Leffler <jo...@informix.com>
}Subject: Re: Dynamic SQL vs. ESQL/C
}
}Jonathan Leffler wrote:
}> Should we post something to c.d.i to explain why two of the more visible
}> Informix people seem to be disagreeing but really aren't?
}
}I'm a bit tied up today, but if you have the time, by all means post
}something.
===========================================================================
}From: c...@wetware.com (Scott Ellard)
}Date: 19 Jul 1996 09:00:13 GMT
}X-Informix-List-Id: <news.26346>
}
}In article <31EF1C...@informix.com> da...@informix.com writes:
}>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:
}
}I'm afraid I have to mildly disagree with my esteemed and learned
}colleague Dave. Your answer is colored somewhat by the Informix view
}of the world with respect to static and dynamic SQL. Relative to the
}more classic definitions of those terms, all Informix SQL via ESQL/C
}is dynamic.
}
}The classic definition of static SQL includes the notion of completely
}parsing, verifying the statement, creating an access plan etc. during
}the compile process. Usually some sort of identifiers are assigned and
}registered in the database. Upon execution of the application, the
}identifiers are used. This is similar to the state that is reached
}after a successful prepare during Informix's always dynamic mode of
}operation. The pure static handling described above is implemented
}on DB2 on IBM mainframes for example.
}
}Of course Dave is right that the use/non-use of descriptors vs. host
}variables etc does have some impact, but again these issues are still
}within the domain of dynamic sql.
}
}Scott
===========================================================================