Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
SQL doing more than it should? |
Fri, May 11 2007 12:15 AM | Permanent Link |
"Adam H." | Hi,
I have the following SQL statement: Select S.ClientID, S.Season, P.Product, G.Grade, B.Bin, 0.0 Received, 0.0 MovIn, 0.0 Outloads, 0.0 Regrades, 0.0 WHouseIn, Sum(Coalesce(S.Nett,0)) Adjustments, 0.0 Shrink From StockTrans S inner join Names N on (N.ID = S.ClientID) left outer join Product P on (P.ID = S.ProductID) left outer join Variety V on (V.ID = S.VarietyID) left outer join Grade G on (G.ID = S.GradeID) left outer join Bins B on (B.ID = S.BinID) where (FALSE) AND (((S.Type <> 'B') and (S.Type <> 'O') and (S.Type <> 'G') and (S.Type <> 'R') and (S.Nett <> 0))) /*Changed to pick up any "extra's" that may exist */ Group by ClientID, Season, Product, Grade, Bin For some reason, this SQL takes 1.6 seconds to execute. The plan of the SQL (see below), indicates that the FALSE statement at the start seems to be joined with the other statement, instead of executing first. Can someone please explain to me why the second line is being executed after the first FALSE statement? Thanks & Regards Adam. ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ Select S.ClientID, S.Season, P.Product, G.Grade, B.Bin, 0.0 Received, 0.0 MovIn, 0.0 Outloads, 0.0 Regrades, 0.0 WHouseIn, Sum(Coalesce(S.Nett,0)) Adjustments, 0.0 Shrink From StockTrans S inner join Names N on (N.ID = S.ClientID) left outer join Product P on (P.ID = S.ProductID) left outer join Variety V on (V.ID = S.VarietyID) left outer join Grade G on (G.ID = S.GradeID) left outer join Bins B on (B.ID = S.BinID) where (FALSE) AND (((S.Type <> 'B') and (S.Type <> 'O') and (S.Type <> 'G') and (S.Type <> 'R') and (S.Nett <> 0))) /**/ Group by ClientID, Season, Product, Grade, Bin Tables Involved --------------- StockTrans (S) table opened shared, has 20516 rows Names (N) table opened shared, has 943 rows Product (P) table opened shared, has 24 rows Variety (V) table opened shared, has 66 rows Grade (G) table opened shared, has 130 rows Bins (B) table opened shared, has 7 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: ClientID Season Product Grade Bin Result set will be ordered by the temporary index created for the grouping WHERE Clause Execution ---------------------- The expression: FALSE = TRUE AND S.Type <> 'B' and S.Type <> 'O' and S.Type <> 'G' and S.Type <> 'R' and S.Nett <> 0 has been rewritten and is PARTIALLY-OPTIMIZED, covers 0 rows or index keys, costs 0 bytes, and will be applied to the StockTrans table (S) before any joins Join Ordering ------------- The driver table is the StockTrans table (S) The StockTrans table (S) is joined to the Names table (N) with the INNER JOIN expression: S.ClientID = N.ID The StockTrans table (S) is joined to the Product table (P) with the LEFT OUTER JOIN expression: S.ProductID = P.ID The StockTrans table (S) is joined to the Variety table (V) with the LEFT OUTER JOIN expression: S.VarietyID = V.ID The StockTrans table (S) is joined to the Grade table (G) with the LEFT OUTER JOIN expression: S.GradeID = G.ID The StockTrans table (S) is joined to the Bins table (B) with the LEFT OUTER JOIN expression: S.BinID = B.ID Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join ================================================================================ >>>>> 0 rows affected in 1.641 seconds ================================================================================ |
Fri, May 11 2007 2:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
The brackets? But more importantly it will never produce a result. FALSE and'd with anything is FALSE. Roy Lambert |
Fri, May 11 2007 2:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< For some reason, this SQL takes 1.6 seconds to execute. The plan of the SQL (see below), indicates that the FALSE statement at the start seems to be joined with the other statement, instead of executing first. >> DBISAM still evaluates the other portions of the expression. If you want to generate an empty result set for a given query, have you considered just using MaxRowCount=0 instead ? It still may involve some execution time, however, since filters, etc. will still be applied to the source tables before rows will be generated in the result set. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 11 2007 7:31 PM | Permanent Link |
"Adam H." | Hi Roy and Tim,
Thanks for your replies. Roy: > The brackets? But more importantly it will never produce a result. FALSE > and'd with anything is FALSE. Indeed, and that's what I'm trying to accomplish (to start off with at least anyway). The problem I face is the following: I have various reports that involve complex queries (which can take some time to execute). The report and query are on a form that I showmodal prior to running the query. The user has the option (on this form), to set date ranges, and other report criteria prior to running the query and printing the report. I use a little procedure I've made to replace certain lines in the SQL to meet the criteria (hence the FALSE line will be removed when the user presses the PRINT button). The reason I have the FALSE line to start with is because I'm using ReportBuilder. By default, when you open up ReportBuilder to change the report, it will normally activate the connected datasets, thus opening up the Query. If I forget to turn this off (or forget to do another number of options to change the default settings), when I save the project, the query is active when the form is created. Thus, when the user chooses to run that report, and the form is created, the Query is opened, and the user must wait for the query to execute before the form shows, and they can choose their criteria, at which time the query is closed, and rerun with the new restraints. The first 'opening' is a complete waste of time, and looks bad too. I've found that I can't seem to always catch myself to recheck the query's active state before saving the form after making any changes to the Report, and thus - the user can get a number of reports/forms that execute the SQL on creation of the form. (In fact, this is rather common across multiple projects with a wide variety of reports). I thought about having an ABORT option on the query - but that aborts the creation of the form also, so that was no good. I then decided to change the SQL in the query just enough to return a zero result set - so that way, if I do accidently leave the query active - it will execute quickly, as the result set will be minimal. I do this by having the default SQL include a "where FALSE" as part of it's where clause. (I was using where 0=1 prior) Tim: > DBISAM still evaluates the other portions of the expression. > > If you want to generate an empty result set for a given query, have you > considered just using MaxRowCount=0 instead ? It still may involve some > execution time, however, since filters, etc. will still be applied to the > source tables before rows will be generated in the result set. Would MaxRowCount=0 be any quicker than a "where False" clause, or would it pretty much do the same thing (being that both will require some execution time)? Is their an SQL command that causes the abortion of a query that I can place within the SQL script, and remove before I want the query to actually run after the user presses the print button? Is their another option that I may have missed that may help with this issue? Thanks for your help! Adam. |
Sat, May 12 2007 4:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
In that case hold the rest of the query after FALSE in a string and add it only after the form has been shown. Roy Lambert |
Mon, May 14 2007 5:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< Would MaxRowCount=0 be any quicker than a "where False" clause, or would it pretty much do the same thing (being that both will require some execution time)? >> It could still result in some delays due to the other filter conditions. If you want a guaranteed instantaneous response time, then Roy's idea is the best - just don't include a WHERE clause until you actually want to run the query and use MaxRowCount=0 along with that idea. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, May 14 2007 10:01 PM | Permanent Link |
"Adam H." | Hi Roy and Tim,
Thanks heaps for your replies! You've both been extremely helpful! (once again Cheers Adam. |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |