Are you using the {AutoCommit => 0} argument to DBI->connect()? If not,
do so, and then add a $dbh->commit; line when you're done with the
inserts. Should help a lot.
----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
[EMAIL PROTECTED] * andrew_perrin (at) unc.edu
On Mon, 3 Jun 2002, Nicolas Nolst wrote:
>
> Hi all,
>
> I have developped a perl script to populate a database with two tables:
> sessions and actions.
>
> the table actions contains the following columns: session_id, url,
> timestamp. The column session_id references to the table sessions.�
>
> the table sessions contains the following columns: session_id, remote_ip,
> phone_type, phone_number. The column session_id is serial.
>
> The lines of the table actions which are part of the same session have
> the same session_id.
>
> There are then more lines in the table actions than in the table session.
>
>
> To fill the two tables, I first need to know if the session already
> exists for a certain phone_type, a certain remote_ip and a certain
> phone_number:
>
> SELECT�session_id�FROM sessions�WHERE (phone_number = ?)�AND (remote_ip�
> = ?)�AND (phone_type = ?)�ORDER BY�session_id;
>
> I also need to apply a criteria to�know if I have to�add a new entry in
> the table sessions or not:
>
> SELECT�(max(timestamp) + ?)<?�FROM actions�WHERE (session_id = ?); �
>
> �
>
> If the session already exists I add a line in the table actions with a
> INSERT
>
> If the session doesn't exist or if the criteria is true, I add a line in
> the table sessions with an INSERT, retrieve the session_id of the line
> just added with the following request
>
> SELECT�session_id�FROM sessions where (msisdn=?)�AND (remote_ip=?)�AND
> (user_agent=?)�ORDER�BY session_id�DESC�LIMIT 1
>
> and the add with a INSERT a line in the table actions.
>
> I have put indexes on sessions(session_id), sessions(msisdn),
> actions(session_id).
>
> My problem is that populating my database is slower� when the data gets
> bigger and the performance falls dramatically. I thought that is would be
> improve with my indexes but the problem still persists.
>
> Could you please give me some clues that could solve this issue.
>
> Thanks.
>
>
> �
>
> Nicolas Nolst
>
> [belgium_gs.gif]
>
> ________________________________________________________________________________
> Join the world�s largest e-mail service with MSN Hotmail. Click Here
>
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
