May 5, 2017 at 9:58 am
I believe the denormalization idea was to put all the search data in 1 table, in a separate database, so that the production tables would not be impacted by searching. It gets occasional inserts as new member records are added.
I updated statistics yesterday.
May 6, 2017 at 3:12 am
Denormalization is probably the worst possible choice for optimising a search.
Well, there are not many things due normalisation is good for.
The biggest one of them is making it simpler for developers who do not understand data structure, entity joining, index strategies and other freaky DBA stuff.
Your case proves that denormalisation does not help searches.
But getting to the point:
You did not answer my question: "Why did you decide the full text index is even an issue here?"
Did you try to exclude the full text parts from the filters completely?
Did you try to run run the query with full text filters only?
How does it go?
_____________
Code for TallyGenerator
May 8, 2017 at 1:30 pm
I think you and others are correct that it's not Full-text causing the problem, rather the code below, is not making use of the regular indexes.
However on our "Stage" server, running the same code, it runs very quickly, I posted both actual execution plans. (above)
(memepf_funccode1 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900')
or memepf_funccode2 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900')
May 8, 2017 at 2:44 pm
Codeay be the same on Stage server, what about the data?
_____________
Code for TallyGenerator
May 8, 2017 at 3:49 pm
Data is just about exactly the same. The Stage database was copied to the new server for testing, when the difference in performance was noticed.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply