Can Joins between views and table hurt performance

Question

I am new in sql server,

my manager has given me job where i have to find out performance of query in sql server 2008.

That query is very complex having joins between views and table. I read in internet that joins between views and table cause performance hurt?

If any expert can help me on this? Any good link where i found knowledge of this? How to calculate query performance in sql server?

Answer 1

A view (that isn't indexed/materialised) is just a macro: no more, no less

That is, it expands into the outer query. So a join with 3 views (with 4, 5, and 6 joins respectively) becomes a single query with 15 JOINs.

This is a common "I can reuse it" mistake: DRY doesn't usually apply to SQL code.

Otherwise, see Oded's answer

Answer 2

Oded is correct, you should definitely start with the query plan. That said, there are a couple of things that you can already see at a high level, for example:

CONVERT(VARCHAR(8000), CN.Note) LIKE '%T B9997%'

LIKE searches with a wildcard at the front are bad news in terms of performance, because of the way indexes work. If you think about it, it's easy to find all people in the phone book whose name starts with "Smi". If you try to find all people who have "mit" anywhere in their name, you will find that you need to read the entire phone book. SQL Server does the same thing - this is called a full table scan, and is typically quite slow. The other issue is that the left side of the condition uses a function to modify the column (specifically, converting it to a varchar). Essentially, this again means that SQL Server cannot use an index, even if there was one for the column CN.Note.

My guess is that the column is a text column, and that you will not be allowed to change the filter logic to remove the wildcard at the beginning of the search. In this case, I would recommend looking into Full-Text Search / Indexing functionality. By enabling full text indexing, and using specific keywords such as CONTAINS, you should get better performance.

Again (as with all performance optimisation scenarios), you should still start with the query plan to see if this really is the biggest problem with the query.

Answer 3

Look at the query plan - it could be anything. Missing indexes on one of the underlying view tables, missing indexes on the join table or something else.

Take a look at this article by Gail Shaw about finding performance issues in SQL Server - part 1, part 2.