Database

Re: SQL 2000 won't import data

1-May-07 8:40

I believe this may be an issue with your Jet Driver for Excel. What happens is that it decides within 8 records by default what type the column is. There is a registry change that will need to be made in order to overcome this, if it is the same cause that I am describing.

Solution from the link follows[^]

onedaywhen wrote:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.

ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:

Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied.

_Cle^aKO

"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)

Bryan Bradford1-May-07 11:29

Bryan Bradford

1-May-07 11:29

I had made the registry edits but had forgot the IMEX=1...THANKS!!!!
It's always the simple things that get you!

Note to anyone else having this problem:
When you are using DTS you can change the IMEX=1 by going into Disconnected Edit and adding it to the Value of the Extended Properties under your source connection.

Thanks,
Bryan

How can I minimize a datatable's memory usage?

Marcus J. Smith1-May-07 6:59

1-May-07 6:59

I am attempting to improve a current VB.NET 2.0 program that originated in VB6 with disconnected recordsets and arrays by replacing the loops that look for matches with a DataTable.Find call. This seems to work all well and good except that the current and old program both use some time of in memory storage until it is time to write the output to a file.

The in memory storage was a disconnected recordset and is now...you guessed it...a datatable. I noticed that the program memory usage with the recordset stayed around 20 K while the program with the datatable seems to increase with each record dealt with so when the program has read in 500 K records it is using 700 K memory. Why is this and is there a way for me to minimize the datatable memory impact? I want to avoid using the VB6 style.

One tidbit - I currently do not have the option to utilize a table in SQL for this.

kubben1-May-07 7:21

1-May-07 7:21

Are you really only keeping a dataTable or do you have a dataset as well? The dataset has a lot more overhead. Here is another question. If you don't need the datatable functions then why not use a dataview? That should be more light weight and should allow you to do what you need to do. You can start with a datatable and then keep the view with dataTable.Defaultview.

Ben

Marcus J. Smith1-May-07 8:01

1-May-07 8:01

I build the blank datatable in the beginning and add to it so I need that functionality. As for the dataviews, do they allow for finding rows?

kubben1-May-07 8:04

1-May-07 8:04

Yes, there is a RowFilter property. When you set this property the only rows you will see are the ones that meet that filter criteria. If you want to see all the rows again set the rowfilter to "".

Hope that helps.
Ben

Marcus J. Smith1-May-07 8:17

1-May-07 8:17

So if I want to hold 1 million records (made up number) in memory which would utilize less memory, the datatable or the dataview?

What is it about the disconnected recordset that allows it to store the exact same amount of records (1 million (made up number)) and utilizes 2.5% of the memory that the datatable utilizes?

kubben1-May-07 8:26

1-May-07 8:26

Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.

Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.

Ben

Marcus J. Smith2-May-07 10:08

2-May-07 10:08

kubben wrote:
Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.

Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.

Alright, let me say that I have appreciated your help so far, I never thought to use a DataView for this purpose. The speed was reduced by 30 minutes so it is 46% faster that it was before BUT the memory usage is still unreal. The reason I say unreal is because I watched it with the disconnected recordset and it never rose much above 20,000 KB. This process now uses 850,000 KB at the late end of the process. Do you have any additional ideas of what to do to lower that other than using the disconnected recordset?

kubben2-May-07 10:28