Dynamic query vs dynamic where clause

P

pinne65

Member
Hello,

I know the table I need to query in advance but not the fields. So I read the ABL pgming books about dynamic queries. In essence they say that you'll take a performance hit by using dynamic queries, of course. But, if you can get away with having just the where clause dynamic it will not be as bad.

The way this is done is by assigning a handle to the static query and opening it just as a dynamic query. How can this be faster? Or am I missing something here???

DEFINE QUERY crapQry FOR crap.

DEFINE VARIABLE hQry AS HANDLE NO-UNDO.

hQry = QUERY crapQry:HANDLE

hQry:QUERY-PREPARE("FOR EACH crap WHERE type = 'baja'").

hQry:QUERY-OPEN().
...........

TIA!
 
Premature optimization is the root of all evil. Ok, not really but it does come close.

Anyhow... does it really matter? If you cannot do it at all with a static query then how can you compare performance?
 
Dammit,

I though 17 was the root ;)

Tom, I'm not sure I'm following:

" If you cannot do it at all with a static query then how can you compare performance?"






Follow up question: So the QUERY-PREPARE method is the way to do it, (dynamic WHERE), even for static queries?
 
Darned if I know. I can't quite figure out what you're trying to do...

You said: "I know the table I need to query in advance but not the fields." From this proceeds a discussion of using dynamic queries. From that I think I'm hearing "I cannot do this with a static query, thus I need to use a dynamic query. But a dynamic query might be 'too slow', how can this be faster than a static query?"

I may be misreading this but it seems to me that you have determined that you cannot do the job at all with a static query right? I don't know enough about what you're really trying to do to argue otherwise so I'll accept that for now.

Since you must use a dynamic query letting unseen performance worries cloud your judgment at this point is "premature optimization".

Beyond that I have no idea where you see anything that would translate as "if you can get away with having just the where clause dynamic it will not be as bad". This really doesn't make any sense to me and I seriously doubt that it (whatever "it" is) is true.

Yes, dynamic queries can be somewhat slower than their static counter-parts. But if you cannot solve your problem with a static query it hardly matters, that a retrospectively constructed hypothetical static query would, in theory, have been slightly faster.

How much slower are dynamic queries? It depends. Usually "not much". Occasionally "enough to notice". Rarely "a lot". Basically most of the time nobody is going to notice. If a significant result set is being returned you can adjust some of the queries attributes and/or your coding style to work with different size "chunks". You can also make sure not to incur temp file IO by setting the forward-only attribute to "yes" (if this is valid for your usage...). Other than that it is pretty much the same rules and techniques that you would use for a static query. Proper bracketing and good index definitions are the main keys.

Does that help?
 
Tom,

Thanks for the extensive answer. Now I must for my own sanity dig up the section in the ABL manual where they were talking about static queries w dynamice WHEREs vs completely dynamic queries.

Basically I just want to allow query access to a set table through a web service and allow the caller to use one or more fields for row selection. Hence the desire to use a "dynamic where". The table name is fixed.

BRB...
 
Pheew,

found it. Don't know if I'm taking it out of context or what.

From the OE Dev ABL handbook 10.1B, sec 19-2, Using Dynamic Queries and Buffers:

"It’s important to note that these dynamic features are not meant to replace all the static
statements in earlier versions of ABL (Advanced Business Language), or to be the standard way
to write code for all new applications. There is a significant performance cost to using dynamic
data management statements, which can be well worth the cost when the flexibility is needed
but an unnecessary expense and complication, otherwise. If your tables, fields, and queries are
known and fixed when you write the procedure, then there is no need to use dynamic statements
to manage them. Likewise, if you have a static object, such as a query, on a specific table or
tables but need to modify just the WHERE clause at run time or access some of its attributes and
methods, you can easily do this through a handle attached to the static object. This keeps your
code simpler and more efficient."
 
IMHO that passage is mixing two concepts, simplicity and efficiency, as if they are one while doing justice to neither.
 
You must log in or register to reply here.
Back
Top