Re: Dynamic SQL vs. ESQL/C

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