C# Performance vs. T-SQL Stored Proc Performance
Posted on 2005-04-07
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
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.