Hi Adam, Thanks for the tips. I tried rewriting the query, but it has similar performance outcomes. SQL9 update ( select mfd_id, max1, HighestN (vari_20131218,vari_20140204,vari_20140815,vari_20141221,vari_20150106, vari_20151005,vari_20151208,vari_20151224,vari_20160109,vari_20160430, vari_20170228,vari_20170620,vari_20180420, 1) as val from d threads 8 batch 256 ) set max1 = val; This is the sql8 version that gets the outcome I was after. It was ultimately faster then using the function approach. But obviously requires additional writing time. Everything is a trade-off. It would probably be worth applying sloots approach below.... This is probably likely to the fastest approach in 9 (from my basic understanding). Although nothing beats the basic max(a,b,c,d,e,f,g) query in 8. SQL8 update d set max1 = max(vari_20131218, vari_20140204, vari_20140815, vari_20141221, vari_20150106, vari_20151005, vari_20151208, vari_20151224, vari_20160109, vari_20160430, vari_20170228, vari_20170620, vari_20180420); update d set max2 = iif(vari_20131218=max1,vari_20140204,vari_20131218); update d set max2 = iif(vari_20140204 < max1 and vari_20140204 > max2,vari_20140204,max2); update d set max2 = iif(vari_20140815 < max1 and vari_20140815 > max2,vari_20140815,max2); update d set max2 = iif(vari_20141221 < max1 and vari_20141221 > max2,vari_20141221,max2); update d set max2 = iif(vari_20150106 < max1 and vari_20150106 > max2,vari_20150106,max2); update d set max2 = iif(vari_20151005 < max1 and vari_20151005 > max2,vari_20151005,max2); update d set max2 = iif(vari_20151208 < max1 and vari_20151208 > max2,vari_20151208,max2); update d set max2 = iif(vari_20151224 < max1 and vari_20151224 > max2,vari_20151224,max2); update d set max2 = iif(vari_20160109 < max1 and vari_20160109 > max2,vari_20160109,max2); update d set max2 = iif(vari_20160430 < max1 and vari_20160430 > max2,vari_20160430,max2); update d set max2 = iif(vari_20170228 < max1 and vari_20170228 > max2,vari_20170228,max2); update d set max2 = iif(vari_20170620 < max1 and vari_20170620 > max2,vari_20170620,max2); update d set max2 = iif(vari_20180420 < max1 and vari_20180420 > max2,vari_20180420,max2); update d set max3 = iif(vari_20131218=max2,vari_20131218,-2); update d set max3 = iif(vari_20140204 < max2 and vari_20140204 > max3,vari_20140204,max3); update d set max3 = iif(vari_20140815 < max2 and vari_20140815 > max3,vari_20140815,max3); update d set max3 = iif(vari_20141221 < max2 and vari_20141221 > max3,vari_20141221,max3); update d set max3 = iif(vari_20150106 < max2 and vari_20150106 > max3,vari_20150106,max3); update d set max3 = iif(vari_20151005 < max2 and vari_20151005 > max3,vari_20151005,max3); update d set max3 = iif(vari_20151208 < max2 and vari_20151208 > max3,vari_20151208,max3); update d set max3 = iif(vari_20151224 < max2 and vari_20151224 > max3,vari_20151224,max3); update d set max3 = iif(vari_20160109 < max2 and vari_20160109 > max3,vari_20160109,max3); update d set max3 = iif(vari_20160430 < max2 and vari_20160430 > max3,vari_20160430,max3); update d set max3 = iif(vari_20170228 < max2 and vari_20170228 > max3,vari_20170228,max3); update d set max3 = iif(vari_20170620 < max2 and vari_20170620 > max3,vari_20170620,max3); update d set max3 = iif(vari_20180420 < max2 and vari_20180420 > max3,vari_20180420,max3);
|