Please help me understand these inefficient sql queries
Joined: 12-Dec-2005
Joined: 01-Jul-2005
Joined: 12-Dec-2005
Joined: 08-Oct-2008
Joined: 12-Dec-2005
Thanks for the follow up. As I understand relations... I am able to basically construct filters on related entities. However, I actually want to retrieve those related entities as well.
For example, I am doing this:
var customers = new EntityCollection<CustomerEntity>();
RelationPredicateBucket contactFilter = new RelationPredicateBucket();
contactFilter.Relations.Add(CustomerEntity.Relations.IndividualEntityUsingCustomerId);
contactFilter.Relations.Add(IndividualEntity.Relations.ContactEntityUsingContactId);
using (var adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, contactFilter, 100);
}
This successfully gives me the top 100 customers that have a related record in the Individual and Contact tables. However, I also want to retrieve the Contact entity so that I can get at the name. Can't I just get the top 100 customers and their names without some grossly inefficient query?
Thanks again, Matt
EDIT: I'm not sure if the forum ate your previous post, or if you deleted it. So, my response doesn't really make sense anymore 
Nevertheless, I did try adding an "order by" using a SortExpression object. Still no dice. Perhaps this whole thing is just a philosophical issue, but it seems a like it would be a common problem. Is there no way to get llblgen to use joins versus multiple queries?
MattHornsby wrote:
Thanks for the follow up. As I understand relations... I am able to basically construct filters on related entities. However, I actually want to retrieve those related entities as well.
Please see the fetch of a projected set and entity fetches as two different things: entity fetches have the notion of fetching a graph, as entities can have related entities. A projected set is always flat and doesn't have 'related data'. So if you want to fetch a projection of an entity set, there's no 'related data'.
For example, I am doing this:
var customers = new EntityCollection<CustomerEntity>(); RelationPredicateBucket contactFilter = new RelationPredicateBucket(); contactFilter.Relations.Add(CustomerEntity.Relations.IndividualEntityUsingCustomerId); contactFilter.Relations.Add(IndividualEntity.Relations.ContactEntityUsingContactId); using (var adapter = new DataAccessAdapter()) { adapter.FetchEntityCollection(customers, contactFilter, 100); }This successfully gives me the top 100 customers that have a related record in the Individual and Contact tables. However, I also want to retrieve the Contact entity so that I can get at the name. Can't I just get the top 100 customers and their names without some grossly inefficient query?
It pains me to read someone calling the queries 'grossly inefficient' as there's no other way to do them efficiently (as otherwise there have to be a gazillion code paths, one for every situation they can be used it.).
Anyway, as I said above: if you want to fetch a projection, just define the projection (e.g. with Linq or in a dynamic list), if you want to fetch a graph with entities, it's a different type of query: parent query + prefetch path.
So in your case,you want to fetch customers and their related contacts through the individual table. This is an inheritance hierarchy btw, (Customer <- Individual), which can be created automatically in the designer, and which makes things easier to work with, as you then simply fetch 'individuals' which will make the runtime add the join with customers automatically.
To stay with your query, I'll write the query you want with the efficient tuning below.
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.ParameterisedPrefetchPathThreshold = 100;
var metaData = new LinqMetaData(adapter);
var q = (from c in metaData.Customer
join i in metaData.Individual on c.CustomerId equals i.CustomerId
join co in metaData.Contact on i.ContactId equals co.ContactId
select c)
.Take(100)
.WithPath(cp=>cp.Prefetch<IndividualEntity>(c=>c.Individual)
.SubPath(ip=>ip.Prefetch<ContactEntity>(i=>i.Contact)));
//...
}
This will produce the following 3 queries: customers:
Generated Sql query:
Query: SELECT DISTINCT TOP(@p2) [LPA_L1].[AccountNumber], [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[CustomerType], [LPA_L1].[ModifiedDate], [LPA_L1].[rowguid] AS [Rowguid], [LPA_L1].[TerritoryID] AS [TerritoryId] FROM (( [AdventureWorks].[Sales].[Customer] [LPA_L1] INNER JOIN [AdventureWorks].[Sales].[Individual] [LPA_L2] ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID]) INNER JOIN [AdventureWorks].[Person].[Contact] [LPA_L3] ON [LPA_L2].[ContactID] = [LPA_L3].[ContactID])
Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 100.
Individuals: Generated Sql query:
Query: SELECT [AdventureWorks].[Sales].[Individual].[ContactID] AS [ContactId], [AdventureWorks].[Sales].[Individual].[CustomerID] AS [CustomerId], [AdventureWorks].[Sales].[Individual].[Demographics], [AdventureWorks].[Sales].[Individual].[ModifiedDate] FROM [AdventureWorks].[Sales].[Individual] WHERE ( [AdventureWorks].[Sales].[Individual].[CustomerID] IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100))
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11001.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11094.
etc...
Contact
Generated Sql query:
Query: SELECT [AdventureWorks].[Person].[Contact].[AdditionalContactInfo], [AdventureWorks].[Person].[Contact].[ContactID] AS [ContactId], [AdventureWorks].[Person].[Contact].[EmailAddress], [AdventureWorks].[Person].[Contact].[EmailPromotion], [AdventureWorks].[Person].[Contact].[FirstName], [AdventureWorks].[Person].[Contact].[LastName], [AdventureWorks].[Person].[Contact].[MiddleName], [AdventureWorks].[Person].[Contact].[ModifiedDate], [AdventureWorks].[Person].[Contact].[NameStyle], [AdventureWorks].[Person].[Contact].[PasswordHash], [AdventureWorks].[Person].[Contact].[PasswordSalt], [AdventureWorks].[Person].[Contact].[Phone], [AdventureWorks].[Person].[Contact].[rowguid] AS [Rowguid], [AdventureWorks].[Person].[Contact].[Suffix], [AdventureWorks].[Person].[Contact].[Title] FROM [AdventureWorks].[Person].[Contact] WHERE ( [AdventureWorks].[Person].[Contact].[ContactID] IN (SELECT [AdventureWorks].[Sales].[Individual].[ContactID] AS [ContactId] FROM [AdventureWorks].[Sales].[Individual] WHERE ( ( [AdventureWorks].[Sales].[Individual].[CustomerID] IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100)))))
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11001.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11094.
etc...
This is very efficient, as it can 1) always deal with any graph form (multi branch, skewed 1:n relationships and 2) doesn't lead to data duplication in the resultset and 3) is easy to consume, as it uses existing code paths
I can tweak this down further, as you are only interested in the FirstName and LastName of contact, not in the other fields. I can use fields exclusion, so my query then becomes in C#:
var q = (from c in metaData.Customer
join i in metaData.Individual on c.CustomerId equals i.CustomerId
join co in metaData.Contact on i.ContactId equals co.ContactId
select c)
.Take(100)
.WithPath(cp=>cp.Prefetch<IndividualEntity>(c=>c.Individual)
.SubPath(
ip=>ip.Prefetch<ContactEntity>(i=>i.Contact)
.Include(x=>x.FirstName, x=>x.LastName)
));
and the Contact query then becomes:
Generated Sql query:
Query: SELECT [AdventureWorks].[Person].[Contact].[ContactID] AS [ContactId], [AdventureWorks].[Person].[Contact].[FirstName], [AdventureWorks].[Person].[Contact].[LastName] FROM [AdventureWorks].[Person].[Contact] WHERE ( [AdventureWorks].[Person].[Contact].[ContactID] IN (SELECT [AdventureWorks].[Sales].[Individual].[ContactID] AS [ContactId] FROM [AdventureWorks].[Sales].[Individual] WHERE ( ( [AdventureWorks].[Sales].[Individual].[CustomerID] IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100)))))
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11001.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11094.
Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11070.
Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11087.
... etc.
As you can see, it only includes the fields which are vital to the entity (pk, fk and the included fields). I can also do this with individual to limit the resultset returned for individual.
EDIT: I'm not sure if the forum ate your previous post, or if you deleted it. So, my response doesn't really make sense anymore
Nevertheless, I did try adding an "order by" using a SortExpression object. Still no dice. Perhaps this whole thing is just a philosophical issue, but it seems a like it would be a common problem. Is there no way to get llblgen to use joins versus multiple queries?
Yes, you can use joins, that's no problem as I showed above. Rules of thumb: - to fetch a graph, use eager loading with prefetch paths - to fetch an individual set of entities and filter on related entities use relations/joins - you can combine those per level of the prefetch path.
Joined: 12-Dec-2005
Hi Franz,
Thanks for taking the time to explain things.
Also, I apologize for the "grossly inefficient" comment - I've just been struggling with what I think is a fairly common scenario and was a little cranky after a few days of tinkering. As has been my experience in the last few years working with LLBLGen, if something is not working as I expect it to, its usually because of something dumb that I'm doing.
So, on to a couple of questions/comments about what you wrote.
First, thank you for putting together the query I was looking for - that was part of the battle. The main concern I have is that even with the optimized query, it still takes more time on average than the equivalent Entity Framework query that uses joins. I ran the following two pieces of code 1000 times each:
Entity Framework:
var context = new AdventureWorksEntities();
IQueryable<Customer> q =
from x in
context.Customers.Include("Individual.Contact")
.Where(x => x.Individual.Contact.FirstName == "David")
select x;
Stopwatch clock = new Stopwatch();
clock.Start();
for (int i = 0; i < 1000; i++)
{
q.ToList();
}
clock.Stop();
Console.WriteLine(clock.ElapsedMilliseconds / 1000);
LLBLGen:
using (var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
IQueryable<CustomerEntity> q = (from c in metaData.Customer
join i in metaData.Individual on c.CustomerId equals i.CustomerId
join co in metaData.Contact on i.ContactId equals co.ContactId
select c)
.WithPath(cp => cp.Prefetch<IndividualEntity>(c => c.Individual)
.SubPath(
ip => ip.Prefetch<ContactEntity>(i => i.Contact)
)).Where(c=>c.Individual.Contact.FirstName == "David");
Stopwatch clock = new Stopwatch();
clock.Start();
for (int i = 0; i < 1000; i++)
{
q.ToList();
}
clock.Stop();
Console.WriteLine(clock.ElapsedMilliseconds / 1000);
}
Results: EF: 29ms LLBLGen: 105ms
The EF query is over three times as fast on average, and the code more concise. I'm not trying to get into a religious war, because I love LLBLGen and really don't want to use Entity Framework. I'm just struggling to understand why I'm not getting comparable performance.
Am I just being really dumb about how I'm thinking about the problem? It's okay if you say yes
For this particular scenario, their chosen way of doing prefetch paths pays off. However as soon as you add another include, or have a 1:n relationship, it is totally different.
Eager loading is something that can be done in a couple of ways. We chose to use 1 query per graph node, which is on average the best choice. MS chose to use joins which gives lots of problems (see the EF wish list http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions for some horror stories about include 
) in many scenarios, luckily for them, not in this particular case .
You can make it a little more efficient for llblgen pro when creating an inheritance hierarchy, and remove the join to contact (although it's not really that much of a problem in the performance).
So in this particular case, our eager loading system is a bit slower than theirs, but as we decided years ago already: this is really the best way to handle this, especially when you think about more complex graphs which give huge problems with joins or multiple nodes (e.g. you fetch 3 or 4 nodes in a graph).
Joined: 12-Dec-2005
No problem, glad we could help out
If you have any further questions, just let us know