September 21, 2016 at 4:09 am
Hi All,
I have two table customerorder and customerorderdest. I wrote SP which load data from customerorder into customerorderdest. currently i am using select top 1 but i want it dynamic. first transaction will fetch first row... second transaction will fetch second row ... so on.....
Also let me know is there any locking effect because all 6 transaction using same tables. Please find attachment to create tables with data and SP.
Thanks,
Lat
September 21, 2016 at 4:23 am
Lat
Why are you doing this row-by-row? Why not update everything in one go?
John
September 21, 2016 at 5:17 am
I have total more than 1 million records and my SP will use by .net application with 15-20 thread. so Application read one row and update status base on some parameter.
September 21, 2016 at 5:42 am
A million rows is not large, and processing row by row is inefficient.
Don't do it from .net (even slower), do whatever you need as a single transaction in T-SQL.
If the workload needs paralleling, SQL can do it automatically.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2016 at 6:52 am
Understand but .net application need to update status back to table. so i am worried about locking issue
September 21, 2016 at 7:00 am
I strongly recommend you reconsider the entire process.
Leave .net out, other than to call the stored procedure. Write the load process in T-SQL in a single batch. Do the same with whatever status the app would have sent.
That way you don't need to manage multi-threading in the app, you don't need to worry about locking, and the process will likely run faster than one-row-at-a-time roundtrip processing could ever have done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2016 at 7:02 am
GilaMonster (9/21/2016)
I strongly recommend you reconsider the entire process.Leave .net out, other than to call the stored procedure. Write the load process in T-SQL in a single batch. Do the same with whatever status the app would have sent.
That way you don't need to manage multi-threading in the app, you don't need to worry about locking, and the process will likely run faster than one-row-at-a-time roundtrip processing could ever have done.
I agree. And if locking proves to be a problem after doing this, please open another thread to discuss the changes needed to your proc to resolve it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 23, 2016 at 9:55 pm
Here's another vote for leaving .NET entirely out of it. You will get enormous throughput increases by just having SQL do this in a procedure. Running 20 instances of any application all of which are inserting/updating/deleting one row at a time is like a death by a thousand cuts. Slow and painful.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 24, 2016 at 8:43 am
Piling on with Gail, Phil and Dixie, let me also cast my vote for having .NET call the stored procedure and doing the work there as a set. Row-based processing doesn't scale well at all, while set-based processing does. Don't be intimidated by 1M rows and think you have to engineer a complicated, multi-threaded .NET application. Let SQL Server do what it does best.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply