October 21, 2011 at 9:25 am
We have a stored procedure that runs just fine in SSMS (1 second), but when it's run via ODBC, it runs in 37 seconds (and we have 30 second timeout on all transactions, so it times-out).
My first fix was 'sp_recompile [storedprocedurename]' and that fixed it....for awhile. Now it is going back to the 37 second run time.
The difference that we noticed is that when running from SSMS, if we set the ARITHABORT to OFF, everything works ok. I'm assuming we could just set the ARITHABORT to OFF in the stored procedure, but I don't want that to be the fix.
Any suggestions on how to fix this the right way?
October 21, 2011 at 9:30 am
Without seeing at least the procedure and preferably the execution plans too, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2011 at 9:42 am
GilaMonster (10/21/2011)
Without seeing at least the procedure and preferably the execution plans too, no.
I always forget to include the stuff y'all need...
USE [IARTS]
GO
Set ARITHABORT ON
Declare @VehNoList varchar(8000)
Set @VehNoList = 5140014
DECLARE @getdate-2 datetime,
@VESTMENT_TYPE_OWNER int,
@VESTMENT_TYPE_SI int,
@TITLE_STATUS_ACTIVE int,
@SMALL_REGULAR_TRAILER int,
@SMALL_SEMI_TRAILER int
SELECT @getdate-2 = GETDATE()
SELECT @VESTMENT_TYPE_OWNER = dbo.CDV('VestmentType', 'Owner')
SELECT @VESTMENT_TYPE_SI = dbo.CDV('VestmentType', 'SecurityInterest')
SELECT @TITLE_STATUS_ACTIVE = dbo.CDV('TitleStatType', 'Active')
SELECT @SMALL_REGULAR_TRAILER = dbo.CDV('VehType', 'SmallRegularTrailer')
SELECT @SMALL_SEMI_TRAILER = dbo.CDV('VehType', 'SmallSemiTrailer')
--Get a list of vehicles.
CREATE TABLE #VehTemp (Veh_No int, VIN varchar(30), Veh_Type_ID int, YearMakeModel varchar(50))
EXEC('
Declare @Falsebit
Declare @Truebit
SET@False = 0x0
SET@True = 0x1
INSERT INTO #VehTemp (Veh_No, VIN, Veh_Type_ID, YearMakeModel)
SELECT DISTINCTVeh.Veh_No,
Veh.VIN,
Veh.Veh_Type_ID,
Convert(varchar,[Year]) + '' '' + Make.Val + '' '' + Model.Val
FROM Veh
LEFT OUTER JOINMake ON Veh.Make_ID = Make.Make_ID
LEFT OUTER JOINModel ON Veh.Model_ID = Model.Model_ID
INNER JOINVeh_No ON Veh.Veh_No = Veh_No.Veh_No
WHERE Veh.Veh_No in (' + @VehNoList + ')
ANDVeh.Effective_DT <= ''' + @getdate-2 + '''
AND(Veh.End_DT IS NULL OR Veh.End_DT > ''' + @getdate-2 + ''')
ANDDBO.IsValidSpecialProcessingID(Veh_No.SpecialProcessingID, @False) = @True
')
DELETE FROM #VehTemp WHERE Veh_Type_ID = @SMALL_REGULAR_TRAILER
DELETE FROM #VehTemp WHERE Veh_Type_ID = @SMALL_SEMI_TRAILER
-- Get the ownership information for each vehicle
SELECT VO.Veh_Ownsp_ID,
Title.Title_ID,
Title.Title_No,
TV.VIN,
TV.YearMakeModel,
VR.Veh_Reg_ID,
VR.Reg_End_DT,
Veh_Plate.Veh_Plate_ID,
Plate.Plate_No,
TitleSurrendered = 'false',
VO.Veh_No
INTO#VOTemp
FROM#VehTemp TV
INNER JOIN veh_ownsp VOON TV.Veh_No = VO.Veh_No
INNER JOIN TitleON VO.Veh_Ownsp_ID = Title.Veh_Ownsp_ID
INNER JOINTitle_StatusON Title.Title_ID = Title_Status.Title_ID
AND Title_Status.End_DT IS NULL
ANDTitle_Status.Title_Stat_Type_ID = @TITLE_STATUS_ACTIVE
LEFT OUTER JOIN Veh_reg VRON VR.Veh_Reg_ID = dbo.TR_GetLatestVehReg(VO.Veh_Ownsp_ID)
LEFT OUTER JOIN Veh_Reg_Detail VRDON VR.Veh_Reg_ID = VRD.Veh_Reg_ID
AND VRD.Veh_Reg_Detail_ID = (SELECT MAX(Veh_Reg_Detail_ID) FROM Veh_Reg_Detail WHERE Veh_Reg_ID = VR.Veh_Reg_ID and Penalty = 0 and Current_Owner = 1)
LEFT OUTER JOIN Veh_PlateON VRD.Veh_Plate_ID = Veh_Plate.Veh_Plate_ID
LEFT OUTER JOIN PlateON Veh_Plate.Plate_ID = Plate.Plate_ID
SELECT * FROM#VOTemp
-- Get the vested parties for each ownership
SELECT VP.Vested_Party_ID,
VP.Veh_Ownsp_ID,
VP.Vestment_Type_ID,
VT.Val [Vestment_Type_Val],
VP.Priority,
dbo.GetCustomerLegalName(Cust.Cust_No, @getdate-2) LegalName,
ISNULL(CA.Addr_Line1 + ' ', '') + ISNULL(CA.Addr_Line2 + ' ', '') +
ISNULL(CA.City + ' ', '') + ISNULL(CA.State_Code + ' ', '') + ISNULL(CA.Zip, '') as ResidencyAddress,
CIT.Val AS [Cust_Ident_Type],
CI.Ident_No AS [Cust_Ident_No],
CASE WHEN VP.Vestment_Type_ID = @VESTMENT_TYPE_SI THEN
Veh_SI.SI_Add_DT
ELSE
Cust.Date_Of_Birth
END [DOBSIDate]
FROM #VOTemp VO
INNER JOIN Vested_Party VP ON VO.Veh_Ownsp_ID = VP.Veh_Ownsp_ID
INNER JOIN Cust on VP.Cust_No = Cust.Cust_No AND Cust.Effective_DT <= @getdate-2 AND (Cust.End_DT IS NULL OR Cust.End_DT > @getdate-2)
INNER JOIN Vestment_Type VT ON VP.Vestment_Type_ID = VT.Vestment_Type_ID
LEFT OUTER JOIN Veh_SI on VP.Vested_Party_ID = Veh_SI.Vested_Party_ID
LEFT OUTER JOIN Cust_Addr CA on Cust.Cust_No = CA.Cust_No
LEFT OUTER JOIN Cust_Ident CI ON Cust.Cust_No = CI.Cust_No
LEFT OUTER JOIN Cust_Ident_Type CIT ON CIT.Cust_Ident_Type_ID = CI.Cust_Ident_Type_ID AND CIT.Effective_DT <= @getdate-2 AND (CIT.End_DT IS NULL OR CIT.End_DT > @getdate-2)
WHERE VP.Vestment_Type_ID IN (@VESTMENT_TYPE_OWNER, @VESTMENT_TYPE_SI)
ANDVP.Effective_DT <= @getdate-2
AND(VP.End_DT IS NULL OR VP.End_DT > @getdate-2)
ANDCI.Cust_Ident_ID = DBO.GetCustomerHighestIdentID(Cust.Cust_No, @getdate-2)
ANDCA.Cust_Addr_ID = DBO.GetCustomerResidencyAddrID(Cust.Cust_No, @getdate-2)
ORDER BY VP.Vestment_Type_ID DESC, VP.Priority
-- Get the lookups
SELECT Title_Stat_Type_IDAS [ID],
VALAS [VAL],
[DESC]AS [DESC]
FROM Title_Stat_Type
WHERE EFFECTIVE_DT < @getdate-2
AND (END_DT IS NULL OR END_DT < @getdate-2)
AND Title_Stat_Type_ID <> @TITLE_STATUS_ACTIVE
ORDER BY VAL
exec LUGET '', 'v_Plat_Disp_typ'
SELECT SI_Release_Rea_IDAS [ID],
VALAS [VAL]
FROM SI_Release_Rea
WHERE EFFECTIVE_DT < @getdate-2
AND (END_DT IS NULL OR END_DT < @getdate-2)
ORDER BY VAL
DROP TABLE #VOTemp
DROP TABLE #VehTemp
October 21, 2011 at 9:50 am
Those 2 plans are, as far as I can tell, absolutely identical. Did they perform radically differently?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2011 at 9:53 am
GilaMonster (10/21/2011)
Those 2 plans are, as far as I can tell, absolutely identical. Did they perform radically differently?
No, for the 2 executions I did, one OFF and one ON, they were very close to the same execution time (roughly 1 second). Let me try and reproduce and paste the updated execution plan (for when it runs in 37 seconds).
October 21, 2011 at 10:01 am
Here we go. I was only able to reproduce when I do "exec [procname]"...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply