Обсуждение: performance issues with DBI module when data too big
<div style="background-color:"><div><div><div></div><div></div><p>Hi all,<p>I have developped a perl script to populate adatabase with two tables: sessions and actions.<p>the table actions contains the following columns: session_id, url, timestamp.The column session_id references to the table sessions. <p>the table sessions contains the following columns: session_id,remote_ip, phone_type, phone_number. The column session_id is serial.<p>The lines of the table actions which arepart of the same session have the same session_id.<p>There are then more lines in the table actions than in the tablesession.<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>SELECT session_id FROM sessions WHERE (phone_number = ?) AND(remote_ip = ?) AND (phone_type = ?) ORDER BY session_id;<p>I also need to apply a criteria to know if I have to adda new entry in the table sessions or not:<p>SELECT (max(timestamp) + ?)<? FROM actions WHERE (session_id = ?); <p> <p>If the session already exists I add a line in the table actions with a INSERT<p>If the session doesn't exist or ifthe criteria is true, I add a line in the table sessions with an INSERT, retrieve the session_id of the line just addedwith the following request<p>SELECT session_id FROM sessions where (msisdn=?) AND (remote_ip=?) AND (user_agent=?) ORDER BYsession_id DESC LIMIT 1<p>and the add with a INSERT a line in the table actions.<p>I have put indexeson sessions(session_id), sessions(msisdn), actions(session_id).<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 indexesbut the problem still persists.<p>Could you please give me some clues that could solve this issue.<p>Thanks.<p><br/> <br /><br />Nicolas Nolst <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 world�slargest e-mail service with MSN Hotmail. <a href="http://g.msn.com/1HM300901/158">Click Here</a><br />
Nicolas Nolst wrote: [text/html is unsupported, treating like TEXT/PLAIN] > [...] > <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> Is your database frequently vacuumed and analyzed (and do you frequently read frequently asked questions)? > <P>Thanks.</P> > <P><BR> <BR><BR>Nicolas Nolst </P> And just for the record, do you prefer answers to HTML mail in Postscript, PDF, DVI or nothing at all? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
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 clists@perrin.socsci.unc.edu * 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 worlds largest e-mail service with MSN Hotmail. Click Here > >