Re: SPL DATETIME bug ????

This performance fall off is only on datetimes, if the where clause uses integers then it's fine. If I drop the datetime select into the exec blade the performance is back to 0.3 Art S. Kagel wrote: > Paul Watson wrote: > > The difference is that effectively the second query/spl has replaceable > parameters so it can only be partially optimized at SPL compile time. > The final optimization is performed at execution time which is likely > the extra run time. Fortuneately you are running 9.4, in 7.xx queries > like this were optimized at compile time but the resulting query plan > was often sub-optimal since the actual runtime values for the replacable > parameters could not be determined. > > Art S. Kagel > >> 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 >> >> >>

Paul Watson wrote: > This performance fall off is only on datetimes, if the where clause uses > integers then it's fine. If I drop the datetime select into the exec > blade the performance is back to 0.3 Weird. One would expect that it would not make a difference what the datatype was. I know someone asked, but I did not see and answer, are l_dte1 and l-dte2 DATETIMES or strings? And if you change from one to the other does it make any difference? Art S. Kagel > Art S. Kagel wrote: > >> Paul Watson wrote: >> >> The difference is that effectively the second query/spl has >> replaceable parameters so it can only be partially optimized at SPL >> compile time. The final optimization is performed at execution time >> which is likely the extra run time. Fortuneately you are running 9.4, >> in 7.xx queries like this were optimized at compile time but the >> resulting query plan was often sub-optimal since the actual runtime >> values for the replacable parameters could not be determined. >> >> Art S. Kagel >> >>> 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 >>> >>> >>>