Comparision between Datatable's Select method and stored procedure

[nandan]

Hi,
Has any one ever compared the performance of calling a DataTable's
Select method with a stored procedure doing the same thing?
My point is:
dataRows = DataTable.Select(filter) is better or
Passing paramters to stored procedure?
The datatable holds about 500-700 rows at any given time.

If I select one of the approaches the business logic will go into
respective layers.With dotnet in picture what would be a good approach
- Have the data in Datatable and do a filter on the data or call
stored procedures which has been the convention.
Can some one pl. suggest?
Thanks
Nandan

[Marina]

Just on the time for the Select, I would hope a stored procedure is better.
The database's optimization engine and indexing should be superior to
anything in a datatable.

Now, the only other factor in this, is that calling your stored procedure
would require the overhead of a connection, and getting the data transferred
from the DB server to your app. It is hard to predict what kind of
additional overhead that would take, since we don't know your setup.

You should probably do some tests to see which method is more efficient in
your situation.

"nandan" <ma****@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...

Hi,
Has any one ever compared the performance of calling a DataTable's
Select method with a stored procedure doing the same thing?
My point is:
dataRows = DataTable.Select(filter) is better or
Passing paramters to stored procedure?
The datatable holds about 500-700 rows at any given time.

If I select one of the approaches the business logic will go into
respective layers.With dotnet in picture what would be a good approach
- Have the data in Datatable and do a filter on the data or call
stored procedures which has been the convention.
Can some one pl. suggest?
Thanks
Nandan

[Kyril Magnos]

Hi Nandan,

Once you populate a DataTable, that data is held locally on the server
rather than in the database. So, my guess would be that filtering rows on
the DataTable would probably be faster than going back to the database to
execute a stored proc (YMMV).

The downside to this is that you have to take into account that the data may
not be the most up to date. For instance, if you are designing a stock
tracking website; during trading hours, the data in the DataTable could be
very different from what is in the database. In this instance, I would use
stored procs. But, if you are designing a shopping cart site with data that
does not change often, I would use a data table and perform all my filters
upon that rather than spend the extra bandwidth cost to go back and forth
from the database.

So, now that I have rambled on and on... the simple answer is that the
DataTable will most likely outperform the stored procedures. :-)

--
HTH

Kyril Magnos
"I'm not a developer anymore, I'm a software engineer now!" :-)

"nandan" <ma****@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...
| Hi,
| Has any one ever compared the performance of calling a DataTable's
| Select method with a stored procedure doing the same thing?
| My point is:
| dataRows = DataTable.Select(filter) is better or
| Passing paramters to stored procedure?
| The datatable holds about 500-700 rows at any given time.
|
| If I select one of the approaches the business logic will go into
| respective layers.With dotnet in picture what would be a good approach
| - Have the data in Datatable and do a filter on the data or call
| stored procedures which has been the convention.
| Can some one pl. suggest?
| Thanks
| Nandan

[Zürcher See]

If you need some performance is better use store procedure id DB, that mean
if you have a productive DB with a lot of select pro second, for example a
DB used from a web page with a lot of user.
I prefer to use a dataadpter with sql-parameters and fill a DataTable, it's
easier to change it if is needed.
To filter the resultrows I prefer to use a separated DataView also when the
DataTable has his personal DataView (DataTable.DefaultView)
I use the DataTable.Select method only to avoid to build relation between
tables (Constraint) and use the GetChildren method

"nandan" <ma****@hotmail.com> schrieb im Newsbeitrag
news:c7**************************@posting.google.c om...

Hi,
Has any one ever compared the performance of calling a DataTable's
Select method with a stored procedure doing the same thing?
My point is:
dataRows = DataTable.Select(filter) is better or
Passing paramters to stored procedure?
The datatable holds about 500-700 rows at any given time.

If I select one of the approaches the business logic will go into
respective layers.With dotnet in picture what would be a good approach
- Have the data in Datatable and do a filter on the data or call
stored procedures which has been the convention.
Can some one pl. suggest?
Thanks
Nandan