More effecient SQL for retreiving thousands of records on a vew

Question

I am using Linq to Sql as my ORM and I have a list of Ids (up to a few thousand) passed into my retriever method, and with that list I want to grab all User records that correspond to those unique Ids. To clarify, imagine I have something like this:

List<IUser> GetUsersForListOfIds(List<int> ids)
{
        using (var db = new UserDataContext(_connectionString))
        {
            var results = (from user in db.UserDtos
                           where ids.Contains(user.Id)
                           select user);

            return results.Cast<IUser>().ToList();
        }
}

Essentially that gets translated into sql as

select * from dbo.Users where userId in ([comma delimmited list of Ids])

I'm looking for a more efficient way of doing this. The problem is the in clause in sql seems to take too long (over 30 seconds).

Any thoughts?

Answer 1

Will need more information on your database setup like index's and type of server (Mitch Wheat's post). Type of database would help as well, some databases handle in clauses poorly.

From a trouble shooting standpoint...have you isolated the time delay to the sql server? Can you run the query directly on your server and confirm it's the query taking the extra time?

Select * can also have a bit of a performance impact...could you narrow down the result set that's being returned to just the columns you require?

edit: just saw the 'view comment' that you added...I've had problems with view performance in the past. Is it a materialized view...or could you make it into one? Recreating the view logic as a stored procedure may aslo help.

Answer 2

Have you tried converting this to a list, so the application is doing this in-memory? i.e.:

List<IUser> GetUsersForListOfIds(List<int> ids)
{
        using (var db = new UserDataContext(_connectionString))
        {
            var results = (from user in db.UserDtos.ToList()
                           where ids.Contains(user.Id)
                           select user);

            return results.Cast<IUser>().ToList();
        }
}

This will obviously be memory-intensive if this is being run on a public-facing page on a hard-hit site. If this still takes 30+ seconds though in staging/development, then my guess is that the View itself takes that long to process -OR- you're transferring 10's of MB of data each time you retrieve the view. Either way, my only suggestions are to access the table directly and only retrieve the data you need, rewrite the view, or create a new view for this particular scenario.