|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
large dataSet (continued)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 Show quote > 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 > > 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. -- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Francois Malgreve" <francois.malgreveADgmail.com> wrote in message news:OzRn3c24FHA.3976@TK2MSFTNGP15.phx.gbl... > 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 > > 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. Show quote "Francois Malgreve" <francois.malgreveADgmail.com> wrote in message dsExcelExport.Tables(TableNo).Columns.Count -news:OzRn3c24FHA.3976@TK2MSFTNGP15.phx.gbl... > 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 > 1 dsExcelExport.Tables(TableNo).Rows(intRow).ItemArray(intColumnValue).ToStrin> Excel.Cells(intRow + 2, intColumnValue + 1).Value = > Show quote > g > Next > Next > > Best regards, > > Francois > > Why don't you loop through the rows and write to file yourself?
-- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Francois Malgreve" <francoism@agoda.com_NO_SPAM> wrote in message news:uwCt9S74FHA.2824@TK2MSFTNGP12.phx.gbl... > 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" <francois.malgreveADgmail.com> wrote in message > news:OzRn3c24FHA.3976@TK2MSFTNGP15.phx.gbl... >> 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 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 Francois Malgreve wrote: Show quote > 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" <francois.malgreveADgmail.com> wrote in message > news:OzRn3c24FHA.3976@TK2MSFTNGP15.phx.gbl... > > 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 > > > >  |
|||||||||||||||||||||||
Other interesting topics