Unanswered: Fast searching in and for tons of data
Hi, I hope someone has a great idea to this very fast:
Iīve got two tables, the first one Report contains five columns crit1, crit2, crit3 and data1 , data2! The crit-fields contains data with which I have to look for the needed data1 and data2 in the second table ZDetail. I will illustrate this with a little code:
RsAim Recordset in table Report
RsDet Recordset in table ZDetail
While Not RsAim.EOF
Call funk(RsAim(4).Value, RsAim(5).Value, RsAim(6).Value, RsAim(8).Value)
'The empty fields will get the searched data str1 and str2
Public Sub funk(Sys, CC, P1, P2)
str1 = ""
str2 = ""
RsDet.Open "Select data1, data2 From ZDetail Where crit1 = '" & CC & "' And crit2 = '" & P1 & '" And crit3 = '" & P2 & "'"
While Not RsDet.EOF
str1 = str1 & RsDet(0).Value & ", "
str2 = str2 & RsDet(1).Value & ", "
This works all fine!
But it is much too slow!!!
Iīve got about 70.000 recordsets in which Iīve to put in the data from ZDetail! And ZDetail itself contains 650.000 lines!
So a much faster algorithm is needed!
Anyone a idea? Hope I have explained my problem properbly!
Do you have indexes on you criteria fields? If not try creating them, this will increase your response time.
What is your ulitmat purpose?
Why query the big ZDetail multi times? And not just once looping that?
Can you post your full table design and relationships?
Tip: dont use RsAim(4).Value
1) .value is default, thus unnecessary.
2) Its unreadable and if you create new fields you may have to alter your code. use RsAim!FieldName or RsAim.fields("Fieldname") instead.
ZTemp contains the 70.000 records, which I read into the db Report, so the columns User_Group, UserID, Name, Department, System, ConflCases, Profile_1, Profile_Description_1, Profile_2, Profile_Description_2 are filled. Additionally I create a code for the field User Counter, which tests if the UserID appears the first time and then creates a string from the fields ConflCases, User_Group and 1 for UserID appears the first time or a 0 for repeated appearing.
Iīve writen a loop, which runs through the ZTemp and writes the data into the Report db.
RsRep is the recordset-object in db Report
RsAim is the recordset-object in db ZTemp
RsDet is the recordset-object in db ZDetail
This is a very fast process, so thers is no problem.
But Iīhave to fill the two columns which a left free, so I wanted to fill them with the procedure funk, which initiate the variables str1 and str2 with the right data.
In the ZDetail I look for the transactions, they depend on the ConflCases and the Profile_1 and Profile_2.
The result can be that, there a no transaction, but there can also be one or more.
Public Sub funk(CC, P1, P2)
str1 = ""
str2 = ""
VarCon = CC & Chr(9)
VarPro1 = P1
VarPro2 = P2
strSQLDet = "Select Transaction_1, Transaction_2 From Transaktionen Where (Description_of_Risk Like '" & VarCon & "') And (Profile_1 Like '" & VarPro1 & "') AND (Profile_2 Like '" & VarPro2 & "')"
RsDet.Open strSQLDet, CnDet, , , adCmdText