how to combine 2 separate queries in sql server (1 Viewer)

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
Can someone please help .

I have 2 SELECT queries A and Query B ,

the 2 queries were 1 before I separated them due to running slow taking 18minutes .


I have now split the 2 queries and A runs in 25 secs , B runs instantly . Both produce same number of records

Question
What will be the best way to add them together as I want the results in one lot please
Code:
Query A

SELECT
Key_Match.IDQ,
ProjectReturn_Detail.ProjectID,
Quarters.QtrName AS Quarter,
FROM
ProjectReturn_Detail
INNER JOIN Key_Match ON ProjectReturn_Detail.ProjReturnID = Key_Match.ProjDetID
INNER JOIN ProjectReturn_Leaders ON ProjectReturn_Detail.ProjReturnID = ProjectReturn_Leaders.ProjReturnID
INNER JOIN ProjectReturn_Finance ON ProjectReturn_Detail.ProjReturnID = ProjectReturn_Finance.ProjReturnID
LEFT JOIN RAG ON ProjectReturn_Detail.MPADCAID = RAG.DCAID


Query B
SELECT
    Key_Match.IDQ,
    ProjectReturn_Detail.ProjectID,
    Quarters.QtrName AS Quarter,
    Quarters.SnapshotDate AS [Quarter - Snapshot Date],
    Datediff(day,[StartDateFor],[SnapshotDate])/365.25 AS [Duration - Spent - Forecast (Years)],
    CASE
        WHEN Datediff(day,[StartDateFor],[EndDateFor]) = 0 THEN NULL ELSE
      Datediff(day,[StartDateFor],[SnapshotDate])/Datediff(day,[StartDateFor],[EndDateFor])
      END
        AS  [Duration - Spent - Forecast (%)],
    vwMilestoneAss_Next.[Milestone - Assurance - Next IPA Gate - Name]
FROM
    ProjectReturn_Detail
        INNER JOIN Key_Match ON ProjectReturn_Detail.ProjReturnID = Key_Match.ProjDetID
    INNER JOIN [vwProject - Last QTR] ON ProjectReturn_Detail.ProjectID = [vwProject - Last QTR].ProjectID
    INNER JOIN [vwProject - First QTR] ON ProjectReturn_Detail.ProjectID = [vwProject - First QTR].ProjectID
    INNER JOIN Quarters ON ProjectReturn_Detail.QTRID = Quarters.QTR_ID
    LEFT JOIN vwManMilestoneKDDStartEnd ON ProjectReturn_Detail.ProjReturnID = vwManMilestoneKDDStartEnd.ProjReturnID
    LEFT JOIN vwMilestoneAss_Next ON ProjectReturn_Detail.ProjReturnID = vwMilestoneAss_Next.ProjReturnID;
,.Please let me know if I need to post the queries here
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,354
What type of combined output?
You could union them by adding matching null fields to the first query if you just want them one after the other

EDIT: How many records are you looking at for query 1 - 25 seconds is an age for a SQL query?

EDIT 2: Are all the joining fields indexed?
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
What type of combined output?
You could union them by adding matching null fields to the first query if you just want them one after the other
the 2 queries were 1 query before I split them because the 1 query was running for 18minutes . the split query individually now running much faster but 2 separate queries. I want the output as 1 query running optimally . how do I achieve this.

I cant UNION them because UNION is for combining separate rows . I need to combine Separate COLUMNs of Query A+ Query B in this case because the outputs of Query A is 6000 records and Query B is 6000 record which is what I want . the only issue is the results are Separate.

I hope clear
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:47
Joined
May 7, 2009
Messages
19,169
can you use Union Query?
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
the 2 queries were 1 query before I split them because the 1 query was running for 18minutes . the split query individually now running much faster but 2 separate queries. I want the output as 1 query running optimally . how do I achieve this.

I cant UNION them because UNION is for combining separate rows . I need to combine Separate COLUMNs of Query A+ Query B in this case because the outputs of Query A is 6000 records and Query B is 6000 record which is what I want . the only issue is the results are Separate.

I hope clear

someone suggested to use a LEFT join . but am thinking No because we will be going back to the original issue when the 2 queries together took 18mins . do u think this suggestion is correct
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
can you use Union Query?
Not at all because I am not combining rows . the rows for the separate queries give the same result which is ok . I need to combine the Columns as most of the the Column of Query A is different from Columns in Query B
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
What type of combined output?
You could union them by adding matching null fields to the first query if you just want them one after the other

EDIT: How many records are you looking at for query 1 - 25 seconds is an age for a SQL query?

EDIT 2: Are all the joining fields indexed?
yes all indexed as I have run the Execution plan for recommendation and no suggestions from it . Originally before I split the 2 queries when it took 18 mins ,

I had to separate the Milestone related columns from the Original query and created a separate query B for Milestone . to my surprise the results came out instantly but when run with the Original query was taking 18mins .
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:47
Joined
May 7, 2009
Messages
19,169
where is Quarters table in query A?
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,354
The only significant difference appears to be the left join to in Query A.

LEFT JOIN RAG ON ProjectReturn_Detail.MPADCAID = RAG.DCAID

But you don't use any fields from RAG table so why is it there?
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
sorry guys the code is running ok. code is too long hence I just selected a couple of lines thats why lines of code missing
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,354
So assuming you can't speed up running Query A why not dump it's contents into a temp table in a stored procedure then join that to the Results from query B in the Stored Procedure ?
 

Isaac

Lifelong Learner
Local time
Today, 08:47
Joined
Mar 14, 2017
Messages
8,738
How about you post the code that you originally had, the one that took 18 minutes, and maybe someone will be able to suggest some optimization to make it run faster.

We do not create 2 different queries, both returning the exact same records, each query with different columns, and then add them back together. Normally.
 

Mittle

Member
Local time
Today, 15:47
Joined
Dec 2, 2020
Messages
105
How about you post the code that you originally had, the one that took 18 minutes, and maybe someone will be able to suggest some optimization to make it run faster.

We do not create 2 different queries, both returning the exact same records, each query with different columns, and then add them back together. Normally.
thank you for your help.

As suggested by Minty

1. I have now run the Query Execution plan on Query B and there was recommendations to create a covering index. this is something I did do initially because Query B produced the results instantaneously . anyway after creating the covering index, the Original query before the split into 2 now executes in 27 sec rather than the 18 mins .


2.I thought ok and maybe optimise even further , I now ran the Display execution plan against and I then get another index recommendation 2. I created this second covering index and query runs for 18mins .in a nutshell index recommendation 2 is performing bad .

is anyone able to spot or explain the reason why please .





Code:
1,
CREATE NONCLUSTERED INDEX [IX_MilestoneTypeID_ProjReturnID_MilestoneNotes]
ON [dbo].[Milestone] ([MilestoneTypeID])
INCLUDE ([ProjReturnID],[Actual-ForecastDate],[MilestoneNotes],[Version No])
GO
--*/

2.
CREATE NONCLUSTERED INDEX [IX_MilestoneTypeID_QtrID_ProjRID_AcForecastDate]
ON [dbo].[Milestone] ([MilestoneTypeID])
INCLUDE ([QuarterID],[ProjReturnID],[Actual-ForecastDate])
GO
 

Isaac

Lifelong Learner
Local time
Today, 08:47
Joined
Mar 14, 2017
Messages
8,738
thank you for your help.

As suggested by Minty

1. I have now run the Query Execution plan on Query B and there was recommendations to create a covering index. this is something I did do initially because Query B produced the results instantaneously . anyway after creating the covering index, the Original query before the split into 2 now executes in 27 sec rather than the 18 mins .


2.I thought ok and maybe optimise even further , I now ran the Display execution plan against and I then get another index recommendation 2. I created this second covering index and query runs for 18mins .in a nutshell index recommendation 2 is performing bad .

is anyone able to spot or explain the reason why please .





Code:
1,
CREATE NONCLUSTERED INDEX [IX_MilestoneTypeID_ProjReturnID_MilestoneNotes]
ON [dbo].[Milestone] ([MilestoneTypeID])
INCLUDE ([ProjReturnID],[Actual-ForecastDate],[MilestoneNotes],[Version No])
GO
--*/

2.
CREATE NONCLUSTERED INDEX [IX_MilestoneTypeID_QtrID_ProjRID_AcForecastDate]
ON [dbo].[Milestone] ([MilestoneTypeID])
INCLUDE ([QuarterID],[ProjReturnID],[Actual-ForecastDate])
GO
Please post the full sql of the query that is now performing bad
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,354
If you can post up the SQL of the view we might be able to see something.
 

Jbooker

New member
Local time
Today, 11:47
Joined
May 12, 2021
Messages
25
As others have said, post the SQL so we can help. There are cases when executing 2 queries make sense often if only to simplify the code usually WITH clause is your friend in such cases as it creates common table expression without any need for temp tables or disk io.
 

WayneRyan

AWF VIP
Local time
Today, 15:47
Joined
Nov 19, 2002
Messages
7,122
I'm on an iPad so typing is tedious.

I see 4 views in the 2nd query. The actual execution plan should be showing full scans for them.
You can create them with schemabinding. That will let you put indexes on them and really speed things up.

Failing that, you can do as Minty said earlier and put them in #temp tables. These in turn can have indexes applied to then and further speed things up. Building indexes is less work than full table/view scans.

The CTEs are great for the logical representation of a process, but they can hurt performance at times. Don't get me wrong, I use them a lot. Today I had an ad-hoc query with two CTEs (15 minutes). Materializing them into two #temp tables cut it to under a second. Each case is different.

Just some thoughts.
Wayne
 

Users who are viewing this thread

Top Bottom