long time to execute

[n3w7]

Hi
I'm asking for some help with this code, it takes about 6.5 seconds to execute. is there a quicker way? number of rows in the tables are about 200.

Dim query = From s In dtSupplier, p In dtPost, c In dtCategory, b In dtBudget _
                    Where s!strSupplier = p!strSupplier And c!strBudget = b!strBudget And _
                              s!strCategory = c!strCategory And c!boolExlude = False And _
                              DateAndTime.Year(p!dDate) = DateAndTime.Year(Now()) _
                    Select s!strCategory, s!strSupplier, p!intCost, p!dDate, c!strBudget, b!intBudget, c!boolExlude

 

[jmcilhinney]

I'm not 100% sure but I suspect that your code is actually performing a cross join and then filtering out all the records where the join columns don't match. That's lots of wasted work. I would suggest that you perform a proper join, which uses the Join and On keywords. You might test with just two tables to begin with and use a Stopwatch to time both sets of code to see if there's a difference.

 

[n3w7]

you're right

I changed to this and I saved 6.4 seconds

Dim query = From p In dtPost _
                    Join s In dtSupplier On p!strSupplier Equals s!strSupplier _
                    Join c In dtCategory On s!strCategory Equals c!strCategory _
                    Join b In dtBudget On c!strBudget Equals b!strBudget _
                    Where DateAndTime.Year(p!dDate) = DateAndTime.Year(Now()) And c!boolExlude = False _
                 Select s!strSupplier, p!intCost, p!dDate, c!strCategory, b!intBudget, b!strBudget

Thanks!!!