fastest way to fill DataTable from SQL Query

[Frank Uray]

Hi all

I am looking for the fastest way to fill a DataTable from a SQL Query.

I have already tried the DataAdapter.Fill and DataTable.Load Methods.
As farest I see, there is not much difference between these two methods.
Does anybody know a faster way to get a DataTable filled ?

For a native connection to SQL Server I use:
Data Source=Server\Instance;Initial Catalog=someDatabase;Integrated
Security=SSPI;

Thanks a lot and best regards
Frank Uray


return_DataAdapter = new System.Data.SqlClient.SqlDataAdapter(Query,
return_Connection);
local_DataTable.BeginLoadData();
return_DataAdapter.Fill(local_DataTable);
local_DataTable.EndLoadData();


return_Command.CommandText = Query;
return_Command.Connection = return_Connection;
return_DataReader = return_Command.ExecuteReader();
local_DataTable.BeginLoadData();
local_DataTable.Load(return_DataReader);
local_DataTable.EndLoadData();

 

[Göran Andersson]

Hi all

I am looking for the fastest way to fill a DataTable from a SQL Query.

I have already tried the DataAdapter.Fill and DataTable.Load Methods.
As farest I see, there is not much difference between these two methods.
Does anybody know a faster way to get a DataTable filled ?

As long as you are using a DataTable, there isn't really any point in
looking for any faster ways than the ones that you have already tried.
You might be able to fill a DataTable slightly faster by making your own
optimised version of the Load method, but you couldn't expect to get
more than a few percent performance improvement.

If you want better performance you should get rid of the DataTable to
avoid the extra overhead. You can read values from the DataReader into
custom objects that represent what you have in the DataTable rows now.
Still, you can't expect more than perhaps a 20-30% performance
improvement, so it's up to you to decide if it's worth it.

 

[Alan McBee]

Remember that the biggest performance bottleneck in reading in multiple rows
from the database, after the database call, is the construction of all the
objects to hold all that data. The DataTable simplifies the creation at the
expense of taking a little longer to get it done.

If you already know the number of records and structure of the data being
read, your fastest option is to create an array of custom objects. Bear in
mind that things like strings or other non-value types won't get created
until you actually read in the value (unless you go to extraordinary lengths
and do some of your own string management).

If you really, really have to have a DataTable, you're probably not going to
do much better than the Load method. Do your best to re-use an existing
DataTable instead of re-creating one.

->Alan
http://codecharm.com

 

[Gregory A. Beamer]

I am looking for the fastest way to fill a DataTable from a SQL Query.

I have already tried the DataAdapter.Fill and DataTable.Load Methods.
As farest I see, there is not much difference between these two
methods. Does anybody know a faster way to get a DataTable filled ?

Both use a DataReader to load the DataTable. The only real difference is
the Adapter is a bit more explicit, allowing you more debug points,
while the Load() method hides more. If you want even more control, you
can load the table yourself from a DataReader, but it will take the same
amount of time, if not more, as you are more likely to introduce
inefficiencies to slow things down.

Using a DataReader directly and outputting may be faster, but only
because you go directly to the binding step. It does make the solution
LESS maintainable, however, so you have to determine if performance is
more important than maintenance.

If speed is a real problem, a bigger server (more CPUs or RAM), or
reducing the number of rows in the output, are probably better options
than looking for speed in the construction of the DataTable.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Paul]

Why does it make it less maintainable?

Yes there is an overhead in creating the Model/DAL, but done correctly
changes to the db do not necessarily need to affect the application.

It will be drastically faster and will have a much smaller footprint in
memory.

Good article

http://aspnet.4guysfromrolla.com/articles/050405-1.aspx