Re: counting rows

From: Tony Rogerson <tonyrogerson_at_sqlserverfaq.com>
Date: Sun, 2 Apr 2006 12:15:52 +0100
Message-ID: <e0obp1$nai$1$8302bc10_at_news.demon.co.uk>


I think you've fallen into the usual developer trap in thinking what works small will scale.

If there are only 5 or 6 rows per user then fair enough, but what about a few thousand, 10'000's? Do you really think the IO will scale....

Say it costs 10MBytes to realise the COUNT(*) each time a user queries it, now times that by 10, its 100MBytes, then by a thousand its 1GByte, thats 1GByte of memory (if cached) that needs to be queried in order to realise the result.

Locking aside (which is still a problem because you need to serialise and get an accurate result), IO comes in to play.

The trigger is a simple look up via a unique index, probably a few KBytes and then an update on those same pages - changing a 4 byte value.

Try scaling it up - give each user 1000 pictures and simulate 10 users running COUNT(*) and measure your CPU, now use the trigger method (+1 or -1) and your CPU will be negligable.

If you can post the SQL you used I can show you what I me.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"E. Lefty Kreouzis" <lefty_at_goedel.home.rtfm.gr> wrote in message 
news:slrne2vbpq.rpd.lefty_at_goedel.home.rtfm.gr...

> On 2006-04-02, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> wrote:
>> Hang on a minute, the whole point about the serialisable transaction
>> isolation is that no other connections can insert rows within the key
>> range
>> your query is operating on.
>>
>
> No I'm not, the two transactions are inserting rows with different primary
> keys. Easy to do with autoincrement columns.
>
>> So how on earth can anybody insert a row for that user if you are in the
>> middle of a COUNT(*)?
>>
>
> the row that the other transaction is insering is not visible to the
> transaction
> that does thw count(*) so that it shouldn't modify the result.
>
>> The test you are doing is not realistic, you are isolating the
>> connections
>> by partition the data which in reality isn't something that happens.
>>
> As I said, I am not partitioning anything.
>
>> My point is that the lock held by the trigger will be significantly less
>> in
>> duration than all those COUNT(*) queries.
>>
>
> As I have said again the lock that the trigger will hold will live for the
> duration of the holding transaction which can be quite large (think long
> running transactions).
>
>> Post the SQL you used for SQL Server 2000 and I'll take a look and code
>> it
>> to demonstrate what i'm talking about - current version is SQL Server
>> 2005
>> by the way, its got the snapshot isolation and read committed without
>> writers blocking readers now.
>>
>
> I only have access to an MSDE version of SQL server 200 so that is what I
> ran my
> tests against. I used SquirrelSQL and the freeTDS jdbc driver, opened two
> connections, and disabled autocommit.
>
> Then I did the insert, select in the two windows.
>
> It is good that SQL Server 2005 has this feature now.
>
>
> Lefty Kreouzis
>
Received on Sun Apr 02 2006 - 13:15:52 CEST

Original text of this message