RE: SPL DATETIME bug ????
check the explain for both query, I believe they are deferent.
In the first query the optimizer know the range of date at optimization time, but in the second it do not.
In each query he build deferent query plan.
You should force the optimizer to work as the first query using directives.
Uri
-----Original Message-----
From: owner-informix-list@iiug.org
[mailto:owner-informix-list@iiug.org]On Behalf Of TBP
Sent: Thursday, December 30, 2004 11:21 AM
To: informix-list@iiug.org
Subject: Re: SPL DATETIME bug ????
Paul Watson wrote:
> This is all in SPL, Solaris 8, 9.40UC3
>
> select count(*)
> from table1, table2
> where join
> and datetime between "2004-09-29 09:57:58.00"
> AND "2004-12-29 09:57:58.00">
> returns 0.3 seconds
>
> but
>
> let l_dte1 = "2004-09-29 09:57:58.00"
> let l_dte2 = "2004-12-29 09:57:58.00"
>
> select count(*)
> from table1, table2
> where join
> and datetime between l_dte1 and l_dte2>
> returns 6.5 seconds
>
> The query plan is identical in both, all datetimes are y-f2
>
> Any straws gratefully grasped
>
>
>
What is the datatype of l_dte1 and l_dte2?
***********************************************
This Mail Was Scanned By Mail-seCure System in
Matrix Herzeliya
***********************************************
sending to informix-list