Mssql/Perl DBI performance woes
Im writing an application that does a lot of large inserts to a remote mssql server with Perl DBI.  Not sure if it will be 05 or 08 sql server yet, but I've timed it in both so far and performance is similar.  Basically, there are a large number of rows that need to be inserted are turning out to be the bottleneck by far.  I have tried multi-row inserts in 08 ( and the sub-select UNION ALL trick in 05), changing whether the inserts are fired off during a previous fetch or after, using execut_array() on single-row inserts, all these with/without binding params.

    Psuedocode:
    
    select data query
    while fetchrow {
      do lots of calculations
      construct insert
      1) do inserts here
    }
    2) or do inserts here

The activity monitor on the sql server averages the multi-row inserts at 70ms a piece.  The queries themselves have been limited to 58ish rows a piece because there are 36 fields on the insert, and it easily hits the 2100 parameter limit.

Is there anything obvious I am overlooking?  Any other method I could try to improve the times?  Ignoring issues like latency or hardware, I feel like there has to be another improvement to my Perl workflow or the queries themselves.  (I was looking into sql server bcp, bulk inserts, etc).

Thanks for any advice
Mssql/Perl DBI performance woes
===

Im writing an application that does a lot of large inserts to a remote mssql server with Perl DBI.  Not sure if it will be 05 or 08 sql server yet, but I've timed it in both so far and performance is similar.  Basically, there are a large number of rows that need to be inserted are turning out to be the bottleneck by far.  I have tried multi-row inserts in 08 ( and the sub-select UNION ALL trick in 05), changing whether the inserts are fired off during a previous fetch or after, using execut_array() on single-row inserts, all these with/without binding params.

    Psuedocode:
    
    select data query
    while fetchrow {
      do lots of calculations
      construct insert
      1) do inserts here
    }
    2) or do inserts here

The activity monitor on the sql server averages the multi-row inserts at 70ms a piece.  The queries themselves have been limited to 58ish rows a piece because there are 36 fields on the insert, and it easily hits the 2100 parameter limit.

Is there anything obvious I am overlooking?  Any other method I could try to improve the times?  Ignoring issues like latency or hardware, I feel like there has to be another improvement to my Perl workflow or the queries themselves.  (I was looking into sql server bcp, bulk inserts, etc).

Thanks for any advice