June 20, 2012 at 9:02 am
isuckatsql (6/20/2012)
Sean,The Joins were slower than the subqueries, and actually adding the recentwages and recentjobtitle fields back to the profiles table, gained 30% in performance! That is just something i did not expect!
Thanks
Ian
BTW the more normalization i do, the slower the query gets ! E.F. Codd would not be happy.
LOL actually normalization does make things slower. That is one of tradeoffs and why over normalization makes systems cry. I do think you would be better off to roll your jobtypes, industries etc into another table. I can see from your posted plan that the tables are around 1.5 - 2 million rows or so? There are some serious issue with things because this query just isn't that complicated. How many rows does this return?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 9:15 am
Sean,
The query returns 9 rows, with around 10 fields.
What is frustrating is that the more i follow the correct "rules", the more headaches i get 🙁
Regards
Ian
June 20, 2012 at 9:40 am
isuckatsql (6/20/2012)
Sean,The query returns 9 rows, with around 10 fields.
What is frustrating is that the more i follow the correct "rules", the more headaches i get 🙁
Regards
Ian
Have you updated your statistics recently?
I no expert at reading execution plans but the largest percentage of execution is 92% for the index seek on AllDocuments. It shows an estimated rows as 1 and actual rows as 1,788,145.
We have diverged so far from your original question at this point....sorry.
What is actually wrong with the
SET @QueryCondition = @QueryCondition + ' and contains(dc.doccontent,''' + @Name1 + ''') '
Does it not return what you think it should?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 10:36 am
I update statistics and run DBCC Freeproccache before any testing.
The removal of each Inner join seems to gain about one second in query performance, so i was wondering if removing the Join to the Alldocuments table and using a subquery with the Contains statement would improve performance.
At the moment i am copying the DocContent column to the Profiles table, just to see what kind of performance boost i get.
I know this is going away from normalization, but i am interested in what gains may be available.
So far i have tried Data Compression on the indexes, Data partitioning on the Alldocuments table, and even a Columnstore index on various tables, all without much success.
At least i am learning something 🙂
June 20, 2012 at 1:56 pm
After creating a column in the Profiles table for DocContent and removing the last Join statement, my query searching 3 years of data now takes 0.6 seconds !
Effectively I have removed a lot of normalization and gained a 1000% performance improvement!
This is good but also confusing at the same time.
June 20, 2012 at 2:05 pm
Data normalization is not done for speed. It helps to eliminate data redundency, and potetially various update anomilies that can occur. Sometime it is necessary to denormalize data for performance, but in doing so you need to ensure that you handle any possible issues that may occur as a result.
June 20, 2012 at 2:08 pm
And that is just a brief, 100,000 foot view. There is much more that could be discussed.
June 20, 2012 at 2:39 pm
Lynn Pettis (6/20/2012)
And that is just a brief, 100,000 foot view. There is much more that could be discussed.
Thanks 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply