View taking long time for exevution

SVA 86 Reputation points
2023-06-05T15:18:57.0266667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-06-05T21:00:54.1+00:00

    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.

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-06-06T02:19:18.84+00:00

    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

    0 comments
  4. SVA 86 Reputation points
    2023-06-06T08:15:06.06+00:00

    Resolved it by creating an index. Thanks for your support

    0 comments