Re: counting rows

On 2006-04-02, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> wrote:
> [snip]
> 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.
>

No that isn't correct, the same 10MB are cached per user no matter how many times the user queries select count(*) sto it is 100MB not 1GByte.

> 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.
>

OK I did my test on Postgresql using jmeter on a remote server over Wifi 11 Mbit.

First the schema:

CREATE TABLE pictures (

    id serial NOT NULL,
    user_id integer NOT NULL,
    picture_name text
);
ALTER TABLE public.pictures OWNER TO test; CREATE TABLE users (

    user_id integer NOT NULL,
    name text NOT NULL,
    num_pictures integer
);
ALTER TABLE public.users OWNER TO test;
ALTER TABLE ONLY pictures

    ADD CONSTRAINT pictures_pkey PRIMARY KEY (id); ALTER TABLE ONLY users

    ADD CONSTRAINT users_name_key UNIQUE (name); ALTER TABLE ONLY users

    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); CREATE INDEX picture_user_idx ON pictures USING btree (user_id); ALTER TABLE ONLY pictures

    ADD CONSTRAINT pictures_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id);

we have 10 users (user_1 to user 10) and 1000 pictures per user.

Jmeter has 100 threads, repeated 10

what runs is the following:

insert into pictures(user_id, picture_name) values ( ${user_id}, 'JMeter ${user_id}'); select count(*) from pictures where user_id = ${user_id};

I got a throughput of 16029 transactions per second.

With 10 threads and 100 repeats I get 17192 transactions per second.

I will do the same on SQL Server & Oracle & report the results

Lefty Kreouzis Received on Sun Apr 02 2006 - 17:29:59 CEST