Performance difference in condition statements in mssql vs C#?

[Hmongkeysauce]

If you can put an 'if' conditional statement in either a mssql stored procedure or in the C# source, which would be better in terms of performance and structure? Would it matter?

 

[tfinch2]

Why not write a microbenchmark to find out the performance differences?

IMHO, I'd put it in the stored procedure though. This seems like the cleaner approach.

 

[drebo]

Definitely the stored procedure.

If you put it in the C#, you have to pull the conditional data to figure out which other data you need to pull. In the stored procedure, it (presumably) already has access to that data, and thus your ODBC/ADO/whatever data connector only needs to pull one set of data.

 

[Rangoric]

Originally posted by: drebo
Definitely the stored procedure.

If you put it in the C#, you have to pull the conditional data to figure out which other data you need to pull. In the stored procedure, it (presumably) already has access to that data, and thus your ODBC/ADO/whatever data connector only needs to pull one set of data.

I'm going with this response although you should do a mini benchmark because SQL can be... Funny at times.

If the conditional is something dealing with the database, it should almost always be the case that the SQL is faster though. If it is something outside the database it might be the case that the C# is faster.

 

[clamum]

I think it definitely depends on the context. I assume the if statement has something to do with the data in the database but without more of a description it's hard to answer completely.

 

[Markbnj]

I agree with clamum. More context is needed. What you don't want is to have business logic buried in the database, imo. I prefer to use stored procs as API wrappers for transactional updates and complicated joins. I like it a lot less when I see conditional logic in them, even when the logic is based on the state of the data.

 

[Hmongkeysauce]

It's actually a rather simple block of code.

adapter = (lang == "eng") ? adapter = new SqlDataAdapter("pullEngArticles", con) : adapter = new SqlDataAdapter("pullOtherArticles", con);

I originally had that conditional statement in the stored procedure and passing in the parameter @lang. Benchmarks showed that leaving the conditional statement out in the C# code is about .0004 seconds faster. 🙂 That may not matter but when you start having a few of these on the same page, it may start adding up.

 

[clamum]

I would definitely use the code to do that if statement check.

 

[Rangoric]

Originally posted by: Hmongkeysauce
It's actually a rather simple block of code.

adapter = (lang == "eng") ? adapter = new SqlDataAdapter("pullEngArticles", con) : adapter = new SqlDataAdapter("pullOtherArticles", con);

I originally had that conditional statement in the stored procedure and passing in the parameter @lang. Benchmarks showed that leaving the conditional statement out in the C# code is about .0004 seconds faster. 🙂 That may not matter but when you start having a few of these on the same page, it may start adding up.

This is going to sound odd and you prolly did it already, but did you do it with say 1000 times of each routine?

By the looks of it it should be faster in C# (I'm fairly certain of it) but might as well be sure.