View taking long time for exevution

Question

Hi

Can anyone help me on to identify the issue related view. It is taking long time for execution when calling a view with condition. But when running the view query with the same condition it is running fast.

How can we identify the cause of slowness.

Answer 1

So you are saying that

SELECT ... FROM YourView 

is fast, whereas

SELECT ... FROM YourView WHERE ...

is slow?

You need to compare the query plans; they are obviously different. Close at hand is that there is some misestimate. For instance, without a condition, SQL Server goes for a parallel plan with mainly hash joins. With a condition, maybe the plan is single-threaded and uses loop joins and key lookups.

Note that I am only speculating here, since I don't know anything about your queries.

If you want more specific help, we would need to see:

1. The queries you are running.
2. The view definition.
3. The actual query plans in XML format. Upload these to http://www.pastetheplan.com

Once we have this information, we may ask for more information.

Answer 2

Hi @SVA

The information you give is a bit vague.

If you can provide relevant information based on what Erland said, it can help solve the problem.

You can refer to this document to analyze the actual execution plan.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver16

Best regards,

Percy Tang

Answer 3

Resolved it by creating an index. Thanks for your support