System.Data.DataTable Performance Issue

  • Thread starter Thread starter Buddy
  • Start date Start date
B

Buddy

Hello,

We are using DataTable to store our data that we retrieve
from SQL because it provides us with ROW/COLUMN concept.
Due to DataTable been memory hungary we are finding that
at least 40MB of memory is been created every second which
means the GC is concently running. We want to use
something else that is not memory hungary and will give us
the ROW/COLUMN concept (ie Multi-dimension Arrays).

My question is that, is there any other .Net object that
is light weight which will give me the ROW/COLUMN concept.
Please also note that we do want to store data in the
COLUMNS with different datatypes. For example I could have
2 columns. Column 1 is a string and Column 2 is a double.

Thanks,
 
Hello,

You could try using a multidimentoial array:

object[,,] oArr = new object[ 20, 4 ];

Then you can stick anything you want in any cell, but be
aware that some boxing and unboxing will occur.

You could create a typed multidementional array:

object[] rs = new object[4]; // 4 equals the column count
rs[0] = new int[20]; // 20 equals the row count
rs[1] = new string[20];
rs[2] = new double[20];
rs[3] = new bool[20];

double tmp = ((double[])rs[2])[15]; // 3rd column of 16th
row

or if you are reading from a database you could use a
DataReader. ( Its forward only and read only )

Hope this helps.

Jeff
 
Thanks for the reply,

But how to I get the number of retrieved when using the
DataReader when applying SELECT queries.

Thanks
-----Original Message-----
Hello,

You could try using a multidimentoial array:

object[,,] oArr = new object[ 20, 4 ];

Then you can stick anything you want in any cell, but be
aware that some boxing and unboxing will occur.

You could create a typed multidementional array:

object[] rs = new object[4]; // 4 equals the column count
rs[0] = new int[20]; // 20 equals the row count
rs[1] = new string[20];
rs[2] = new double[20];
rs[3] = new bool[20];

double tmp = ((double[])rs[2])[15]; // 3rd column of 16th
row

or if you are reading from a database you could use a
DataReader. ( Its forward only and read only )

Hope this helps.

Jeff
-----Original Message-----
Hello,

We are using DataTable to store our data that we retrieve
from SQL because it provides us with ROW/COLUMN concept.
Due to DataTable been memory hungary we are finding that
at least 40MB of memory is been created every second which
means the GC is concently running. We want to use
something else that is not memory hungary and will give us
the ROW/COLUMN concept (ie Multi-dimension Arrays).

My question is that, is there any other .Net object that
is light weight which will give me the ROW/COLUMN concept.
Please also note that we do want to store data in the
COLUMNS with different datatypes. For example I could have
2 columns. Column 1 is a string and Column 2 is a double.

Thanks,
.
Click to expand...
.
Click to expand...
 
Hello,

I mean number of rows retrieved.
-----Original Message-----
Thanks for the reply,

But how to I get the number of retrieved when using the
DataReader when applying SELECT queries.

Thanks
-----Original Message-----
Hello,

You could try using a multidimentoial array:

object[,,] oArr = new object[ 20, 4 ];

Then you can stick anything you want in any cell, but be
aware that some boxing and unboxing will occur.

You could create a typed multidementional array:

object[] rs = new object[4]; // 4 equals the column count
rs[0] = new int[20]; // 20 equals the row count
rs[1] = new string[20];
rs[2] = new double[20];
rs[3] = new bool[20];

double tmp = ((double[])rs[2])[15]; // 3rd column of 16th
row

or if you are reading from a database you could use a
DataReader. ( Its forward only and read only )

Hope this helps.

Jeff
-----Original Message-----
Hello,

We are using DataTable to store our data that we retrieve
from SQL because it provides us with ROW/COLUMN concept.
Due to DataTable been memory hungary we are finding that
at least 40MB of memory is been created every second which
means the GC is concently running. We want to use
something else that is not memory hungary and will
Click to expand...
give
us
the ROW/COLUMN concept (ie Multi-dimension Arrays).

My question is that, is there any other .Net object that
is light weight which will give me the ROW/COLUMN concept.
Please also note that we do want to store data in the
COLUMNS with different datatypes. For example I could have
2 columns. Column 1 is a string and Column 2 is a double.

Thanks,
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hello Parker,

I cannot use the 'select count(*)' SQL query because the
code has been converted to C#. This means that I would
have to manually change at least 8000 to 10,000 lines of
code.

Thanks,
 
Hello,

Then, how about counting the records as you go through the reader?

http://support.microsoft.com/?id=308352

Would you please explain a little more why you cannot use "select count(*)"?

All you have to do is to add a few lines to get the record number. Why do
you have to change at least 8000 to 10,000 lines?
 
Hello Parker,

Let me explain the actual problem. We've had Microsoft
profile our system and found that there is a lot of GC
taking place (approx 40MB second of memory is been created
and destroyed), the reason behind is that we are using
DataTable to store our data that we get from SQL server.
The reason we use DataTable is that it was the best way we
could see of getting the data from SQL by using the
SqlDataAdapter and then using the Fill method. The problem
is that the Fill method will fill into a Dataset and not
into a DataTable (even though under the cover the DataSet
has a DataTable). So we would then have to copy the data
from the DataSet into our DataTable, this would result to
the DataSet object been destroyed each time. In a given
second we could be doing up to 50-100 separate queries.

So we started looking at using the DataReader so we could
then copy the data directly into our DataTable but the
problem is that when the data is retrieved the number of
records retrieved is sent to the user and then we can then
fetch back the data. The user will then decide how many
records to fetch, for example we might return 100 and the
user only wants the first 50 records. This means we need
to send the total number of records before retrieving the
data. We could do this by caching the data in another
DataTable but this would mean we are back to square one
with the GC.

Please also note that the code has been CONVERTED from
another language called AM (4-GL language) and we are
testing the conversion, the SQL queries is also embedded
in the code so to run an extra query like COUNT(*) is
almost impossible because the queries is passed to a
common function which then runs the query, we would have
to parse the SQL query to perform the COUNT(*) which is
not the route we would want to take as we would prefer to
leave the query parsing to the provider engine.
For example the user will pass in the following query
SELECT Client, Name, PostCode From Client WHERE Name
Like 'Smith%'
Your approach is to do this
SELECT COUNT(*) From Client WHERE Name Like 'Smith%'
and then run the actual query
SELECT Client, Name, PostCode From Client WHERE Name
This query is simpler but when you have joins then it gets
tricker which requires us to parse it.


Thanks,
 
Hi Buddy,
The reason we use DataTable is that it was the best way we
could see of getting the data from SQL by using the
SqlDataAdapter and then using the Fill method. The problem
is that the Fill method will fill into a Dataset and not
into a DataTable (even though under the cover the DataSet
has a DataTable). So we would then have to copy the data
from the DataSet into our DataTable, this would result to
the DataSet object been destroyed each time. In a given
second we could be doing up to 50-100 separate queries.
Click to expand...

The Fill method will also fill into a DataTable:

Overloads Public Function Fill( _
ByVal dataTable As DataTable _
) As Integer

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatacommondbda
taadapterclassfilltopic2.asp

Please let me know if you have any questions.
 
Back
Top