So I'm looking for ways to optimize the following load statement, and everything I have seen is that you can only use WHERE statements if it is WHERE EXISTS. Currently we only want to load the past 24 months of data, so we have created a cutoff variable, which is then referenced in our where clause on the load statement. The QVD that we are working with is 26 gigs and currently to run the current load statement takes 1.5 hrs. I'm looking for ways to speed this up, but haven't been able to wrap my brain around how I would change the date from WHERE < value to WHERE exists. Here is the load statement and just looking to see if anyone had any suggestions.
Thanks
Jeremy Kofoot
Let vToday = today();
Let cutoff = vToday - 729;
EBILL:
LOAD [Tracking Number EBill],
TNumYear as TrackNumKey,
[Charge Description Code txt],
[Account Number],
[Invoice Date],
[Invoice Number],
[Invoice Amount],
[Transaction Date],
[Package Quantity],
[Package Reference Number 2],
[Package Reference Number 4],
[Entered Weight],
[Billed Weight],
[Package Dimensions],
Zone,
[Charge Category Code],
[Charge Category Detail Code],
[Charge Source],
[Type Code 1],
[Type Detail Code 1],
[Charge Classification Code],
[Charge Description Code],
[Charge Description],
[Net Amount],
[Sender Name],
[Sender Company Name],
[Sender Address Line 1],
[Sender Address Line 2],
[Sender City],
[Sender State],
[Sender Postal],
[Sender Country],
[Receiver Name],
[Receiver Company Name],
[Receiver Address Line 1],
[Receiver Address Line 2],
[Receiver City],
[Receiver State],
[Receiver Postal],
[Receiver Country],
[Third Party Name],
[Third Party Company Name],
[Third Party Address Line 1],
[Third Party Address Line 2],
[Third Party City],
[Third Party State],
[Third Party Postal],
[Third Party Country],
[Miscellaneous Line 1],
[Miscellaneous Line 2],
[UPS Shipment Quantity],
[UPS Accessorial Charges Billed],
[UPS Freight Cost Billed],
[UPS Fuel Surcharge Billed]
FROM \\...\shipping.qvd (qvd)
WHERE [Transaction Date] > $(cutoff);
