View running very slow

[Mittle]

Hello

I have a view A which has been split into 2 separate views B and C

View C takes 2 secs to run


View B takes 6 secs to run


Main View A is taking 32 mins to run.ive run a Display Estimated Execution Plan. nothing been suggested like indexes or anything . also mot of the Cost is 0% or 1% . cant see anything costing the query to run slow

 

[Minty]

32 minutes for a View, wow.
How many records , tables , subqueries etc.

 

[Mittle]

32 minutes for a View, wow.
How many records , tables , subqueries etc.

5931 records in total but lots of text fields but just from an overview .


View B and C are only being called in View A thats all and Views B and C take 6 secs and 2 secs respectively.

 

[Minty]

It's generally considered poor practice to base Views on Views, although there are exceptions.

Can you post up the SQL of all three?

 

[Mittle]

yes ur correct about poor practice to base views on views but am still at early stages just trying to convert the Query as is from access to SQL Server


so the View B and C is where all subqueries are and those are running in 6 sec and 2 secs .

I'll just put a snippet of the View A here . there are about 150 columns so each of the 3 views produce 5931. the only difference is columns numbers. Please note that this view has about 150 columns and ive only given 8 here .

View A



SELECT
[vwHeadline Data pt1].IDQ,
[vwHeadline Data pt1].[ID Number],
[vwHeadline Data pt1].ProjReturnID,
[vwHeadline Data pt1].ProjectID,
[vwHeadline Data pt2].[Start Date - Variance (Days)],
[vwHeadline Data pt2].[EndDate - Variance (Days)],
[vwHeadline Data pt2].[End Date - Variance (%)],
FROM ([vwHeadline Data pt1]
INNER JOIN [vwHeadline Data pt2] ON [vwHeadline Data pt1].ProjReturnID = [vwHeadline Data pt2].ProjReturnID)
LEFT JOIN vwScopeChangeGroup ON [vwHeadline Data pt1].ProjReturnID = vwScopeChangeGroup.ProjectReturnID;
GO

 

[Minty]

Are you saying you produce 5000+ columns of output?
Or rows? Rows is nothing Columns is barmy.

 

[Mittle]

Are you saying you produce 5000+ columns of output?
Or rows? Rows is nothing Columns is barmy.

5931 records/rows and 150 columns

 

[Minty]

Okay, I think we need to see B and C as there is nothing scary in what I can see of A.

 

[Galaxiom]

Are the joins in C based on calculations in A and B?

 

[Minty]

I think you need to move this to a stored procedure and dump the contents of both views into a couple of temp tables then run your final Query (view A) on that, all within the store procedure.

As discussed via PM this is a really simple example of creating a temp table and then displaying the output.
It should get you started.

This is written on a current Azure version azure server, so if you are on a very old version of SQL Server some of these functions may not work.

/****** Object:  StoredProcedure [dbo].[sp_Date]    Script Date: 15/12/2020 10:11:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================
-- Author:        Minty
-- Create date: 15-Dec-2020
-- Description:    Test SP showing newer FORMAT expression in Azure
-- ==============================================================
CREATE PROCEDURE [dbo].[sp_Date_Test]
    -- Add the parameters for the stored procedure here
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DROP TABLE IF EXISTS #TempTable

    SELECT * INTO #TempTable FROM
    (
    SELECT
        Getdate() as DateNorm
        , Format(Getdate(),'d','en-gb' ) as DateFormatted
    ) as X
    
    
    SELECT * FROM #TempTable

    DROP TABLE IF EXISTS #TempTable

END

 

[Mittle]

can anyone please help on the best way to compose the Logic for the following sample stored proc.


1.the main body is INSERT INTO SELECT .....sql



2.The select part returns 100 rows for example .

how do I write the code to run the INSERT ONLY if 100 rows are returned

Create procedure usp_AppNewProject
AS
BEGIN

SET NOCOUNT ON

INSERT INTO Project (ProjectID,ProjectName,JoinDate)
SELECT
ProjectDetail_Q.ProjNo
ProjectDetail_Q.ProjectNme
Quarters.SnapshotDate

FROM ProjectDetail_Q
LEFT Join.......


END

Thanks

 

[Minty]

Do a count of the query results first - Air code something like?

Declare @iCount as Int

SET @iCount = (Count (*) FROM
(
SELECT
ProjectDetail_Q.ProjNo
ProjectDetail_Q.ProjectNme
Quarters.SnapshotDate

FROM ProjectDetail_Q
LEFT Join.......)
)

IF @iCount = 100
   BEGIN
     Your INSERT goes here
   END

 

[Mittle]

Fantastic and thanks . I will try it

 

[Mittle]

Do a count of the query results first - Air code something like?

Declare @iCount as Int

SET @iCount = (Count (*) FROM
(
SELECT
ProjectDetail_Q.ProjNo
ProjectDetail_Q.ProjectNme
Quarters.SnapshotDate

FROM ProjectDetail_Q
LEFT Join.......)
)

IF @iCount = 100
   BEGIN
     Your INSERT goes here
   END

somethings not quite right with the brackets . been trying to figure its out but its not working .

 

[Minty]

Tou might need to give the SELECT query an Alias

) as x

At the end.

 

[Mittle]

somethings not quite right with the brackets . been trying to figure its out but its not working . I changed to this but getting incorrect syntax on the IF

Declare @iCount as Int SET @iCount = SELECT (Count (*) FROM ( SELECT ProjectDetail_Q.ProjNo ProjectDetail_Q.ProjectNme Quarters.SnapshotDate FROM ProjectDetail_Q LEFT Join....... ) IF @iCount = 100 BEGIN Your INSERT goes here END

 

[Mittle]

Tou might need to give the SELECT query an Alias

) as x

At the end.

dont think an alias is needed though

 

[Minty]

Can you post your exact code - the code just posted couldn't possibly work the join ...... in it.

 

[Mittle]

Can you post your exact code - the code just posted couldn't possibly work the join ...... in it.

Declare @iCount as Int

SET @iCount =  SELECT Count (*) FROM
(
SELECT
ProjectDetail_Q.ProjNo
ProjectDetail_Q.ProjectNme
Quarters.SnapshotDate

FROM ProjectDetail_Q
LEFT Join.......
)

IF @iCount = 100
   BEGIN
     Your INSERT goes here
   END

 

[Mittle]

Declare @iCount as Int

SET @iCount =  SELECT Count (*) FROM
(
SELECT
ProjectDetail_Q.ProjNo
ProjectDetail_Q.ProjectNme
Quarters.SnapshotDate

FROM ProjectDetail_Q
LEFT Join.......
)

IF @iCount = 100
   BEGIN
     Your INSERT goes here
   END

all I changed in you code was added the SELECT but I get an incorrect syntax at the beginning of the IF statement