A better way to get table statistics

Question

I'm developing a SQL Server 2012 Express and developer edition (with latest Service Pack) solution.

In my database I have a table CODES with codes. This table has a FLAG column indicating that a code has been printed, read or dropped. Codes are grouped by another column, LEVEL. CODES table has CODE and LEVEL as primary key.

I'm going to update table CODES very quickly, and if I do SELECT COUNT(code) FROM CODES WHERE FLAG=1 to get all codes read, sometime, I block that table, and when we have many many rows, SELECT COUNT CPU goes to 100%.

So, I have another table, STATISTICS to store how many codes has been printed, read or dropped. When I update a row in CODES table, I add 1 to STATISTICS table. I have tried this two ways:

With an UPDATE statement after updating CODES table.

declare @printed bigint;
set @printed = (Select CODES_PRINTED from STADISTICS where LEVEL = @level)

if (@printed is null)
begin
    insert dbo.STADISTICS(LEVEL, CODES_PRINTED) values (@level, 1)
end
else
begin
    update dbo.STADISTICS set CODES_PRINTED = (@printed + 1) where LEVEL = @level;
end

With a TRIGGER in CODES table.

ALTER trigger [dbo].[UpdateCodesStatistics] on [dbo].[CODES]
after update
as
    SET NOCOUNT ON;

    if UPDATE(FLAG)
    BEGIN
        declare @flag as tinyint;
        declare @level as tinyint;

        set @flag = (SELECT FLAG FROM inserted);
        set @level = (SELECT LEVEL FROM inserted);

        -- If we have printed a new code
        if (@flag = 1)
        begin
            declare @printed bigint;
            set @printed = (Select CODES_PRINTED from STADISTICS where LEVEL = @level)

            if (@printed is null)
            begin
                insert dbo.STADISTICS(LEVEL, CODES_PRINTED) values (@level, 1)
            end
            else
            begin
                update dbo.STADISTICS set CODES_PRINTED = (@printed + 1) where LEVEL = @level;
            end
        end
    END

But in both cases I lost data. After running my program I check CODES table and STATISTICS table and statistics data doesn't match: I have less printed codes and read codes in STATISTICS than in CODES table.

This is STATISTICS table that I'm using now:

CREATE TABLE [dbo].[BATCH_STATISTICS](
    [CODE_LEVEL] [tinyint] NOT NULL,
    [CODES_REQUESTED] [bigint] NOT NULL CONSTRAINT [DF_BATCH_STATISTICS_CODES_REQUESTED]  DEFAULT ((0)),
    [CODES_PRINTED] [bigint] NOT NULL CONSTRAINT [DF_BATCH_STATISTICS_CODES_PRINTED]  DEFAULT ((0)),
    [CODES_READ] [bigint] NOT NULL CONSTRAINT [DF_BATCH_STATISTICS_CODES_READ]  DEFAULT ((0)),
    [CODES_DROPPED] [bigint] NOT NULL CONSTRAINT [DF_BATCH_STATISTICS_CODES_DROPPED]  DEFAULT ((0)),
    [CODES_NOREAD] [bigint] NOT NULL CONSTRAINT [DF_BATCH_STATISTICS_CODES_NOREAD]  DEFAULT ((0)),
 CONSTRAINT [PK_BATCH_STATISTICS] PRIMARY KEY CLUSTERED 
(
    [CODE_LEVEL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

By the way, I'm updating and inserting very quickly (more than 1200 rows in a minute).

Any idea what's happening or how can I do it better?

score 0 · Answer 1 · answered Nov 25 '14 at 07:48

0

inserted and deleted can contain multiple (or no) rows. So idioms like set @flag = (SELECT FLAG FROM inserted) are fundamentally broken. From your description, it sounds like an indexed view could work for you instead, something like this:

CREATE VIEW dbo.Statistics
WITH SCHEMABINDING
AS
    SELECT LEVEL, COUNT_BIG(*) as CODES_PRINTED
    FROM dbo.Codes
    WHERE Flag = 1
    GROUP BY LEVEL

and:

CREATE UNIQUE CLUSTERED INDEX IX_Statistics ON dbo.Statistics (LEVEL)

And now SQL Server will (behind the scenes) maintain this data automatically and you don't have to write any triggers (or explicitly maintain a separate table)

answered Nov 25 '14 at 07:48

Damien_The_Unbeliever

234,701
27
340
448

Thanks for your answer. If I want statistics for codes read and dropped, do I need two more indexed views? – VansFannel Nov 25 '14 at 08:02
It depends - I have no idea what your data looks like - you're tossing around phrases like `read`, `dropped` and `printed` but all I've got is your existing code which seems to work in terms of columns called `Flag` and `Level`. It may be possible for all of the stats to exist in a single indexed view, or you may need multiple views. Like I say, I don't know your data model so can't provide better advice at this time. – Damien_The_Unbeliever Nov 25 '14 at 08:04
I think indexed views can affect write performance. In addition, they also have the potential to cause other issues. For example, if one or more of the base tables is subject to frequent updates, then, depending on the aggregations we perform in the indexed view, it is possible that we will increase lock contention on the view's index. So, this is not a better solution. – VansFannel Nov 25 '14 at 08:08
@VansFannel - it's almost always a better solution than trying to manually write triggers and implementing a separate table that is *emulating* an indexed view, which is what your current solution looks like. What makes you think that the triggers you write will have *less* performance effects than the code built into SQL Server for indexed view maintenance? – Damien_The_Unbeliever Nov 25 '14 at 08:09
I have provided all data you need to know. Depending on flag value, the code has been printed, read or dropped. It's easy to understand that I have provided printed code for brevity. – VansFannel Nov 25 '14 at 08:13
@VansFannel - no, it may be easy for *you* to know because you're currently working with this data. I have *no* idea how to determine that a particular row has been `read` or `dropped`. *maybe* a flag value of `0` means dropped. *maybe* a flag value of 22 means read. That information is *not* in your question and all I can vaguely surmise at the moment is that a flag value of 1, apparently, means `printed`. I also have *no* clue where `Level` factors into this, beside the fact that there are apparently multiple levels and these need to be reported separately - but just for print or for others? – Damien_The_Unbeliever Nov 25 '14 at 08:17
I have updated my question with more details. There will be multiple levels, and codes on those levels. – VansFannel Nov 25 '14 at 08:24

A better way to get table statistics

1 Answers1