Sql Server 2005上的查询优化

we have two related tables(inventory and inventoryLocalization) on Sql Server 2005. the first table's script.

我们在Sql Server 2005上有两个相关的表(库存和库存定位)。第一个表的脚本。

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 we have a query depends on these two, as follows:

我们有一个查询取决于这两个，如下所示:

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 guess, we select all records with [language] = 'en' but if there is no record for 'en', query takes 'de'( as default). But that query takes much longer (approximately 13 seconds).

正如您所猜测的，我们使用[language] = 'en'选择所有记录，但是如果没有'en'的记录，查询将使用'de'(作为默认)。但是这个查询需要更长的时间(大约13秒)。

Do you think any other elegant methods for the same result to reduce the amount of time it takes ?

你认为有什么其他优雅的方法可以达到同样的效果来减少花费的时间吗?

Thanks in advance for any assistance you can provide.

谢谢您的帮助。

3 个解决方案

#1

Probably slow because it has to do a query on demand when the case statement evaluates a null and runs the sub-query.

可能会很慢，因为当case语句计算null并运行子查询时，它必须对需求进行查询。

Maybe this will be faster, always do the join to 'de' rather than it doing a query each time a null is found.

也许这将会更快，始终执行'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'

#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');

#3

Try this:

试试这个:

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'

#1