Is SQL code faster than C# code?

Question

Few months ago i started to work at this programming company. One of the practices they use is to do as much work as possible in SQL rather than C#. So, lets say i have this simple example of writing a list of some files: Is something like this: ``` string SQL = @" SELECT f.FileID, f.FileName, f.FileExtension, '/files/' + CAST(u.UserGuid AS VARCHAR(MAX)) + '/' + (f.FileName + f.FileExtension) AS FileSrc, FileSize= CASE WHEN f.FileSizeB < 1048576 THEN CAST(CAST((f.FileSizeB / 1024) AS DECIMAL(6, 2)) AS VARCHAR(8)) + ' KB' ELSE CAST(CAST((f.FileSizeB / 1048576) AS DECIMAL(6, 2)) AS VARCHAR(8)) + ' MB' END FROM Files f INNER JOIN Users u ON f.UserID = u.UserID "; // some loop for writing results { // write... // } ``` Faster or better then something like this: ``` string SQL = @" SELECT u.UserGuid, f.FileID, f.FileName, f.FileExtension, f.FileSizeB FROM Files f INNER JOIN Users u ON f.UserID = u.UserID"; // some loop for writing results { string FileSrc = "/Files/" + result["UserGuid"] +…

Accepted Answer

It just a bad programming practice. You should separate and isolate different parts of your program for ease of future maintenance (think of the next programmer!)

Performance

Many solutions suffer from poor DB performance, so most developers usually limit the SQL database access to the smallest transaction possible. Ideally the transformation of raw data to human readable form should happen at the very last point possible. Also the memory usage of non-formatted data is much smaller, and while memory is cheap, you shouldn't waste it. Every extra byte to be buffered, cached, and transmitted all takes up time, and reduces available server resources

e.g. for a web application formatting should be done by local JavaScript templates from a JSON data packet. This reduces the workload of the backend SQL database and application servers, and reduces the data that needs to be transmitted over the network, all of which speeds up server performance

Formatting and Localisation

Many solutions have different output needs for the same transaction e.g. different views, different localisations etc. By embedding formating into the SQL transaction you will have to make a new transaction for each localisation, this will be become a maintenance nightmare

Also formatted transactions cannot be used for an API interface, you would need yet another set of transaction for the API interface which would have no formatting

With c# you should be using a well tested template or string handling library, or at least string.Format(), do not use the '+' operator with strings, it is very slow

Share the load

Most solutions have multiple clients for one DB, so the client side formatting load is shared with the multiple clients CPU's, not the single SQL database CPU

I seriously doubt SQL is faster than c#, you should perform a simple benchmark and post the results here :-)

Is SQL code faster than C# code?

Solution

Problem