Performance issue on Joins



Great job. This is really cool stuff.

However, I'm not sure we can use it due to performance problems.

I think there is even a performance problem when select one field from a large table with a where clause on an indexed column. But, it seems worse for joins. What I'm seeing is the EF generates massive select statements that include every field regardless of whether they are used or not. Thus, on joins the select becomes really massive.

For example, here's my join:
            ctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

            var myquery = from inv in ctx.Inventors
                          join p in ctx.Poitems on inv.Uniq_Key equals  p.Uniq_Key
                          where inv.Uniq_Key == myUniqKey
                          select new
                              Uniq_Key = inv.Uniq_Key,
                              Ponum = p.Ponum

            foreach (var item in myquery)
                Debug.WriteLine("{0}, {1}",item.Uniq_Key,item.Ponum);
The SQL Select that is generated uses 2 derived tables and includes every field from both tables. I won't include the SQL Select here, but it is 176 lines long.

The query in VFP EF takes 37 seconds. If I run the same Select Join using VFP Oledb it takes less than 0.5 seconds.

Thank you