February 24, 2016 at 1:40 pm
Thanks for the extra tips, Jacob, I'll post results that way next time.
As for the statistics, I've no doubt you're right. The data in these columns is very 'clumpy' (is that even a word?), in that there are huge blocks of rows that have identical data, then huge blocks that are null. That, in fact, is the primary reason for this project - to clean up some legacy design rubbish.
It also just occurred to me, that maybe Null-excluding filtered indexes might be a cheap and effective boost. I already have the indexes, changing them to exclude nulls should be fairly easy.
February 24, 2016 at 2:09 pm
The filtered indexes do seem to help. I didn't run any precise before-and-after metrics, but responses seem snappier. I also ran this command: UPDATE STATISTICS paleodata.tax.podrobnostitmp No idea if it made any difference. Added another twist, though. In line with another tip from ChrisM, I simplified the WHERE clauses of a few queries and it makes a sizeable difference in the execution plan.
The original versions are:
select count(1)
from PaleoData.Tax.PodrobnostiTmp P1
inner join PaleoData.Tax.PodrobnostiTmp P2
on p1.SubPhylum_5000 = p2.SubPhylum_5000
where P1.SubPhylum_5000 is not null and p1.Kingdom_1000 is not null
and p2.SubPhylum_5000 is not null and p2.Kingdom_1000 is nullwhich gives the first execution plan. I then cut out the unnecessary conditions, like this:
select count(1)
from PaleoData.Tax.PodrobnostiTmp P1
inner join PaleoData.Tax.PodrobnostiTmp P2
on p1.SubPhylum_5000 = p2.SubPhylum_5000
where p1.Kingdom_1000 is not null and p2.Kingdom_1000 is nulland the second plan is the result. No noticeable difference in response times, though, and the statistics are still all screwed up - estimates of over 100,000 rows, and actual returned is zero.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply