从同一服务器上的单独数据库连接表时的查询性能

Question

我正在尝试进行一个大规模的查询(可能不是一个好主意，但我不知道另一个方法)，它从位于同一服务器上的两个单独的数据库中提取数据。这是我有的东西

    Select Distinct PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment As ItemCode,
     concat(
      max(case when PartQuality.Quality_Seq = '1' Then Quality.DSC end),' ',
      max(Case When PartQuality.Quality_Seq = '2' then Quality.DSC End),' ',
      max(Case When PartQuality.Quality_Seq = '3' Then Quality.DSC End),' ',
      max(case when PartQuality.Quality_Seq = '4' Then Quality.DSC end),' ',
      max(Case When PartQuality.Quality_Seq = '5' then Quality.DSC End),' ',
      max(Case When PartQuality.Quality_Seq = '6' Then Quality.DSC End),' ',
      max(case when PartQuality.Quality_Seq = '7' Then Quality.DSC end),' ',
      max(Case When PartQuality.Quality_Seq = '8' then Quality.DSC End),' ',
      max(Case When PartQuality.Quality_Seq = '9' Then Quality.DSC End)) As ProdDesc,
      InventoryDB.dbo.ITEM.QtyOnHand
       From SelectDB.dbo.PartDetail
        Join InventoryDB.dbo.ITEM on ITEM.Itemcode = PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment
        Join SelectDB.dbo.PartQuality on PartQuality.Item_ID = PartDetail.Item_ID
        Join SelectDB.dbo.Quality on Quality.QualityCode = PartQuality.QualityCode
        Join SelectDB.dbo.Part on Part.Item_ID = PartDetails.Item_ID
        Join SelectDB.dbo.PartPrefix on PartPrefix.PrefixCode = Part.PrefixCode
        Join SelectDB.dbo.ProductPart on ProductPart.Item_ID = PartDetail.Item_ID
        Join SelectDB.dbo.Product on Product.Product_ID = ProductPart.Product_ID
        Join SelectDB.dbo.ProductMod on PrdouctMod.ProductMod_ID = Product.ProductMod_ID
        Join SelectDB.dbo.MakeModel on MakeModel.MakeModel_ID = Product.MakeModel_ID
        Join SelectDB.dbo.ProductStyle on ProductStyle.Style_ID = ProductStyle.Style_ID                        
        Join SelectDB.dbo.ProductMake on ProductMake.Make_ID = MakeModel.Make_ID
         Where Product.ModelYear = @YEAR
          And ProductMake.Name = @MAKE
          And MakeModel.Name = @MODEL
          And ProductStyle.DSC = @STYLE
          And ProductMod.DSC = @MOD
          And PartPrefix.DSC = @LOC
           Group By PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment, ITEM.QtyOnHand

它目前正常工作，但是当我添加以下行从QtyOnHand获取InventoryDB字段时，查询从立即完成到运行20-30多秒。

Select Distinct . . . . InventoryDB.dbo.ITEM.QtyOnHand
From . . . . 
Join InventoryDB.dbo.ITEM on ITEM.Itemcode = PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment
. . ..
Where . . . . 
Group By . . . . ITEM.QtyOnHand

由于查询在没有这些行的情况下运行得很好，所以我想这一定是与这些行有关的。如果我不得不猜测，这将是Join InventoryDB.dbo.ITEM on ITEM.Itemcode = PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment，因为必须将这三个字段连接在一起，然后搜索匹配值，但我不确定。

Answer 1

您可能是正确的，字符串连接正在放慢速度。

一种方法是创建计算列并在其上构建索引：

alter table PartDetail add ItemCode as (Item_ID + ' ' + Color + Attachment);

create index idx_PartDetail_ItemCode on PartDetail(ItemCode);

然后，ItemCode列将与索引一起在表中物化。这将大大加快处理速度。