score:14
well to start with, i'd find out what query is being generated (in linq to sql you'd set the log on the data context) and then profile it in sql server management studio. play with it there until you've found something that is fast enough (either by changing the query or adding indexes) and if you've had to change the query, work out how to represent that in linq.
i suspect the problem is that you're combining orderby
and take
- which means it potentially needs to find out all the results in order to work out which the top 100 would look like. is code
indexed? if not, try indexing that - it may help by allowing the server to consider records in the order in which they'd be returned, so it can stop after it's found 100 records. you should look at indexes for the other columns too.
score:3
the take(100)
translates to "select top 100" etc. this would help if your problem was an otherwise huge result set, where there are a lot of columns returned. i bet though that your problem is a table scan resulting from the query. in this case, .take(100)
might not help much at all.
so, the likely culprit is the same as if you were doing sql using ado.net: how are your indxes? are the fields being searched fields for which you don't have good indexes? this would cause a drastic decrease in performance compared to queries that do utilize good indexes. add an index that includes code
and name
and see what happens. not using an index for code
is guaranteed to hose you, because of the order by
. also, what field links genealogy_accounts and genealogy_accountclass? a lack of index on either table could hose things. (i would guess an index including searchable is unlikely to help.)
use sql profiler to see the actual query being run (though you can do this in vs too), and to see how bad it really is on the server.
the problem might be linq doing something stupid generating the query, but this is probably not the case. we're finding linq-to-sql often makes better queries than we do. even if it looks goofy, it's usually very efficient. you can put the sql in query analyzer, and check out the query plan. then rewrite the sql to be more human-simple and see if it improve things -- i bet it won't. i think you'll still see a table scan, indicating something is wrong with your index.
Source: stackoverflow.com
Related Query
- Enumerable.Empty<T>().AsQueryable(); This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code
- Does this LINQ code perform multiple lookups on the original data?
- Shorten this LINQ query via the help of an anonymous type?
- LINQ Source Code Available
- How does this linq code that splits a sequence work?
- multiple orderby in this linq code
- How can I combine this code into one or two LINQ queries?
- How can I further simplify this piece of LINQ code
- Is there a bug in this code from 101 LINQ Samples on MSDN? (Update: Fixed)
- C# Linq query help removing foreach loops creating cleaner code
- How to render this map-reduce javascript code to an equivalent LINQ Select-Aggregate?
- LINQ approach to this code
- Linq expression. Help mimize code
- creating Linq to sqlite dbml from DbLinq source code
- How do i convert this linq code to inline sql
- I need help speeding up this EF LINQ query
- Need help turning this LINQ expression into LINQ statement
- why does this linq code get exponentially slower when applying First() to projection?
- How to convert this recursive code to Linq
- Using Linq to build a graph class; can you make this code look better?
- How to write this code using the Linq Extension Method-Syntax?
- Convert this LINQ code back to a Loop (or Why is this object sometimes null)
- How can I refactor this code for LINQ filtering?
- Why this Linq code always throws System.StackOverflowException?
- Need help rewriting this Linq query to move it from code-behind to DAL as reusable object
- My code is very inefficient for this simple Linq usage
- How I change this code to be in linq style
- Why the extension method of where for LINQ in this code would print out a single number while it shouldn't print anything at all?
- Can someone help me improve this linq
- Linq - how to convert this code to linq
More Query from same tag
- Index was out of range when expression converted to linq expression
- Linq query syntax to method syntax when using multiple joins to introduce dynamic restrictions
- Linq XML change parameter
- Logic in retrieving data using dates as filters using linq
- Docusign XML C# LINQ enumeration yielded no results
- Get a several counts using the same state of a list
- Conversion from Hibernate to Entity Framework 6
- how to parse xml , linq c#
- How to select columns with other name in linq?
- Search XML doc with LINQ
- Show null results through the LEFT JOIN with Linq
- Unable to cast object of type 'System.Data.Objects.ObjectQuery`1 [Manager.Data.Channel]' to type 'Manager.Data.Channel'
- LINQ operator to split List of doubles to multiple list of double based on generic delimiter
- Can you reverse order a string in one line with LINQ or a LAMBDA expression
- Three trier Linq query
- Another Linq translation question
- MethodCallExpression is not setting up the orderby correctly
- Using "in" operator in where clause of entity framework
- How to get only objects from list where property x is not null in C#
- Linq - How to turn IQueryable<IEnumerable> into IQueryable
- Multiple left joins in LINQ
- Finding nodes with "missing" attributes
- linq query list of guids
- Populating a dropdown with another
- C# Check null in LINQ query and Transaction Scope giving underlying connection failed to open
- Converting LinqToExcel.RowNoHeader type into string
- Linq Ordering/Grouping by a min value
- LINQ average for pairs
- LINQ query to return whether an item is found in an array?
- How can I achieve that application remains responsive, while threads work in the background?