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?

--
Regards,

Fred Chateau
http://members.home.net/fchateau/

2. Case Sensitive joins

3. Dynamic filter vs static filter...

4. OLE Objects import to SQL Server

5. STATIC vs. DYNAMIC SQL and ODBC

6. DB User-interface in SQL Server 7.0

7. Static vs Dynamic Recordsets

8. Help ?? - USE OF DEFAULTS IN AN RDBMS IMPLEMENTATION

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