Export 50,000 records to a Text File.
-
I'm using a smart device which run Win CE witn SQL CE 2.0. Would like to know what is the best way to export a table in the device with 50,000 records to a text file. Should i use a SQL SELECT statement like "SELECT * FROM TABLE" into a data set and later loop the DataTable in the DataSet and export write it to a text file ? Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ? I try to to use method 1 (loop the DataTable in DataSet, but the proble is it take a long time to select all the record into the dataset, and it look like hang over there to the user :zzz:, because i can't update the status and show to the user. However method 2 (loop DataReader) seen like bit better at least i was able to update the status to the user and let user know the program is running. Please kindly advise. Thank in advance.
-
I'm using a smart device which run Win CE witn SQL CE 2.0. Would like to know what is the best way to export a table in the device with 50,000 records to a text file. Should i use a SQL SELECT statement like "SELECT * FROM TABLE" into a data set and later loop the DataTable in the DataSet and export write it to a text file ? Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ? I try to to use method 1 (loop the DataTable in DataSet, but the proble is it take a long time to select all the record into the dataset, and it look like hang over there to the user :zzz:, because i can't update the status and show to the user. However method 2 (loop DataReader) seen like bit better at least i was able to update the status to the user and let user know the program is running. Please kindly advise. Thank in advance.
kakarato wrote:
into a data set and later loop the DataTable in the DataSet and export write it to a text file ? Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ?
If you use a DataAdapter to fill a DataSet then the entire contents are read with a DataReader (that is how a DataAdapter gets the information from the database) and the DataTable is populated with the data, you have to wait for this operation to complete before you can access even one row. Then you can write it to your file. With a DataReader you can write the rows directly to the file as you get them. You do not have to wait for a DataTable object to be filled and you do not waste that memory either. As you have discovered already that the user is left hanging around for a long time while the DataSet is filled, while the DataReader allows you to give feedback on progress immediately. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
kakarato wrote:
into a data set and later loop the DataTable in the DataSet and export write it to a text file ? Or select in it into a DataReader and loop the DataReader? Which one will provide a better performance ?
If you use a DataAdapter to fill a DataSet then the entire contents are read with a DataReader (that is how a DataAdapter gets the information from the database) and the DataTable is populated with the data, you have to wait for this operation to complete before you can access even one row. Then you can write it to your file. With a DataReader you can write the rows directly to the file as you get them. You do not have to wait for a DataTable object to be filled and you do not waste that memory either. As you have discovered already that the user is left hanging around for a long time while the DataSet is filled, while the DataReader allows you to give feedback on progress immediately. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell