0

I have a table that is highly hit on my database.

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 currently
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 partitioning all these indexes according to the imageSize?

On what factors would it depend mainly (in this case)?

Most queries use the ImageSize as one of the parameter, 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.

  • 1
    Partitioning won't improve the performance, but it can make your queries slower, because at least as far as I know, it can reduce parallelism. To know what to do, you should include the most used queries / the queries you have problems with. – James Z Jul 19 '16 at 11:56
  • 2
    Partitioning isn't for performance. littlekendra.com/2016/05/03/… – Erik Darling Jul 19 '16 at 12:02
  • 1
    Partitioning is mostly a manageability feature. If my calculations are correct, the table is only about 1GB if all columns are filled completely so it will be memory-resident if used frequently. Add queries to your question if performance is the concern. Memory-optimized features may be a better approach depending on the queries. – Dan Guzman Jul 19 '16 at 12:03

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.