Dynamic SQL vs. ESQL/C

Dynamic SQL vs. ESQL/C

Post by Joan Armstro » Sun, 31 Dec 1899 09:00:00



Hello,

Does anyone have *real data* on the performance of dynamic sql (called
from C) vs. ESQL/C?

I'd be most interested in tests run on an HP9000 with Online 5.x.

Thanks,
--Joan Armstrong

--

Dogs: Copper Canyon's Belle Starr (Frannie) (Aussie) JHD NA NAC NGC NJC RSN
RGN RJN CGC; Zoie (AussieXAirdale); Wind Dance Nick of Time (Nick) (Aussie)

 
 
 

Dynamic SQL vs. ESQL/C

Post by Joan Armstro » Sun, 31 Dec 1899 09:00:00


        Jonathan,

        Thanks for your reply.


Quote:>(1) How do you propose to call dynamic SQL from C?

        ESQL/C.

Quote:>(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.

        I am not going to call the ESQL/C library functions directly or
        anything fancy/stupid like that!

        I expected option 1 to be faster but that is not what my initial
        testing is showing.  Why?

        Thanks again,
        --Joan

 
 
 

Dynamic SQL vs. ESQL/C

Post by Alexander J. Os » Sun, 31 Dec 1899 09:00:00



> Does anyone have *real data* on the performance of dynamic sql (called
> from C) vs. ESQL/C?

Unfortunately, I don't have real data since my OnLine 7.10.UC2 has decided to
give me segmentation faults, but what you may wish to do is compare the C code
generated by ESQL/C in both cases; my guess is they are very much the same
(that is, EXEC SQL statements get translated into dynamic SQL C code).

But please, if someone knows for sure, correct me.  I'm just going on cursory
inspection of the generated C code.
--
Alexander J. Oss
Computer Systems Manager
Films for the Humanities & Sciences

 
 
 

Dynamic SQL vs. ESQL/C

Post by Jonathan Leffl » Sun, 31 Dec 1899 09:00:00


(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.

Yours,


>Date: 17 Jul 1996 19:49:03 GMT
>X-Informix-List-Id: <news.26257>

>Does anyone have *real data* on the performance of dynamic sql (called
>from C) vs. ESQL/C?

 
 
 

Dynamic SQL vs. ESQL/C

Post by Jonathan Leffl » Sun, 31 Dec 1899 09:00:00


Hi,

>Date: Thu, 18 Jul 96 09:11:26 -0700


>>(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.

Quote:>    I am not going to call the ESQL/C library functions directly or
>    anything fancy/stupid like that!

Phew!

Quote:>    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,

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...

 
 
 

Dynamic SQL vs. ESQL/C

Post by David L. Kosenk » Sun, 31 Dec 1899 09:00:00



> (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.

--
Dave Kosenko, Informix Professional Services
****************************************************************************
While it is true that there is more than one way to skin a cat, the cat
himself generally fails to appreciate the differences.

 
 
 

Dynamic SQL vs. ESQL/C

Post by Scott Ella » Sun, 31 Dec 1899 09:00:00



>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

 
 
 

Dynamic SQL vs. ESQL/C

Post by Bodo Wipperman » Sun, 31 Dec 1899 09:00:00


Hello folks !

I have made the experience, that in almost all situations, where
a SQL-Statement is used many times the dynamic SQL is much faster
than the "static" SQL. I wrote an Application which is inserting
and updating a lot of rows ( about 1 Million ) in a batch job.
The first Version was a "static" one and it was not very fast.
Then i tried dynamic SQL and the same Application was about
2 times faster.

So my conclusion is , wherever you have to use one Statement
serveral times , you should prefer dynamic SQL.

The problem is on the other hand it is more difficult to port this
ESQL/C-program to another Database ( Oracle,DB/2,...).

( I use Informix 7.12 on IBM RS/6000 )

und Tsch?
  from Germany
Bodo

--
my home is my castle

 
 
 

Dynamic SQL vs. ESQL/C

Post by Brenda Ree » Sun, 31 Dec 1899 09:00:00


<snip>

Quote:

> This contrasts with systems such as DB2 which actually pre-compile the SQL
> statements.  

<snip>
Oracle and Sybase pre-compile too...
 
 
 

Dynamic SQL vs. ESQL/C

Post by William Harr » Sun, 31 Dec 1899 09:00:00



Quote:>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

Let me offer up my own disagreement in this thread of continuing mild
disagreements.  :-)

If you're really concerned about performance, the case I outline below is the
most common use of dynamic ESQL.  Only in exceptional circumstances does one
get a completely fresh query string or completely different query criteria
every time a particular query is done.  Most of the "dynamic" SQL I've
encountered knows what the query looks like, is building a query that may
differ in what particular fields need to be selected or what query criteria has
been specified, but once that information is determined remains pretty static
for the duration of the application.  It can can do something like this:

if(first_time_through)
    {
    declare structs
    get query string
    prepare query string
    describe prepared string into structs
    malloc space for vars in structs
    declare cursor for prepared statement
    first_time_through = FALSE
    }

open/fetch/close

Performance of something like this is, over the long run, going to be almost
indistinguishable from the performance of a similarly coded static SQL
approach, is it not?  Both dynamic and static can be coded to do the cursor
setup once.
--

CARS Information Systems
4000 Executive Park Drive
Cincinnati, OH  (USA) 45241

 
 
 

1. Static vs. Dynamic ESQL

 dynamic SQL may be hard for your impact analysis tool to deal with - I
 have also seen it make life harder from the code configuration
 management point of view.                                       Colin.

______________________________ Reply Separator _________________________________
Subject: Re: Static vs. Dynamic ESQL

Date:    03/09/97 12:48


Interesting question.  Some years back when we wrote a bunch of stuff
using dynamic ESQL, we got much wagging of fingers and dire warnings
about how slow dynamic SQL was.  We didn't have a choice, so I pretty
much ignored them.  Now, I sort of wonder what the fuss was all about.
Certainly you give up the ability to use REPEATED, which is a BIG win
when used in the right places.  And, you have an extra server round-trip
when you PREPARE a statement to fill in all the SQLDA poop.  But I don't
see that performance should be direly, drastically worse than
static SQL.  Yes, there will be some impact.

You might look at the OpenAPI too if you're doing something that
needs a generic interface.  If it had existed 6 years ago I would
have used it instead of dynamic SQL.

It's been a while since I did anything serious (new) with embedded SQL,
so newer/more fact-based responses are welcome.

--
Karl Schendel            Phone: (412) 963-8844
Telesis Computer Corp      Fax: (412) 963-1373


2. FREE DA NEWSLETTER - ISSUE 4.0

3. Dynamic Execute Statement vs. Passing Dynamic SQL

4. ISNUM, ISINT - incorrect behaviour

5. ESQL 5.07.UC1 vs ESQL 9.40.UC2

6. Duplicate transaction log

7. ESQL 5.02 vs. ESQL 7.2

8. prohibit deletion of certain records in table

9. Need help on dynamic SQL of esql

10. Question about Dynamic SQL in ESQL

11. Dynamic sql in esql/c

12. Using dynamic sql parameters in ESQL/C

13. dynamic SQL in ESQL 7.22