2

I have a table that is used heavily. This table has 370,370 records.

When I run the following select:

select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages] 
group by [ImageSize] 
order by [ImageSize] 

I get this result:

enter image description here

Basically there are only 5 different imageSize objects in that table.

This is the current table definition:

IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL 
DROP TABLE [dbo].[ProductImages] 
GO

CREATE TABLE [dbo].[ProductImages] ( 
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]  
PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc, 
                          [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc, 
                          [SortOrder] asc, [ImageFormat] asc, 
                          [ImageSize] asc))

GO

And here are the definitions of the current inexes

  • 1 clustered index
  • 4 non clustered indexes
  • 1 filtered index
  • and the primary key non clustered defined above

    CREATE CLUSTERED INDEX 
    [CIX_ProductImages__ProductImageID] 
               ON [dbo].[ProductImages] ([ProductImageID] asc)
    
    CREATE NONCLUSTERED INDEX
    [IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL] 
               ON [dbo].[ProductImages] 
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
        [ImageSize] asc, [ImageFormat] asc, 
        [BodenImageURL] asc)
               INCLUDE ([BodenDomainURL])
    
    CREATE NONCLUSTERED INDEX
    [IX_ProductImages_ImageFormat_ImageSize_INCL] 
        ON [dbo].[ProductImages] 
        ([ImageFormat] asc, [ImageSize] asc)
               INCLUDE ([SeasonItemID], [Language],
         [Tier1], [Tier2], [BodenDomainURL], 
        [BodenImageURL])
    
    
    CREATE NONCLUSTERED INDEX 
    [IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL] 
        ON [dbo].[ProductImages] 
        ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc, 
        [ImageFormat] asc)
               INCLUDE ([ProductImageID], [BodenImageURL])
    

This is a filtered index:

    CREATE NONCLUSTERED INDEX 
    [IDX_ProdImages_GetProductListingPageDenormalisedData] 
        ON [dbo].[ProductImages] 
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
        [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
               INCLUDE ([BodenImageURL])
               WHERE ([ImageWidth]=(195) 
        AND [ImageHeight]=(252) 
        AND [ImageFormat]='ProductMain')

Can I expect significative gains by changing the 4 non clustered above indexes to 20 FILTERED INDEXES according to the imageSize?

Most queries use the ImageSize as one of the parameters. It is even in the clustered index.

I could modify the indexes and table structure.

This table is mostly used for reading. There is only 1 write a day, and that would not normally affect many records.

  • 3
    You say that "most queries use the ImageSize as one of the parameter", yet there is no index with ImageSize as the first column. Why? – ypercubeᵀᴹ Jul 20 '16 at 12:13
  • 1
    There's only 300k records in your table, so reading it should be fast, if your indexes are even somewhat correct. They look a little strange, but like I said yesterday, to really understand your problem, you should include the queries you're having trouble with and/or the most common ones. – James Z Jul 20 '16 at 12:25
  • 1
    I wouldn't add a filtered index on a column with that evenly distributed values. As @ypercubeᵀᴹ indicates, you will probably get better results by changing your indexes to better suit your queries. – Jonathan Fite Jul 20 '16 at 12:46
  • 1
    Keep in mind there's a number of issues with filtered indexes, the optimizer will not always use them even if you met the conditions. @JamesZ also points out that your table is relatively small. If your image data is too large, consider splitting the image metadata into a separate table from the image data, and connecting the tables by foreign keys. – Arthur D Jul 20 '16 at 13:02
  • 2
    @ArthurD What I can figure out, this is just the metadata with the URLs, no actual images there. The urls are probably wasting space being nvarchars, but otherwise it should be really small table. – James Z Jul 20 '16 at 13:09

Your Answer

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

Browse other questions tagged or ask your own question.