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

Adam


The brackets? But more importantly it will never produce a result. FALSE and'd with anything is FALSE.

Roy Lambert

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

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.

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

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

Hi Roy and Tim,

Thanks heaps for your replies! You've both been extremely helpful! (once
again

Cheers

Adam.