0

I am using c#.

I have data in a table. The table has 5 columns of integer and decimal values. There are about 30,000 rows in it.

What is the fastest way to transfer this into my application?

I have tried using a DataReader, iterating through it, and using the Read(), GetDecimal and GetInteger functions. But it is quite slow.

Would binding the table to a DataTable using a sqladapter be faster?

Ginger
  • 8,320
  • 12
  • 56
  • 99
  • 2
    The sqladapter will use internally the same classes you're using at the moment (datareader). Share some code so we can investigate. – BennyM Jul 16 '11 at 14:29
  • 2
    what is taking long time? Query to the Database or iterating over the Datareader and building your own object? – Asdfg Jul 16 '11 at 14:34
  • 1
    Use readonly, forward only access to your table, if the "read" is a single thing you want from it. Does your test DB on your pc or on some server? I mean is there some netwotk latency percentage too in your "bad" performance? – Tigran Jul 16 '11 at 15:19
  • this link may help you.. [http://stackoverflow.com/questions/1148345/is-dataset-slower-than-datareader-due-to][1] [1]: http://stackoverflow.com/questions/1148345/is-dataset-slower-than-datareader-due-to – ahmed saud Jul 16 '11 at 15:33

2 Answers2

4

No, binding to a DataTable will not be faster than data-reader; in fact it will add overhead.

I would create a class that matches the table, and use either data-reader (manually) or "dapper" to fill it into a List<T>. There won't be much speed difference between those two, as from lots of profiling: that is as fast as you'll get (short of moving the data closer to avoid LAN).

One option might be to cache the data locally, so most times you read you don't hit the DB at all. I would suggest maybe using protobuf-net to serialize to a file on the local disk; again - about the fastest you'll get. You could also store some token so you can tell when the data is stale - maybe the last timestamp or similar. Then you just check the DB to query the latest timestamp; if te same, read from your local copy. Otherwise load from the DB and then rewrite your local copy.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • How would I fill it into a list from a data-reader? I have looked through the documentation but cannot find the part that would help me fill the list http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx – Ginger Jul 17 '11 at 12:43
  • @user with reader.Read() and list.Add() - ore use "dapper" and it will do it for you – Marc Gravell Jul 17 '11 at 13:05
0

The DataReader option is faster and will consume less memory as compare to using DataTable; however it depends how you use these options.

• If you iterate and create a collection of objects of your custom class using DataReader then obviously reader is fast.

• But if you iterate over reader and create a DataTable then it will impact negatively on the performance.

Waqas Raja
  • 10,802
  • 4
  • 33
  • 38