Static vs. Dynamic ESQL

Static vs. Dynamic ESQL

Post by Colin.MacKel.. » Thu, 04 Sep 1997 04:00:00

 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

Quote:> Subject: Static vs. Dynamic ESQL

> As someone who knows very little about the relative benefits of
> using static versus dynamic ESQL statements under Ingres, is there
> anyone out there who can shed some light on the tradeoffs?

> The reason I ask is the project I'm working on has a huge amount
> of static ESQL ... my projectlet is new stuff and there's a strong
> appeal to using dynamic statements for pure flexibility.  Do I
> lose anything by doing so?  Eg. performance, etc.

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


1. Dynamic vs Static Stored Procedures

I would appreciate it if someone would explain what exactly is the
difference between a dynamic and a static stored procedure.

Specifically, if I am using a stored procedure which is basically nothing
more than a select statement in which I submit values for the where clause,
is this a dynamic procedure? If so, is there any advantage to it over
submitting the select statement directly in an ADO conn.execute directive?


Fred Chateau

2. Case Sensitive joins

3. Dynamic filter vs static filter...

4. OLE Objects import to SQL Server


6. DB User-interface in SQL Server 7.0

7. Static vs Dynamic Recordsets


9. performance of dynamic vs. static SQL

10. Dynamic SQL Vs Static SQL using Pro*C..

11. Dynamic vs. Static

12. Dynamic vs. Static Hashed Files

13. Dynamic Files vs Static Hashed - Performance Issues