Incorrect Actual Rows and other issues
2016-09-19 20:06:44
I ran a simple query against AdventureWorks2014 Database which is causing few issues for PE.
- Actual Number of Rows is showing 4 times as many rows as actually returned.
- Total Time of execution while waiting for the query to complete goes all the way to 16 before stopping and showing mouse spinning icon for few more seconds before completing.
- Duration for the same query in SSMS with Actual Execution Plan is under 500 milliseconds!
- Switching between history versions takes anywhere between 10-20 seconds.
Below is the query and scalar function, it is calling.
SELECT p.FirstName , p.LastName , pp.PhoneNumber , dbo.VerifyPhoneFormat(pp.PhoneNumber) AS FormatType FROM Person.Person AS p JOIN Person.PersonPhone AS pp ON p.BusinessEntityID = pp.BusinessEntityID GO -- ============================================= -- Author: Vlady Oselsky -- Create date: 09-19-2016 -- Description: Demo -- ============================================= CREATE FUNCTION dbo.VerifyPhoneFormat ( @PhoneNumber VARCHAR(20) ) RETURNS VARCHAR(20) AS BEGIN -- Declare the return variable here DECLARE @Indx1 INT DECLARE @Indx2 INT SET @Indx1 = CHARINDEX('-', @PhoneNUmber, 0) SET @Indx2 = CHARINDEX('-', @PhoneNUmber, @Indx1 + 1) IF ( @Indx1 = 4 AND @Indx2 = 8 ) BEGIN RETURN 'US' END RETURN 'UNKNOWN' END GO