Jason
-----Original Message-----
From: Stacy Young [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 1:15 PM
To: CF-Talk
Subject: RE: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
We use bind variables extensively as well. The only case in which they
can cause negative side effects relating to reporting. Please see my
last post for more detailed summary info.
In our case the effects were devastating due to our partitioning (we
partition by month)...it was causing full table scans on enormous
tables. (tens of millions of rows)
-Stace
_____
From: Dave Carabetta [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 12:21 PM
To: CF-Talk
Subject: RE: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
>Wow, folks using CF for reporting w/ Oracle DBs should re-evaluate the
>use of using bind variables. For transactions and the like they are
fine
>but in the case of reporting, bind vars can wreak havoc.
>
>The over simplified explanation is that bind variables can inhibit the
>work of Oracles optimizer in a couple ways. The optimizer ends up using
>heuristics to generate the best explain plan rather than using
>information about data distribution. (from stat tables etc)
>
>The problems may have been exaggerated in our case considering the
sheer
>volume of data (and DB links) we have...but nonetheless we've seen
>dramatic improvements in response time after removing all bind
>variables! (couple minutes down to seconds!)
>
>See Oracle technet for additional info!
>
Can you provide any links to this technet info? TechNet is huge and
their
search features stink. We use Oracle exclusively and haven't had any big
performace issues. In fact, our stability *improved* when we converted
some
legacy non-cfqueryparam-ed queries to use cfqueryparam. I've never heard
of
the negative effects you describe, though you have certainly peaked my
interest.
Also, is the bind variable issue with any particular set of JDBC
drivers?
The DataDirect that ships with MX? The ones directly from Oracle? Type
IV?
Thanks for any info you can provide.
Regards,
Dave.
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
