Is there anything faster than SqlDataReader in .NET?

Question

I need to load one column of strings from table on SqlServer into Array in memory using C#. Is there a faster way than open SqlDataReader and loop through it. Table is large and time is critical.

EDIT I am trying to build .dll and use it on server for some operations on database. But it is to slow for now. If this is fastest than I have to redesign the database. I tough there may be some solution how to speed thing up.

Solution 1

Data Reader

About the fastest access you will get to SQL is with the SqlDataReader.

Profile it

It's worth actually profiling where your performance issue is. Usually, where you think the performance issue is, is proven to be totally wrong after you've profiled it.

For example it could be:

1. The time... the query takes to run
2. The time... the data takes to copy across the network/process boundry
3. The time... .Net takes to load the data into memory
4. The time... your code takes to do something with it

Profiling each of these in isolation will give you a better idea of where your bottleneck is. For profiling your code, there is a great article from Microsoft

Cache it

The thing to look at to improve performance is to work out if you need to load all that data every time. Can the list (or part of it) be cached? Take a look at the new System.Runtime.Caching namespace.

Rewrite as T-SQL

If you're doing purely data operations (as your question suggests), you could rewrite your code which is using the data to be T-SQL and run natively on SQL. This has the potential to be much faster, as you will be working with the data directly and not shifting it about.

If your code has a lot of necessary procedural logic, you can try mixing T-SQL with CLR Integration giving you the benefits of both worlds.

This very much comes down to the complexity (or more procedural nature) of your logic.

If all else fails

If all areas are optimal (or as near as), and your design is without fault. I wouldn't even get into micro-optimisation, I'd just throw hardware at it.

What hardware? Try the reliability and performance monitor to find out where the bottle neck is. Most likely place for the problem you describe HDD or RAM.

Solution 2

If SqlDataReader isn't fast enough, perhaps you should store your stuff somewhere else, such as an (in-memory) cache.

Solution 3

No. It is actually not only the fastest way - it is the ONLY (!) way. All other mechanisms INTERNALLY use a DataReader anyway.

Solution 4

I suspect that SqlDataReader is about as good as you're going to get.

Solution 5

SqlDataReader is the fastest way. Make sure you use the get by ordinal methods rather than get by column name. e.g. GetString(1);

Also worthwhile is experimenting with MinPoolSize in the connection string so that there are always some connections in the pool.

View more solutions

Comments

[tomfanning]

Agreed - can you load the data in advance and iterate from a collection in memory?

[watbywbarif]

I have tested some thing, SqlDataReader is obviously faster than DataSet ;) Yes, loading time is hitting performance worst.

[watbywbarif]

Query is only one column select, there is no place for optimization there, only to redesign the database ;(

[watbywbarif]

It sounds strange but as this .dll is used on server it seems that i get data fater by SqlDataReader than by building one row in SQL.

[watbywbarif]

And im am not sending to client, .dll is used on same machine as server for some internal usage.

[watbywbarif]

Can you explain more about MinPoolSize, I don't see how this should help?

[msarchet]

@watbywbarif an index will still help even on a single column select

[LukeH]

Ha! Would either of the downvoters care to elaborate on what's wrong with this answer?

[topski]

+1 for "rewrite as T-SQL". The ideal query is one that only retrieves absolutely necessary data. If you're retrieving 100k rows to the client app, then processing there, then perhaps you should re-consider your logic.

[softveda]

In .Net DB connections are returned to a connection pool after being closed and then eventually the underlying SQL server connection is closed after a period of inactivity. This generates the login and logout events. In certain scenario (infrequent web service calls) it may be beneficial to always have some ready connections in the pool to handle the first request quickly rather than having to open a new connection with the SQL server.

[watbywbarif]

I don't know how would more threads speed up loading data as SqlDataReader reads sequential?

[watbywbarif]

Responsiveness is not problem.

[Ian Ringrose]

Each thread could use it's own SqlDataReader, provided you can something you can be in the "where" to define the data between the threads.

[Ian Ringrose]

Have you created an index that ONLY contains the single column you are selecting on?

[watbywbarif]

Nice idea, maybe it can help. +1

[Brandon Moore]

Ordering, Grouping, etc. are faster to handle in C# than in SQL? That doesn't seem likely but maybe you know something I don't.

[Jörgen Sigvardsson]

Throwing hardware at it doesn't really help. SqlClient uses an internal buffer based on the packet size. The biggest packet size is 32768 bytes, and that WILL impact the throughput. No sane amount of hardware will help you there.

[badbod99]

@JörgenSigvardsson - That's assuming your performance issue is related to the network speed and volume of data. It could be, but there are plenty of other places I would look first.

[DarthGizka]

To put this into perspective: I have a similar problem where ExecuteReader() plus an empty rdr.Read() loop takes 50 ms for 100k records but actually accessing the fields in the loop takes 200 ms (that's after optimising field access from things like (int)rdr["foo"] to rdr.GetInt32(0); before that it was more like 350 ms). For comparison: DataTable.Load() takes 750 ms in this situation. Anyway, the SqlDataReader interface is clearly the limiting factor here. Pulling the values from a CSV takes about 15 ms including object creation (with hand-wrought code).