Any suggestions to speed up a script

PREPARE ResultStmt FROM
'UPDATE InProg SET LoggedOn =
(SELECT COUNT(*) FROM UsersLog X WHERE
 (X.StartDateTime BETWEEN AtTime AND EndPoint)
  OR
 (X.FinishDateTime BETWEEN AtTime AND EndPoint)
  OR
 (X.StartDateTime < AtTime AND X.FinishDateTime > EndPoint)
 )';

Executing it takes c15 seconds but a query & subselect

PREPARE ResultStmt FROM
'SELECT * FROM
(SELECT StartPoint AS AtTime,
(SELECT COUNT(*) FROM UsersLog X WHERE
 (X.StartDateTime BETWEEN StartPoint AND EndPoint)
  OR
 (X.FinishDateTime BETWEEN StartPoint AND EndPoint)
  OR
 (X.StartDateTime < StartPoint AND X.FinishDateTime > EndPoint)
 ) AS LoggedOn
FROM InProg) R WHERE R.LoggedOn >= ?';

is almost instant.

I need the temporary table version so I can export the data to csv.

The other parts of the script are nice and fast. I have all the indices I can to test with and it makes no difference. It may be that the subselect is ignoring them.

Roy Lambert

Roy

Could you declare a cursor in the script, run the (fast) query, FETCH the result into a variable, then run the UPDATE using the variable as a param.

The UPDATE would then just read:

' UPDATE <Table> SET <field> = ? '

Which I am guessing would be v fast.

--

My guess for the reason for this is I think that EDB has to evaluate the value of the SELECT for every row, so it will be running a LOT of times.

Adam


>Could you declare a cursor in the script, run the (fast) query, FETCH the result into a variable, then run the UPDATE using the variable as a param.
>
>The UPDATE would then just read:
>
>' UPDATE <Table> SET <field> = ? '
>
>Which I am guessing would be v fast.
>
>--
>
>My guess for the reason for this is I think that EDB has to evaluate the value of the SELECT for every row, so it will be running a LOT of times.

It may just be me being thick, but since the value of COUNT(*) is going to change for each row in InProg I would still need to run the fast query 1440 times which (to my brain at least) is what I need to do for the query as well. I'm probably missing something very simple

Roy