Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Confusingly slow situation |
Sun, Jun 8 2014 4:08 PM | Permanent Link |
Adam Brett Orixa Systems | The following query is fast (< 2 seconds):
SELECT ProductsID FROM StockCounts WHERE DateDone = (SELECT Max(DateDone) FROM StockCounts) but this is terribly slow (>100 seconds ... not sure how much more!) SELECT ProductsID FROM StockCounts WHERE DateDone = DateLastStockCount() The FUNCTION DateLastStockCount() only does this: CREATE FUNCTION "DateLastStockCount" () RETURNS DATE BEGIN DECLARE Crsr CURSOR FOR Stmt; DECLARE Result DATE; PREPARE Stmt FROM ' SELECT Max(DateDone) as MD FROM StockCounts '; OPEN Crsr; FETCH FIRST FROM Crsr('MD') INTO Result; RETURN Result; I would have expected the 2 versions to run at more or less the same speed. I guess that the Function is re-running for every instance of the WHERE, rather than being cached. Is there any way around this? (EDB 2.17b2) |
Sun, Jun 8 2014 7:11 PM | Permanent Link |
Raul Team Elevate | On 6/8/2014 4:08 PM, Adam Brett wrote:
> The following query is fast (< 2 seconds): > I would have expected the 2 versions to run at more or less the same speed. I guess that the Function is re-running for every instance of the WHERE, rather than being cached. What does execution plan say ? Since the function does not take any params i'd say EDB is not smart enough to optimize it for the 2nd case and does runs it for every row but it is able to optimize the 1st case with the max. Might be worthwhile opening incident report and having Tim take a look. Raul |
Mon, Jun 9 2014 3:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I think your assumption is correct. The parser can determine that (SELECT Max(DateDone) FROM StockCounts) only need to be run once and its value substituted in the query whereas it has no idea of the internals of DateLastStockCount() so it has to run it on a per row basis. Until such time as Tim has a flag DOTHISONCEATTHESTARTOFTHEQUERY, or he strips the sql fom the function and inlines it to the query I can't see things changing. If you have an index on DateDone I'd guess that the execution plan would show an index scan for the first query and a row scan for the second. Roy Lambert |
Mon, Jun 9 2014 8:49 AM | Permanent Link |
Adam Brett Orixa Systems | Right on both Roy
I guess the behaviour do make sense. A function could give different results at different moments of a SELECT so I suppose it should be re-run. I would actually prefer the default to be that such a simple function returns 1 result and is not re-run, otherwise result data might actually be from different periods. No doubt Tim will inform us that this is "ISO Standard Behaviour" ... |
Mon, Jun 9 2014 9:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>No doubt Tim will inform us that this is "ISO Standard Behaviour" ... Much as I like to blame those wonderful people that make up the sql standards committee I don't think I really can in this case Here's a nasty suggestion: you could subclass the query component, parse the sql before its run and obtain values for any of your functions that you know produce a static result and plonk the result into the functions place, or not so nasty move over to using scripts only. Hmmm. I wonder how Tim would take to the suggestion of a new function type - FixedFunction almost identical to normal functions just that they are run once before the query executes and the result substituted for the function? Roy Lambert |
Tue, Jun 10 2014 4:33 AM | Permanent Link |
Adam Brett Orixa Systems | Roy
My solution was to put the whole thing in a stored procedure that returns a cursor. Retrieve the MaxDate into a local var in the procedure, then pass this as a param into a Statement. Should have thought of it before. I'm only now getting used to using Stored Procedures to return data. It is a powerful method as you have all the options of scripts within a mechanism that acts like a query. I still think (in the context of my original thread) that the function should only return a single value though, rather than 1 per returned row ... Adam |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |