• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 64
  • Last Modified:

Final fix-up needed for stored proc

Hi Experts,

This is in regards to the following question

https://www.experts-exchange.com/questions/29077470/How-to-select-unique-records-from-a-stored-procedure.html?anchor=a42430209¬ificationFollowed=202446823#a42430209

a- The total count are less then expected (although by a small number).
b- it takes very long to execute
c- the following line is not working
, E.LastName + '' '' + E.FirstName As EmpName 

Open in new window

Thanks
0
bfuchs
Asked:
bfuchs
  • 15
  • 10
  • 6
1 Solution
 
PortletPaulfreelancerCommented:
I could read all of the comments in that previous question, and I might as a result guess which code you are now using, but it would be far more reliable if you add the code of your stored process here please, so we don't guess incorrectly.
0
 
PortletPaulfreelancerCommented:
by the way, try this

          E.LastName + ' ' + E.FirstName As EmpName
0
 
bfuchsAuthor Commented:
@Portletpaul,

Right, here is the code

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotesUniqueEmp2]    Script Date: 01/17/2018 15:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotesUniqueEmp2] (
 @strWhere varchar(5000) ,
 @strRetColums varchar(500) = null

 )
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000) = ''
declare @strWhere2 varchar(5000)

--set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

if @strRetColums is null

SET @tmp = ';with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   SELECT  --TOP (99.9999) PERCENT 
             CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
		   , O.LastName + '' + O.FirstName as EmpName
 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID  
'

--IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where O.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end
print @tmp
exec (@tmp)

Open in new window


The expected results should be the same as the following

Select distinct employeeid from orientations

E.LastName + ' ' + E.FirstName As EmpName
This is how I had originally and didnt work, see other post.

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
Thanks, try this

          E.LastName + space(1) + E.FirstName As EmpName

Because you are pushing a string into a variable using quotes inside that string can get complex, the space() function helps avoid this.
1
 
Mark WillsTopic AdvisorCommented:
Not Performing

1) make sure clustered primary key
2) add indexes for the predicates used in joins
3) apply some of that "where" clause to the CTE if possible

ALTER TABLE employeestbl   
ADD CONSTRAINT PK_employees_ID PRIMARY KEY CLUSTERED (ID);  
GO  
ALTER TABLE facilitiestbl   
ADD CONSTRAINT PK_facilities_ID PRIMARY KEY CLUSTERED (ID);  
GO  
ALTER TABLE employeesdocstov   
ADD CONSTRAINT PK_employeesdocstov_ID PRIMARY KEY CLUSTERED (ID);  
GO  
ALTER TABLE orientations   
ADD CONSTRAINT PK_orientations_ID PRIMARY KEY CLUSTERED (ID);  
GO  
ALTER TABLE orientationnotes   
ADD CONSTRAINT PK_orientationnotes_ID PRIMARY KEY CLUSTERED (ID);  
GO  


create index idx_orientations_id on orientations (id, employeeid)
create index idx_orientationnotess_id on orientationnotes (id, orientationid)
create index idx_OrientationsFacility_id on orientations (id, Facilityid)
create index idx_EmployeesDocsTov_id on EmployeesDocsTov (id, EmployeeID)

Open in new window


As for stringing in names (or quoted identifiers) must double up those single quotes, or use space() :
		   , E.LastName + '' '' + E.FirstName as EmpName   -- from employeestbl

Open in new window

see : https://docs.microsoft.com/en-us/sql/t-sql/functions/space-transact-sql
And probably should check NULL  ie isnull(lastname,'''')  -- doubling up those single quotes again.
0
 
PortletPaulfreelancerCommented:
Regarding the count being lower than expected, I trust you realize that because we don't have access to the tables we cannot be certain and that really this is best investigated by yourself. You are using a where clause, so due to that the overall row number may be less than expected.
0
 
Mark WillsTopic AdvisorCommented:
Can you show us a mix of where clauses ? Might be able to parse for use in the CTE.
0
 
Mark WillsTopic AdvisorCommented:
@portletpaul,

Despite having to ' ... guess which code you are now using,' the CTE didnt really change at all, and there is a DB60.zip at Post : 42432142

That db60.zip is my reasons for the PK and indexes commentary above.
0
 
bfuchsAuthor Commented:
Hi Experts,

Just one thing to clarify..
My issue with record count is without using any where clause, therefore I believe there must be something missing here..

Again, the entire stored proc should be based on the Orientation/OrientationNotes tables, all the other tables are lookups, and a record should come up even if no records in OrientationNotes table.


Re applying new indexes, would it be helpful if I send the current indexes so you can examine which are really missing, or perhaps the execution plan? as I dont want to over-index those tables..

3) apply some of that "where" clause to the CTE if possible
This would be a big task, as currently the entire where string is being build dynamically in FE and used in various areas, not necessary in this stored proc.

E.LastName + space(1) + E.FirstName As EmpName
This works.
E.LastName + '' '' + E.FirstName As EmpName
(From other post), actually I see this is also working, not sure what was causing this to fail last time I tested..

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
Starting with EmpName

When joining two character strings, if either in NULL then the result will also be NULL. So it might be wise to accommodate for that scenario no matter how unlikely
isnull(E.LastName,'''') + '' '' + isnull(E.FirstName,'''') As EmpName
-- OR
isnull(E.LastName,space(0)) + space(1) + isnull(E.FirstName,space(0)) As EmpName

Open in new window

Either way, you need to either put up with doubling up single quotes, or, making sure of bracket counts

Honestly thought this was resolved in the other thread,  Post : 42436792
so, strongly recommend to handle NULLs

Next: Indexes

Yes please - show us the indexes you currently have in place. While overindexing is often a problem, having the right indexes in place is worth it. Especially in tables when there are ID + FK-like-attribute relationships in big tables.

Finally: Row Counts

Having a lesser count implies one of the inner joins isnt behaving as expected. The left outer joins means that data is optional, so would not decrease the rowcount.

Having a greater count than expected is a different story - any join could be contributing.

It is also possible that any "where" clause is excluding data - but you have eliminated that - so is not a consideration.

The catch-22 is any additional integrity measures in building the CTE_Notes, or, CTE_Orientations will likely impact on execution times.

To investigate... first check the count from the CTE's and inner joins
--First check we have correct count of orientations
; with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
) select count(*) as O_Count from cte_Orientations

-- Now check that we still have the same number when we add CTE_Notes
;with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
         , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
) select count(*) as N_Count from CTE_Notes

-- Now check with our inner joins
-- Now check that we still have the same number when we add CTE_Notes
;with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
         , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
) select count(*) as J_Count 
  from CTE_Notes C
  INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
  INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
  INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 

Open in new window

Can be run all together. If you can reply with the O_Count, N_Count and J_Count we can then move onto next steps...
0
 
PortletPaulfreelancerCommented:
I would suggest simplification by doing more in a single CTE. The following CTE gathers all the output columns needed (I think) from the 2 tables involved, so you can avoid re-joining them later. I also suggest using row_number() to determine the "latest" orientation.
;with CTE_Notes As
( 
  Select  EmployeeID
        , ID
  FROM (
        Select
             O.EmployeeID
		   , O.ID AS OrientationID
		   , O.Active
		   , O.DateEntered
		   , O.DueDate
		   , O.DueDateInitial
		   , O.Initial AS OrientationInitial 
		   , O.LastName + space(1) + O.FirstName as EmpName
		   , N.ID
		   , N.ID as LastNoteID
		   , N.Initial
		   , N.Mailing
		   , N.Note
           , row_number() over(partition by O.EmployeeID order by O.ID DESC) as rn
      from Orientations O
      Left Outer Join OrientationNotes N on O.ID = N.OrientationID
       ) d
  WHERE rn = 1
)

Open in new window


Note, if your where clause is accessing columns from aliases O or N, then I would include that portion of the where clause in the CTE itself. (As Mark rightly points out this would be more efficient and should also help ensure the rows returned are consistent).

After that CTE, the following query would look something like this:
SELECT
      CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
           Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS red
    , c.ID
    , c.EmployeeID
    , c.FacilityID
    , c.Active
    , c.Note
    , COALESCE(c.Day, c.DateEntered) AS day
    , c.Initial
    , c.ID                           AS orientationid
    , c.DateEntered
    , c.Initial                      AS orientationinitial
    , c.Traveler
    , T.TovInfoLastDay               AS lastday
    , c.DueDate
    , c.Mailing
    , c.DueDateInitial
    , T.Last_Orient_Note_Day
    , e.AvailibilityPDays
    , e.AvailibilityPShifts
    , e.City
    , e.LastName
    , e.FirstName
    , e.Title
    , e.AddressLine1
    , f.Name                         AS facilityname
    , c.ID                           AS lastnoteid
    , T.TovInfoFacility1             AS lastfacility
    , e.Degree
    , e.NotSendTextMsg
    , e.NotSendEmail
    , e.Email
    , e.Beeper
    , e.SMSProvider
    , e.ExpectedGraduation
    , c.empname
FROM CTE_Notes c
INNER JOIN Employeestbl e ON c.EmployeeID = e.ID
INNER JOIN Facilitiestbl f ON o.FacilityID = f.ID
-- and the where clause ....

Open in new window


If there is still an issue about the number of rows returned try left joins instead of inner joins (although I hope that isn't needed)
0
 
bfuchsAuthor Commented:
Hi,

@Mark,
First thanks for the concatenating suggestion, and as mentioned I believe this was my mistake as now it seems to work.
show us the indexes you currently have in place.
Is there a script I can run that will return all indexes of a table?
If you can reply with the O_Count, N_Count and J_Count we can then move onto next steps...
O_Count and N_Count are exactly the same, while J_Count is less.

@PorletPaul,
I will test that, compare execution time against the current version & let you know.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
@PortletPaul,
getting many errors..

First I get the following
Msg 207, Level 16, State 1, Line 14
Invalid column name 'LastName'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'FirstName'.

Open in new window


Then when I comment out this line
		   , O.LastName + space(1) + O.FirstName as EmpName

Open in new window

I get
Msg 4104, Level 16, State 1, Line 66
The multi-part identifier "o.FacilityID" could not be bound.
Msg 207, Level 16, State 1, Line 28
Invalid column name 'Last_Orient_Note_Day'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'Last_Orient_Note_Day'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'FacilityID'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'Active'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'Note'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'Day'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'Day'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'DateEntered'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'Initial'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'DateEntered'.
Msg 207, Level 16, State 1, Line 39
Invalid column name 'Initial'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'Traveler'.
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "T.TovInfoLastDay" could not be bound.
Msg 207, Level 16, State 1, Line 42
Invalid column name 'DueDate'.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'Mailing'.
Msg 207, Level 16, State 1, Line 44
Invalid column name 'DueDateInitial'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "T.Last_Orient_Note_Day" could not be bound.
Msg 4104, Level 16, State 1, Line 55
The multi-part identifier "T.TovInfoFacility1" could not be bound.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'empname'.

Open in new window


Tried playing around with adding/removing fields but so far not successful..

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
J_Count is less = Facilities Table or Employees table
Orientations table might have entries for FacilityID or EmployeeID that no longer exist.

So, lets find out, by commenting out facilities join, if the J_count is NOT as per the previous expect O and N counts, then we have a problem with EmployeeID in the Orientations Table. If count is as expected, then we have a problem with FacilitiesID in Orientations Table
;with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
         , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
) select count(*) as J_Count 
  from CTE_Notes C
  INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
  INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
--  INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID  

Open in new window

Then we can check (and correct) referential integrity - these queries should not return any rows, and it they do, there is a problem.
Select O.ID, O.FacilityID from orientations O where O.facilityID not in (select id from Facilitiestbl)

Select O.ID, O.EmployeeID from orientations O where O.EmployeeID not in (select id from Employeestbl)

Open in new window

Lets get those indexes - probably the easiest way is
exec sp_helpindex 'employeestbl'
exec sp_helpindex 'facilitiestbl'
exec sp_helpindex 'Orientations'
exec sp_helpindex 'OrientationNotes'

Open in new window

0
 
PortletPaulfreelancerCommented:
Sorry, I forgot to expand a select clause within the CTE. Please try this query:
;
WITH
      cte_notes AS (
                  SELECT
                        EmployeeID
                      , orientationid
                      , Active
                      , DateEntered
                      , DueDate
                      , DueDateInitial
                      , orientationinitial
                      , empname
                      , Day
                      , ID
                      , lastnoteid
                      , Initial
                      , Mailing
                      , Note
                  FROM (
                        SELECT
                              o.EmployeeID
                            , o.ID                                   AS orientationid
                            , o.Active
                            , o.DateEntered
                            , o.DueDate
                            , o.DueDateInitial
                            , o.Initial                              AS orientationinitial
                            , o.LastName + SPACE(1) + o.FirstName    AS empname
                            , n.Day
                            , n.ID
                            , n.ID                                   AS lastnoteid
                            , n.Initial
                            , n.Mailing
                            , n.Note
                            , ROW_NUMBER() OVER (PARTITION BY o.EmployeeID 
                                                 ORDER BY o.ID DESC) AS rn
                        FROM Orientations o
                        LEFT OUTER JOIN OrientationNotes n ON o.ID = n.OrientationID
                  ) d
                  WHERE rn = 1
            )
SELECT
      CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
             Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS red
    , n.ID
    , c.EmployeeID
    , c.FacilityID
    , c.Active
    , n.Note
    , COALESCE(c.Day, c.DateEntered) AS day
    , n.Initial
    , c.ID                           AS orientationid
    , c.DateEntered
    , c.Initial                      AS orientationinitial
    , c.Traveler
    , t.TovInfoLastDay               AS lastday
    , c.DueDate
    , n.Mailing
    , c.DueDateInitial
    , t.Last_Orient_Note_Day
    , e.AvailibilityPDays
    , e.AvailibilityPShifts
    , e.City
    , e.LastName
    , e.FirstName
    , e.Title
    , e.AddressLine1
    , f.Name                         AS facilityname
    , c.lastnoteid
    , t.TovInfoFacility1             AS lastfacility
    , e.Degree
    , e.NotSendTextMsg
    , e.NotSendEmail
    , e.Email
    , e.Beeper
    , e.SMSProvider
    , e.ExpectedGraduation
    , c.empname
FROM cte_notes c
INNER JOIN Employeestbl e ON c.EmployeeID = e.ID
INNER JOIN Facilitiestbl f ON o.FacilityID = f.ID

Open in new window

If there are missing columns just comment out the related rows of code, as I cannot verify the query against the tables. This should allow you to work out what is missing in the CTE to support the main query.
0
 
bfuchsAuthor Commented:
@Mark,

Looks like you have spotted the row count issue, its the facility join that makes a difference.
Would have to consult with users if they want to include those records..

Attached you will find the indexes.

@PortletPaul,
I get same error messages as the first one..

Thanks,
Ben
idx.xlsx
0
 
bfuchsAuthor Commented:
Hi Experts,

I see the first two issues got solved, just the optimization is not complete yet.
I will leave office now & hope to return on Sun.
Hopefully by then you have a solution for it, as current version takes approx 1.5 min to execute.

Have a nice weekend!

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
You can make the Facilities a left outer join. It will just show NULL info. Quite legitimate...

Have a good weekend, will catch you next week...
0
 
Mark WillsTopic AdvisorCommented:
For a different approach from the CTE - because that will be the costly part - can you please run as a departure from the CTE to compare execution times...
   SELECT  --TOP (99.9999) PERCENT 
             CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red
           , N.ID
           , O.EmployeeID 
           , O.FacilityID
           , O.Active
           , N.Note
           , COALESCE (N.Day, O.DateEntered) AS Day
           , N.Initial
           , O.ID AS OrientationID
           , O.DateEntered
           , O.Initial AS OrientationInitial 
           , O.Traveler
           , T.TovInfoLastDay AS LastDay
           , O.DueDate
           , N.Mailing
           , O.DueDateInitial
           , T.Last_Orient_Note_Day
           , E.AvailibilityPDays
           , E.AvailibilityPShifts
           , E.City
           , E.LastName
           , E.FirstName
           , E.Title
           , E.AddressLine1
           , F.Name AS FacilityName
           , N.ID as LastNoteID
           , T.TovInfoFacility1 AS LastFacility
           , E.Degree
           , E.NotSendTextMsg
           , E.NotSendEmail
           , E.Email
           , E.Beeper
           , E.SMSProvider 
           , E.ExpectedGraduation
           , E.LastName + ' ' + isnull(E.FirstName,'') as EmpName
 
FROM         Employeestbl E
CROSS APPLY (Select max(id) as LastOrientationID from Orientations where EmployeeID = E.ID) LO 
INNER JOIN   Orientations O on LastOrientationID = O.ID
CROSS APPLY (Select max(id) as LastNoteID from OrientationNotes where OrientationID = O.ID) LN 
LEFT OUTER JOIN  OrientationNotes N on LastNoteID = N.ID
LEFT OUTER JOIN  Facilitiestbl F ON O.FacilityID = F.ID                                       -- left outer join for the moment 
LEFT OUTER JOIN  EmployeesDocsTov T ON E.ID = T.EmployeeID  

Open in new window

Currently reviewing indexes and noticed we didnt do the EmployeesDocsTov table...
0
 
bfuchsAuthor Commented:
Hi Mark,

As you're reviewing the indexes already, lets have a complete thing.
Here its the missing table.
IX_EmployeesDocsTov_EmployeeID	nonclustered located on PRIMARY	EmployeeID
IX_EmployeesDocsTov_Run_Query	nonclustered located on PRIMARY	Run_Query
PK_EmployeesDocsTov	clustered, unique, primary key located on PRIMARY	ID

Open in new window


Will test soon the revised code.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Just tested the select portion and it took 42 sec.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Whats about the query execution plan, would that be helpful to determine what steps to take to improve performance?

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
Yes it would...

42secs down from 1.5minutes is a good start  - are you getting the correct / expected results
0
 
bfuchsAuthor Commented:
Hi Mark,

are you getting the correct / expected results
Looks like results are fine.

Re testing performance, I'm now getting both in few seconds, guess as everything is cached in memory.

how can I flush it out of memory?

Besides we have to take in consideration that the system is now not in use by other users..

Attached are the execution plans

Thanks,
Ben
Old.sqlplan
new.sqlplan
0
 
bfuchsAuthor Commented:
Hi Mark,

If that's kind of a complicated task, I can close this one & open another..let me know.

Thanks,
Ben
0
 
Mark WillsTopic AdvisorCommented:
Common 'flush' is
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Open in new window


But best advice is Glenn Berry https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/

Also use RECOMPILE with your stored proc...
0
 
bfuchsAuthor Commented:
Hi Mark,

Tried with recompile option but didn't make a diff, second time took only 3 sec..
Guess will try the other options after 5 as this may affect users.

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Hi,

OK with the DROPCLEANBUFFERS in hand I was able to get a more accurate count of the execution time, and looks like both are more or less the same..approx 1.20.

So I guess will close the question at this point & lets start new one on the missing indexes..

Thanks,
Ben
0
 
bfuchsAuthor Commented:
Thank you very much!!
(Just another couple of sessions like this & I'll become a SQL Pro-:)
0
 
Mark WillsTopic AdvisorCommented:
Just another couple of sessions like this and you will start answering questions !! :) :)

The indexes arent too bad. A couple of improvements are possible, but minor in the scheme of things.

Would also be good to include some indications of "where" clauses.

Being driven by Employee, or Orientations, or CTE each have their own requirements. I am thinking driven by Employee and cross apply is going to be most efficient.

If you could please leave a forwarding address, it would be greatly appreciated...
0
 
bfuchsAuthor Commented:
Hi Mark,

The indexes arent too bad..but minor in the scheme of things.
Then I guess this will not take place very soon..
If you could please leave a forwarding address, it would be greatly appreciated...
The following is something involving this stored proc, although not a pure SQL issue.
https://www.experts-exchange.com/questions/29080218/Form-not-applying-new-record-source-after-requery.html?anchor=a42446237¬ificationFollowed=203077781#a42446237

Thanks,
Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 15
  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now