VB.net + SQLDMO = slower than VB6 !!!

1 May 2006 1:31 PM

boo73uk

Dear all,

Porting and enhancing an existing VB6 to .Net. I need to make this app
quicker and basically the main work is iterating through a set of
tables and BCP'ing out the data on a SQL2000 DB.

Using the VB6 app this takes 51 secs.
Using VB.net app its a whopping 63 secs.

This may not sound like much but it has to do this 200 times so it
multiplies out quite a difference.

The .Net uses SQLDMO going through an interop, so I assume this is
where the performance hit is coming from. How do I solve this? Move to
SQL2005?!

I was actually thinking of putting the BCP in a stored proc and calling
that, removing all traces of SQLDMO.

Any thoughts would be helpful.

Thanks
Pete
--Code snippet below
--Added Ref to SQLDMO beforehand

Dim objBulkCopy As SQLDMO.BulkCopy
objBulkCopy = New SQLDMO.BulkCopy
With objBulkCopy
    .MaximumErrorsBeforeAbort = 1
    .DataFilePath = F_EXPORT_DIR & CStr(pstrJobId) & "\" & SiteID & "\" &
objTableDefinition.TableName & ".bcp"
    .UseBulkCopyOption = False
    .DataFileType =
SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat
    .UseServerSideBCP = False
    .ImportRowsPerBatch = 0
End With

1 May 2006 2:50 PM

Cor Ligthert [MVP]

Hi,

Are you sure that you need VB for this and cannot do it completely with a
SQL command.

And than ask the solution in a SQL server newsgroup.

In fact is there not much VB6 over VBNet code, so probably has VBNet more
overhead.

Cor

<boo7***@yahoo.co.uk> schreef in bericht
Show quoteHide quote

news:1146490271.959718.229280@y43g2000cwc.googlegroups.com...
> Dear all,
>
> Porting and enhancing an existing VB6 to .Net. I need to make this app
> quicker and basically the main work is iterating through a set of
> tables and BCP'ing out the data on a SQL2000 DB.
>
> Using the VB6 app this takes 51 secs.
> Using VB.net app its a whopping 63 secs.
>
> This may not sound like much but it has to do this 200 times so it
> multiplies out quite a difference.
>
> The .Net uses SQLDMO going through an interop, so I assume this is
> where the performance hit is coming from. How do I solve this? Move to
> SQL2005?!
>
> I was actually thinking of putting the BCP in a stored proc and calling
> that, removing all traces of SQLDMO.
>
> Any thoughts would be helpful.
>
> Thanks
> Pete
> --Code snippet below
> --Added Ref to SQLDMO beforehand
>
> Dim objBulkCopy As SQLDMO.BulkCopy
> objBulkCopy = New SQLDMO.BulkCopy
> With objBulkCopy
> .MaximumErrorsBeforeAbort = 1
> .DataFilePath = F_EXPORT_DIR & CStr(pstrJobId) & "\" & SiteID & "\" &
> objTableDefinition.TableName & ".bcp"
> .UseBulkCopyOption = False
> .DataFileType =
> SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat
> .UseServerSideBCP = False
> .ImportRowsPerBatch = 0
> End With
>

1 May 2006 4:14 PM

aaron.kempf@gmail.com

yeah get rid of the DMO if you can

call it all as a simple sproc