Improve the performance of a collection with multiple records

Question

Improve the performance of a collection with multiple records

Navigation

#1 by (0 votes)

0

I have the following code:

For Each Subitem In lstGastoResumen_Area
    Dim MesRealSub
    Dim MesPPTOSub

    MesRealSub = Aggregate y In lstGastoResumen Where y.Area = Subitem And y.Anio = Anio1 Into Sum(y.FebRealSoles)
    MesPPTOSub = Aggregate y In lstGastoResumen Where y.Area = Subitem And y.Anio = Anio1 Into Sum(y.FebPptoSoles)
Next

Where lstGastoResumen is a hashset and contains more than 80,000 rows and lstGastoResumen_Area has 20 rows. This code takes 20 seconds to execute. Could someone tell me how to improve the performance?

vb.net linq collectionjs

asked by Luis Espinoza 22.02.2018 в 16:38

source

1 answer

How to optimize sql query? Problem when running Program in visual studio using vsn

score 0 · Answer 1

From what I see in your code, the problem seems to be that you're always working on the entire collection, so you're doing where twice in each iteration, that's 40 times.

Linq allows for example to group the data. You could first group by area and at that time and filter by year, and then work on the new collection.

I do not know much about the syntax of VB.net, but I mean something like this:

Dim lstGastoResumenByArea = From y In lstGastoResumen
                            Where y.Anio = Anio1
                            Group by Area = y.Area
For Each Subitem In lstGastoResumen_Area
    Dim MesRealSub = From gasto In lstGastoResumenByArea
                     Where gasto.Area = Subitem
                     Aggregate y In gasto.Area
                     Into Sum(y.FebRealSoles)
    Dim MesRealSub = From gasto In lstGastoResumenByArea
                     Where gasto.Area = Subitem
                     Aggregate y In gasto.Area
                     Into Sum(y.FebPptoSoles)
Next

In fact the part of

                 From gasto In lstGastoResumenByArea
                 Where gasto.Area = Subitem

you should be able to do it only once, saving the result in another variable.

You may even be able to improve even more if the% share% of you do it in Aggregate ...

More information (in English): Group By Clause (Visual Basic)