Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been using the Where Not Exists () method successfully to perform incremental loading of some very large QVDs for many years. It was relatively quick under Publisher 12.20 SR4:
This operation took less than 2 minutes under 12.20 SR4. The %Key field was constructed using the Hash128() function since the underlying database table has multiple primary key fields.
We upgraded to 12.40 SR1 yesterday, and this operation now takes 41 minutes!
This is seriously impacting the performance of my database loaders that load from very large tables.
Any ideas on how to mitigate or work around this?
It turns out that there was an easy fix. I was able to use this statement just before the Concatenate:
Unmap *
This restored the performance to what it had been in 12.20 SR4.
Note that this worked for me because the QVD being concatenated had already been mapped previously.
So I believe I have found the root cause. There is a behavioral change in script execution between 12.20 SR4 and 12.40 SR1 that results in a big performance hit whenever Map Using and Where Exists are used together.
I am performing a Map Using to map values in the load. I ran all four combinations and timed the Concatenate operation:
Note that the Map Using does not map the %Key field in my case.
Now to figure out how to work around it...
It turns out that there was an easy fix. I was able to use this statement just before the Concatenate:
Unmap *
This restored the performance to what it had been in 12.20 SR4.
Note that this worked for me because the QVD being concatenated had already been mapped previously.