Following with a user complain about SQL timeout I bumped into one issue with a view. I ran the Object Execution Statistics report and saw that one particular SP, which querying a view containing about 3 tables and 12 views, is coming up with high numbers. I am not a dba specialist and would like to know where to start from ?
Reduce the logical reads. Likely also need to reduce the physical reads too.
Any idea on how to reduce those reads ? They are not a lot of rows involved here but I reckon I should reduce the number of requests to a common table where I am storing various different type of information. All the views in yellow on the attached picture are indeed selecting data from the same table called tblCodes: the property's type (it is a real estate website), the property's status, the property's operation types, etc
Update: I made some significant improvement removing the LEFT OUTER JOIN now the sp is running way faster. Anyway I'd love to know if an alternative exists to make such a view or actually sp faster.
So that is at least 15 tables in the view. This is asking a lot of the optimizer.
Also views within views are generally a bad idea.
I would start by creating one query only based on tables and then look at how that can optimized.
Once you have the logic all in one place it is easier to spot things like tables being referenced multiple times when once would do, variables which cause parameter sniffing etc.