IF Statements affecting performance ?

Question

IF Statements affecting performance ?

meandnayan

Old Hand

Points: 360
More actions
July 30, 2009 at 9:00 pm

#224316

Dear all,
I know that the code without IF statements would be better enough than a code with IF statements.
But, just a matter of curiosity, will the IF statements perform really bad when we have a big chunk of data to process row by row.
For example, I have a scenario where there is an entity which has different sub entities and I have a main service for the host entity and child services for the child entities. When creating new entry, I call the host service and put in all the host and the child data in it to process which then eventually calls the database host procedure and different child procedures accordingly.
For an update of a child, I call the child service only and eventually which then points to the child stored procedure and process the data.
This will create multiple entry and exit points for a single area (the host)
What I am thinking is of changing it so that I have only a single entry and exit points (the host) and then let the core entry (procedure) decide which child procedures to be called according to the data passed in it and do the processing.
Will this be a performance issue ? Am bit confused. I think that the present stuff is okey but, from the maintanance point of view, its bit diffcult.
The new structure in the database would be a great help in logical terms as well as in terms of maintanance, etc. is what I think. Am bit confused.
Can anyone share some light on this please..?
Thank you.
Regards,

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

meandnayan (7/30/2009)
I know that the code without IF statements would be better enough than a code with IF statements.

Maybe

But, just a matter of curiosity, will the IF statements perform really bad when we have a big chunk of data to process row by row.

I would suspect on a big chunk of data, the row-by-row will be more of a problem than the if statements are.

Want to post the code and let us look?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Heh... Gail took the words right out of my mouth.

Post your code and let's have a look-see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

I don't think IF statements are related to performance at all. Using IF statements is flow control - not processing so there really isn't anything that would cause a performance issue related to using IF.

As others have already stated, the row by row processing is what's causing performance issues and can most likely be reduced a lot by converting to a set-based approach.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Jeffrey Williams (7/31/2009)
I don't think IF statements are related to performance at all. Using IF statements is flow control - not processing so there really isn't anything that would cause a performance issue related to using IF.

It is possible to get a form of parameter sniffing with multiple execution paths.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

GilaMonster (8/1/2009)
Jeffrey Williams (7/31/2009)
I don't think IF statements are related to performance at all. Using IF statements is flow control - not processing so there really isn't anything that would cause a performance issue related to using IF.
It is possible to get a form of parameter sniffing with multiple execution paths.

Now you have me interested... Could it be worse than the fact that the code is RBAR to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Unlikely.

Oddly enough, row-by-row code doesn't usually suffer badly from parameter sniffing. Parameter sniffing is worst when the optimiser compiles a statement thinking that there's a very small number of rows and when the statement executes there's a very large number of rows. If you're working on one row at a time then that's not likely to happen (possible, not likely)

If this was my code, I'd be converting the code to set-based first then looking to see if there's parameter sniffing as a result of the if statements.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Heh... nope. I knew that and I've asked the question incorrectly... What I should have as about the following is...

GilaMonster (8/1/2009)
Jeffrey Williams (7/31/2009)
I don't think IF statements are related to performance at all. Using IF statements is flow control - not processing so there really isn't anything that would cause a performance issue related to using IF.
It is possible to get a form of parameter sniffing with multiple execution paths.

... could you explain that a bit more for me because I'm not sure how what you said apples to what Jeffrey said.

Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Oh sure. I must actually write a blog post on this, people keep asking.

If statements don't have a direct affect on performance. They can't, they're just flow-control, not actual processing. The problem comes more from multiple execution paths in a proc rather than directly from an IF statement.

The potential problem has to do with when the compile happens. I'm sure you know that when a stored proc is first executed the entire procedure is compiled and all queries in that procedure are optimised. That includes queries that would not be executed because of flow-control statements. When that compile happens, the optimiser will compile all the queries and use the parameters passed for that execution to generate execution plans for all the queries in the procedure

Taking the example I used in a presentation last year...

CREATE PROCEDURE MultipleExecPaths (

@TransactionType char(1) = NULL

)

AS

IF @TransactionType IS NULL

SELECT max(transactionDate) from Production.TransactionHistory

ELSE

SELECT max(transactionDate) from Production.TransactionHistory where TransactionType = @TransactionType

Let's say that the first execution, the one that requires a compile, has no parameter passed. That means that the first branch of the IF statement will be executed. That's fine, no problem. However the optimiser also compiles the second branch, despite the fact that it will not execute based on those parameter values. Optimiser doesn't check flow-control, so it doesn't know (or doesn't care)

The second branch will be optimised for a where clause "where TransactionType = NULL", which of course cannot return any rows (assuming default ansi nulls settings). Hence optimiser generates a plan optimal for 1 row.

Next time someone runs the procedure they pass a parameter value of 'S'. In this case the second branch of the IF will be executed, using the plan previously generated. Not returning 0 rows, but several thousand. The plan generated on the earlier execution was completely inappropriate because it was generated with parameter values that it will never run with.

I don't have AdventureWorks on my laptop, so run these and see just how badly that proc runs when the parameter is passed only on the second execution

SET STATISTICS IO ON

SET STATISTICS TIME ON

GO

Print 'No Parameter'

EXEC MultipleExecPaths

Print 'with Parameter'

EXEC MultipleExecPaths @TransactionType = 'S'

Then contrast with how it runs when the calls are in the opposite order

exec sp_recompile 'MultipleExecPaths'

Print 'with Parameter'

EXEC MultipleExecPaths @TransactionType = 'S'

Print 'No Parameter'

EXEC MultipleExecPaths

That's the form of parameter sniffing I was talking about.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

Ah! Got it. Thanks for taking the time to explain. Heh... it's funny... I knew that could be the case in a single query but I wasn't putting 2 and 2 together on the multipath thing. I guess the big reason there is that I'm mostly and ETL and batch processing type of data troll... Although I'll use IF for flow control, I don't use it that way. Thanks again, Gail.

It would be a great addition to your blog but it would also make a hellofa nice article for SSC, too!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

Jeff Moden (8/1/2009)
It would be a great addition to your blog but it would also make a hellofa nice article for SSC, too!

Maybe what I'll do is blog it, then (if Steve approved) combine a couple blog posts that I've done on bad query constructs into an article and publish it here. This, catch-all queries, functions in where clause, implicit conversions, etc. All based on the presentation I did at PASS last year

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

GilaMonster (8/1/2009)
Jeff Moden (8/1/2009)
It would be a great addition to your blog but it would also make a hellofa nice article for SSC, too!
Maybe what I'll do is blog it, then (if Steve approved) combine a couple blog posts that I've done on bad query constructs into an article and publish it here. This, catch-all queries, functions in where clause, implicit conversions, etc. All based on the presentation I did at PASS last year

That would be awesome.

Speaking of PASS... I'm not a speaker. I never got any notification one way or the other. I can't even check what I did wrong because the submittal site is closed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)