DataTable load is blocking application

[Frank Uray]

Hi all

I have a little application with one MdiContainer Form
and one thread filling a DataTable with large amount of data
from a SQL Server database.

When I use
SqlDataAdapter.Fill(local_DataTable);
or local_DataTable.Load(local_SqlDataReader);
it blocks sometimes my application.

Does anybody knows why this happens
and if there is a better solution ?
There are no cross-thread calls at all.

Thanks a lot and best regards
Frank Uray

 

[Alberto Poblacion]

I have a little application with one MdiContainer Form
and one thread filling a DataTable with large amount of data
from a SQL Server database.

When I use
SqlDataAdapter.Fill(local_DataTable);
or local_DataTable.Load(local_SqlDataReader);
it blocks sometimes my application.

If you are doing this on a separate thread, it should NOT be blocking
your application unless it produces some undesirable side effects. For
instance, if you are bringing a million records into the DataTable, and each
record requires 1 KB of storage, this would mean that a Gigabate of memory
space needs to be allocated to your application. If this causes the
operating system to swap out some of the memory pages that are needed for
the operation of the form, the form will appear to be locked when you try to
operate it while those pages are being swapped back into memory.

 

[Frank Uray]

Hi Alberto

Thanks for your reply.

Yes, I think you are right

Do you know: Is it possible to allocate more memory
for the application while starting ?

Thanks and best regards
Frank Uray

 

[Alberto Poblacion]

Do you know: Is it possible to allocate more memory
for the application while starting ?

Yes, but it may not work as expected. You can certainly create a new
DataTable and use a loop to add DataRows with fake data, and this will
allocate enough memory to hold them. You could then use a DataReader to
bring in data from your database and overwite the already assigned memory in
the datatable. This should work well for Value-Types, such as integers,
which can overwrite the already-assigned memory. However, Reference-Types
such as Strings are a different matter. They will not be overwritten in
place; rather, new objects will be allocated and the old ones which you were
using as placeholders will be made available to the garbage collector. This
will do weird things to your memory allocation, since the GC will run
whenever it considers appropriate in order to reclaim the memory of the
freed objects, and before that happens, the new allocations will eat up more
memory. You can try it out and see how it behaves, but I suspect that the
net result is not going to be much of an improvement over your current
situation.

 

[Mr. Arnold]

Hi all

I have a little application with one MdiContainer Form
and one thread filling a DataTable with large amount of data
from a SQL Server database.

When I use
SqlDataAdapter.Fill(local_DataTable);
or local_DataTable.Load(local_SqlDataReader);
it blocks sometimes my application.

Does anybody knows why this happens
and if there is a better solution ?
There are no cross-thread calls at all.

Thanks a lot and best regards
Frank Uray

The better solution would be to use a SQLDataReader and a List<T> of
objects, instead of a datatable which is slow.

 

[sebastian.dau]

Some comments from my side:

1) Executing long running methods in seperate threads is common sense
and easy to do with .NET base classes like BackgroundWorker,
ThreadPool, ThreadStart.
See:
http://msdn.microsoft.com/en-us/library/8xs8549b.aspx
http://msdn.microsoft.com/en-us/library/kbf0f1ct.aspx

2) Generally speaking: it is not a wise idea to preconsume memory
before using it except for specific (rare) performance benefits.
Especially in memory managed environments like .NET you will most
propable end up eating unused memory (that is dumped by the garbage
collector anyway) before the actual table load method allocates new
memory for its own table objects itself. That make your appliction
execution performance feel even slower than before.

See: http://msdn.microsoft.com/en-us/magazine/bb985010.aspx for
further details.

Cheers, Sebastian [MCPD]

 

[Frank Uray]

Hi Alberto

Thanks again for your answer.

I will work on this further, the idea is good.

Regards
Frank Uray