mdy function runs slowly
hello
I have a sql script like this;
select b.istno, b.yil, b.ay, b.gun,777, -777, hadise, basla, bitis ==20
from kkl222 b =20
where b.istno =3D 5974 and b.yil =3D 1969 and =
=20
(basla between 1400 and 2059 or =20
bitis between 1401 and 2100 or basla < 1401 and bitis > 2100) and =
=20
not exists (select * from kkl221 bb =
=20
where b.istno=3Dbb.istno and =
=20
b.yil=3Dbb.yil and b.ay=3Dbb.ay and b.gun=3Dbb.gun ) =
=20
and =
=20
not exists (select * from kkl221 bb =
=20
where b.istno=3Dbb.istno and =
=20
mdy(b.ay,b.gun,b.yil)=3Dmdy(bb.ay,bb.gun,bb.yil)-1 and =
=20
bb.y_mik07=3D7777 and =
=20
bb.y_mik14=3D7777 and =
=20
bb.y_mik21=3D7777) =
but it runs very slow.
when I cancel the mdy row using --, it runs very fast.
why mdy function doesn't run properly?
informix verision is 7.31 and operating system is sco openserver 5.04
thanks.
mustafa sert
The problem is that the MDY() is being called from the NOT EXISTS sub-query. It
must be run twice for EVERY RECORD in the kk1221 table (once for that table's
record and once against the correlated record from the outer query). In
addition it is a correlated sub-query so it must be run for every row that
satisfies the other criteria of the outer query. Could be millions of function
calls (I do not know the cardinality of the tables). Try turning the NOT EXISTS
sub-query into an ANSI style outer join the matches rows that do not join.
Something like:
SELECT a.*
FROM tab_a a LEFT OUTER JOIN tab_b b
ON a.key = b.key and mdy(a.mo, a.da, a.yr) = mdy(b.mo, b.da, b.yr)
WHERE b.key IS NULL AND ....;
This has the advantage of only running mdy() once for each row of 'tab_a' and
once for each row of 'tab_b' that matches that row in 'tab_a'. Many times fewer
function calls.
Art S. Kagel
----- Original Message -----
From: Mustafa Sert <msert@meteor.gov.tr>
At: 3/16 8:10
> hello
> I have a sql script like this;
>
>
> select b.istno, b.yil, b.ay, b.gun,777, -777, hadise, basla, bitis => =20
> from kkl222 b =20
> where b.istno =3D 5974 and b.yil =3D 1969 and =
> =20
> (basla between 1400 and 2059 or =20
> bitis between 1401 and 2100 or basla < 1401 and bitis > 2100) and =
> =20
> not exists (select * from kkl221 bb =
> =20
> where b.istno=3Dbb.istno and =
> =20
> b.yil=3Dbb.yil and b.ay=3Dbb.ay and b.gun=3Dbb.gun ) =
> =20
> and =
> =20
> not exists (select * from kkl221 bb =
> =20
> where b.istno=3Dbb.istno and =
> =20
> mdy(b.ay,b.gun,b.yil)=3Dmdy(bb.ay,bb.gun,bb.yil)-1 and =
> =20
> bb.y_mik07=3D7777 and =
> =20
> bb.y_mik14=3D7777 and =
> =20
> bb.y_mik21=3D7777) =
>
>
> but it runs very slow.
> when I cancel the mdy row using --, it runs very fast.
>
> why mdy function doesn't run properly?
> informix verision is 7.31 and operating system is sco openserver 5.04
>
> thanks.
> mustafa sert
Over and
above what Art suggests, why on earth are you storing the
decomposed dates in 3 columns instead of in a single column?
Then, instead of writing:
mdy(b.ay,b.gun,b.yil) = mdy(bb.ay,bb.gun,bb.yil)-1
You'd simply write:
b.date_col = bb.date_col - 1
Similarly, for:
b.yil = bb.yil and b.ay = bb.ay and b.gun = bb.gun
You'd write:
b.date_col = bb.date_col
This involves no explicit function evaluation at all.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
"ART KAGEL, ...." <KAGEL@bloomberg.net>
Sent by: forum.subscriber@iiug.org
03/16/2004 08:32 AM
To
ids@iiug.org
cc
Subject
Re: mdy function runs slowly [2681]
The problem is that the MDY() is being called from the NOT EXISTS
sub-query. It
must be run twice for EVERY RECORD in the kk1221 table (once for that
table's
record and once against the correlated record from the outer query). In
addition it is a correlated sub-query so it must be run for every row that
satisfies the other criteria of the outer query. Could be millions of
function
calls (I do not know the cardinality of the tables). Try turning the NOT
EXISTS
sub-query into an ANSI style outer join the matches rows that do not
join.
Something like:
SELECT a.*
FROM tab_a a LEFT OUTER JOIN tab_b b
ON a.key = b.key and mdy(a.mo, a.da, a.yr) = mdy(b.mo, b.da, b.yr)
WHERE b.key IS NULL AND ....;
This has the advantage of only running mdy() once for each row of 'tab_a'
and
once for each row of 'tab_b' that matches that row in 'tab_a'. Many times
fewer
function calls.
Art S. Kagel
----- Original Message -----
From: Mustafa Sert <msert@meteor.gov.tr>
At: 3/16 8:10
> hello
> I have a sql script like this;
>
>
> select b.istno, b.yil, b.ay, b.gun,777, -777, hadise, basla, bitis => =20
> from kkl222 b =20
> where b.istno =3D 5974 and b.yil =3D 1969 and =
> =20
> (basla between 1400 and 2059 or =20
> bitis between 1401 and 2100 or basla < 1401 and bitis > 2100) and
=
> =20
> not exists (select * from kkl221 bb =
> =20
> where b.istno=3Dbb.istno and =
> =20
> b.yil=3Dbb.yil and b.ay=3Dbb.ay and b.gun=3Dbb.gun ) =
> =20
> and =
> =20
> not exists (select * from kkl221 bb =
> =20
> where b.istno=3Dbb.istno and =
> =20
> mdy(b.ay,b.gun,b.yil)=3Dmdy(bb.ay,bb.gun,bb.yil)-1 and
=
> =20
> bb.y_mik07=3D7777 and =
> =20
> bb.y_mik14=3D7777 and =
> =20
> bb.y_mik21=3D7777) =
>
>
> but it runs very slow.
> when I cancel the mdy row using --, it runs very fast.
>
> why mdy function doesn't run properly?
> informix verision is 7.31 and operating system is sco openserver 5.04
>
> thanks.
> mustafa sert