0

I have a stored procedure with couple parameters. My data table type has 2 columns (int, nvarchar).

When I run this stored procedure and pass IEnumerable<SqlDataRecords> with defined type then query results on my machine is 9 times slower than the same stored procedure without passing this parameter.

The stored procedure doesn't touch this param. Only passing.

It looks like something (sql server?) do with data passed as structured (table-valued) value.

Maybe I am missing something. Maybe there is special switch to:

  • off any kind of validation
  • anything else?

Type:

CREATE TYPE dbo.MyData AS TABLE 
(
    [Ver] INT NOT NULL,
    [Name] NVARCHAR(225) NOT NULL
)

Stored procedure:

CREATE PROCEDURE [dbo].[SaveData]
    (@Id UNIQUEIDENTIFIER, @Data MyData READONLY)
AS
BEGIN
    SET NOCOUNT ON;
END

UPDATE 1:

I've changed query. This stored procedure does nothing. Difference is in passing value or null.

UPDATE 2:

Added stored procedure and type definition.

UPDATE 3:

I'm using SQL Server 2014 Express.

UPDATE 4:

5000 iterations with parameter takes 11281ms (443/sec), without table-valued param - 1029ms (4856/sec).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dariol
  • 1,959
  • 17
  • 26

1 Answers1

0

I would advise running SQL Profiler to examine your queries on the database. You can also look into using SSMS Activity Monitor by right clicking on the database, by clicking on 'activity monitor', and may get lucky and see a 'recent expensive query'.

hth

  • Added Update 2 to question. – dariol Aug 05 '16 at 17:41
  • w/o looking at your database one could assume enumerable things. if you pass an enumerable object into a query you are typically using 'contains' or 'in'. if you are seeing exponential time in your query you can start by looking at indexes, relationships, network latency, etc. –  Aug 05 '16 at 17:46