Where Not Match slowing load way down. Can it be faster?

[Anonymous]

I am loading a table from a qvd file and trying to use a where not match but it takes my load from 3 minutes to 2-1/2 hours. Is there anything else I can do or any way to speed that up?

LOAD 
  StoreDateKey, 
  [Original Count Date],
  [Inventory Row #],
  [Prd Number Tmp],
  [Count Date],
  [Store Number],
  [Prd Number],
  [Product Loaction],
  [Adjustment Ref],
  Vendor,
  [Open Order Units],
  [On Order Units],
  [Incoming ISST PO Units],
  [MTD Sales Units],
  [MTD Usage Received],
  MTD Usage Adjustments],
  [Date Last Received],
  [Year],
  [Period],
  [Changed by],
  [Date Changed],
  [Time Changed],
  [Created by Program],
  [Created by],
  [Creation Date],
  [Time Created]
FROM $(vQVDFolder)\$(vYear)_Inventory_Raw.qvd (qvd)
where not match(Vendor,'076','086');


,'086');

[santiago_respane]

Hi,

i would use EXISTS instead for this kind of things when we have a lot of data.

Something like this:

first load an inline with the values you want to include or exclude:

VendorsToExclude:

LOAD * INLINE [

VendorX

076

086

];

and then perform your load as follows:

LOAD  

  StoreDateKey,  

  [Original Count Date], 

  [Inventory Row #], 

  [Prd Number Tmp], 

  [Count Date], 

  [Store Number], 

  [Prd Number], 

  [Product Loaction], 

  [Adjustment Ref], 

  Vendor, 

  [Open Order Units], 

  [On Order Units], 

  [Incoming ISST PO Units], 

  [MTD Sales Units], 

  [MTD Usage Received], 

  MTD Usage Adjustments], 

  [Date Last Received], 

  [Year], 

  [Period], 

  [Changed by], 

  [Date Changed], 

  [Time Changed], 

  [Created by Program], 

  [Created by], 

  [Creation Date], 

  [Time Created] 

FROM $(vQVDFolder)\$(vYear)_Inventory_Raw.qvd (qvd) 

where NOT EXISTS(VendorX,Vendor);

Then drop your inline:

DROP TABLE VendorsToExclude;

Please let me know if this helps.

Kind regards,

[krishna_2644]

Hi Sally,

Try doing the optimised load first and then apply the where condition in the preceding load.

Somthing like in the attached qvw.

Hope that helps

Krishna

[maxgro]

Could you quickly load the matching Vendor before the load from the qvd?

If yes you can change the load from the qvd to an optimized load (where exists ...):

load

     ...

where exist(Vendor);