Function, View or adhoc SQL

PaulMolloy · #1 (**permalink**) 06-17-10, 05:06

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

pootle flump · #2 (**permalink**) 06-17-10, 05:38

I would maintain your versioning in another table. Minimises key length, simplifies your SQL, reduces page splits etc.

pootle flump · #3 (**permalink**) 06-17-10, 05:41

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?

PaulMolloy · #4 (**permalink**) 06-17-10, 05:50

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.

PaulMolloy · #5 (**permalink**) 06-17-10, 05:54

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.

pootle flump · #6 (**permalink**) 06-17-10, 05:57

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

pootle flump · #7 (**permalink**) 06-17-10, 05:58

Inline table functions are perfectly efficient. Don't put code that relies on data structures in your front end.

pootle flump · #8 (**permalink**) 06-17-10, 06:02

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

pootle flump · #9 (**permalink**) 06-17-10, 08:47

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

Internet Services

Web Development

Digital Marketing

Consumer Tech