Performance differences in static and dynamic queries ??

[bigwill]

Hi all.

I am having some speed problems when creating queries with 'OR' inside it.
first some background info. I am searching in a table with many records(over 2 mill) that has many columns (over 50) and of course many indexes.

To be able to reproduse this slow performace i have written 2 small programs. The dynamic query uses over 5 sec, and the static query .01 sec. Why the big difference in speed ???

Dynamic code (This takes 5 seconds):

def temp-table tt no-undo
field id as int.
def var queryForTables as char no-undo.
def var qh as handle no-undo.
def var queryToRun as char no-undo.
def var i as int no-undo.
def var dtstart as datetime no-undo.
def var dtend as datetime no-undo.

/* Start debugging */
dtstart = now.

create query qh.
qh:ADD-BUFFER(BUFFER Activity:HANDLE).

queryToRun = 'for each Activity no-lock '
+ ' where Activity.a-contr1 = "SomeValue " '
+ ' or ( Activity.a-brand = "apple" and QlowestRequisitionZone = 2 ): '.


/* Run query and build temp-table to return*/
qh:query-prepare(queryToRun).
qh:query-Open().

qh:get-last().
do while available Activity:
create tt.
assign tt.id = activity.a-actno.

assign i = i + 1.
if i = 250 then
leave.

qh:get-prev().
end.

/* Close query */
qh:query-close().
delete object qh.

/* end debugging */
dtend = now.

Static code (This takes .01 seconds):
def temp-table tt no-undo
field id as int.

def var i as int no-undo.
def var dtstart as datetime no-undo.
def var dtend as datetime no-undo.

dtstart = now.
for each Activity no-lock
where Activity.a-contr1 = "telenorfly"
or ( Activity.a-brand = "apple" and QlowestRequisitionZone = 2 ):

create tt.
assign tt.id = activity.a-actno.

i = i + 1.

if i = 250 then
leave.
end.
/* end debugging */
dtend = now.

 

[RealHeavyDude]

Hi all.

/* Run query and build temp-table to return*/
qh:query-prepare(queryToRun).
qh:query-Open().

qh:get-last().
do while available Activity:
create tt.
assign tt.id = activity.a-actno.

assign i = i + 1.
if i = 250 then
leave.

qh:get-prev().
end.

/* Close query */
qh:query-close().
delete object qh.

Your dynamic and static queries are not the same. Is there any specific reason as to why you fetch the last row of the query result first and then use get previous as I don't see a BY in your FOR EACH statement. Furthermore you should use the INDEXED-REPOSITION option on the query because otherwise the query will fetch everything between the current and the desired position ....

Heavy Regards, RealHeavyDude.

 

[medu]

qh:get-last().
do while available Activity:
create tt.
assign tt.id = activity.a-actno.

assign i = i + 1.
if i = 250 then
leave.

qh:get-prev().
end.

can you explain why do you need to go backward on that? try if going forward only makes any difference...

qh:forward-only = true.
qh:query-open().

do while qh:get-next(no-lock) and i lt 250:
   i = i + 1.
   ...

end.

 

[medu]

Your dynamic and static queries are not the same. Is there any specific reason as to why you fetch the last row of the query result first and then use get previous as I don't see a BY in your FOR EACH statement. Furthermore you should use the INDEXED-REPOSITION option on the query because otherwise the query will fetch everything between the current and the desired position ....

Heavy Regards, RealHeavyDude.

now see, that's why google added such a nice feature like 'update conversation'

 

[bigwill]

Thanks for the reply

Since this is a search program i thought it would be smart to start searching from the newest record and backwards (i leave at 250 recs), since this is probably the record the user is interested in.

If i rewrite and uses get-first - get-next it speeds up. Hmmmmm. Can someone explain the INDEXED-REPOSITION to me since i have never used it before .

 

[medu]

Since this is a search program i thought it would be smart to start searching from the newest record and backwards (i leave at 250 recs), since this is probably the record the user is interested in.

then just add something like 'by [some date field] descending' at the end, really use forward-only and forget about reposition in this case, and don't forget the no-lock option on get-next()