473,378 Members | 1,393 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Comparision between Datatable's Select method and stored procedure

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
Nov 16 '05 #1
3 5885
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

Nov 16 '05 #2
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
Nov 16 '05 #3
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

Nov 16 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: nandan | last post by:
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...
5
by: Bhavna | last post by:
Hello I want to push the data in my datatable into my database. How must i do this? I have been told to create a insert query. I have done this and have a select statement below it to retrieve the...
5
by: Stefan Turalski \(stic\) | last post by:
Hi, I'm wondering if there is a way to send a method parametrs by ref when DataTabel is a type of this value ? I done some sort of select over DataTable columns, just by removing them froma...
0
by: Jason MacKenzie | last post by:
If have a stored procedure that seems to work fine through query analyzer but if I attempt the same thing via code, no exceptions are thrown but no results are ever returned. I'm using the ODBC.NET...
4
by: Fred Nelson | last post by:
Hi: I have built and loaded a datatable with three columns and defined the first as a primary key as below: ' define datatable dim fvtable as new DataTable dim fvrow as DataRow...
1
by: Jeff | last post by:
hey asp.net 2.0 Is it possible to send a System.Data.DataTable as a input parameter to a stored procedure (MS Sql Server)? Jeff
0
by: Maart_newbie | last post by:
Hi all, I've got a question about returning the value of a pk-column to a DataTable after inserting a row (via a data-adapter) using MySql5. Here is the SQL and code concerned: ...
0
by: Jeff | last post by:
..NET 2.0 I'm working on a .NET 2.0 project which are using a DataSet for all communication with the database. Here is the problem: I want to search a table based on a matching criteria (I...
3
by: Nuno Magalhaes | last post by:
Hello, I have a DataTable in which the items are of type MyClass. How can I use Select to compare a MyClass instance with the DataTable? Is there any overriden method (like the ToString()) to...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.