[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

C# Performance vs. T-SQL Stored Proc Performance

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
FROM MyTable WITH (NOLOCK)
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,

Karl
0
karlkawano
Asked:
karlkawano
  • 2
  • 2
  • 2
  • +2
5 Solutions
 
vladimir_kalashnikovCommented:
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.
0
 
karlkawanoAuthor Commented:
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?

Thanks,

Karl  
0
 
_TAD_Commented:

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.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
karlkawanoAuthor Commented:
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,

Karl
0
 
_TAD_Commented:

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.
0
 
rajaloysiousCommented:
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

cheers
0
 
WebSpecialsCommented:
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
0
 
WebSpecialsCommented:
oh forgot my machine specification

Win XP
512 mb ram
Centrino 1.6
2m cache

mfg WebSpecials
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now