How to avoid stored procedure executions by multiple times

Question

1.00/5 (1 vote)

See more:

Hi Experts,
I'm request you the suggestions and ideas to accomplish my request.

in my website running all over the countries . in my work flow there is too many inserts in multiple tables , everyday all the branches, they are getting daily basis report. which i was used stored procedure to generate report. becoz there is lot of joins criteria.

my point is i've 30 branches so everyday minimum 40-50 executions will occur. my plan is single time i've execute the stored procedure then getting records for all branches and push it to one global table after that whenever client side trying to generate the report means we can get the report data from global table . because its direct (select * from globaltable where date and branch instead of so much of joins)

is this right way or please suggest me any other way to achieve this to improve system performance.

What I have tried:

Need an idea to avoid same procedure execute multiple times.

Posted 21-Jun-17 2:05am

sencsk

Updated 21-Jun-17 2:09am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2017-06-21T02:09:00

Solution 1

As SPs borned to run simultaneously you have to be creative here...
Think of something like this:

1. Check if there is a temp table by name of today date
2. No - create one and run the rest of the SP to push data into, then select data from the table
3. Yes - select data from the table

Posted 21-Jun-17 2:09am

Kornfeld Eliyahu Peter

Comments

sencsk 21-Jun-17 8:13am

Hi Peter, thanks for writting.. but in my case even though user can generate existing previous dates to or between a month.. there is so many time frames

Kornfeld Eliyahu Peter 21-Jun-17 8:16am

Then wrap the SP into an other SP and run it for each day in your time frame...
Then the wrapper can return the UNION of all the relevant tables...
Other idea can be to handle a control table...
If there is no indication for the current day in the control table, then push the data to the data table and update the control table...
It will give you a single table with data for multiply days..

sencsk 21-Jun-17 8:35am

im creating one job to insert rows every 30 mins. I'm inserting the stored procedure result to one Newtable. so is there a way to sync newtable with stored procedure output.