View running very slow (1 Viewer)

[Minty]

You need to add brackets around the entire Select

SET @iCount = 
( SELECT Count (*) FROM
    (
    Your SELECT GOES HERE
    )
)

 

[Mittle]

No it doesnt work . I will send the code

 

[Mittle]

ALTER PROCEDURE [dbo].[usp_AppProjectDetail]
    
AS

Declare @iCount as Int ;
SET @iCount = (select  Count(*)

FROM
(
SELECT
        Project.ProjID,
        Quarters.QTR_ID,
        UpldProjectDetail.ProjName,
        Department.DepartmentID,
                UpldProjectDetail.MultiPD,
        
FROM    ProjectLifecycle
RIGHT JOIN UpldProjectDetail
LEFT JOIN Project ON UpldProjectDetail.ProjNo = Project.MPAProjectID
INNER JOIN Quarters ON UpldProjectDetail.Quarter = Quarters.QtrName
artment.Department ON ProjectLifecycle.LifecycleStage = UpldProjectDetail.LifecycleStage
ORDER BY UpldProjectDetail.ProjName)

)


IF @iCount = 142

BEGIN
INSERT

 

[Mittle]

I get incorrect syntax near ')'. expecting AS,ID or Quoted _ID. after the second bracket just before the IF statement

 

[Minty]

Try adding

) as X

as I suggested before.
SQL gets funny when you don't provide an alias sometimes.

 

[Mittle]

ok but if I add the as X

not sure how to reflect this in the `count() because that surely must change too.

 

[Minty]

Sometimes
SQL can be very odd about what it is fussy about.

You could use Count ( X.* ) to see if it makes any difference, as in theory that would be the correct syntax.

 

[Mittle]

it doesnt work . ive tried that

not sure why this is so difficult

 

[Mittle]

it doesnt work . ive tried that

not sure why this is so difficult

ALTER PROCEDURE [dbo].[usp_AppProjectDetail]
    
AS

Declare @iCount as Int ;
SET @iCount = (select  Count(X.*)

FROM
(
SELECT
        Project.ProjID,
        Quarters.QTR_ID,
        UpldProjectDetail.ProjName,
        Department.DepartmentID,
                UpldProjectDetail.MultiPD,
        
FROM    ProjectLifecycle
RIGHT JOIN UpldProjectDetail
LEFT JOIN Project ON UpldProjectDetail.ProjNo = Project.MPAProjectID
INNER JOIN Quarters ON UpldProjectDetail.Quarter = Quarters.QtrName
artment.Department ON ProjectLifecycle.LifecycleStage = UpldProjectDetail.LifecycleStage
ORDER BY UpldProjectDetail.ProjName)as X

)


IF @iCount = 142

BEGIN
INSERT

 

[Minty]

Okay I have just tried this and truncated this is the exact syntax I have used

Declare

    @iRecs as Integer

SET @iRecs = (SELECT Count(*) FROM 
    (


    SELECT top 100 Providername,
                Min(ACB.AvgContract_MonthlyCostforComparison) As HeadLine_Price
    rest of select goes here
    .
    .
    .
    .
    .   
    )
    as x)
    
Print @iRecs

Remove the ; after your declare statement.

What error are you getting?

 

[Mittle]

I get column names was specified multiple times for 'x'

but this query runs ok on its own

 

[Minty]

I get column names was specified multiple times for 'x'

but this query runs ok on its own

Okay - so what is your exact code now? Have you removed the x.*

 

[Mittle]

I used your code exactly as it is typed so yes ive removed the x.*


I will try on a simpler code to see if the subquery works

 

[Minty]

In then SSMS Editor simply highlight only the query section and press F5, it will execute the code that is highlighted.

 

[Mittle]

Minty

dont worry SSMS editor is not the issue . I know how to use it .Apologies and thank you for your help . tomorrow is another day to see what the issue is with the query in question

anyway I have tried the same on a simpler query as below and it runs with no issue .

its so frustrating because the other query runs ok on its own

Declare @iCount as Int
SET @iCount = (select  Count(*)

FROM


(SELECT
    Pd.ProjReturnID,
    Pd.ProjectID,
    Pd.QTRID,
    Pr.MPAProjectID,
    [MPAProjectID] + '-' + [QtrName] AS ID_Q,
    Quarters.QtrName
FROM Quarters
INNER JOIN Project pr
INNER JOIN Pd ON Pr.ProjID = Pd.ProjectID ON Quarters.QTR_ID = Pd.QTRID
LEFT JOIN Key_Match ON Pd.ProjReturnID = Key_Match.ProjDetID
WHERE Key_Match.ProjDetID Is Null)as x
  )
 
  Print @icount


  IF @iCount = 142

BEGIN
PRINT 'you have the correct number of rows '
END
ELSE  IF @icount <> 142
PRINT 'Error: No new Projects '

 

[Minty]

Okay so the principle is good - it must be an issue with the underlying query.
Hope you get to the bottom of it.

 

[Mittle]

Yes indeed! thanks so much for your time today . at one point I thought to myself it cant be this difficult with some code as simple as that .

 

[WayneRyan]

Mittle,

I just started reading this thread and I have a few thoughts that might help.
SQL Server does quite a bit of planning before it runs any query. Its query
analyzer is very adept at working with tables. When you run SSMS and look at
a table; a lot is revealed. There are primary keys, indexes, foreign keys and
STATISTICS. These things help the server develop a very efficient execution
plan. If you start removing any of these, the plan suffers, and the performance
suffers along with it.

Now, your three views have none of these components. I mean literally NONE of
them. For each candidate row in one of the 3 source views, it must do a COMPLETE
scan of the other views when joining them together. These full view scans will
kill your performance.

Fortunately, you have a few things you can do. Some depend on volume of data and
the specifics of your production environment.

1) You can investigate creating the view(s) with SCHEMABINDING. Once your view is
defined you can add indexes on the column(s) you join on. One of the drawbacks to
this is that the table is literally bound to your view. To alter the table design
you'll have to drop your view first. This might suffice as a solution and is
pretty much transparent to your logic that joins the views.

2) You can look at using CTEs in your query:

With View1 As
   (SQL for view1),
     View2 As
   (SQL for view2),
     View3 As
   (SQL for view3)
Select -- This is your select that will join the three views
From   view1 ...

Each CTE expression will efficiently extract their share of data.
The drawback is that if as the individual result sets get larger, performance
starts to suffer.

3) The 3rd method was referenced earlier. Using a stored procedure and creating
#Temp tables. The flow is roughly:

Select * into #Temp1 from view1
Create index idx_YourPK on #Temp1 (Product_Number)

Select * into #Temp2 from view1
Create index idx_YourPK on #Temp2 (Product_Number)

Select * into #Temp3 from view1
Create index idx_YourPK on #Temp3 (Product_Number)

Then use your original Select (with the views) and swap out the view names
for the #Temp table names.

At 1st thought, that seems like a lot of preparatory work to set up the final
query with the #Temp tables, but it is NOTHING compared to the amount of activity
by repeatedly completely scanning the views!

Overall, the 1st two methods mean you're still using views, while the last
relegates you to using a stored procedure. You might even convert the stored
procedure into a table-valued function, but that won't be seen by MS Access.

Hope that some of this helps,
Wayne

 

[Mittle]

Mittle,

I just started reading this thread and I have a few thoughts that might help.
SQL Server does quite a bit of planning before it runs any query. Its query
analyzer is very adept at working with tables. When you run SSMS and look at
a table; a lot is revealed. There are primary keys, indexes, foreign keys and
STATISTICS. These things help the server develop a very efficient execution
plan. If you start removing any of these, the plan suffers, and the performance
suffers along with it.

Now, your three views have none of these components. I mean literally NONE of
them. For each candidate row in one of the 3 source views, it must do a COMPLETE
scan of the other views when joining them together. These full view scans will
kill your performance.

Fortunately, you have a few things you can do. Some depend on volume of data and
the specifics of your production environment.

1) You can investigate creating the view(s) with SCHEMABINDING. Once your view is
defined you can add indexes on the column(s) you join on. One of the drawbacks to
this is that the table is literally bound to your view. To alter the table design
you'll have to drop your view first. This might suffice as a solution and is
pretty much transparent to your logic that joins the views.

2) You can look at using CTEs in your query:

With View1 As
   (SQL for view1),
     View2 As
   (SQL for view2),
     View3 As
   (SQL for view3)
Select -- This is your select that will join the three views
From   view1 ...

Each CTE expression will efficiently extract their share of data.
The drawback is that if as the individual result sets get larger, performance
starts to suffer.

3) The 3rd method was referenced earlier. Using a stored procedure and creating
#Temp tables. The flow is roughly:

Select * into #Temp1 from view1
Create index idx_YourPK on #Temp1 (Product_Number)

Select * into #Temp2 from view1
Create index idx_YourPK on #Temp2 (Product_Number)

Select * into #Temp3 from view1
Create index idx_YourPK on #Temp3 (Product_Number)

Then use your original Select (with the views) and swap out the view names
for the #Temp table names.

At 1st thought, that seems like a lot of preparatory work to set up the final
query with the #Temp tables, but it is NOTHING compared to the amount of activity
by repeatedly completely scanning the views!

Overall, the 1st two methods mean you're still using views, while the last
relegates you to using a stored procedure. You might even convert the stored
procedure into a table-valued function, but that won't be seen by MS Access.

Hope that some of this helps,
Wayne

Hi Wayne

thanks for your help in showing some insight .

this issue has now been resolved with the help of a few experts on this site .

and yes the views are now running perfect

all your comments re:
primary keys , Foreign keys
statistics
schema binding views
have all been taken into account plus execution plans have been run on all the views and recommendations as to creating indexes have all been applied .

 

[Isaac]

Were you able to use with schemabinding? I've generally found that there are so many "gotchas" to that, it ends up being damn near useless. Too many "but's"

Curious