[thesite] Tip Harvester question (was: [***] Formatting tips )

[Warden, Matt]

> I know that in some languages, e.g., Perl and C, you can use bind
> variables to speed up queries.  Oracle can cache the SQL in its SGA
> and the do a variable binding/substitution just before execution.  In
> Perl I've seen 50 times speed up on frequently executed queries.

You mean like prepared/parameterized statements?

select blah from thetips where evolt='sexy' and insertdate=? and whatever=?

and then the above is sent to the database and, through the language, you can
set those two parameters. Is that what you're talking about? Or are you
talking about something different. If you are, then is that better than the
prepared statement option? The query is run through the optimizer prior to
batch loading/selecting/other so the database path is already figured out.
Then, it doesn't have to go through the optimizer every time.

> You may also want to turn off auto-commits.  If you are commiting
> after each query, Oracle needs to write the transaction to the
> redologs each time.  Of course, this means that your rollback segments
> must be large enough to contain all of the uncommitted data.

That's something I've never thought of. Can it be done through the language
(not sure if that would be considered SQL). IOW, could you turn off autocommit
for the duration of the loading and then turn them back on after you have
committed everything and completed loading?


--
mattwarden
mattwarden.com