Click here to Skip to main content
14,271,874 members
Rate this:
Please Sign up or sign in to vote.
See more:
Facing an issue in stored procedure when executed by multiple users at same time trying to insert thousand of row data in table, SQL cant handle multiple insertion at same time,is there any way to fix this issue.

What I have tried:

i am using SET "LOCK_TIMEOUT 15000" in procedure.
Posted
Updated 16-Aug-19 4:30am
v3
Comments
MadMyche 14-Aug-19 9:32am
   
Really hard to help you fix code we cannot see; without at least an overview of the table indexes and the procedure optimization, how are we supposed to have an answer?
Sivachandran R 14-Aug-19 11:25am
   
Actually syncing data from mobile to sql and sql to Mobile through web api . Table format is having clustered and non clustered index. At same time multiple devices send and receive huge amount of data from sql server. Procedure has normal insert and update into tables. I will post the sp when I reach my work place. Thanks for your reply
Rate this:
Please Sign up or sign in to vote.

Solution 1

Upload the transactions from each device as a batch to a flat file; then start a processor for each batch instead of "force feeding".
   
Comments
Sivachandran R 15-Aug-19 12:54pm
   
Thanks for your reply. Can you please give me more details regarding “batch as flat file”,since I am very new to android device application development, even if any article.
Gerry Schmitz 16-Aug-19 11:20am
   
How many devices? Spawn multiple servers and sync for a 25 cent solution.
Sivachandran R 16-Aug-19 13:02pm
   
Around 10 devices each devices post around 2500 records to same table at same time. All the data in Json object post through web api.so which can be the better option for answer 2.
Rate this:
Please Sign up or sign in to vote.

Solution 2

To my understanding of the issue - assuming the stored proc is doing a lot of business logic, you can do;
1) Dump the data from each user into a Db table (Not files - very hard to manage). Use a Windows service or a scheduled task to read the data from this table and execute the storeprocedure.
2) Dump the data from each user into a Db table (Not files - very hard to manage). Use a SQL job to read the data from this table and execute the storeprocedure.
3) Make your API call single threaded so it will make any subsequent call to wait using object locks. This needs a lot of managed code to achieve based on how many users are posting data. If the Stored proc is doing straight insert this is a good option.
   
Comments
Gerry Schmitz 16-Aug-19 11:13am
   
The "transactions" arrive as "flat records".

Whatever your feelings are about "flat files", the UPLOAD starts with a "message" FILE (XML; JSON; BINARY) that is FLAT. The SERVER updates the DB with FLAT records from the device. Whether the FLAT records get STAGED in a DB file is an INTERMEDIATE step between FLAT and DB.

Do you understand that he says the "DATABASE server is the bottle neck"? There are "file" (upload) servers (i.e. FTP) and DATABASE servers; and there is a DIFFERENCE.
Vivek_Dutta yesterday
   
Thank you for emphasizing the key points. I surely have missed that. The files could be staged on the server (API can just dump the flat files to a server location) and a windows service could read each file and transact the data into the DB. Making sure no other process inserts records into these tables.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100