Click here to Skip to main content
16,019,107 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hello I have a web api 2 which called for a stored procedure and I almost called this 3000 web api so I have 3000 execution of a stored procedure and certain moment I had a bug conextion timeout
my comandTimeout is 3 minute
what the solution at the architecture or the SQL Server level to solve this scalability problem thank you
Posted
Updated 29-Sep-16 4:20am
v2
Comments
[no name] 29-Sep-16 8:18am    
How many times are you going to repost this? What answers were you already given? It's going to be the exact same answer.

1 solution

As i mentioned before in your prior reposts. You haven't given us nearly enough information to help you.

It seems as though you are calling 1 stored procedure 3,000 times, or calling 3,000 stored procedures at the same time. Either way that is insane.

Your issue isn't infrastructure related, it is design related. You need to rethink the design of what it is you are trying to accomplish rather than just throwing more money at the problem in terms of hardware/resources.

Remember, we don't have access to your hard drive, your code, nor do we read minds or know anything about your project. You've stated your problem as though we have in depth knowledge of your issue/project and expect someone to offer meaningful suggestions. Until you take a step back and realize you probably need to rethink your SP, your going to run into this problem constantly
 
Share this answer
 
Comments
AbassiOmar 29-Sep-16 11:22am    
thank you for the answer
I explain my problem

I have one stored procedure that will execute a web api
but I have 3000 customers that will consume the API web at the same time
the execution time of my stored procedure is approximately 1 second
So I had a scalability problem
so my question is is this-that it there's a solution to the 3000 mile called in a sort of quee
or if there is someone who has worked on this problem before
cordially
thank you
David_Wimbley 29-Sep-16 11:52am    
So currently you run 3,000 stored procedures at 1 second each sequentially. Meaning one after the other. That is about 50 minutes of processing in total. It is no wonder you timeout.

I really think you need to rethink what you are doing but lets run with trying to fix the 3,000 SP problem.

So you have a few options, but will run into scale issues later, ignoring the problem won't make it go away as you are just giving it more room to grow by adding infrastructure.

1) You can use a pub/sub architecture (event driven) where anytime the customer needs to execute the SP, they publish to say...redis...and then a subscriber to redis then acts on that published notification

2) Load balancing/rethinking your SP to a degree - Rather than be number 2,898 in line...have the customer execute the SP on demand. So instead of executing 1 stored procedure 3,000 times in one request. You execute it, potentially, 1 time but your server might have to handle 3,000 requests which shouldnt be that big of a deal.

3) Queue the jobs in a scheduler and run via a window service or something like that.

4) If you still insist on running the one stored procedure 3,000 times then look into mulit-threading/Tasks. There is plenty of documentation on this so a simple google search will provide you tons of examples.

There is no 100% proper solution for your problem as you need to decide what best fits your needs.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900