Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am running some performance tests and I started with a dataset of 900.000 records the update took 10 seconds or about 90000 records per second. Then I increased the number of records to 9.000.000 and the update took 170 seconds or about 52941 records updated per second.

Why does the number of records updated per seconds drop when the total number of records increases?

The distribution in the data is the same, the larger set is the smaller set copied 10 times. The size of the data and log files did indeed increase since I copied the data 10 times. I did shrink the log file after that. There is no other workload on the database server at this moment.

The SPROC doing the update is the following:

CREATE PROCEDURE updatePoints      @ids NVARCHAR(MAX)    AS 
DECLARE @points1 INT
DECLARE @points2 INT

SET @points1 = 1
SET @points2 = 2

UPDATE  p
SET     points = (  0
                   + CASE WHEN 
                               p.[x] = m.[x]
                               AND p.[y] = m.[y]
                          THEN @points1
                          ELSE 0
                     END
                   + CASE WHEN 
                               p.[u] = m.[u]
                               AND p.[v] = m.[v]
                          THEN @points2
                          ELSE 0
                     END

                     )
FROM    p 
        JOIN  m ON m.id = p.mid
        JOIN platform.Split(@ids) i ON i.Value = m.id
WHERE   m.[status] = 1
share|improve this question
Does the split function scale linearly? I'd test that first. If it does please post the plan. – Martin Smith Jan 11 '12 at 21:03
@ids contains the same 10 id's in both cases so the performance of the split function is equal. – olle Jan 11 '12 at 21:31
I am seeing a 800% difference in estimated rows and actual rows. I will update the statistics tomorrow (don't have the proper permission currently) and see if the problem persists and update the question accordingly. – olle Jan 11 '12 at 21:52
That might be because of the TVF. It will assume it contains 1 row not 10 so might propagate from there. What join types and what join order do you get and what is the estimated vs actual coming out of each iterator? Also if you insert the result from split into a #temp table with a primary key on id first does that improve things? (This will allow it to use statistics on the split result) – Martin Smith Jan 11 '12 at 21:54

2 Answers

up vote 4 down vote

If you post the execution plans there may be obvious differences that we can highlight. Other than plans it could be:

  • Different distribution of values in the dataset, leading to more frequent page splits
  • Data and or log file growth
  • A sort or hash operation spilling to tempdb
  • Contention with other workloads

Need more information (queries, sample data, schema, plans) for better answers.

share|improve this answer
Ok I will collect them and update the question with them. THanks so far. – olle Jan 11 '12 at 20:49
Marked this answer as correct since the problem was out of date statistics which was visible in the execution plan as a difference in nr of expected and actual rows. – olle Jan 12 '12 at 10:58
up vote 1 down vote

Did you take a look at the execution plans? There could be several explanations. The plan might have changed. Or maybe in your first test the data was already in cache whereas in the second case it had to be read from disc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.