Yippee. Someone who shares my problems.
Jamie, 20 million against 830. Nice. Must have hurt when you first ran it.
Currently I am working with a small set of the data. 1000 rows import will turn into around a million against around ±75 mill. Not quite your numbers but....
I think the whole lookup thing is flawed as it currently sits. It's one of those things that worked well in dev with a few hundred rows or worked really well on a 64 proc machine with 64GB RAM (Mine's on order)
Your suggestion or dyanamically altering the query will have an effect but still it's limited. How do you propose to limit the lookup (Fact?) data? You cannot do it by time because you might have a shopper who last bought 3 years ago so you'll miss that. (BTW: These are the arguments I am facing as well)
My first thought was to cache sucessful queries. i.e. lookup does a lookup and when found caches that record. Move on to the next one. In some cases this would be a huge advantage but others a waste of time as well. Both you and I sit in Retail. The chances of a customer coming to a shop 3 times a day are slim so caching those records in the hopes you'll come across it again is a waste of time. Although, this method would get around the "hang in there while I cache 23 million records for you" 10 minute caching.
With the scale of the data, the only way I can see to get good results is to dump it to a table and do a join. Merge or Loop would be streaks ahead of putting everything in a bucket and sifting through it.
Where I can see the Lookup being brilliant is if you loading 20 million records against 300k. i.e. Purchases against stock (Will be more than 300k ) Even then, a loop or merge join might actually beat it. It to is cached data.
Which brings me to another idea. Maybe I am wrong here...
I just queried a table containing 20 mil rows. This would have flooded the SQL cache as well. Have I just wiped my cache of other tables / queries in order to do this?
Ahh well, the joys or working with bleeding edge tech
May the force be with you!
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!