More effective "WHERE IN" #51

Open
MichaelLogutov opened this Issue Jun 18, 2011 · 4 comments

3 participants

@MichaelLogutov

It's very common for SQL queries to have a "where in (@0)" conditions. PetaPoco works fine with it but there are some problems.

var ids = new[] {};
return db.Query<MyEntity> (Sql.Builder.Where ("Id in (@0)", ids));

This will generate invalid SQL. But I understand that this is not a trivial task to solve (because it could be a very complex expressions with multiple variables passed to Where method and get rid of the part that relly on passed empty sequence parameter is require parsing the whole SQL). Another problem is that PetaPoco generates multiple input parameters for each of the item in the enumeration. So if I have like 1000 ids it's gonna be hell of a SQL with 1000 parameters. Since the most common "where in" clause is filtering by numerical enumerations I have a suggestion to include some additional WhereIn methods that will take care of big enumeration parameters and also properly work with empty sequence problem:

/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <typeparam name="T">Type of the id.</typeparam>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn<T> (this Sql sqlObj, string columnName, IEnumerable<T> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (@0)", ids);
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn (this Sql sqlObj, string columnName, IEnumerable<int> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn (this Sql sqlObj, string columnName, IEnumerable<long> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}

/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <typeparam name="T">Type of the id.</typeparam>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn<T> (this Sql sqlObj, string columnName, IEnumerable<T> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (@0)", ids);
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn (this Sql sqlObj, string columnName, IEnumerable<int> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn (this Sql sqlObj, string columnName, IEnumerable<long> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}
@DaveRobbins

You may want to consider solving this on the SQL side with the following function:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

From SQL you use it in this fashion:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

Basically you could then just provide a comma delimited string and let SQL do the work.

@MichaelLogutov

It's not effective at all. Using parsing methods only necessary if you want to preserve order of the ids and return values.
PS: oh, and CLR is actually a better choice over your method - there is a good article about it somewhere for MSSQL 2005

@DaveRobbins

From the stand point of creating a SQL statement that is passed to server I see your point - a SQL statement is being created anyway so you are not gaining anything.

However we've been using this in production for 4 years and it works very well on the SQL Server side. If you can find that article on the CLR please post the link. I would be interested in reading it.

@MichaelLogutov

http://www.sommarskog.se/arrays-in-sql.html

On our production websites we were using "temp table/generated inserts" methods and found it to be more CPU consuming compared to "CLR-parse table valued" function on MSSQL 2005 with latest SPs.

@pleb pleb changed the title from [Suggestion] More effective "WHERE IN" to More effective "WHERE IN" Dec 5, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment