0

I have two SQL Server 2014 tables one has "en-GB" cultured names and descriptions, and a second has translations for other cultures.

The "en-GB" table...

CREATE TABLE [dbo].[HomePages] 
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePage] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The translation table:

CREATE TABLE [dbo].[HomePageTranslations] 
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

There will always be values in en-GB table, but may or may not be equivalent values in the translation table. The query I am using should return the translated values if they exist or fall back on the en-GB values if they don't. The query is in the stored procedure here:

CREATE PROCEDURE [dbo].[HomePage_GetForKeyAndCulture]
(
    @Key NVARCHAR(25),
    @CultureName NVARCHAR(5)
)
AS
BEGIN
    SELECT      [base].[Id],
                [base].[Key],
                COALESCE(translation.[Name], base.[Name]) [Name],
                COALESCE(translation.[Description], base.[Description]) [Description],
                [base].[Active]

    FROM        [dbo].[HomePages]               [base]
    LEFT JOIN   [dbo].[HomePageTranslations]    [translation]
            ON  [translation].[HomePageId]  =   [base].[Id]
            AND [translation].[CultureName] =   @CultureName

    WHERE       [Key]                       =   @Key;
END

As you can see I am using a LEFT JOIN and the COALESCE function to achieve this. I know LEFT JOINS are not overly performant, so is there a more performant way to achieve this?

EDIT: Following advise tables have now had indexes added and look like...

The HOMEPAGE table...

CREATE TABLE [dbo].[HomePages] 
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePages] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX.HomePages_Key]
    ON [dbo].[HomePages]([Key] ASC);

And the TRANSLATION table...

CREATE TABLE [dbo].[HomePageTranslations] 
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_HomePageTranslations_HomePageIdCultureName]
    ON [dbo].[HomePageTranslations]([HomePageId] ASC, [CultureName] ASC);
  • Are you experiencing an issue or performance problem? Is this slow? Where are you looking for a performance increase (runtime, reads, etc.)? – dfundako Jun 16 '16 at 16:07
  • I'm not at the moment and wont with this pair of tables, but I am trying to work out a good solution as one of the other entities will have prospectively thousands of rows in the "en-gb" table and 10s of thousands of rows in the translation tables... EDIT: I don't want to choose a bad solution for similar queries as i want to keep them all similar so there is a consistent code approach. – Dib Jun 16 '16 at 16:10
  • Also there will be joins between the various entitys so one query may have four or five of these pairs with ever increasing record counts in the result set and table data as the joins go further into the detail. – Dib Jun 16 '16 at 16:13
  • 2
    Just add an index on HomePageTranslations.HomePageId and you will be fine. LEFT JOIN may be is slower than INNER , but with proper indexes in place you will see no difference . In general, "don't fix it till it's broken". There is no silver bullet that works efficiently for all cases. – a1ex07 Jun 16 '16 at 16:14
  • 1
    In that design you could have duplicate [HomePageId] [CultureName]. Maybe add a unique index/constraint on [CultureName], [HomePageId]. And it should be used by the join – paparazzo Jun 16 '16 at 18:37
1

no one has bothered with an answer

an index on [HomePages] [Key]

an index on [HomePageTranslations] [CultureName], [HomePageId]
and make it a unique constraint if appropriate

the query itself looks fine to me

you could probably just use a view and an index on [HomePageTranslations] [HomePageId] and have fine response
10s of thousands is not that much

  • I have applied the indexes suggested (and updated the original question with the new table creation scripts). I will investigate whether using a view gives me more benefit too. – Dib Jun 17 '16 at 7:56

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.