Very slow linq preformance

[willempipi]

Hi, I'm working for the first time with linq on a new project. I've made a few queries now, that are quite simple. They are running very fast, but, for my last query i had to do some more complex things(5 joins) and now the preformance is really really bad. It takes more than a minute to retreive 400 records(and it often gives time-outs). At first i thought it might be that the query was wrong. So i copied the query that was excuted in debug mode and ran it directly on the SQL Server using SQL Management studio. This excuted in less than a second, so the problem is not the query. Then i thought it might be that the objects that are used to join the tables together where very heavy for the application. So i moved my complete query to a stored procedure, and used linq to only excute the stored procedure, witch makes linq kind of obsolete. This gave exactly the same behaviour, the stored procedure's execution time is over a minute and often ends up in a time out, while when i excute the stored procedure directly on the sql server the excution time is less than a second. I've searched google for others with the same problem, but i cannot find anyone with the same problem. Please help me, i see the advantages of linq but with these kind of problems i think i have to switch to something different than linq. Few things, i don't know if it makes a difference: - I'm using .dbml datacontext files - The joins i make in the query are not described in this datacontext. The linq query:

var externeuitvoeringen =
from euv in ((UitvoeringenDataContext)_DataContext).tblExternUitvoerings

    join join\_brs in ((UitvoeringenDataContext)\_DataContext).tblBrandstofs
    on euv.lng\_euv\_brs\_ID equals join\_brs.lng\_brs\_ID
    into brs\_temp
    from brs in brs\_temp.DefaultIfEmpty()

    join join\_adr in ((UitvoeringenDataContext)\_DataContext).tblAandrijvings
    on euv.lng\_euv\_adr\_ID equals join\_adr.lng\_adr\_ID
    into adr\_temp
    from adr in adr\_temp.DefaultIfEmpty()

    join join\_vsn in ((UitvoeringenDataContext)\_DataContext).tblVersnellings
    on euv.lng\_euv\_vsn\_ID equals join\_vsn.lng\_vsn\_ID
    into vsn\_temp
    from vsn in vsn\_temp.DefaultIfEmpty()

    join join\_eku in ((UitvoeringenDataContext)\_DataContext).tblExternKoppelUitvs
    on euv.str\_euv\_ID equals join\_eku.str\_eku\_euv\_ID
    into euk\_temp
    from euk in euk\_temp.DefaultIfEmpty()

    join join\_euh in ((UitvoeringenDataContext)\_DataContext).tblExt

[willempipi]

After playing around with this problem i notest that the base table doesn't have a primary key, after changing this the query excuted as fast as directly on the server. Also setting some indexes improved the speed. Why this should make a difference is a mystery to me...

[Dan Mos]

willempipi wrote:

Also setting some indexes improved the speed. Why this should make a difference is a mystery to me...

That's the purpose of indexes: to speed up serach. As a side note an index slows down inserts and updates but in a more than acceptable manner. An index is like the key in a dictionary<key,value>. So it speeds up a lot the search.

[Not Active]

There also other ways to speed up your queries, maybe this will help Entity Framework Performance[^]

---

I know the language. I've read a book. - _Madmatt