| Subject | Re: [firebird-support] Is this a bug? |
|---|---|
| Author | Helen Borrie |
| Post date | 2006-11-20T02:54:21Z |
At 03:57 AM 17/11/2006, you wrote:
non-correlated. Under the stricter distribution rules that query
will probably return an exception in Firebird 2.
In the second query, the subquery is correlated - meaning that the
subquery's search key is linked back to a column in the outer
query. And yes, a correlated subquery will be slower than a
non-correlated one. The non-correlated query runs the subquery once
only, whereas the correlated one runs it once for each row in the A stream.
The non-correlated query is more sensible (you don't need the
equality test) and you can still run it in Fb 2.0, as long as you
distribute things correctly. In this example, the optimizer
encounters ambiguity in the subquery. Fb 1.5 would silently throw a
warning on this syntax and, if you're using an interface that ignores
warnings (as most do) you would not know about it. For Firebird 2,
the chicken has come home to roost. The first query should be:
SELECT a.TYRE_STOCK_LOCATION, a.DESCRIPTION FROM TYRE_STOCK_LOCATIONS a
WHERE
a.TYRE_STOCK = 1
AND a.TYRE_STOCK_LOCATION NOT IN (
SELECT b.TYRE_STOCK_LOCATION FROM TYRE_STORAGES b)
Provided the join columns have indexes, the second query could be
faster if you use a left join instead of the correlated subquery:
SELECT
A.TYRE_STOCK_LOCATION,
A.DESCRIPTION
FROM TYRE_STOCK_LOCATIONS A
left join Tyre_Storages b
on b.Tyre_Stock_Location = a.Tyre_Stock_Location
where
a.TYRE_STOCK = 1
and b.Tyre_Stock_Location is Null
./heLen
>Hi there,They are different queries. In the first one, the subquery is
>
>i have the following two statements that i expect to deliver the same
>results and they did so on Firebird SS-1.5.3.4870.nptl.i686. Yesterday
>we updated to Firebird 2.0.0.12748-0.nptl.i686 and since then the first
>statement returns nothing and the second statement returns the expected
>row, but afaik has a worse execution time:
>
>1:
>SELECT TYRE_STOCK_LOCATION,DESCRIPTION FROM TYRE_STOCK_LOCATIONS WHERE
>TYRE_STOCK = 1 AND TYRE_STOCK_LOCATION NOT IN (SELECT
>TYRE_STOCK_LOCATION FROM TYRE_STORAGES)
>
>2:
>SELECT TYRE_STOCK_LOCATION,DESCRIPTION FROM TYRE_STOCK_LOCATIONS A WHERE
>TYRE_STOCK = 1 AND NOT EXISTS (SELECT TYRE_STOCK_LOCATION FROM
>TYRE_STORAGES B WHERE B.TYRE_STOCK_LOCATION = A.TYRE_STOCK_LOCATION)
>
>Could someone explain that to me?
non-correlated. Under the stricter distribution rules that query
will probably return an exception in Firebird 2.
In the second query, the subquery is correlated - meaning that the
subquery's search key is linked back to a column in the outer
query. And yes, a correlated subquery will be slower than a
non-correlated one. The non-correlated query runs the subquery once
only, whereas the correlated one runs it once for each row in the A stream.
The non-correlated query is more sensible (you don't need the
equality test) and you can still run it in Fb 2.0, as long as you
distribute things correctly. In this example, the optimizer
encounters ambiguity in the subquery. Fb 1.5 would silently throw a
warning on this syntax and, if you're using an interface that ignores
warnings (as most do) you would not know about it. For Firebird 2,
the chicken has come home to roost. The first query should be:
SELECT a.TYRE_STOCK_LOCATION, a.DESCRIPTION FROM TYRE_STOCK_LOCATIONS a
WHERE
a.TYRE_STOCK = 1
AND a.TYRE_STOCK_LOCATION NOT IN (
SELECT b.TYRE_STOCK_LOCATION FROM TYRE_STORAGES b)
Provided the join columns have indexes, the second query could be
faster if you use a left join instead of the correlated subquery:
SELECT
A.TYRE_STOCK_LOCATION,
A.DESCRIPTION
FROM TYRE_STOCK_LOCATIONS A
left join Tyre_Storages b
on b.Tyre_Stock_Location = a.Tyre_Stock_Location
where
a.TYRE_STOCK = 1
and b.Tyre_Stock_Location is Null
./heLen