how to combine 2 separate queries in sql server

[Mittle]

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

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]

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]

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]

can you use Union Query?

 

[Mittle]

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]

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

 

[arnelgp]

original issue when the 2 queries together took 18mins . do u think this suggestion is correct

you need to try first.

 

[arnelgp]

Quarters.QtrName AS Quarter

on query A, where is the table/alias?

 

[Mittle]

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 .

 

[Mittle]

on query A, where is the table/alias?

Apologies I dont understand the question .

 

[arnelgp]

where is Quarters table in query A?

 

[Minty]

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]

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]

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]

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]

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 .

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]

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 .

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]

If you can post up the SQL of the view we might be able to see something.

 

[Jbooker]

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]

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