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