Hi all,

I have a TSQL code which takes 1 min 20 sec.  Kindly let me know the possibilities to increase the performance at least query runs in 20 to 30 sec. please find the below query:

DECLARE @Temp table
      (
	
      )
/*
--Get All the transfer in and out patients except bed moves (where from and to locations are same)

*/

INSERT INTO @Temp 
       (
		
	  )

SELECT      (
)	
FROM A

WHERE
(
AND 
 )

/*

 ----Get REST (ignore patients who were already listed in transfer in or out) of the patients with bed moves (where from and to locations are same, 
 ----2 records one for out and one for in will be created in each case so just getting transfer in  i.e movementTypeCd 30 to avoid duplicate records )


*/

 INSERT INTO 
            @Temp
SELECT       ( 
			
	      )

Please post this query with CTE or any other techniques would help.

Thanks.

• Edited by BI-Help 7 hours 46 minutes ago

Hi Naomi,

Could you please post this query with CTE so that I can test it once as per the above post. I tried it and couldn't able to get the same result.

Thanks.

This is a big no-no for performance:

WHERE LTRIM(RTRIM(InstNameReportMovement)) <> LTRIM(RTRIM(OtherInstitutionName))

This will result in a forced index scan and tank performance for both objects. Fixing this will improve performance.

Additionally, RTRIM is not needed to compare values. Test it like this:

--VARCHAR, NVARCHAR, CHAR, & NCHAR all the same
DECLARE @A TABLE (C VARCHAR(50))
DECLARE @B TABLE (C VARCHAR(50))

INSERT INTO @A (C) VALUES ('XYZ')
INSERT INTO @B (C) VALUES ('XYZ                ')

SELECT a.c, DATALENGTH(a.c) A_Len, b.c, DATALENGTH(b.C)
FROM @A a
INNER JOIN @B b on (a.C = b.C)
GO

Both of these will match XYZ even though the data is longer in @B. You'll see that the data length of A is 3 and B is 19; yet, they both still match each other.

As for the LTRIM, that's a different story. I would recommend you evaluate the data and determine if anything actually has a leading space stored. If it does, clean it up on both systems and remove ltrim from the query too.

This will get you going on the road to solid, predictable performance of this query. :)

I hope that helps!

Here is what I meant:

;with cte as (SELECT      MovementDate,
            MovementTypeDesc,
            mov.CDCNumber,
            MovementTime,
            LocationReportMovement, 
            OtherLocationCode,
			OtherInstitutionName,
            InstReportMovement,
			InstNameReportMovement,
            OtherInstitutionCode,
			movementTypeCD,
			CASE WHEN movementTypeCD = 30 
AND   LTRIM(RTRIM(InstNameReportMovement)) <> LTRIM(RTRIM(OtherInstitutionName))

THEN 'Transfer In' 
WHEN movementTypeCD = 90 
AND   LTRIM(RTRIM(InstNameReportMovement)) <> LTRIM(RTRIM(OtherInstitutionName))
THEN 'Transfer Out'

CASE WHEN movementTypeCD = 30 and
AND 
  LTRIM(RTRIM(InstNameReportMovement)) = LTRIM(RTRIM(OtherInstitutionName)) THEN 'Bed Move'
 END AS ChangeType,
			RANK() OVER (PARTITION BY mov.CDCNumber  ORDER BY movementDate DESC) AS rankNo	
				
FROM [HCODS].dbo.vSTSExternalMovement  mov 

WHERE
InstReportMovement = '100000000'

  AND MovementDate BETWEEN '20131201' AND '20140601'
  AND movementTypeCD IN ('30', '90') --- 30 for Transfer In and 90 for Transfer out
)

...

Use cte instead of @temp in the second statement. Compare original select * @temp and select * from cte that you're getting the exact same rows with same data.

Hi Daniel,

Thanks for your nice post , I will try it and let you know.

Thanks.

Hi Naomi and all,

Could you please post this query with CTE so that I can test it once as per the above post. I tried it  with Inner Join instead of Left Join and with same results query performance improved from 1 min 20 sec to 1 min 10 sec .

Please add the above query with CTE so that I can test it in my environment.

Thanks.

• Edited by BI-Help 13 hours 10 minutes ago

I posted you the cte already. In your original query for now keep the @temp and also the cte. Comment out the original final select statement and instead do

select * from @temp order by MovementDate

;with cte as (....)

select * from cte order by MovementDate -- use other columns as necessary to make order exactly the same

Make sure that you're getting the same results (exactly).

Once you confirmed that, in the second query switch from @temp into cte (and comment out all @temp related code). Use INNER JOIN as this is what your query was doing a

Hi Naomi,

Thank you ,  I will try it and let you know if it works.

Thanks.

Why did you keep this condition

AND 
  LTRIM(RTRIM(InstNameReportMovement)) <> LTRIM(RTRIM(OtherInstitutionName))
  

In your CTE query? If you notice, I removed it and tried to combine 2 of your queries into 1 (as there was no need for the 2 separate queries).

Also, your current tests are not conclusive as the output doesn't match directly - I asked you to order both of your queries the same way so we can be sure we're getting the same data. It is good that number of rows matched, but it's not 100% guarantee we got CTE query to return exactly the same info.

Anyway, what I meant for your final query - you have

from @hCods as h.

If we're using CTE this part will be

from cte as h. Also, as I said before, use INNER JOIN and not the LEFT join as this is what your query was doing already. If you wanted a left join, then you needed to move conditions on the right table into the JOIN condition.

Hi Naomi,

I used all your above posts and tried with CTE and Inner Join instead of left Join, now I got the exact results . Thank you so much for your time.

Thanks.