[dba-VB] ADO.NET datasets - speed test

Gustav Brock gustav at cactus.dk
Sun Feb 26 07:02:19 CST 2012

Hi Shamil

I started with VS2005, and somehow I at once noticed the tableadapters and datatables and didn't look back ...

I haven't had use for n-tier systems nor DALs, so I haven't worked with these. Today I think the Entity Framework is the most promising - and web services for remote apps.

But currently I'm busy with other tasks so I haven't the time I wish I had for going deeper into this and the datasets.


>>> Salakhetdinov Shamil <mcp2004 at mail.ru> 26-02-12 11:57 >>>
Hi Gustav --

> That looks good. But I have nothing to compare with - I've never used OleDbCommand.
Lucky man! :)
I seems to have started to develop using VS a bit earlier than yourself when using ADO.NET Datasets especially  in ASP.NET applications was a kind of "bad practice" because of datasets "heavy weight" comparing to OleDbDataReader/SqlDataReader, . That was based on real experts' opinions. (BTW, have a look at DNN sources they use SqlCommand and SqlDataReader quite a lot, till nowadays).

Do you also use Dataset Project property of datasets, which I have mentioned here recently?

The latter is becoming a real "life saver" for my current customer project having several MS Access lookup backends and one main backend keeping consolidated data from several reports imported from external sources..

Thank you.

-- Shamil

25 февраля 2012, 21:35 от "Gustav Brock" <gustav at cactus.dk>:
> Hi Shamil
> That looks good. But I have nothing to compare with - I've never used OleDbCommand.
> /gustav
> >>> Salakhetdinov Shamil <mcp2004 at mail.ru> 24-02-12 12:24 >>>
> Hi All --
> *I have a custom application using several MS Access backends where data are loaded/imported from several .csv (report) files and then processed to get common lookup values. The source tables for the latter ones (lookup values) are loaded from MS Access databases into memory to speed-up the whole process - here are the loading stats:
> - lookup dataset #1 - loaded 4014 rows in 0.218 seconds
> - lookup dataset #2 -loaded 1425 rows in 0.105 seconds
> - lookup dataset #3 -*loaded 14955 rows in 0.236 seconds
> - lookup dataset #4 -*loaded 29316 rows in 0.493 seconds
> - lookup dataset #5 -*loaded 9394 rows in 0.535 seconds
> Speed of data loading into memory looks impressive, isn't? - the testing system is a simple for nowadays PC - an year 2007 production dual core Pentium laptop with 3GB of memory running Win7 32bit.
> And *
> 100,000*searches (using ADO.NET data set .Select method) against loaded in memory datatsets takes ~5 seconds,
> 500,000 *searches - 21 seconds,
> 1,000,000 searches - 46 seconds
> Not bad at all.
> **I have mentioned here already that I haven't used ADO.NET datasets that much with MS Access backends - I have mainly used OleDbDataReader to get data into memory and then manipulate it via custom classes and update backend *using OleDbCommand and "action queries" .
> Based on the above stats I'm reconsidering now my usual techniques and I will be switching to mainly using ADO.NET datasets with MS Access backend for all kinds of data manipulation operations for desktop applications...
> Still ADO.NET datasets could be "heavy/memory consuming" for web/ASP.NET applications - that was ~year 2005 opinion from some experts I based my practice on - I suppose it should be now (year 2012 - and VS2010) checked how "heavy" ADO.NET datasets really are comparing with OleDbReader and related *"low level" data manipulation techniques - have you seen anywhere such stats?
> Thank you.
> -- Shamil*

More information about the dba-VB mailing list