All geek questions in one place

Executing the same code with exec SP compared to exec SP in the query window reports the same results as another run time

Problem

We are trying to understand why the execution of the same code by calling the stored procedure and executing the contents of the stored procedure in the query window shows very different execution times, but returns exactly the same set of results from 183 lines.

Test1

Running the next SP from SSMS takes 5 minutes to return the results.

EXEC uspFleetSummaryReportSelectByDateCommand @UserID = 1468, @Date = '2015-09-28'

For reference, this is the detailed information about SP:

    CREATE PROCEDURE [dbo].[uspFleetSummaryReportSelectByDateCommand]
    (
      @UserID int,
      @Date DateTime
      )
    AS

    DECLARE @CustomerID int
    SET @CustomerID = (Select CustomerID FROM [User] WHERE UserID = @UserID)

    SELECT  j.JourneyID,
            j.DeviceID,
            j.StartDate,
        j.EndDate,
        ISNULL(JourneyDistance, 0.0) AS [JourneyDistance],
        CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, j.StartDate, j.EndDate), 0), 114) AS [Duration],
        v.Registration,
        v.InitOdometer,
        jt.Name AS [JourneyType],
        dt.Name AS [DeviceType],
        PrivateJourney = (dbo.fxIsPrivateJourney(j.JourneyTypeID, j.DeviceID, @UserID)),
        CONVERT(VARCHAR(8), DATEADD(SECOND, ISNULL(e.IdleTime, 0), 0), 114) AS [IdleTime]
FROM Journey j WITH (NOLOCK) 
INNER JOIN Vehicle v WITH (NOLOCK) ON v.DeviceID = j.DeviceID
INNER JOIN JourneyType jt WITH (NOLOCK) ON jt.JourneyTypeID = j.JourneyTypeID
INNER JOIN Device d WITH (NOLOCK) ON d.DeviceID = j.DeviceID
    INNER JOIN Configuration config WITH (NOLOCK) ON config.ConfigurationID = d.ConfigurationID
INNER JOIN DeviceType dt WITH (NOLOCK) ON dt.DeviceTypeID = config.DeviceTypeID
LEFT OUTER JOIN (
    SELECT 
    e.JourneyId,
    SUM(DATEDIFF(SECOND, e.StartDateTime, e.EndDateTime)) AS [IdleTime]
    FROM [Event] e WITH (NOLOCK)
    WHERE e.JourneyId = JourneyID AND e.EventType = 4/*Idle Event*/
    GROUP BY e.JourneyId
) e ON e.JourneyId = j.JourneyID
WHERE   j.StartDate BETWEEN @Date AND DATEADD(DAY,1,@Date)
        AND (j.JourneyDistance IS NOT NULL)
        AND DATEDIFF(MINUTE,j.StartDate,ISNULL(j.EndDate,getdate())) > 0
        AND j.DeviceID IN (Select v.DeviceID 
                                    FROM Vehicle v WITH (NOLOCK)
                                INNER JOIN Customer c WITH (NOLOCK) ON         c.CustomerID = v.CustomerID
                                INNER JOIN [User] u ON u.CustomerID = c.CustomerID
                                WHERE   v.CustomerID = @CustomerID AND u.UserID = @UserID
                                        AND (v.LevelOneID = u.LevelOneID Or u.LevelOneID is null) 
                                        AND (v.LevelTwoID = u.LevelTwoID Or u.LevelTwoID is null) 
                                        AND (v.LevelThreeID = u.LevelThreeID Or u.LevelThreeID is null) 
                                        AND (v.LevelFourID = u.LevelFourID Or u.LevelFourID is null) 
                                        AND (v.LevelFiveID = u.LevelFiveID Or u.LevelFiveID is null) 
                                        AND (v.DriverID = u.LevelSixID Or u.LevelSixID is null)
                                        AND ISNULL(v.HideFromCustomer,0) != 1
                             )
ORDER BY Registration,j.JourneyID

Test2

But executing the same SP code and setting the variables takes 10 seconds to return the results.

SP . SSMS script.

DECLARE @UserID INT = 13651
DECLARE @Date DATETIME = '2015-09-28'

DECLARE @CustomerID int
SET @CustomerID = (Select CustomerID FROM [User] WHERE UserID = @UserID)

SELECT    j.JourneyID,
        j.DeviceID,
        j.StartDate,
        j.EndDate,
       ISNULL(JourneyDistance, 0.0) AS [JourneyDistance],
       CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, j.StartDate, j.EndDate), 0), 114) AS [Duration],
       v.Registration,
       v.InitOdometer,
       jt.Name AS [JourneyType],
       dt.Name AS [DeviceType],
        PrivateJourney = (dbo.fxIsPrivateJourney(j.JourneyTypeID, j.DeviceID, @UserID)),
        CONVERT(VARCHAR(8), DATEADD(SECOND, ISNULL(e.IdleTime, 0), 0), 114) AS [IdleTime]
FROM Journey j WITH (NOLOCK) 
INNER JOIN Vehicle v WITH (NOLOCK) ON v.DeviceID = j.DeviceID
INNER JOIN JourneyType jt WITH (NOLOCK) ON jt.JourneyTypeID = j.JourneyTypeID
INNER JOIN Device d WITH (NOLOCK) ON d.DeviceID = j.DeviceID
INNER JOIN Configuration config WITH (NOLOCK) ON config.ConfigurationID =     d.ConfigurationID
INNER JOIN DeviceType dt WITH (NOLOCK) ON dt.DeviceTypeID = config.DeviceTypeID
LEFT OUTER JOIN (
    SELECT 
    e.JourneyId,
    SUM(DATEDIFF(SECOND, e.StartDateTime, e.EndDateTime)) AS [IdleTime]
    FROM [Event] e WITH (NOLOCK)
    WHERE e.JourneyId = JourneyID AND e.EventType = 4/*Idle Event*/
    GROUP BY e.JourneyId
) e ON e.JourneyId = j.JourneyID
    WHERE    j.StartDate BETWEEN @Date AND DATEADD(DAY,1,@Date)
       AND (j.JourneyDistance IS NOT NULL)
       AND DATEDIFF(MINUTE,j.StartDate,ISNULL(j.EndDate,getdate())) > 0
       AND j.DeviceID IN (Select v.DeviceID 
                                    FROM Vehicle v WITH (NOLOCK)
                                   INNER JOIN Customer c WITH (NOLOCK) ON     c.CustomerID = v.CustomerID
                                    INNER JOIN [User] u ON u.CustomerID =     c.CustomerID
                                   WHERE    v.CustomerID = @CustomerID AND u.UserID = @UserID
                                            AND (v.LevelOneID = u.LevelOneID Or u.LevelOneID is null) 
                                           AND (v.LevelTwoID = u.LevelTwoID Or u.LevelTwoID is null) 
                                           AND (v.LevelThreeID =     u.LevelThreeID Or u.LevelThreeID is null) 
                                           AND (v.LevelFourID =     u.LevelFourID Or u.LevelFourID is null) 
                                           AND (v.LevelFiveID =     u.LevelFiveID Or u.LevelFiveID is null) 
                                           AND (v.DriverID = u.LevelSixID Or u.LevelSixID is null)
                                            AND ISNULL(v.HideFromCustomer,0) != 1
                            )
ORDER BY Registration,j.JourneyID

, .

, .

CUSTOMERID .

.

, , .

. SP , EVENT TEST1.

   CREATE NONCLUSTERED INDEX [290915_EventTypeJourneyID, EventTypeJID,>] ON     [dbo].[Event] 
(
    [EventType] ASC,
    [JourneyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB =     OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  =     ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

TEST1 1 .

, , , , ? , , .

.

Sql server 2008 64- .

.JOURNEY( 350 )

CREATE TABLE [dbo].[Journey](
    [JourneyID] [int] IDENTITY(1,1) NOT NULL,
    [StartAddress] [varchar](500) NULL,
    [StartPostcode] [varchar](50) NULL,
    [EndAddress] [varchar](500) NULL,
    [EndPostcode] [varchar](50) NULL,
    [JourneyTypeID] [int] NULL,
    [Comment] [varchar](500) NULL,
    [DriverID] [int] NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [IdleTimeEngineOn] [int] NULL,
    [TimeSinceLastJourney] [int] NULL,
    [JourneyDistance] [decimal](8, 2) NULL,
    [DeviceID] [int] NOT NULL,
    [tempJourneyID] [int] NULL,
    [tempCustomerID] [int] NULL,
 CONSTRAINT [Journey_PK] PRIMARY KEY CLUSTERED 
(
    [JourneyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Journey]  WITH CHECK ADD  CONSTRAINT [Device_Journey_FK1]     FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Device] ([DeviceID])
GO

ALTER TABLE [dbo].[Journey] CHECK CONSTRAINT [Device_Journey_FK1]
GO

ALTER TABLE [dbo].[Journey]  WITH CHECK ADD  CONSTRAINT [Driver_Journey_FK1]     FOREIGN KEY([DriverID])
REFERENCES [dbo].[Driver] ([DriverID])
GO

ALTER TABLE [dbo].[Journey] CHECK CONSTRAINT [Driver_Journey_FK1]
GO

ALTER TABLE [dbo].[Journey]  WITH NOCHECK ADD  CONSTRAINT [JourneyType_Journey_FK1] FOREIGN KEY([JourneyTypeID])
REFERENCES [dbo].[JourneyType] ([JourneyTypeID])
GO

ALTER TABLE [dbo].[Journey] CHECK CONSTRAINT [JourneyType_Journey_FK1]
GO

.EVENT( 36 )

CREATE TABLE [dbo].[Event](
    [EventID] [int] IDENTITY(1,1) NOT NULL,
    [StartDateTime] [datetime] NULL,
    [EndDateTime] [datetime] NULL,
    [StartLocationID] [int] NOT NULL,
    [EndLocationID] [int] NULL,
    [AlertRaised] [bit] NULL,
    [EventRuleID] [int] NULL,
    [DeviceID] [int] NOT NULL,
    [EventMessage] [varchar](max) NULL,
    [TopSpeed] [decimal](4, 1) NULL,
    [SpeedZone] [int] NULL,
    [EventType] [int] NULL,
    [ImpactId] [int] NULL,
    [NotificationStatus] [bit] NULL,
    [CableBreakZone0] [int] NULL,
    [CableBreakDistance0] [int] NULL,
    [CableBreakZone1] [int] NULL,
    [CableBreakDistance1] [int] NULL,
    [AdValue] [int] NULL,
    [DriverId] [int] NULL,
    [VehicleId] [int] NULL,
    [JourneyId] [int] NULL,
 CONSTRAINT [Event_PK] PRIMARY KEY CLUSTERED 
(
    [EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =     OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [Device_Event_FK1]     FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Device] ([DeviceID])
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [Device_Event_FK1]
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [Event_Impact_FK]     FOREIGN KEY([ImpactId])
REFERENCES [dbo].[Impact] ([ImpactID])
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [Event_Impact_FK]
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [EventRule_Event_FK1]     FOREIGN KEY([EventRuleID])
REFERENCES [dbo].[EventRule] ([EventRuleID])
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [EventRule_Event_FK1]
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [FK_Event_Driver]     FOREIGN KEY([DriverId])
REFERENCES [dbo].[Driver] ([DriverID])
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [FK_Event_Driver]
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [FK_Event_Journey]     FOREIGN KEY([JourneyId])
REFERENCES [dbo].[Journey] ([JourneyID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [FK_Event_Journey]
GO

ALTER TABLE [dbo].[Event]  WITH CHECK ADD  CONSTRAINT [FK_Event_Vehicle]     FOREIGN KEY([VehicleId])
REFERENCES [dbo].[Vehicle] ([VehicleID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Event] CHECK CONSTRAINT [FK_Event_Vehicle]
GO
+4
3

SQL Server , SQL Server, .

  • () . , .

    • , , , .

    • , , , (, = @variable @variable NULL- ) - , sniffing.

    • , , .

  • , - , , , , , .

    • , , .

  • , , 1, , . , , - .

, , , SSMS? .

: , , , IO. , - , ( , ..)

+2

SSMS , , :

  • SP SET ANSI_NULLS ON.

  • SP :

    DECLARE @pUserID INT, @pDate DATETIME;
SELECT @pUserID = @UserID, @pDate = @Date;

SELECT ... @pUserID ...;
+1

Had the same problem on sql 2008 server Running this once fixed it for me:

USE master
ALTER DATABASE [dbname] SET ARITHABORT ON WITH NO_WAIT;

ssms always sets ARITHABORT to, clients written in dotnet do not, so both use a different plan with different statistics.

0
source

Source: https://habr.com/ru/post/1609438/

More articles:


All Articles