Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Script SQL / PSM Speed |
Fri, Mar 15 2019 7:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Mar 15 2019 10:30 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Fri, Mar 15 2019 2:09 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |