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,
-----Original Message-----
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 doyou have to change at least 8000 to 10,000 lines?
--
Parker Zhang
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and
confers no rights.
.