Query Performance when Joining table from separate database on same server

Question

I am trying to make a massive query (Probably a bad idea, but I don't know another way to do this) that pulls data from two separate databases that are on the same server. Here is what I have

    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

It currently workings but when I added in the following lines to obtain the QtyOnHand field from the InventoryDB, the query went from completing instantly to taking 20-30+ seconds to run.

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

Since the query runs fine without these lines I figured it must be something with these lines. If I had to guess, it would be the Join InventoryDB.dbo.ITEM on ITEM.Itemcode = PartDetail.Item_ID+' '+PartDetail.Color+PartDetail.Attachment due to having to concatenate those three fields together and then search for matching value, but I'm not sure of anyway around that.

One thing, when you use function on column or concatenate you can be sure that Optimizer will skip indexes if exists any and do table scan. — Lukasz Szozda
– Lukasz Szozda, Commented Sep 2, 2015 at 14:26
I imagine that join is taking away any index possibilities. Check the execution plan with and without it. — TTeeple
– TTeeple, Commented Sep 2, 2015 at 14:27
Why no InventoryDB.dbo.ITEM.QtyOnHand in the GROUP BY clause? — jarlh
– jarlh, Commented Sep 2, 2015 at 14:27
@Lad2025 What do you mean by that? I don't think I'm following you. — Xestius
– Xestius, Commented Sep 2, 2015 at 14:57
Basically learn about indexes, index_scan, index_seek and execution plan for starting optimizing queries. — Lukasz Szozda
– Lukasz Szozda, Commented Sep 2, 2015 at 14:58

Gordon Linoff · Accepted Answer · 2015-09-02 14:28:57Z

1

You are probably correct that the string concatenation is slowing things down.

One method is to create a computed column and build an index on it:

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

create index idx_PartDetail_ItemCode on PartDetail(ItemCode);

The ItemCode column will then be materialized for the table, along with an index. This should greatly speed the processing.

answered Sep 2, 2015 at 14:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Xestius Over a year ago

Would this be creating a permanent new column on my table? Also, what is the create index doing (sorry if this should be obvious, I'm just kind of diving in headfirst) to the column that wasn't there before?

Xestius Over a year ago

Well indexing just that column didn't help, but indexing the entire database helped tremendously. Thank you for setting me in the right direction.

Gordon Linoff Over a year ago

@Xestius . . . A computed column is calculated when you access the column, in general. In this case, though, the column is materialized so the value is stored in the index.

Collectives™ on Stack Overflow

Query Performance when Joining table from separate database on same server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related