How to integrate external list to raw sql query join as table in a linq raw sql query

Question

i have the following query running 10-20 times / page in my project. i have tried to run this query with linq to sql, linq to entities but this is far more faster then them.

The question is if i could pass in the external list(sContentIds) into query with a join statement, would it make the query faster then using SQL IN statement? If so how can i achieve this. sContentIds.Count may vary from 1-40 most of the times.

List<filterContentsPCDTO> cContents = unitOfWork.ExecuteQuery<filterContentsPCDTO>(@"SELECT c.ContentId, c.ContentPageId, c.CreatedById, p.PCA, p.PCC, p.PCD, c.AlbumId, a.AlbumTypeId 
                                FROM Contents c
                                INNER JOIN Privatizations p ON c.ContentId = p.ContentId
                                LEFT JOIN Albums a ON c.AlbumId = a.AlbumId
                                WHERE c.ContentId IN (" + string.Join(",", sContentIds) + ")").ToList();

We are working on ASP.NET MVC4 framework and using unit of work pattern for database interactions. Normally i had built this query like follows but it was 5 times slower then raw sql query.

var cContents = unitOfWork.ContentRepository
                .GetFiltered(x => contentIds.Contains(x.ContentId)).Select(x => new filterContentsPCDTO()
                {
                    ContentId = x.ContentId,
                    ContentPageId = x.ContentPageId,
                    CreatedById = x.CreatedById,
                    PCA = x.Privatization.PCA,
                    PCC = x.Privatization.PCC,
                    PCD = x.Privatization.PCD,
                    PrivatizationModifiedById = x.Privatization.ModifiedById,
                    AlbumId = x.AlbumId,
                    albumTypeId = x.AlbumId == null ? -1 : x.Album.AlbumTypeId
                }).ToList();

Implementation of GetFiltered Method

public IEnumerable<T> GetFiltered(
            Expression<Func<T, bool>> filter = null,
            Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
            string includeProperties = "")
        {
            IQueryable<T> query = _dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            foreach (var includeProperty in includeProperties.Split
                (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }

            if (orderBy != null)
            {
                return orderBy(query);
            }
            else
            {
                return query;
            }
        }

No, `x => contentIds.Contains(x.ContentId)` will also be compiled to an SQL `WHERE c.ContentId IN (" + string.Join(",", sContentIds) + ")")`. Why that is five times slower depends on your `GetFiltered()` implementation and the resulting SQL. — CodeCaster, May 13 '14 at 10:58
Yes i know, But using the raw query method can i pass in the sContentIds like a table in database and make a join to it to filter results with inner join? — Azadrum, May 13 '14 at 11:00
I don't think this will improve performance. If the 'ContentId in' query is too slow, look at the query plan and see what it's doing. — Mike Stockdale, May 13 '14 at 15:17

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

If you're using SQL Server 2008 (or newer) and increasing performance is the main objective here (and you're maybe willing to abandon LINQ to SQL for this scenario), I would recommend writing this query as a stored procedure that takes a user-defined table type as a parameter. This will allow you to pass your entire sContentIds collection to the database and still benefit from advantages of a stored procedures over an ad-hoc query.

First, define the table type as something like:

CREATE TYPE [dbo].[ContentList] AS TABLE(
    [ContentId] [int]
)

Then create the procedure as something like:

CREATE PROCEDURE [dbo].[usp_GetContents]
    @contentIds ContentList READONLY
AS

SELECT c.ContentId
       ,c.ContentPageId
       ,c.CreatedById
       ,p.PCA
       ,p.PCC
       ,p.PCD
       ,c.AlbumId
       , a.AlbumTypeId 
FROM Contents c
    INNER JOIN Privatizations p
        ON c.ContentId = p.ContentId
    LEFT JOIN Albums a
        ON c.AlbumId = a.AlbumId
WHERE c.ContentId IN (SELECT ContentId FROM @contentIds)

Then you should be able to call it from C# using the technique described in this answer (basically, create a DataTable from your list then add it like a regular parameter). Unfortunately it looks like this is tough to do with LINQ to SQL, but, as I said, if increasing performance is the main goal, this could be an option.

edited May 23 '17 at 12:06

Community

1
1

answered May 14 '14 at 16:40

chrnola

332
1
9

This is the closest answer i could get i think. And yes the main goal is increasing the performance. Thanks for the detailed answer. A qestion here is, i didnt know create type command, this query will be used much, it wont create a leak of values i sent to sql via user defined table type, right? – Azadrum May 20 '14 at 14:47
@Azadrum Would you mind clarifying what you mean by "a leak of values"? Are you concerned about excessive memory usage on the server? Just to clarify: you only need to execute the `CREATE TYPE` command _once_ and it should not be part of your stored procedure or the command executed from within your application. – chrnola May 20 '14 at 14:53
The values i sent will be removed after the query is done right? I must have been confused to see table datatype. I got that feeling, it stores my parameter list to somewhere into database to use in the query. – Azadrum May 20 '14 at 14:58
@Azadrum That's correct. The user-defined table type parameter in this procedure is significantly different from, say, a temporary table. Table variables are not visible from outside the scope in which they are used and are automatically cleaned up once they fall out of said scope. [This question](http://stackoverflow.com/questions/5653520/how-do-i-drop-table-variables-in-sql-server-should-i-even-do-this) touches on cleaning up table variables. – chrnola May 20 '14 at 15:08
@Azadrum This should help, however, you may also need to look into [tuning the performance](http://technet.microsoft.com/en-us/library/ms176005(v=sql.105).aspx) of the actual query. – chrnola May 20 '14 at 15:24
This is more like a error through the project but surely will help in the most used queries. Thanks for the extra – Azadrum May 20 '14 at 15:27
I started to use table types on my queries instead of contains. – Azadrum Oct 18 '14 at 06:49
@Azadrum - there are additional ways to turn these querys don't think this is the last possible answer. – Hogan Mar 10 '15 at 17:47

How to integrate external list to raw sql query join as table in a linq raw sql query

1 Answers1

Linked