Optimizing LINQ query

Scott MacMaster

I'm been researching LINQ over the past few days. I really like it and I'm hoping to switch over to using it for all future queries. However, my attempts has been dissappointing due to how long it takes to process. My LINQ query takes nearly 7 times longer to run then my standard approach (LINQ takes 3,702 MS, standard approach takes 530 MS). I'm loading data from a SQL database. The code loads 14,568 records and organizes the data into my data structures. Can someone suggest a way to speedup my LINQ query? Thanks,

Public Sub LoadByLINQ(ByVal connection As DB)

    Dim db As DataClasses1DataContext = New DataClasses1DataContext(connection.connection)
    db.ObjectTrackingEnabled = False

    Dim terms As Integer() = {2, 3, 4}

    Dim startTime As Date = Now

    Dim sections = From s In db.HE\_COURSE\_SECTIONs \_
                   Where s.HE\_YEAR\_TERM\_ACADEMIC\_YEAR >= FromYear \_
                        And s.HE\_YEAR\_TERM\_ACADEMIC\_YEAR <= ToYear \_
                        And terms.Contains(s.HE\_YEAR\_TERM\_ACADEMIC\_TERM) \_
                    Select s.HE\_YEAR\_TERM\_ACADEMIC\_YEAR, \_
                            s.HE\_YEAR\_TERM\_ACADEMIC\_TERM, \_
                            s.ACADEMIC\_CREDIT, \_
                            s.COURSE\_PREFIX, \_
                            s.COURSE\_NUMBER, \_
                            s.COURSE\_SEC, \_
                            s.Year, s.Term

    Dim count As Integer = 0
    For Each section In sections

        count += 1

        If years.ContainsKey(section.Year) = False Then
            years.Add(section.Year, New Year(section.Year))
        End If

        If years(section.Year).Terms.ContainsKey(section.Term) = False Then
            years(section.Year).Terms.Add(section.Term, New Year.Term(section.Term))
        End If

        Dim sectionKey As New SectionKey(section.Year, section.Term, section.COURSE\_PREFIX, section.COURSE\_NUMBER, section.COURSE\_SEC)

        If years(section.Year).Terms(section.Term).Sections.ContainsKey(sectionKey) = False Then
            years(section.Year).Terms(section.Term).Sections.Add(sectionKey, New Year.Term.Section(section.COURSE\_PREFIX, section.COURSE\_NUMBER, section.COURSE\_SEC))
        End If

        If section.ACADEMIC\_CREDIT Is Nothing = False Then
            years(section.Year).Terms(section.Term).Sections(sectionKey).Credits = CDec(section.ACADEMIC\_CREDIT)

Chintan Desai

Plz done post such code which irritates.

Regards Chintan HCL Technologies...India (Carefully) listen->(Deeply)Think->(Clearly)Understand->(Patiently) reply...

Scott MacMaster

what?

Pete OHanlon

I would suspect that your problem here is the Contains portion of the clause. Have you profiled the SQL to see what's being produced?

Deja View - the feeling that you've seen this post before.

My blog | My articles

Scott MacMaster

I have read about how you need to make sure datatypes match nicely to avoid overhead with boxing/unboxing variables. However, after trying various datatypes I was not able to change the execution time (it never even got slower). I just tried changing the Contains to this

And (s.HE_YEAR_TERM_ACADEMIC_TERM = 2 _
Or s.HE_YEAR_TERM_ACADEMIC_TERM = 3 _
Or s.HE_YEAR_TERM_ACADEMIC_TERM = 4)

however, that had no effect on the speed. I've also tried removing the select part since projection isn't required. I always like to use it to remove transferring unneeded data. Amazingly, this actually reducing the execution time by 2 seconds. So now I have a LINQ query that only 3 times slower then the other method. So there's still work to do to optimize it. Do you have any other ideas? Also, I have no idea how to look at the SQL it produces. I'd be interested in learning how. Thanks,

Pete OHanlon

Try LinqPad. It's a great little tool that will show you what your SQL will be, and also it shows you what your Lambda expressions would be.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Simon P Stevens

Pete O'Hanlon wrote:

LinqPad

Nice. I never knew about this. Thanks. :)

Simon

DaveyM69

Just found this tool thanks to your post. Very handy and cool! :-D

Dave
BTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)