Stored procedure getting results from views that are joining several tables

Question

Stored procedure getting results from views that are joining several tables

jdc

Old Hand

Points: 367
More actions
August 2, 2019 at 7:14 pm

#3668355
Hi,
We have a stored procedure which is executing during a batch job and it is taking 7 seconds to complete and that batch job runs hours to complete. And I'm seeing this procedure called continuously during the batch job and each RPC:completed is taking 7 seconds and returing same rows
Started troubleshooting this procedure and I have noticed that this procedure has 2 views that are selected rows joining several tables.
So, is having views in stored procedures good idea? Should we make those views to permanent tables to improve the performance?
- This topic was modified 5 years, 3 months ago by jdc.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

A much better idea is to find out why the batch job is taking 7 hours. Is it actually because this stored procedure taking 7 seconds? While that may certainly appear to be the problem, I suspect that the batch job is a load of RBAR that needs to be fixed so that it doesn't have to call the stored procedure (I'm making a bit of an assumption here) once for every row or whatever in the overall batch job.

--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)

Chris Wooding SSCarpal Tunnel Points: 4556 More actions · Answer 2

If the stored procedure is returning the same results each time, it might be worth amending it to store those results in a table that can be referred to whenever necessary. Then you only need to call the stored procedure once at the start of the job. That said, it doesn't sound as if the stored procedure and its views are the only problem (as Jeff suggested).

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

There's nothing inherently wrong with views in stored procedures. However if the data from the view doesn't change during the entire batch process you might consider preloading the data into a staging table and using that in the SP instead of querying the view every time.