Re: counting rows

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@xxxxxxxxxxxxxxxxxxx> wrote in message
news:slrne2vbpq.rpd.lefty@xxxxxxxxxxxxxxxxxxxxxx
On 2006-04-02, Tony Rogerson <tonyrogerson@xxxxxxxxxxxxxxxx> 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



.

Relevant Pages

  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Authentication options with SS2005
    ... This trigger prevents from 'AuditLogin' login having more that one ... If there are more than one connections, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Trigger Deadlock
    ... RAISERROR 20000 'trg_myTable_UPDATE Update Trigger Failed. ... COMMIT TRANSACTION ... As for why you are getting deadlocks, ... SQL Server error log. ...
    (comp.databases.ms-sqlserver)
  • Re: An error occurred while enlisting in a distributed transaction
    ... rollback any changes, ... So far it works fine running against a local SQL Server, ... connections, enabled network, turned off my firewall etc. ... An error occurred while enlisting in a distributed transaction. ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Triggers from one MSDE to another using DTC
    ... In order to do a Distributed Transaction you must SET XACT_ABORT ... Wayne Snyder, MCDBA, SQL Server MVP ... > OK fine, as the error says, use XACT_ABORT ON around the trigger INSERT ...
    (microsoft.public.sqlserver.server)