Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unhappy 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

    RsAim.MoveNext
    Wend


    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 & ", "
    RsDet.MoveNext
    Wend
    RsDet.Close
    End Sub




    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!

    Regards!
    naderda
    ><>

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    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.

    Regards

  3. #3
    Join Date
    Jan 2004
    Posts
    3

    Serching the ZDetail - Thanx!!!!

    Iīve got to create a new database "Report", this is new and empty database, which I have to fill with data from two other dbs, ZTemp and ZDetail!

    Report contains 1 table "Report" with 12 columns: User_Group, UserID, Name, Department, System, ConflCases, Profile_1, Profile_Description_1, Transaktionen_1, Profile_2, Profile_Description_2, Transaktionen_2, User Counter

    ZTemp contains 1 table "SOD" with 10 columns: User_Group, UserID, Name, Department, System, ConflCases, Profile_1, Profile_Description_1, Profile_2, Profile_Description_2

    ZDetail contains 1 table "Transaktionen" with 6 columns: System, Profile_1, Profile_2 Description_of_risk, Transaction_1, Transaction_2

    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.

    While Not RsAim.EOF
    RsRep.AddNew
    RsRep.Fields("User_Group") = RsAim.Fields("User_Group")
    RsRep.Fields("UserID") = RsAim.Fields("UserID")
    RsRep.Fields("Name") = RsAim.Fields("Name")
    RsRep.Fields("Department") = RsAim.Fields("Department")
    RsRep.Fields("System") = RsAim.Fields("System")
    RsRep.Fields("ConflCases") = RsAim.Fields("ConflCases")
    RsRep.Fields("Profile_1") = RsAim.Fields("Profile_1")
    RsRep.Fields("Profile_Decription_1") = RsAim.Fields("Profile_Decription_1")
    RsRep.Fields("Profile_2") = RsAim.Fields("Profile_2")
    RsRep.Fields("Profile_Description_2") = RsAim.Fields("Profile_Description_2")
    Call funk(RsAim.Fields("System"), RsAim.Fields("ConflCases"), RsAim.Fields("Profile_1"), RsAim.Fields("Profile_2"))
    RsRep.Fields("Transaktion_1") = str1
    RsRep.Fields("Transaktionen_2") = str2
    If RsAim.Fields("UserID") = LastUserID Then Counter = 0 Else Counter = 1
    RsRep.Fields("User_Counter") = Left(RsAim.Fields("ConflCases"), 5) & Counter & Left(RsAim.Fields("User_Group"), 2)
    RsRep.Update

    LastUserID = RsAim.Fields("UserID")
    RsAim.MoveNext
    Wend

    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

    While Not RsDet.EOF
    str1 = str1 & ", " & RsDet(0).Value
    str2 = str2 & ", " & RsDet(1).Value
    RsDet.MoveNext
    Wend
    RsDet.Close

    If str1 = "" Then str1 = "No data"
    If str2 = "" Then str2 = "No data"

    End Sub

    So I have a new query for each recordset in db Report because every recordset is "unique" in the combination of the ConflCases and the Profiles_i. So I canīt just loop once through ZDetail.

    You told me something about indexes. But I donīt know, what they are and how to use them!

    Hope, this is now clear enough for you to understand my project!
    Thanks for your help, and your time!!!
    Regards naderda
    ><>

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Lookup indexes in the access help...

    Why would you use a complex thing like recordsets to move information from 1 place to another...???? Why dont you simply use an append query?


    Regards

  5. #5
    Join Date
    Jan 2004
    Posts
    3
    I donīt believe I could do this. Because I have to store information from more than one cell in one! See the last loop over the recordset.

    But perhaps, I havenīt understood the append-query! But I donīt know how I could programme that!

    Regards naderda

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •