slowness in select

[loic-guenin]

Hello I face huge slowness when using DynamicParameters with a list.

/ The setup / I am using Dapper 2.0.30 framework .Net 4.6.2. My Db is Azure SQL Server As a service from Azure.

The table I request has this structure:

TABLE [MY_TABLE](
    [first_id] [numeric](12, 0) NOT NULL,
    [second_id] [int] NOT NULL,
    [entry_start] [datetime] NOT NULL,
    [entry_end] [datetime] NOT NULL,
    [value] [numeric](20, 10) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [first_id] ASC,
    [second_id] ASC,
    [entry_start] ASC,
    [entry_end] ASC
)

/ The prove that this is not an index problem / When I do the following, there is no problem, it is almost instantaneous:

var request = "select first_id, second_id, entry_start, entry_end, value MY_TABLE where first_id in (2, 3) and entry_start >= @start_date and entry_end <= @end_date";

var param = new DynamicParameters();
param.Add("start_date", startDate);
param.Add("end_date", endDate);
var result = connection.Query<MyTableDto>(request, param);

/ The problem /

But when I want to use a param for first_id, the request remains stucked!!

var request = "select first_id, second_id, entry_start, entry_end, value MY_TABLE where first_id in @ids and entry_start >=  @start_date and entry_end <= @end_date;";
var ids = new List<int> { 2, 3 };
var param = new DynamicParameters();
param.Add("ids", ids);
param.Add("start_date", startDate);
param.Add("end_date", endDate);
var result = connection.Query<MyTableDto>(request, param);

/ Help please :) / Do you have any idea of what causes this slowness?

Regards!

[mgravell]

The first thing I'd try here would be "option unknown", i.e. try:

const string request = @"
select first_id, second_id, entry_start, entry_end, value
from MY_TABLE
where first_id in @ids
and entry_start >=  @start_date and entry_end <= @end_date
option (optimize for (@ids unknown));";

the formatting here is just for readability; the only functional difference is the addition of option (optimize for (@ids unknown)), which tells SQL Server not to make indexing/strategy assumptions about the query based on what the values are the first time the query is run.

Any use?

[mgravell]

Additional and it shouldn't really matter, but you might find it more convenient to do:

var result = connection.Query<MyTableDto>(request, new {
    ids,
    start_date = startDate,
    end_date = endDate
});

This is a completely unrelated topic and shouldn't impact RDBMS performance - just: you might find it more convenient.

[loic-guenin]

Thank you Marc. Unfortunately, still the same behaviour.... Time out when requesting with @ids as DynamicParams :(

[mgravell]

If you code it as regular ADO.NET but with parameters: does it time out there? I'm trying to narrow down on where the problem is occurring

[loic-guenin]

:( My problem is about the array parameter

in @providerId

Is it possible to have such parameter with ADO.NET ?

[mgravell]

No; dapper rewrites this in one of two ways:

As: in (@ids0, @ids1) Or as something involving string_split

For comparison purposes, I'd just try it with the first version.

On Mon, 13 Jan 2020, 09:13 loic-guenin, notifications@github.com wrote:

:( My problem is about the array parameter

in @providerId

Is it possible to have such parameter with ADO.NET ?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/StackExchange/Dapper/issues/1397?email_source=notifications&email_token=AAAEHMGNTDPNL77OCJCCEXDQ5QWCBA5CNFSM4KFHDGH2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIX7R4Q#issuecomment-573569266, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAEHMB35OB4VL6ZKNO2QL3Q5QWCBANCNFSM4KFHDGHQ .

[loic-guenin]

Ok so no timeout with ADO.NET...

[temnava]

Is there any progress about this issue?

[shiyi-lab]

Is there any progress about this issue?

[mgravell]

Without specific reproducible scenarios we can work with, it isn't obvious that there even is a problem, let alone what should be done (at any layer, not necessarily Dapper) to resolve it. If folks want to help work with us on specific concrete examples, great! But ... we can't do much in a knowledge vacuum.