-1

There are 1,00,000 Update Statement available in a SQL table EexecuteQueue

Below is the Step I am planning to do.

Identify the Logical Processor of the Database server.

The queries available in the EexecuteQueue table will be split in to number of (logical processor-2) and execute in different thread.

My assumption is Instead of executing 1,00,000 update statement sequentially, threads will execute 25,000 update statements in parallel (If we have 4 Threads).

My Question Is my assumption correct? Is it good to user Threads in CLRSQL?

Thanks in advance.

user1451208
  • 19
  • 2

1 Answers1

0

My assumption is Instead of executing 1,00,000 update statement sequentially, threads will execute 25,000 update statements in parallel (If we have 4 Threads). Is my assumption correct?

Yes, but is completely irrelevant. Doing 25k operations on 4 threads by no means implies is going to be faster than doing 100k operations on a single thread. Such an assumption is, at best, naive. You need to identify your bottlenecks and address them accordingly, depending on your findings. Read How to analyse SQL Server performance.

Is it good to user Threads in CLRSQL?

No.

To speed up batch updates, use set based operations. Reduce number of round trips. Batch commit.

Remus Rusanu
  • 273,340
  • 38
  • 408
  • 539
  • Thanks for your suggestions – user1451208 Mar 06 '14 at 16:34
  • Is it good to split Threads in my Window application to call the CLR Stored Procedure 4 times with 25K Query execution each? – user1451208 Mar 07 '14 at 07:27
  • No, the only thing good is to measure performance and address bottlenecks. Random 'lets try this' are never a good approach. Besides, adding threads is by definition a bad idea when you can submit non-blocking async requests with [`BeginExecuteNonQuery`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.beginexecutenonquery(v=vs.110).aspx) – Remus Rusanu Mar 07 '14 at 08:26