无循环的表达式计算 [英] expression calculation without loops
问题描述
数据集1 :(关于Salesdetails)
Id,P1,P2,P3 ......... P50
1,1223,AABBB,12Ac,..........
数据集2 :(关于查找广告系列的公式)
ID,公式
1,如果(((p1 ="123")和(Left(p2,2)="AA"))或(Mid(P4,1,3)="A4")
2,....
我们正在将Px(例如P1)值与Dataset1 P值组合在一起,并得到true或false.如果为true,则添加到列表中,最后返回计算为True的广告系列ID列表.
如果Dataset1有7行,Datset2有200行,则循环每行并计算表达式,大约需要3500毫秒(对于和,并行)
而且在公式中,我们使用Left,Mid,Right,例如
您是否有一个没有循环或更快方式的想法? (并行循环的速度不如每个循环正常)
样本:
Hi,
Dataset 1 : ( about Salesdetails)
Id , P1 , P2 , P3 , ...... P50
1 , 1223 , AABBB , 12Ac , ..........
Dataset 2 : (about formulas to find campaing)
ID , Formula
1 , if ((p1="123") and (Left(p2,2)="AA") ) or (Mid(P4,1,3)="A4")
2 , ....
We are combining Px( e.g. P1) values with Dataset1 P values and gets true or false. If true adds to list and at the end returns list of campaign Ids which calculation is True.
If Dataset1 has 7 rows and Datset2 has 200 rows ,loops every lines and calculate expression and it takes ~3500 ms ( for and parallel for )
And also in formulas we are using Left , Mid , Right , e.g.
Dou you have an idea to do this without loops or faster way ? (Parallel loops not fast as nornal for each loops)
Sample :
Try
Dim _list As New List(Of Class_Result)
For Each r_formul As Data.DataRow In _Formuledata.Rows 'dataset2 has 210 records
For Each r_order As Data.DataRow In _OrderData.Rows ' dataset1 has 7 records
' gets P values from r_order and replace it in r_formul and calculates with MSscript
GetResult(r_formul, r_order, _list)
Next
Next
Return _list
Catch ex As Exception
Return Nothing
End Try
推荐答案
重新设计数据库是我认为可以避免这种情况的唯一方法.
您可以将公式重新定义为SQL,然后执行它们-这样您返回的记录集将包含您当前正在循环中计算的布尔值.
任何替代方法都取决于公式中允许的灵活性.
例如,如果有确定的绝对函数列表,则可以在将数据添加到数据库时预先计算值.
另外,如果您必须使用Mid,Left等.似乎您存储的数据不是原子的-即,如果p1以"AA"开头表示,那么您确实应该有一个列(在该表或其他地方)描述其含义,以便您的查询可以原子访问数据
A database redesign would be the only way I can think you could avoid this.
You could re-define your formulae to be in SQL and then execute them - so the record set you get back includes the boolean you are currently calculating in the loop.
Any alternative depends upon the flexibility allowed in your formulae.
for example, if there is a definitive list of absolute functions available, it would be possible to pre-calculate the values when adding data to the database.
Also, if you have to use Mid, Left etc. it seems your stored data is not atomic - i.e. if p1 beginning with ''AA'' means something, then you really should have a column (on that table or elsewhere) describing what it means, so your query can access the data atomically
这篇关于无循环的表达式计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!