Login | Register
My pages Projects Community openCollabNet

Discussions > dev > Performance enhancement patch for Propel

propel
Discussion topic

Back to topic list

Performance enhancement patch for Propel

Reply

Author Alan Pinstein <apinstein at mac dot com>
Full name Alan Pinstein <apinstein at mac dot com>
Date 2006-04-16 20:47:10 PDT
Message Hi All-

I am running a script that does lots of inserts and I noticed that it
was going kind of slow.

One thing I know from my experience with SQL is that preparing
statements is slow. So I implemented a simple "prepared statement
cache" to see how that would affect performance.

Some simple log output:

Preparing and caching statement (0.00690102577209 s)
Using cached statement (0.00147700309753 s)
Using cached statement (0.00101280212402 s)
Using cached statement (0.00102305412292 s)
Using cached statement (0.00147294998169 s)
Using cached statement (0.00101089477539 s)
Using cached statement (0.00138592720032 s)
Using cached statement (0.00114607810974 s)
...

So, an average of about .0055 seconds is saved per insert. While this
might seem small, our import script processes about 2.5M records;
this will yield a savings of about 3.8 hours of processing time on
something that takes about 24 hours.

If everyone could take a look and see if the patch is good... my area
of uncertainties are:

1) Currently unbounded memory consumption -- could implement an
algorithm to keep only most common items
2) Maybe this could should be separated out into a separate class to
manage the cache?
3) I am not familiar enough with the details of "connection" to know
if there is a problem having the cache not keep track of the
connection the statement was prepared on... that is I don't know if
the prepared statement is bound to the connection or the local libpg.
4) Right now I am only using this on INSERTS, but maybe it should be
expanded for selects as well?

Here is the patch (also attached)
Attachments

« Previous message in topic | 1 of 12 | Next message in topic »

Messages

Show all messages in topic

Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-16 20:47:10 PDT
     Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-16 21:20:06 PDT
         Re: [propel-dev] Performance enhancement patch for Propel Mat Taylor <mat at sharrow dot me dot uk> Mat Taylor <mat at sharrow dot me dot uk> 2006-04-17 13:06:46 PDT
             Re: [propel-dev] Performance enhancement patch for Propel "Jordan S dot Jones" <jordansjones at gmail dot com> "Jordan S dot Jones" <jordansjones at gmail dot com> 2006-04-17 14:15:20 PDT
                 Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-17 14:39:23 PDT
         Re: [propel-dev] Performance enhancement patch for Propel Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2006-04-18 10:13:11 PDT
             Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-18 10:52:48 PDT
             Re: [propel-dev] Performance enhancement patch for Propel Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2006-04-18 11:21:33 PDT
             Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-18 11:52:38 PDT
                 Re: [creole-dev] Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-19 11:16:21 PDT
                     Re: [propel-dev] Re: [creole-dev] Re: [propel-dev] Performance enhancement patch for Propel Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2006-04-19 11:40:51 PDT
                         Re: [propel-dev] Re: [creole-dev] Re: [propel-dev] Performance enhancement patch for Propel Alan Pinstein <apinstein at mac dot com> Alan Pinstein <apinstein at mac dot com> 2006-04-19 12:07:48 PDT
Messages per page: