Join problem
Posted: Fri 23 Oct 2009 12:53
I have problems with joins and left outer joins
This will result in a first select where all itemType is selected, and then the orderlines query.
I was expecting a join in one sql. Our program is running really slow since some joined tables have 100000 of posts.
If i instead do
a new sql is run for each Row!!! Can't have a long list of sql that runs.
This example may be fixed by specifying that the refered table should be loaded at same time (loadoption), but if the link is multiple "steps" away, I still get ugly performance.
I also tried to do a storeprocedure like this
This will also generate a select to get all the lines, and then, for each line, call the getItemTypeName function.
Running 5.25.39, can't run a later version since our long-running-storeprocedures will timeout.
Code: Select all
from l in header.Orderlines
join t in context.ItemType on l.itemId equals t.Id
select new { l.id, l.desc, t.name }
I was expecting a join in one sql. Our program is running really slow since some joined tables have 100000 of posts.
If i instead do
Code: Select all
... new { l.id, l.desc, l.ItemType.name }
This example may be fixed by specifying that the refered table should be loaded at same time (loadoption), but if the link is multiple "steps" away, I still get ugly performance.
I also tried to do a storeprocedure like this
Code: Select all
... new {l.id, l.desc, context.GetItemTypeName(l.id) }
Running 5.25.39, can't run a later version since our long-running-storeprocedures will timeout.