6
\$\begingroup\$

The following code achieves what I require, reading 10000 rows at a time:

var query = repository.Subscribers.ToList().Skip(numberToSkip).Take(numberInBatch);

Is there an alternative which will make this faster?

Here's the full code:

protected static string ContactUrl = BaseUrl + "/api/contact";  


protected static int NumberInBatch = 10000;
protected static int NumberToSkip = 10000;
protected static int NumberToCountUpto = 100000;

public static string GetSubscribers(int numberToSkip, int numberInBatch)
    {
        using (var repository = new SubscriberEntities())
        {
            var index = 0;
            string output = null;
            var query = repository.Subscribers.ToList().Skip(numberToSkip).Take(numberInBatch);

            foreach (var subscriber in query)
            {
                var telephone = ContactHelper.GetTelephoneFromEmail(subscriber.EmailAddress);


                string requestCreateContact = RequestHelper.CreateContact(subscriber.EmailAddress, telephone);
                if (index > 0) output = output + ",";
                output = output + (requestCreateContact);

            }
            return output;
        }
    }

    //Batch Create
    public static void SendBatchCreate()
    {

        for (int i = NumberToSkip; i < NumberToCountUpto; i += NumberInBatch)
        {
            var requestCreateMultipleContacts = GetSubscribersAndCreateContacts(i, NumberInBatch);

            RequestHelper.SubmitPostRequest(requestCreateMultipleContacts, ContactUrl);
        }
        Console.ReadKey();
    }
\$\endgroup\$
  • \$\begingroup\$ A simple way to speed up the Skip().Take() LINQ expression is to use GetRange() instead. I learnt that the hard way! \$\endgroup\$ – Simon Apr 12 '14 at 3:15
16
\$\begingroup\$

I would first suggest logging the database operation to see what SQL is executed. EF may not be running the SQL you think it is.

The red flag I see is that you call ToList before the Skip and Take. Generally in LINQ statements, ToList forces execution immediately.

This suggests your EF code is pulling every record from the table up front, building a list, and then applying the Skip/Take to the list. If that's the case, the logged SQL should show a select statement without any filtering.

I suspect what you really want to do is run the ToList after the Skip and Take. This should apply the filtering inside the executed SQL, but the logging will show you for sure.

\$\endgroup\$
  • 2
    \$\begingroup\$ Calling ToList will definitely materialize the query, and the rest of the chained LINQ calls operate with Linq-to-Objects in memory, on the client. A profiler will show something like SELECT [all fields] FROM Subscribers, embedded in an sp_executesql call. \$\endgroup\$ – Mathieu Guindon Apr 11 '14 at 23:01
4
\$\begingroup\$
var query = repository.Subscribers.ToList().Skip(numberToSkip).Take(numberInBatch);

foreach (var subscriber in query)
{
    var telephone = ContactHelper.GetTelephoneFromEmail(subscriber.EmailAddress);


    string requestCreateContact = RequestHelper.CreateContact(subscriber.EmailAddress, telephone);
    if (index > 0) output = output + ",";
    output = output + (requestCreateContact);

}
return output;

The name query isn't appropriate here, because you've materialized the actual query with ToList and filtered it with Linq-to-Objects.

This would do it:

var query = repository.Subscribers.Skip(numberToSkip).Take(numberInBatch);

Now the query hasn't executed yet, because you haven't iterated it. That's how Linq-to-Entities works (Linq-to-SQL works the same way), the materialization of the query is deferred to the very last possible moment, which enables you to return the IQueryable<T> and keep adding to it (say, with a Where clause), all the while without hitting the database server.

You don't need to call ToList: iterating it is enough.


Another performance-related issue you have here, is that you're concatenating the output string in a loop. This is bad, because a string is an immutable object in .NET, so you're creating many, many, many objects here, without even noticing.

The solution is to use a StringBuilder instead of straight concatenations. This code should run much faster with 10K rows:

var query = repository.Subscribers.Skip(numberToSkip).Take(numberInBatch);
var builder = new StringBuilder();

foreach (var subscriber in query)
{
    var telephone = ContactHelper.GetTelephoneFromEmail(subscriber.EmailAddress);
    var contact = RequestHelper.CreateContact(subscriber.EmailAddress, telephone);

    if (index > 0) // is this condition ever true?
    {
        builder.Append(",");
    }

    builder.Append(contact);
}

return builder.ToString();
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.