performance issues with DBI module when data too big

performance issues with DBI module when data too big

Post by Nicolas Nols » Wed, 05 Jun 2002 01:59:56



<html><div style='background-color:'><DIV>
<DIV>
<DIV></DIV>
<DIV></DIV>
<P>Hi all,</P>
<P>I have developped a perl script to populate a database with two tables: sessions and actions.</P>
<P>the table actions contains the following columns: session_id, url, timestamp. The column session_id references to the table sessions.?</P>
<P>the table sessions contains the following columns: session_id, remote_ip, phone_type, phone_number. The column session_id is serial.</P>
<P>The lines of the table actions which are part of the same session have the same session_id.</P>
<P>There are then more lines in the table actions than in the table session.</P>
<P><BR>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:</P>
<P>SELECT?session_id?FROM sessions?WHERE (phone_number = ?)?AND (remote_ip? = ?)?AND (phone_type = ?)?ORDER BY?session_id;</P>
<P>I also need to apply a criteria to?know if I have to?add a new entry in the table sessions or not:</P>
<P>SELECT?(max(timestamp) + ?)<??FROM actions?WHERE (session_id = ?); ? </P>
<P>?</P>
<P>If the session already exists I add a line in the table actions with a INSERT</P>
<P>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</P>
<P>SELECT?session_id?FROM sessions where (msisdn=?)?AND (remote_ip=?)?AND (user_agent=?)?ORDER?BY session_id?DESC?LIMIT 1</P>
<P>and the add with a INSERT a line in the table actions.</P>
<P>I have put indexes on sessions(session_id), sessions(msisdn), actions(session_id).</P>
<P>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.</P>
<P>Could you please give me some clues that could solve this issue.</P>
<P>Thanks.</P>
<P><BR>?<BR><BR>Nicolas Nolst </P>
<DIV></DIV>
<DIV></DIV><IMG src="http://www.3dflags/World/Gif/belgium_gs.gif">
<DIV></DIV></DIV></DIV></div><br clear=all><hr>Join the worlds largest e-mail service with MSN Hotmail. <a href='http://g.msn.com/1HM300901/158'>Click Here</a><br></html>
 
 
 

performance issues with DBI module when data too big

Post by Ulrich Spric » Thu, 06 Jun 2002 22:54:20


Hi Nicolas,
perhaps you should not use html mails... use plain text instead!
greetings, ulli



Quote:> <html><div style='background-color:'><DIV>
> <DIV>
> <DIV></DIV>
> <DIV></DIV>
> <P>Hi all,</P>
> <P>I have developped a perl script to populate a database with two

...

 
 
 

1. performance issues with DBI module when data too big

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. Central Subscriber Replication: Implementation

3. Datasource listbox VB6

4. Performance issue while read a complete table, using perl interface DBI

5. IN Clause as a Variable

6. performance issue using DBI

7. rapidfile problem

8. Postgres and Perl: Which DBI module?

9. ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

10. Postgres and Perl: Which DBI module?