Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Function, View or adhoc SQL

    I've got a table which can potentially contain several versions of each record in different languages. I need to be able to retrieve the chosen record in the preferred language if it exists falling back on the English record if it's not there.
    e.g.
    Code:
    Id  | LanguageCode | Value
    1   | ENG          | Hello
    1   | FRA          | Bonjour
    2   | ENG          | Thanks
    2   | DEU          | Danke
    If I want the table in french I should get;
    Code:
    Id  | LanguageCode | Value
    1   | FRA          | Bonjour
    2   | ENG          | Thanks
    I'm after opinions on which is the best/fastest approach to get the data.
    The platform is SQL Server 2008 accessed from .NET, I expect up to about 50k records in the table when populated.

    I've written a table value function like this
    Code:
    CREATE FUNCTION [dbo].[MyLocalisedTable](@LanguageCode varchar(3))
    RETURNS TABLE 
    AS
    RETURN 
    (
    SELECT Id, LanguageCode, Value
    FROM (SELECT  Id, LanguageCode, Value, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY case when LanguageCode=@LanguageCode THEN 0 ELSE 1 END, case when LanguageCode='ENG' THEN 0 ELSE 1 END) as rowno FROM MyTable) as MyTable 
    WHERE rowno=1
    )
    Which I can use very simply as
    Code:
    SELECT * FROM dbo.MyLocalisedTable('FRA') WHERE Id=1
    Now that seems to work but I dont have enough experience in high-traffic systems to know if it's efficient enough. There's also a complicating factor that I want to start including record versioning by dates to maintain an archive, so the table could wind up with a couple of date fields as well.

    Code:
    Id  | LanguageCode | FromDate   | ToDate     | Value
    1   | ENG          | 01/01/2010 | 01/04/2010 | Hello
    1   | ENG          | 01/04/2010 | NULL       | Hi
    1   | FRA          | 01/01/2010 | NULL       | Bonjour
    2   | ENG          | 01/01/2010 | 01/04/2010 | Thank you
    2   | ENG          | 01/04/2010 | NULL       | Thanks
    2   | DEU          | 01/01/2010 | NULL       | Danke
    In a situation like that what would be the best primary key and index choices? I'm assuming the first 4 fields as a composite pk, but I'm not sure if I should be adding another unique id field instead.

    Any hints would be much appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would maintain your versioning in another table. Minimises key length, simplifies your SQL, reduces page splits etc.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looking at your function, you either want the passed language code or English, nothing else, so I would put in a WHERE clause.
    Also, don't you want to filter this by the ID too?

  4. #4
    Join Date
    Jun 2010
    Posts
    9
    So you think something like this would be quicker?

    Code:
    SELECT Id, LanguageCode, Value FROM MyTable 
    WHERE Id=1 
    AND (LanguageCode='FRA' 
    OR (LanguageCode='ENG' AND NOT EXISTS (SELECT Id FROM MyTable WHERE Id=1 AND LanguageCode='FRA'))
    )
    Update speed isnt too important, I need to squeeze whatever I can from retrieval speed.

  5. #5
    Join Date
    Jun 2010
    Posts
    9
    I've got two concerns, 1. the best SQL for selecting the data and 2. whether a function in the SQL would add a performance cost overhead compared to writing out the SQL longhand in the .NET code.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually this is what I had in mind but the above might work too - you should compare the plans and IO.
    Code:
    CREATE FUNCTION [dbo].[MyLocalisedTable](@LanguageCode varchar(3), @id INT)
    RETURNS TABLE 
    AS
    RETURN 
        (
            SELECT  Id
                  , LanguageCode
                  , Value
            FROM 
                    (
                        SELECT  Id
                              , LanguageCode
                              , VALUE
                              , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY case when LanguageCode=@LanguageCode THEN 0 WHEN LanguageCode='ENG' THEN 1 END) AS rowno 
                        FROM    dbo.MyTable
                        WHERE   LanguageCode    IN(@LanguageCode, 'ENG')
                            AND id              = @id
                    ) AS MyTable 
            WHERE rowno=1
        )
    GO

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Inline table functions are perfectly efficient. Don't put code that relies on data structures in your front end.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, it could be simplified further:
    Code:
    CREATE FUNCTION [dbo].[MyLocalisedTable](@LanguageCode varchar(3), @id INT)
    RETURNS TABLE 
    AS
    RETURN 
        (
    
            SELECT  TOP 1
                    Id
                  , LanguageCode
                  , value
            FROM    dbo.MyTable
            WHERE   LanguageCode    IN(@LanguageCode, 'ENG')
                AND id              = @id
            ORDER BY
                    CASE WHEN LanguageCode=@LanguageCode THEN 937 ELSE 90075 END
        )
    GO

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this a rarely changing table then you can increase efficiency further.

    Add a persisted computed column:
    Code:
     is_not_english  AS CAST(CASE WHEN LanguageCode = 'ENG' THEN 1 ELSE 0 END AS BIT) PERSISTED
    Then add a covering index:
    Code:
    CREATE UNIQUE NONCLUSTERED INDEX ix_MyTable_id_LanguageCode_is_not_english_u_nc
    ON dbo.MyTable (id ASC, LanguageCode ASC, is_not_english ASC)
    INCLUDE (value)
    WITH    (
            FILLFACTOR = 100
            )
    Code:
    CREATE FUNCTION [dbo].[MyLocalisedTable](@LanguageCode varchar(3), @id INT)
    RETURNS TABLE 
    AS
    RETURN 
        (
    
            SELECT  TOP 1
                    value
            FROM    dbo.MyTable
            WHERE   LanguageCode    IN(@LanguageCode, 'ENG')
                AND id              = @id
            ORDER BY
                    is_not_english
        )
    GO
    Last edited by pootle flump; 06-17-10 at 10:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •