0

I have a table with more than 2 million rows, with needed indexes.

When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, it runs very quickly (<100 ms)!

Also sp_executesql for the same query acts like stored procedure and runs very fast.

I just want to no why the plain query is slow? Is this normal?

I've indexed my table, so I do not think this is because of my lack of indexes

This is plain query:

SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = 40
                        AND ArrivalDateTime >= '2012-10-12'
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC

This is procedure:

CREATE PROC TestProc
(
  @datetime DATETIME2(0) ,
  @deviceId INT
)
AS 
BEGIN
        DECLARE @dataTimeLocal DATETIME2(2);
        SET @deviceIdLocal = @datetime

    DECLARE @did INT= @deviceId;
    SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = @deviceIdLocal
                        AND ArrivalDateTime >= @dataTimeLocal
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC
END

and Logs table:

CREATE TABLE [dbo].[Logs](
[Id] [int] IDENTITY(1001,1) NOT NULL,
[DeviceId] [int] NOT NULL,
[TypeId] [tinyint] NOT NULL,
...
other non related fields
...
[YearFa] [smallint] NULL,
[MonthFa] [tinyint] NULL,
[DayFa] [tinyint] NULL,
[Year] [smallint] NULL,
[Month] [tinyint] NULL,
[Day] [tinyint] NULL,
[Hour] [tinyint] NULL,
[DeviceLogNumber] [int] NULL,

CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
(
    [Id] 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 ON
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_DeviceId]  DEFAULT ((1)) FOR [DeviceId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_Logs_TypeId]  DEFAULT ((1)) FOR [TypeId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_ArrivalDateTime]  DEFAULT (getdate()) FOR [ArrivalDateTime]
GO

ALTER TABLE [dbo].[Logs]  WITH NOCHECK ADD  CONSTRAINT [FK_Logs_Devices] FOREIGN KEY([DeviceId])
REFERENCES [dbo].[Devices] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Update1:

For some dates like '2010/10/10' in where clause query runs immediately but for some dates like '2012/01/10' it takes so long (19 secs)!!

This is execution plan of the query: enter image description here

2 Answers 2

1

Could you check your execution plans for the procedure/query? The execution plan of a procedure is also compiled and stored in the library section, while most of the times, the one for a query is computed ad-hoc (not always though). But still, the generation of a execution plan, surely doesn't take that long. So maybe, because of expired statistics (for instance), the plan of the query is not optimal, while the one stored in the procedure it is.

In other words, a good starting point are the plans. If you wish, you could post them here. Then you could check the resource profiles under each operation it is executed, to make sure that both of them are able to use the same amount of resources.

1
  • i've updated questions.
    – r.zarei
    Mar 4, 2014 at 13:41
0

If a SP is faster for some parameter values and very slow for others you should take a look at "parameter sniffing". It means that the plan for the first call is stored and the second (with another parameter) uses the same plan which might be inefficient. Examine the query plan and look for the parameter list inside the xml representation. If the ParameterCompiledValue is another than your ParameterRuntimeValue you're using an old plan for another parameter value.

2
  • He won't experience sniffing since he is reassigning input params to variables at the top of the SP.
    – JNK
    Mar 6, 2014 at 15:15
  • oh, the sp seems to have a copy&paste error (variables are mixed up).
    – Jens W.
    Mar 6, 2014 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.