<div style="background-color:"><div></div><div></div>Hi all,<br /><br />I have developped a perl script to populate a
databasewith two tables: sessions<br />and actions.<br /><br />the table actions contains the following columns:
session_id,url, timestamp.<br />The column session_id references to the table sessions.<br /><br />the table sessions
containsthe following columns: session_id, remote_ip,<br />phone_type, phone_number. The column session_id is
serial.<br/><br />The lines of the table actions which are part of the same session have the same<br />session_id.<br
/><br/>There are then more lines in the table actions than in the table session.<br /><br /><br />To fill the two
tables,I first need to know if the session already exists for a<br />certain phone_type, a certain remote_ip and a
certainphone_number:<br /><br />SELECT session_id FROM sessions WHERE (phone_number = ?) AND (remote_ip = ?)<br />AND
(phone_type= ?) ORDER BY session_id;<br /><br />I also need to apply a criteria to know if I have to add a new entry in
the<br/>table sessions or not:<br /><br />SELECT (max(timestamp) + ?)<? FROM actions WHERE (session_id = ?);<br
/><br/><br /><br />If the session already exists I add a line in the table actions with a INSERT<br /><br />If the
sessiondoesn't exist or if the criteria is true, I add a line in the<br />table sessions with an INSERT and then add a
linewith a INSERT in the table actions (I use nextval and currval).<br /><br />I have put indexes on
sessions(session_id),sessions(msisdn),<br />actions(session_id) and actions(timestamp). I process one log file of about
20000lines every day. All the lines are processed in one transaction (autocommit set to 0).<br /><br />My problem is
thatpopulating my database is slower when the data gets bigger<br />and the performance falls dramatically. I thought
thatis would be improve with<br />my indexes but the problem still persists.<br /><br />Could you please give me some
cluesthat could solve this issue.<br /><br />Thanks.<br /><br /><br /><br />Nicolas Nolst <div></div><div></div><img
src="http://www.3dflags/World/Gif/belgium_gs.gif"/><div></div></div><br clear="all" /><hr />MSN Photos is the easiest
wayto share and print your photos: <a href="http://g.msn.com/1HM500901/157">Click Here</a><br />
