I haven't dealt with MS databases in several years but some of this
stuff will likely still apply. First a couple notes on the underlying
databases, if you're running Access be sure to compact the database
after the insert particularly if this is going to be a repeated process,
in addition to the gained performance there may be a file size
limitation and the file can swell incredibly during a large insert If
you're running MS SQL then make sure that the cluster index on the table
isn't set up in a way that the insert will require a lot of rearranging
of data.
If you can take the table off line during the insert the best
solution would be to use Perl to dump the data into a standard format
(if it isn't already) and use the databases native importing tools. If
you are going to be inserting with the DB online then depending on the
quantity of data and how much the DB is accessed, then record locking
during inserts/updates can create a serious lag. My suggested solution
for this scenario would be to create long INSERT statements which will
add multiple rows at a time...most SQL databases support some form of
this with something along the lines of multiple sets of values in
parentheses but the specific command format varies. You can tailor the
number of lines to suit your need, balancing the extra speed of the
fewer, larger database transactions with momentarily freeing up the
system. The smaller inserts can also cause problems if they're not
getting processed by the DB fast enough and end up backed up. I think
MS SQL at least also allows for the setting of precedence for certain
SQL statements or processes, and creating a stored procedure may provide
an additional performance boost.
Luke wrote:
> Hello,
> I am looking for a proper, fastest and most reasonable way to insert
> data from pretty big file (~1,000,000 lines) to database. I am using
> Win32::ODBC (ActiveState Perl) module to connect with Access/MSSQL
> database and inserting line after line. I was wondering if there is a
> better way to do it...
> Maybe create hash with part of data (maybe all of it - what are the
> limitations ?)
> What is other way to do it instead 'INSERT INTO...' statement after
> reading each line ?
>
> Thanks for any help in advance...
>
> Luke
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
