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

[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

 

[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

 

[aaron.kempf]

yeah get rid of the DMO if you can

call it all as a simple sproc