large dataSet (continued)

  • Thread starter Thread starter Francois Malgreve
  • Start date Start date
F

Francois Malgreve

Hi,

In a previous post I was asking how could I save (import) a large DataSet
(potentially 60.000 rows) into a SQL Server 2000 DB. It is a simple Data
transfer operation.
It seemed that the best way was to do it through a DTS.
I then started with a small DataSet that I saved into an Excel format. Then
I call the DTS that will import the excel file into the DataBase. This works
well.

But my problem is that the performance is VERY VERY low. It takes 30 seconds
to save a 250 rows Dataset into an excel sheet. Then my question is, does
anyone know how to save a DataSet in Excel in a better way? I will give a
code snippet .
Or should I save my DataSet into an other format and change my DTS? What
other format could I use?

Any help and clue would be very appreciated as I am kind of stuck here. I
did some research on the net, saw plenty of stuff to read from Excel but
much less to save on excel. Except something about Excel XML but it is not
an Excel file, just some XML format that Excel can understand.

Code snippet:
Basically to insert all fields from the dataSet into the Excel sheet, I just
loop through all rows, and for each rows loop through each columns. I then
insert manually the value from the DataSet into the Excel sheet. Well I can
guess that looping like that is unefficient but how else can I do?

''dsExcelExport is the dataSet
''Excel is the instance of excel.

For intRow = 0 To dsExcelExport.Tables(TableNo).Rows.Count - 1
For intColumnValue = 0 To dsExcelExport.Tables(TableNo).Columns.Count -
1
Excel.Cells(intRow + 2, intColumnValue + 1).Value =
dsExcelExport.Tables(TableNo).Rows(intRow).ItemArray(intColumnValue).ToStrin
g
Next
Next

Best regards,

Francois
 
You should use 2 DataAdapters, and 1 DataSet.
First adapter for data import, second for export.
And do not use Excel. Read about this command ->
myDataSet.AcceptChangesDuringFill=FALSE
 
Hi there,

You would use DTS is you are not using DataSets IOW you should use DTS to
directly import rows from source and not through a DataSet.
What is your source?
If you need to use a DataSet then you are stuck (.net 2 might help you by
using batch update) - I really don't think that exporting from dataset and
using DTS will yield better performance, specially not if you are dumping
data to Excel first.
 
Linas:
Ok but don't you think that doing 50.000 inserts into a remote DB could be
an issue?
Anyway I will give it a try and let you know. My application and some of my
DB servers are literally at the opposite side of the planet. A ping takes
already 250 ms.

Miha:
Actually if I could find a way to dump a DataSet efficiently (fast) in a
simple format (such as CSV) and import it in SQL server that would be good
enough :) As then I can call a DTS to read that file and import the data
into SQL Server. DTS tasks are very fast to insert data in bulk and I would
really like to be able to leverage that great feature of SQL Server. But
well my main obstable so far has been to dump my DataSet into a flat file. I
tried to save it as XML as it is a built in feature of DataSet and then run
a XSLT on it to transform the data from XML format to CSV format but it is
by FAR too slow and hangs the computer on which it runs - CPU 100% usage.

Best regards,

Francois.



Francois Malgreve said:
Hi,

In a previous post I was asking how could I save (import) a large DataSet
(potentially 60.000 rows) into a SQL Server 2000 DB. It is a simple Data
transfer operation.
It seemed that the best way was to do it through a DTS.
I then started with a small DataSet that I saved into an Excel format. Then
I call the DTS that will import the excel file into the DataBase. This works
well.

But my problem is that the performance is VERY VERY low. It takes 30 seconds
to save a 250 rows Dataset into an excel sheet. Then my question is, does
anyone know how to save a DataSet in Excel in a better way? I will give a
code snippet .
Or should I save my DataSet into an other format and change my DTS? What
other format could I use?

Any help and clue would be very appreciated as I am kind of stuck here. I
did some research on the net, saw plenty of stuff to read from Excel but
much less to save on excel. Except something about Excel XML but it is not
an Excel file, just some XML format that Excel can understand.

Code snippet:
Basically to insert all fields from the dataSet into the Excel sheet, I just
loop through all rows, and for each rows loop through each columns. I then
insert manually the value from the DataSet into the Excel sheet. Well I can
guess that looping like that is unefficient but how else can I do?

''dsExcelExport is the dataSet
''Excel is the instance of excel.

For intRow = 0 To dsExcelExport.Tables(TableNo).Rows.Count - 1
For intColumnValue = 0 To
Click to expand...
dsExcelExport.Tables(TableNo).Columns.Count -
 
You might want to check out my csv parser/writer utility for this.
http://www.csvreader.com
It should write out the data directly from the DataTable to your csv
format in basically the fastest way possible. I'm also close to
releasing a new version that will work with 2.0's SqlBulkCopy class to
insert the rows directly into the database from the csv file. If you're
adventurous, and are working on 2.0, you can try creating an instance
of DataTableReader and pass it into the SqlBulkCopy class to insert the
rows directly, although I haven't yet benchmarked that method.

Bruce Dunwiddie
 
Back
Top