Professional Web Applications Themes

Want to compare databases - ASP Database

I posted the message below but did not get any replies, can anyone recommend a more appropriate newsgroup or forum where I might inquire? Thanks! ======================================== I have two MS-Access databases with about 2000 records each, that I need to compare using ASP. Currently I open db1, read the first record, search db2 for that data, then advance the first database one record and repeat. It is obviously very slow, as it is constantly searching the entire db2 for info from the first. I am looking for a better way to do this. I am thinking about creating two arrays ...

  1. #1

    Default Want to compare databases

    I posted the message below but did not get any replies, can anyone recommend
    a more appropriate newsgroup or forum where I might inquire?

    Thanks!

    ========================================

    I have two MS-Access databases with about 2000 records each, that I need to
    compare using ASP. Currently I open db1, read the first record, search db2
    for that data, then advance the first database one record and repeat. It is
    obviously very slow, as it is constantly searching the entire db2 for info
    from the first.

    I am looking for a better way to do this. I am thinking about creating two
    arrays and reading all records from both tables into the arrays, then
    processing them much the same way. However, since I am still pretty much
    unfamiliar with ASP capabilities, I am wondering if there isn't some easier
    way.

    Thoughts, or good advice?

    Thanks!



    Kevin Guest

  2. #2

    Default Re: Want to compare databases

    Please more details:
    1) is there only 1 table in each database
    2) compare a complete record
    3) or some fields in that record
    4) what is the link between the 2 tables in the 2 db's
    5) Do the second table use an index (maybe thats why it is slow)
    6) and if the match is found? Delete ?


    John Guest

  3. #3

    Default Re: Want to compare databases

    "Kevin Ingram" <com> wrote in message
    news:newsguy.com... 
    recommend 
    to 
    is 
    easier 

    Presuming that your looking for matches on a "key" field (or fields) then
    try the following:

    1) Read database table 1 and build a dictionary with the key;
    2) Read database table 2 and compare each key with the dictionary.

    Here's an example; change Constants cARN, cARO, and cCOL; watch for
    word-wrap:

    Option Explicit
    '
    ' This VBS (Visual Basic Script) program does the following:
    ' 1) Reads database table 1 into a dictionary;
    ' 2) Reads database table 2 and compares against the dictionary.
    '
    '*
    '* Declare Constants
    '*
    Const cVBS = "compare.vbs"
    Const cMDB = "compare.mdb"
    Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    Const cARN = "ARN_InvHistoryHeader"
    Const cARO = "ARO_InvHistoryDetail"
    Const cCOL = "InvoiceNumber"
    '*
    '* Declare Variables
    '*
    Dim arrRST(999)
    arrRST(0) = cCOL & ":"
    Dim intRST
    Dim strRST
    Dim strSQL
    '*
    '* Declare Objects
    '*
    Dim objADO
    Set objADO = CreateObject("ADODB.Connection")
    objADO.Open cDSN & cMDB
    Dim objDIC
    Set objDIC = CreateObject("Scripting.Dictionary")
    Dim objRST
    '*
    '* Read Table 1
    '*
    intRST = 0
    strSQL = "SELECT " & cCOL
    strSQL = strSQL & " FROM " & cARN
    strSQL = strSQL & " ORDER BY " & cCOL
    Set objRST = objADO.Execute(strSQL)
    Do Until objRST.EOF
    intRST = intRST + 1
    strRST = objRST(cCOL)
    objDIC.Add strRST, intRST
    objRST.MoveNext
    Loop
    Set objRST = Nothing
    '*
    '* Read Table 2
    '*
    intRST = 0
    strSQL = "SELECT DISTINCT " & cCOL
    strSQL = strSQL & " FROM " & cARO
    strSQL = strSQL & " ORDER BY " & cCOL
    Set objRST = objADO.Execute(strSQL)
    Do Until objRST.EOF
    intRST = intRST + 1
    strRST = objRST(cCOL)
    If objDIC.Exists(strRST) Then
    arrRST(intRST) = "+ " & strRST
    Else
    arrRST(intRST) = "- " & strRST
    End If
    objRST.MoveNext
    Loop
    Set objRST = Nothing
    '*
    '* Report
    '*
    strRST = ""
    For intRST = 0 To UBound(arrRST)
    If arrRST(intRST) <> "" Then
    strRST = strRST & arrRST(intRST) & vbCrLf
    End If
    Next
    WScript.Echo strRST
    '*
    '* Destroy Objects
    '*
    objADO.Close
    Set objADO = Nothing
    Set objDIC = Nothing


    McKirahan Guest

  4. #4

    Default Re: Want to compare databases

    Both databases contain a table called CUSTOMERS. There is a field in that
    table called USERNAME. The purpose of the script is to locate USERNAMEs in
    DB1 that do not exist in DB2, and vice versa. Once a list of username
    differences is established, we process them separately its just the time it
    is taking to get the list of differences is too long.

    The databases have no links to each other, they reside on different servers.
    I have indexed the USERNAME fields and that helped, but not enough. Just
    looking for the most efficient way to get the list of differences.

    "John" <n o b o d com> wrote in message
    news:kGz1d.245656$telenet-ops.be... 


    Kevin Guest

  5. #5

    Default Re: Want to compare databases

    "Kevin Ingram" <com> wrote in message
    news:newsguy.com... 
    it 
    servers. 
    [snip]

    Try the following; watch for word-wrap.

    Option Explicit
    '
    ' This VBS (Visual Basic Script) program does the following:
    ' 1) Reads table 1 into a dictionary; Key=USERNAME, Item="1".
    ' 2) Reads table 2 into a dictionary; Key=USERNAME, Item="2"
    ' unless the Key=USERNAME already exists then Item="3".
    ' 3) Sorts the dictionary by Key.
    ' 4) Reads the dictionary and reports Item and Key.
    '
    '*
    '* Declare Constants
    '*
    Const cVBS = "compare0.vbs"
    Const cMD1 = "compare1.mdb"
    Const cMD2 = "compare2.mdb"
    Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    Const cTBL = "CUSTOMERS"
    Const cCOL = "USERNAME"
    '*
    '* Declare Variables
    '*
    Dim strDIC
    Dim strRST
    Dim strSQL
    Dim strWSE
    '*
    '* Declare Objects
    '*
    Dim objAD1
    Set objAD1 = CreateObject("ADODB.Connection")
    objAD1.Open cDSN & cMD1
    Dim objAD2
    Set objAD2 = CreateObject("ADODB.Connection")
    objAD2.Open cDSN & cMD2
    Dim objDIC
    Set objDIC = CreateObject("Scripting.Dictionary")
    Dim objRST
    '*
    '* Read Table 1
    '*
    strSQL = "SELECT " & cCOL
    strSQL = strSQL & " FROM " & cTBL
    strSQL = strSQL & " ORDER BY " & cCOL
    Set objRST = objAD1.Execute(strSQL)
    Do Until objRST.EOF
    strRST = objRST(cCOL)
    objDIC.Add strRST, "1"
    objRST.MoveNext
    Loop
    Set objRST = Nothing
    '*
    '* Read Table 2
    '*
    strSQL = "SELECT " & cCOL
    strSQL = strSQL & " FROM " & cTBL
    strSQL = strSQL & " ORDER BY " & cCOL
    Set objRST = objAD2.Execute(strSQL)
    Do Until objRST.EOF
    strRST = objRST(cCOL)
    If objDIC.Exists(strRST) Then
    objDIC.Remove strRST
    objDIC.Add strRST, "3"
    Else
    objDIC.Add strRST, "2"
    End If
    objRST.MoveNext
    Loop
    Set objRST = Nothing
    '*
    '* Report
    '*
    Call SortDictionary(objDIC,1)
    For Each strDIC In objDIC
    strWSE = strWSE & objDIC.Item(strDIC) & " : " & strDIC & vbCrLf
    Next
    WScript.Echo strWSE
    '*
    '* Destroy Objects
    '*
    objAD1.Close
    Set objAD1 = Nothing
    objAD2.Close
    Set objAD2 = Nothing
    Set objDIC = Nothing

    Function SortDictionary(objDict,intSort)

    '*================================================ ==========================
    '
    '* Function SortDictionary()
    '*
    '* Sorting a Scripting Dictionary Populated with String Data
    '* http://support.microsoft.com/support/kb/articles/Q246/0/67.ASP

    '*================================================ ==========================
    '
    Const dictKey = 1
    Const dictItem = 2
    '*
    '* Dictionary Count
    '*
    Dim i
    i = objDict.Count
    If (i < 2) Then Exit Function
    '*
    '* Dictionary Array
    '*
    Dim arrDict()
    ReDim arrDict(i,2)
    Dim j
    j = 0
    Dim objKey
    For Each objKey In objDict
    arrDict(j,dictKey) = CStr(objKey)
    arrDict(j,dictItem) = CStr(objDict(objKey))
    j = j + 1
    Next
    '*
    '* Shell Sort
    '*
    Dim k
    Dim strKey
    Dim strItem
    For j = 0 to (i - 2)
    For k = j to (i - 1)
    If StrComp(arrDict(j,intSort),arrDict(k,intSort),vbTe xtCompare) 
    strKey = arrDict(j,dictKey)
    strItem = arrDict(j,dictItem)
    arrDict(j,dictKey) = arrDict(k,dictKey)
    arrDict(j,dictItem) = arrDict(k,dictItem)
    arrDict(k,dictKey) = strKey
    arrDict(k,dictItem) = strItem
    End If
    Next
    Next
    '*
    '* Dictionary Clear
    '*
    objDict.RemoveAll
    '*
    '* Dictionary Build
    '*
    For j = 0 to (i - 1)
    objDict.Add arrDict(j,dictKey), arrDict(j,dictItem)
    Next
    End Function


    McKirahan Guest

  6. #6

    Default Re: Want to compare databases

    Kevin Ingram wrote: 

    the first thing is to reevalueate whether this really needs to be done via
    ASP. How often does this need to happen? It seems more suited for an offline
    activity to me.
     

    The fastest way will involve getting both sets of data into a single
    database. Is there any chance of doing that? Can you create a link in one
    database to the table in the other database?

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  7. #7

    Default Re: Want to compare databases

    The databases are on different servers in remote locations, the ASP
    application is billing software that needs to update between the two. No way
    to connect the databases, and need ASP to make the update available by web
    to billing.

    It works as it is, just very slow. I realize most of the problem is that
    they are MS-Access databases, but that cannot be changed as they are part of
    another application on the remote end that requires it... just trying to
    work with what we have available, and make it as quick and easy as possible.

    We are playing with reading both tables into arrays and comparing them then,
    but wondered if there was a more efficient and acceptable way to do it. Also
    looking into the DICTIONARY suggestions that have been made, looks a lot
    like the array idea but may be a little slicker.. going to try both.

    If you have other suggestions would sure like to try them!


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > the first thing is to reevalueate whether this really needs to be done via
    > ASP. How often does this need to happen? It seems more suited for an[/ref]
    offline 
    >
    > The fastest way will involve getting both sets of data into a single
    > database. Is there any chance of doing that? Can you create a link in one
    > database to the table in the other database?
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Kevin Guest

  8. #8

    Default Re: Want to compare databases

    Would it make more sense to use tion to keep the databases in sync?
    You CAN use tion between databases on remote servers. See
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;190766 which links
    to this:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/intrjet4.asp

    This may also be useful:
    http://support.microsoft.com?kbid=282977

    Bob Barrows

    Kevin Ingram wrote: 

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  9. #9

    Default Re: Want to compare databases

    "McKirahan" <com> wrote in message
    news:NTB1d.192304$.. [/ref]
    that [/ref]
    in 
    > it 
    > servers. 
    > [snip]
    >
    > Try the following; watch for word-wrap.
    >
    > Option Explicit
    > '
    > ' This VBS (Visual Basic Script) program does the following:
    > ' 1) Reads table 1 into a dictionary; Key=USERNAME, Item="1".
    > ' 2) Reads table 2 into a dictionary; Key=USERNAME, Item="2"
    > ' unless the Key=USERNAME already exists then Item="3".
    > ' 3) Sorts the dictionary by Key.
    > ' 4) Reads the dictionary and reports Item and Key.
    > '
    > '*
    > '* Declare Constants
    > '*
    > Const cVBS = "compare0.vbs"
    > Const cMD1 = "compare1.mdb"
    > Const cMD2 = "compare2.mdb"
    > Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    > Const cTBL = "CUSTOMERS"
    > Const cCOL = "USERNAME"
    > '*
    > '* Declare Variables
    > '*
    > Dim strDIC
    > Dim strRST
    > Dim strSQL
    > Dim strWSE
    > '*
    > '* Declare Objects
    > '*
    > Dim objAD1
    > Set objAD1 = CreateObject("ADODB.Connection")
    > objAD1.Open cDSN & cMD1
    > Dim objAD2
    > Set objAD2 = CreateObject("ADODB.Connection")
    > objAD2.Open cDSN & cMD2
    > Dim objDIC
    > Set objDIC = CreateObject("Scripting.Dictionary")
    > Dim objRST
    > '*
    > '* Read Table 1
    > '*
    > strSQL = "SELECT " & cCOL
    > strSQL = strSQL & " FROM " & cTBL
    > strSQL = strSQL & " ORDER BY " & cCOL
    > Set objRST = objAD1.Execute(strSQL)
    > Do Until objRST.EOF
    > strRST = objRST(cCOL)
    > objDIC.Add strRST, "1"
    > objRST.MoveNext
    > Loop
    > Set objRST = Nothing
    > '*
    > '* Read Table 2
    > '*
    > strSQL = "SELECT " & cCOL
    > strSQL = strSQL & " FROM " & cTBL
    > strSQL = strSQL & " ORDER BY " & cCOL
    > Set objRST = objAD2.Execute(strSQL)
    > Do Until objRST.EOF
    > strRST = objRST(cCOL)
    > If objDIC.Exists(strRST) Then
    > objDIC.Remove strRST
    > objDIC.Add strRST, "3"
    > Else
    > objDIC.Add strRST, "2"
    > End If
    > objRST.MoveNext
    > Loop
    > Set objRST = Nothing
    > '*
    > '* Report
    > '*
    > Call SortDictionary(objDIC,1)
    > For Each strDIC In objDIC
    > strWSE = strWSE & objDIC.Item(strDIC) & " : " & strDIC & vbCrLf
    > Next
    > WScript.Echo strWSE
    > '*
    > '* Destroy Objects
    > '*
    > objAD1.Close
    > Set objAD1 = Nothing
    > objAD2.Close
    > Set objAD2 = Nothing
    > Set objDIC = Nothing
    >
    > Function SortDictionary(objDict,intSort)
    >
    >[/ref]
    '*================================================ ========================== 
    '*================================================ ========================== 
    StrComp(arrDict(j,intSort),arrDict(k,intSort),vbTe xtCompare) 
    > strKey = arrDict(j,dictKey)
    > strItem = arrDict(j,dictItem)
    > arrDict(j,dictKey) = arrDict(k,dictKey)
    > arrDict(j,dictItem) = arrDict(k,dictItem)
    > arrDict(k,dictKey) = strKey
    > arrDict(k,dictItem) = strItem
    > End If
    > Next
    > Next
    > '*
    > '* Dictionary Clear
    > '*
    > objDict.RemoveAll
    > '*
    > '* Dictionary Build
    > '*
    > For j = 0 to (i - 1)
    > objDict.Add arrDict(j,dictKey), arrDict(j,dictItem)
    > Next
    > End Function
    >[/ref]

    To identify only the differences, comment out the following line:

    objDIC.Add strRST, "3"

    Lines prefixed with "1" are only in the 1st table.
    Lines prefixed with "2" are only in the 2nd table.


    McKirahan Guest

  10. #10

    Default Re: Want to compare databases

    Another thing that might help is a compact & repair operation on both
    databases. I know I've seen some performance issues resolved by doing
    that before with access.

    Kevin Ingram wrote:
     

    joker Guest

Similar Threads

  1. Compare 2 PDF
    By Claus_Allgeier@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 2
    Last Post: December 10th, 02:08 PM
  2. compare
    By Piet in forum MySQL
    Replies: 3
    Last Post: April 9th, 01:57 AM
  3. How do I compare two tables?
    By Lord Merlin in forum ASP Database
    Replies: 3
    Last Post: July 20th, 11:50 AM
  4. Federated Databases, joins across databases etc
    By Benjamin Stewart in forum IBM DB2
    Replies: 2
    Last Post: August 1st, 03:05 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139