4

I'm running > 50K simple selects on a table in sql server and I'd really like to speed it up. I've read that you can pull the data into memory and use something like a dataview to query against.

Is there a way to use something in Linq to end up with - 

var dv = CreateTheDataViewFromDatabase();

var result = dv.Where("CategoryId=2 And Uncle='bob'");

And would this be a lot faster than querying the database so many times? I'm concerned there's no index on the dataview so the gains of having it in memory might be lost.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user1266921
  • 225
  • 3
  • 14
  • 3
    If it were a small enough table, it seeems sql server would have pulled it into memory already...so you only have the communication issue. – Mark Schultheiss Jan 14 '13 at 23:12
  • What happens when someone adds / changes / deletes a record _after_ you have loaded it over at the client? you won't see the change. How do you decide that client records are stale, and you need to refresh from the server? What about in x years with 20 users running your app now looking at 100K records and you are loading all that data cross the network to the client? I suggest you do some analysis on the performance issue. Maybe you are selecting unneeded columns, or maybe the issue is rendering the data on the client side and has nothing to do with database or network. – Nick.Mc Jan 15 '13 at 06:25

4 Answers4

3

I expect the fastest way to do this will be to generate a single query that returns results, in order, for the entire set of "50K simple selects", such that you can run the whole thing as a single query and iterate over the results sequentially.

The reason for this is that when you pull the table down to memory, you lose any index information that might have been available in sql server to help query the results. Of course, the penalty is that your query is returning 50K queries worth of data, but if you do it right vis a vis a datareader vs a data table, you should be able to avoid keeping unnecessary information in memory on the client.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • I would definitely bring the entire result set into memory all at once if you're using LINQ. I have a command line app that does something similar, and runs various reports, builds excel files, xml files, etc, all on a fairly wide result set from a LINQ query that often runs 30-40k rows, and performance is quite reasonable. – Kevin Dahl Jan 15 '13 at 02:59
  • Thanks @Kevin, for your processing are you taking minutes or hours to process. I'm taking hours at the moment hence the need to improve – user1266921 Jan 15 '13 at 06:48
  • 1
    Our processing ranges from 4-5 minutes, and most of that is spent rendering PDF/XLS reports (up to 600 at times) off of the dataset. Pulling the data into memory is likely around a minute of total execution time. – Kevin Dahl Jan 15 '13 at 14:55
1

Yes, you can use Linq against your DataTable by using AsEnumerable. For example:

var rows = from row in dt.AsEnumerable()
            where row.Field<int>("CategoryId") == 2 && row.Field<string>("Uncle") == "bob" 
            select row;

Make sure you add a reference to System.Data.DataSetExtensions

Update: Dynamic SQL

It is possible to build your conditions on the fly. Have a look at Dynamic LINQ and Predicate Builder

Ulises
  • 13,229
  • 5
  • 34
  • 50
  • Thanks @Ulises I need to build the query dynamically though, is it possible to chain the linq where clauses together on the fly? – user1266921 Jan 15 '13 at 06:50
1

It sounds like your program is spending a large amount of time communicating with the database. You can speed up the process substantially if you query the database once and save the results in your computer's memory. If you're using an ADO.NET Entity Data Model then you can easily query your database and save the records you retrieve in an IEnumerable. Then you can perform queries against the saved values using LINQ. 

//Get the data from your database 
public IEnumerable<DataValue> GetDataValues()
{
    try
    {
        using (var db = new MyEntities())
        {
            return (from data in db.DataValues
                   select data).ToList(); 
        }
    }
    catch(Exception e)
    {
        Console.WriteLine(e); 
    }
}


//Perform operations on the data
public void DoSomething()
{
    var data = GetDataValues(); 
    var result = data.Where(p => p.CategoryId == 2 && p.Uncle == "Bob"); 
    //etc...
}

You can find some decent examples on using LINQ here and here.

jake
  • 1,027
  • 8
  • 9
  • How would you then deal with synchronization issues? Ie, im working in my memory, but the database has been changed, now the two are out of sync? – Zapnologica Nov 13 '14 at 07:48
  • That's what the EntityFramework is for. http://msdn.microsoft.com/en-us/data/ef.aspx – jake Nov 14 '14 at 02:56
  • Entity Framework does not keep your table in memory though? if you query EF every time, you will loose out on performance. – Zapnologica Nov 14 '14 at 05:38
  • The EntityFramework's ObjectStateManager tracks the changes to entities. You can also manually mark entities as modified and submit them back to your database. http://msdn.microsoft.com/en-us/library/system.data.objects.objectstatemanager(v=vs.110).aspx – jake Nov 16 '14 at 02:45
0

It's possible as shown in the other answers, but there are some things to consider:

First of all, what is the total amount of the data that needs to be pulled in. If that amount will need so much memory that virtual memory kicks in big time, you lose all the benefits of speed by the amount of time the OS needs on paging.

Second, 50k+ of queries is a lot. Consider a design-change/refactoring to reduce the amount of queries. The benefits of a different algorithm can out-perform your suggested solution.

lboshuizen
  • 2,746
  • 17
  • 20
  • @Iboshuizen yea, I thought long and hard about changing whats happening but every one of the 50k needs to run a select, they are all unique so Im stuffed in that regard. – user1266921 Jan 15 '13 at 06:53