C# Performance vs. T-SQL Stored Proc Performance

Posted on 2005-04-07
Last Modified: 2008-01-09
Hi all,

I realize this is a hard question to answer but here goes.  We are debating in our company which way we should develop for modeling financial information.  Should the processing code in C# or T-SQL.  The benefits I see in C# is OO (classes, encapsulation, etc), organization of classes (i.e. not just a plethora of stored procs), the ability for users to process data on their machines without many users constantly having SQL Server run huge intensive processes, bumping against maintenance and updating processes at various times, and possibly moving code over to serverside for webqueries in the future for asp and webservices.  The downfalls I see are software deployment which initially will just be for internal use so that is almost a null issue.

So I did some testing...

Using C#, I pulled a set of data (1.9 million records) into a arraylist and perform sorting.  The time it took to pull the data across the network and populate the arraylist with classes was about 20 sec.  I then created the programming to sort a class member which took 25 sec to sort.

Now if I run the following sql statements:

Create Table #tmp (ID int, kDate smalldatetime, Pct float)

Insert INTO #tmp (ID, kDate, Pct)
SELECT ID, kData, Pct
ORDER BY Pct  --Pct is not indexed

Here is the execution plan
          Total Run Time      2:48
          Execution Cost      
Cluster Index Scan      69%      1:55
Sort                      29%      0:48
Parallelism                      3%      0:05
Select                      0%      0:00

So, my conclusions are pulling data into a arraylist (and that was just a quick one I pulled, there maybe a better data structure) and sorting is faster than SQL Server running that same query.  Especially since I need to put the data in a temp table worry about locks,  etc.  Are my conclusions valid?  I also know that there is a lot of performance tuning that could be done to our servers.  We are running on a CL380, 2 x 1 gig procs and 4 gig ram.

Any thoughts or recommendations are greatly appreciated.

Kind Regards,

Question by:karlkawano
    LVL 4

    Assisted Solution

    i was generally under the impression that it was better to let SQL Server do the sorting, etc.  I guess there are certain circumstances when it is faster to code the sorting in C#, but generally and ORDER BY clause should be faster.  The difference in performance could just mean that there is a greater load on your database than on the app server.  In this case it would make sense to focus on optimizing your stored procedures and focusing your upgrades on your Database server.  As far as organization is concerned, it is often easier to take advantage of oo programming and while it may not be as efficient as other methods, in the long run, it makes the code much easier to maintain and is much more scalable.  So really, i guess it is a question of balancing efficiency with scalability and code-reuse.  Not sure if i mentioned anthing new to you, but hope it helps.

    Author Comment

    That is pretty much my opinion too.  One other thing I failed to mention (I guess because I am searching for that now) is this:  The first step is to perform many rankings on the data which is the reason for the sorting.  After it is sorted, I need to search and eliminate records (like a where clause).  I do have heuristics I can use when going through the data and my thought is I can eliminate the one criteria that has the greatest impact on the data first and then run other conditions to eliminate records afterwards.  This would be used like a where clause but with some intelligence built in.

    I have found that the placement of conditions in a where clause do not matter since SQL Server optimizes it for you.  Do you happen to know if using my AI technique will produce better performance than a where clause with statistics turned on to isolate data?


    LVL 23

    Assisted Solution


    For what its worth, here is my take on "best practices".

    Stored procedures are compiled code on the database.  C# (or any other language for that matter) must send it's SQL code to the database which then interprets that code.  Therefore, any code that is executed more than 50 times per day (for example) should be set up as a stored procedure.  

    Further, I would also encourage you to create a stored procedure for all CrUD functions for each table.

    If you want to know which is the best method (all stored procedures vs all embeded SQL), the really big companies actually do neither.  They have all of their SQL code stored inside the database itself with only a small handfull of stored procedures that get you started.


    Author Comment

    Hi Tad,

    Thanks for the response.  Let me make one more clarification.  The pull from the database only happens once or twice which would hit stored procedures.  It is the resulting sorting and calculations that we are curious about.  So, in other words, do we pull the data once into a C# arraylist and do all the sorting, ranking, calculating and present the results on the local machine OR do we build all the sorting (well, that is built in using ORDER BY), ranking (we need special ranking routines we write ourselves in another SP), and calculating using temp tables, SPs, SQL Server Functions and SQL queries, and then pull the resulting data to the local machine for presenting the data?

    Sorry, I didn't elaborate further.

    I also realize this is a tough question to answer not knowing the project, but any answers or thoughts I can consider are of great use.

    Thanks again,

    LVL 23

    Accepted Solution


    Since this is only happening once or twice per person, I think performance is the least of your worries.

    Network bandwidth... should be the same either way.  At some point all/most of the information has to go across the network.

    Memory... A million records can be handled by the server or a relatively beefy pc without a problem.  What about older computer?  Or computers with only 128 MB of RAM?  How many people could be sorting this same data at one time?  Will you re-sort with each request? If 100 people hit that server around the same time, everyone would notice.

    What are you plans for the future?  Obviously this is a thick client app... Have you given any considderation about going to a web based app in the future?  If you do, you'll most certainly want to do all of your sorting in the database (that would be a lot of work for a web server that needs to serve up web pages).

    Finally, another option would be to create a web service to do the work for you.  The down side is that you'll have twice as much network traffic (passing data from the db to the web service, and then from the web service to your web/winform app).  The plus side is that you can put that web service on a robust server somewhere that can perform those types of tasks.  In fact, my company has done just that... we have a very robust server that handles virtually all of our company's web services.

    Personally, I would try to do as much work on the db server as possible, and anything that consumes too many resources I'd send to a web service.  I have this thing about extensibility... if you decide to put all of your work on the client machine, and then go to a web client, you've just changed your entire architexture.  On the other hand, if you keep your win client as light as possible, and then switch to a web client, your architechture can remain mostly intact.
    LVL 8

    Assisted Solution

    This is my opinion-)
    You generally need to do sorting at the server (database) - this is obvious for performance reasons.
    Moreover the sorting code would be complicated if you put in more- Never ever it came to my thought of putting the sorting logic in the front end-)

    **Also more importantly Sorting can be made a lot lot quicker if you build and index on the columns in the WHERE clause

    LVL 2

    Assisted Solution

    just a comment

    if you do sorting dont use array lists, they are really slow.
    C# implements Fast Quicksort on arrays and need no casting.
    A array list need to cast the items. My little tests:

    40 000 Integers
    Quicksort: 00:00:00.0100144
    .NET Sort: 00:00:00.0100144      //Array.Sort()
    FastQuicksort: 00:00:00.0100144
    Bubble Sort: 00:00:09.6438672
    Array List Sort: 00:00:00

    2 000 000 Integers
    Quicksort: 00:00:09.3734784
    .NET Sort: 00:00:00.5908496
    FastQuicksort: 00:00:00.5608064
    Bubble Sort: 00:00:00                    //Deactivated because long execution time
    Array List Sort: 00:00:04.8169264

    35 000 000 Integer
    Quicksort: 00:00:00                  //Deactivated because long execution time
    .NET Sort: 00:00:11.7569056
    FastQuicksort: 00:00:11.7869488
    Bubble Sort: 00:00:00            //Deactivated because long execution time
    Array List Sort: 00:00:00        //Deactivated because long execution time

    mfg WebSpecials
    LVL 2

    Expert Comment

    oh forgot my machine specification

    Win XP
    512 mb ram
    Centrino 1.6
    2m cache

    mfg WebSpecials

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Article by: Ivo
    Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now