Stored Procedure execution

Database

Stored Procedure execution

Hello All, 

I have run few adhoc queries where it has many COALESCE statements also query run time was 30 seconds and Impact CPU is 1500 but, if I try same query in stored procedure and CALL it. It was taking long time and huge Impact CPU. Also I see all COALESCE  are converted to CASE statements which was not changed when ran it as a adhoc and in SP I see query got lots of braces '('.

Is Query after ruuning as adhoc and running from SP differs a lot ? I see output query in DBC

2 REPLIES
Senior Apprentice

Re: Stored Procedure execution

The parser will convert all COALESCE/NULLIF into CASE expressions (they're just convenient shortcuts) and adds lots of brackets.

So seems like you looked at dbc.QryLogExplainV?

Perfromance should be similar for adhoc vs. SP, can you show more details of the SP?

Re: Stored Procedure execution

Thanks Dieter for a quick response.

Actually SP is too big  to share. I want to know that whether SP decreases any performance of a query or not.

I really thank you and appreciate you Dieter. I learned a lot with yours posts.