Query Optimization on Sql Server 2005

Question

we have two related tables (inventory and inventory) on Sql Server 2005. the first table is a script.

CREATE TABLE [dbo].[inventory](
    [code] [varchar](35) NOT NULL,
    [salePrice1] [decimal](22, 2) NULL,
 CONSTRAINT [PK_inventory] PRIMARY KEY CLUSTERED 
(
    [code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and the second is <

CREATE TABLE [dbo].[inventoryLocalization](
    [code] [varchar](35) NOT NULL,
    [language] [varchar](2) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [description] [nvarchar](max) NULL,
 CONSTRAINT [PK_inventoryLocalization] PRIMARY KEY CLUSTERED 
(
    [code] ASC,
    [language] ASC,
    [name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And this query depends on these two:

select  i.[code],
        iL.[language], 
        case  
        when iL.[name] is null then (select iL2.name  from dbo.inventoryLocalization iL2 where iL2.[language] = 'de' and iL2.code = i.[code])
        else iL.[name]
        end as [name]
from dbo.inventory i
left join dbo.inventoryLocalization iL on i.code = iL.code and iL.[language] =  'en';

as you assume, we select all entries with [language] = 'en', but if there is no entry for "en", the request accepts "de" (by default). But this request takes much longer (approximately 13 seconds).

Do you think that any other elegant methods for the same result will reduce the time spent on this?

Thanks in advance for any help you can provide.

Answer 1

, , , case null .

, , 'de', , .

select  i.[code],
        iL.[language], 
        case  
        when iL.[name] is null then iL2.name
        else iL.[name]
        end as [name]
from dbo.inventory i
left join dbo.inventoryLocalization iL on i.code = iL.code and iL.[language] =  'en'
left join dbo.inventoryLocalization iL2 on i.code = iL2.[code] and iL2.[language] = 'de'

Answer 2

select i.code
      ,coalesce(en.language, de.language) as language
      ,coalesce(en.name, de.name) as name
  from inventory i
  left join inventoryLocalization en on(i.code = en.code and en.language = 'en')
  left join inventoryLocalization de on(i.code = de.code and de.language = 'de');

Answer 3

Try the following:

select  i.[code],
        iL.[language], 
        coalesce(il.[name],il2.[name],'Unknown') as [name]
from dbo.inventory i
left join dbo.inventoryLocalization iL on i.code = iL.code and iL.[language] =  'en';
left join  dbo.inventoryLocalization iL2 on i.code=il2.code and il2.[language] = 'de'