Prepared statements do not go very well with connection
pooling, unless you're going to a bulk insert (a whole
lot of records in the same transaction). The reason is
that the statement is linked to the connection, so if you
insert 1 record, put the connection back, and then insert
another record, there's no guarantee that you will have the
same connection, thus, you cannot use that same prepared
statement.
However, if you know you're going to insert a bunch all
at once (which is where you get your real performance boost),
you can prepare the statement, do all of your inserts, and
then close the statement.
The real performance increase is not going to occur by
preparing a statement that is not executed very often
(such as inserting users, or updating login time), but
when you want to insert hundreds or thousands of records
at once. I've done some timing of this stuff for work,
using a relatively slow oracle server. To insert 10000
records which each have 2 varchar(255) fields, and a date
field, i came up with the following:
inserting one at a time with it's own non-prepared
statement took approximately 57 seconds
inserting in blocks of 1000 with a non-prepared statement
for each block took approximately 52 seconds
inserting in blocks of 1000 with a prepared statement
for each block took approximately 42 seconds.
so you can see you get around a 20% performance boost.
I have seen it as high as 75% - 80% depending on the
record structure.
I would extend the Peer class, and make bulkInsert method
which would take in an array of that object, get a connection,
prepare the statement, insert, and then close the statement.
The drawback is that you have to make sure that you
a) put the right types of objects in the parameters
(String, Integer, java.sql.Date, java.sql.Timestamp, etc..)
b) if you change your object model, you'll have to adjust
your bulk insert code, since torque won't generate it
for you (hm..... an interesting suggestion for future
torque releases)
On Mon, 2001-09-10 at 10:05, Gareth Coltman wrote:
> Hi,
>
> I'm trying to performance tune my application, and am finding database performace my
>biggest problem. I am using Turbine to handle
> all my security, and I notice that the sql produced by the UserPeer (and I assume
>all SQL generated by Peers) doesn't use and kind
> prepared statement. I understand that the SQL in Peers is being generated on the
>fly, but requests like SELECT * WHERE USER_ID = ?
> could easily be anticipated!??
>
> Comments?
>
> Gareth
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
--
Matt Inger ([EMAIL PROTECTED])
Sedona Corporation
455 S. Gulph Road, Suite 300
King of Prussia, PA 19406
(484) 679-2213
"Self-respect - the secure feeling that no one,
as yet, is suspicious." -H.L. Mencken
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
