1

I found a table that looks like this:

 CREATE TABLE [dbo].Table1 (  
  id INT primary key IDENTITY (1, 1),  
  [idUser] INT  NOT NULL ,  
  [Amount] INT NOT NULL ,  
  [Attempts] INT NOT NULL ,  
  [date] [datetime] NOT NULL ,  
  [SUM_Amount] INT NOT NULL   
 ) ON [PRIMARY]  

This table is created and populated with aggregated data for a particular period by a job.

Particularities:

  • This table will hold up to a million rows
  • idUser is unique
  • sum_Amount is running total of previous rows of amount.

This table will last as is, no update or delete or insert operation. Just this type of queries:

 select top (@n) * from table1
 order by [SUM_Amount] desc, [Attempts] desc

 select top (@n) * from table1
 where [SUM_Amount] >=@m order by [SUM_Amount] asc 

I think it will improve preformance to change to a clustered index like this:

 CREATE TABLE [dbo].Table2 (  
  id INT IDENTITY (1, 1),  
  [idUser] INT  NOT NULL ,  
  [Amount] INT NOT NULL ,  
  [Attempts] INT NOT NULL ,  
  [date] [datetime] NOT NULL ,  
  [SUM_Amount] INT NOT NULL  

 CONSTRAINT [PK_Nueva]
   PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)   

 ) ON [PRIMARY]  

I read that using a no unique clustered index will add a 4 bytes hidden column (http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx), so I decide to add Identity to cluster index (not sure if it is the right approach)

I want to ask (at the risk of sound ridiculous, but need to be sure):

  • How could it be improved?
  • Will I have an impact on disk size?
  • Should I rebuild the index once all data have been inserted?

EDIT:

About id, I think is there just as a bad habit. I'll kept it, not sure how previous job calculate running total (I've no access to it)

There are a lot of tables like this, like hundreds for each day(don't ask me why). That is why DBA team ask me to not create a new index because of size issues. That is why I thinking of rearrange table structure via clustered index. Also changing data types which exceeds normal ranges.

4

Yes, placing a clustered index like you suggest will give you excellent query performance for those two particular types of queries, but it may spell disaster for most other SELECT queries on that table. I would offer an alternative solution:

I would suggest adding a basic clustered index on a single, unique column like idUser:

ALTER TABLE dbo.Table1 ADD
    PRIMARY KEY CLUSTERED (idUser)
        WITH (FILLFACTOR=100);

... and then building a separate, non-clustered indexe specifically for your queries:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    WITH (FILLFACTOR=100);

The clustered index (the primary key) will not add any space to your table - it's just another way of organizing the storage by presorting the rows in a binary tree, which will make access to the table a lot quicker.

The non-clustered index will take up some space, but it's comparably small: Both SUM_Amount and Attempts are integer values, so they're 4 bytes large each, meaning that your non-clustered index should end up at roughly 12 MB with a million rows (including the "clustering key", which is also an integer).

Your two example queries will use the non-clustered index to find the largest/smallest values, and this will generate a Key lookup operator in the execution plan, but because you're using TOP (@n), I recon you won't even notice the performance cost of that. If you absolutely need even better performance and want to eliminate the Key lookup, you could INCLUDE () all the relevant columns in the non-clustered index to form a so-called covering index, effectively making the index a sorted copy of the original table:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    INCLUDE (id, idUser, Amount, date)
    WITH (FILLFACTOR=100);

There is no need to rebuild an index if the data hasn't changed, particularly if the entire table is populated in a single batch. If the table has been populated over time, however, you may see some fragmentation.

4

A million rows and a narrow table is not really a big table. I would expect most queries to return sub-second, unless you are pulling back the whole table to client, in which case rendering the rows in the SSMS grid is really taking the time.

Having said that, this might be a good use for indexed views ( and there aren't many! ). I don't tend to use them a lot as they can destroy INSERT / UPDATE / DELETE performance on the underlying table. However as your table is static and you have custom reporting you requirements, you can create multiple non-clustered indexes on the indexed view to service them. They also can help with aggregates. Here's my simple rig with 1 million rows which shows how indexed views with supporting indexes might work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
IF OBJECT_ID('dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1 (
    id                  NUMERIC PRIMARY KEY IDENTITY (1, 1),  
    [idUser]            INT  NOT NULL,  
    [Amount]            INT NOT NULL,  
    [Attempts]          INT NOT NULL,  
    [date]              DATETIME NOT NULL,  
    [SUM_Amount]        INT NOT NULL
) ON [PRIMARY]
GO


-- Add one million rows
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.Table1 ( idUser, Amount, Attempts, date, SUM_Amount )
SELECT rn % 33 AS idUser, rn % 44 AS Amount, rn % 7 AS Attempts, DATEADD( day, rn % 300, '1 Jan 2014' ) AS [date], rn % 77 AS SUM_Amount
FROM cte
GO

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Original Queries START
------------------------------------------------------------------------------------------------

-- Original queries
DECLARE @n INT = 10, @m INT = 33

SELECT TOP (@N) * 
FROM dbo.table1
ORDER BY [SUM_Amount] DESC, [Attempts] DESC

SELECT TOP (@n) * 
FROM dbo.table1
WHERE [SUM_Amount] >= @m 
ORDER BY [SUM_Amount] ASC
GO

-- Original Queries END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Indexed Views START
-- with supporting non-clustered indexes
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
GO
CREATE VIEW dbo.vw_table1_reports
WITH SCHEMABINDING
AS
SELECT id, idUser, Amount, Attempts, [date], SUM_Amount
FROM dbo.table1
GO

CREATE UNIQUE CLUSTERED INDEX cdx_vw_table1_reports ON dbo.vw_table1_reports ( id )
GO

CREATE INDEX idx_vw_table1_reports1 ON dbo.vw_table1_reports ( [SUM_Amount] DESC, [Attempts] DESC )
INCLUDE ( idUser, Amount, [date] )
GO

CREATE INDEX idx_vw_table1_report2 ON dbo.vw_table1_reports ( [SUM_Amount] ASC )
INCLUDE ( idUser, Amount, Attempts, [date] )
GO

-- Indexed Views END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Revised Indexes START
------------------------------------------------------------------------------------------------

DECLARE @n INT = 10, @m INT = 33

-- Revised query 1
SELECT TOP (@n) * 
FROM dbo.vw_table1_reports
ORDER BY [SUM_Amount] DESC, [Attempts] DESC

-- Revised query 2
SELECT TOP (@n) * 
FROM dbo.vw_table1_reports
WHERE [SUM_Amount] >= @m 
ORDER BY [SUM_Amount] ASC
GO

-- Revised Indexes END
------------------------------------------------------------------------------------------------

Any my results:

enter image description here

Note the original queries are executing in less than a second, but the revised ones are 'quicker'.

Drop and recreate these indexes if you have to reload the table.

In summary, if your queries are returning sub-second, you don't need to worry too much about indexing. If not, consider indexed views, considering the constraints around their use and possible overheads.

  • Why indexed views as opposed to just indexes @wBob? I suppose I was thinking they give you the opportunity to roll in more complex criteria then we see here for really custom reporting, aggregations etc, but yeah, ordinary indexing would be simpler if requirements allow. OK thanks Bob! – wBob Nov 14 '14 at 15:16
  • Thanks @wBob. This static tables actually acts as indexed views of original table. – Horaciux Nov 14 '14 at 15:22
  • So you could use the same indexes I've applied against the indexed views to your original table to get similar results. – wBob Nov 14 '14 at 15:28
0
  1. Why you are creating the column name "id" (if there is no reason for it, I suggest to delete it ).
  2. For the Clustered Index issue, I think that his KEY should be only, "[SUM_Amount]". the reason is that your queries are going to do "INDEX SCAN". and for this type of operation CLUSTERED index give us the best performance (Clustered index pages are sorted in additional to the data).
  • 1
    There is also a problem with the datatype on your id column. Specifying NUMERIC without precision or scale defaults to NUMERIC(18,0) and takes 9 bytes to store. This can therefore store numbers up to 999,999,999,999,999,999 aka 999 quadrillion or 999 million billion (ok I had to Wolfram Alpha that : ). As you already know your table will only ever store ~1 million rows, consider NUMERIC(6,0) at only 5 bytes or INT at 4 bytes would actually be much more appropriate here. Spend some time studying your datatypes here. – wBob Nov 14 '14 at 14:26
  • @wBob just bad editing. They use to be numeric(18,0) I didn't hesitate to turn to int. Thanks for noticing. – Horaciux Nov 14 '14 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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