Two SQL server views runs independently fast but when join takes time a lot

Question

0.00/5 (No votes)

See more:

There are two views which executes separately fast but when joined becomes slow.How to resolve this?

What I have tried:

I have created Proper Non-Clustered index Index on table.When two views joined I select only expected columns and also there are data is large and when there are live transactions going on then how to resolve this ?Also there are not showing any additional index required in Query execution plan..Then how to resolve this ?

Posted 10-Jan-19 17:43pm

suresh shewale

Updated 11-Jan-19 2:13am

Add a Solution

Comments

Santosh kumar Pithani 11-Jan-19 0:26am

Hi suresh, can you show sample queries and count of records you fetching?

CHill60 11-Jan-19 4:06am

My spy camera isn't working today. Can you post the details of the views and some sample data.

3 solutions

Solution 3

Without seeing the definitions of the TABLEs, VIEW, and how they are being JOINed together, it is really hard to determine what the problem could be.

As RickZeeland answered, it is always best to utilize the Execution Plan viewer is Sql Studio to see what is going on within the query.

My recommendation would be to create a new query by piecing together the query definitions for the existing views and work out the performance issue. I would then create a new view from the resulting query. This will have the advantage of not only being optimized but it will not be dependent upon the other views which may need to be changed sometime in the future

Posted 11-Jan-19 2:13am

MadMyche

Solution 2

Hello RickZeeland
thanks for Reply
I already started analyzing views in SQL Execution Plan .
For this
1. First I found unwanted columns after joining Views
2. Non-Clustored Index Created for Views
3. Computed columns avoided in where clause

Issue Resolved ...
Also Sorry Santosh and CHill60 as I couldn't reply you as i started to analyze plan and Thanks

Posted 11-Jan-19 1:28am

suresh shewale

Comments

Maciej Los 11-Jan-19 7:46am

This is not an answer. Please, delete it to avoid down-voting.
To post comment, please use "Have a question or comment" widget (under the solution #1) or "Reply" to reply someone's comment.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2019-01-10T23:13:00

Solution 1

If you have SQL Server Management Studio installed you can analyze your query with 'Execution plan', see: Display an Actual Execution Plan - SQL Server | Microsoft Docs[^]

Posted 10-Jan-19 23:13pm

RickZeeland