数据库比较 [英] Database comparing

问题描述

大家好！


我有一些问题，也许有人可以帮助我...

我写表比较的脚本，但是它很有效slovly。有脚本，任何人都可以提供一些提示如何使这个过程更快，如果有可能在VB中。

想法是比较两个表，如果有一些区别，则写在第三个表中。有些表包含超过200 000行，每行包含大约30个参数（列）。

*********************** *************************** *********************** ********************

选项比较数据库


Sub Parametru_Izmainas（）

Dim bssSiemens作为数据库

Dim rstTableNew作为Recordset，rstTableOld作为Recordset，rstParamChange作为记录集

Dim rstKonf作为记录集

Dim n As long，tx As Integer，as As Integer>
Dim fname1 As String，fname2 As String，fname3 As String，fname4 As String

Dim TableNew As Variant，TableOld As String，TableList As Variant

Dim strCriteria As String，strCriteriaSect As String

Dim NewParam As Variant，OldParam As Variant

Set bssSiemens = CurrentDb

设置rstParamChange = bssSiemens.OpenRecordset（" Izmainas"，dbOpenDynaset）

设置rstKonf = bssSiemens.OpenRecordset（" Konfiguracija&q uot;，dbOpenDynaset）


对于a = 2到5

''如果a = 1那么

''TableList =数组（Bsc）

ElseIf a = 2然后

TableList = Array（" Btsm"）

ElseIf a = 3 Then

TableList = Array（" Bts"）

ElseIf a = 4然后

TableList = Array（" AdjC"）

ElseIf a = 5然后

TableList =数组（" Chan"）

结束如果


For TableList中的每个TableNew

TableOld = TableNew& 旧

设置rstTableNew = bssSiemens.OpenRecordset（TableNew）

设置rstTableOld = bssSiemens.OpenRecordset（TableOld，dbOpenDynaset）

如果rstTableNew .RecordCount = 0然后GoTo 300

rstTableNew.MoveFirst

fname1 = rstTableNew.Fields（0）.Name

fname2 = rstTableNew.Fields（ 1）.Name

fname3 = rstTableNew.Fields（2）.Name

fname4 = rstTableNew.Fields（3）.Name

fname5 = rstTableNew.Fields（4）.Name

rstTableNew.EOF = False

NewParam = rstTableNew.GetRows（1）

id1 = NewParam （0,0）

id2 = NewParam（1,0）

id3 = NewParam（2,0）

id4 = NewParam（3） ，0）

id5 = NewParam（4,0）


如果a = 2那么

strCriteria =" [" ; &安培; fname1& "] =" &安培; id1& " AND [" &安培; fname2& "] =" &安培; id2

ElseIf a = 2然后

strCriteria =" [" &安培; fname1& "] =" &安培; id1& " AND [" &安培; fname2& "] =" &安培; id2& " AND [" &安培; fname3& "] =" &安培; id3

ElseIf a = 4然后

strCriteria =" [" &安培; fname1& "] =" &安培; id1& " AND [" &安培; fname2& "] =" &安培; id2& " AND [" &安培; fname3& "] =" &安培; id3& " AND [" &安培; fname4& "] =" &安培; id4

ElseIf a = 4然后

strCriteria =" [" &安培; fname1& "] =" &安培; id1& " AND [" &安培; fname2& "] =" &安培; id2& " AND [" &安培; fname3& "] =" &安培; id3& " AND [" &安培; fname4& "] =" &安培; id4& " AND [" &安培; fname5& "] =" &安培; id5

Else

strCriteria =" [" &安培; fname1& "] =" &安培; id1


结束如果

strCriteriaSect =" [bsc] =" &安培; id1& " AND [btsm] =" &安培; id2& " AND [bts] =" &安培; id3

rstKonf.FindFirst strCriteriaSect

rstTableOld.FindFirst strCriteria


如果rstTableOld.NoMatch那么

rstParamChange.AddNew

rstParamChange！Date = Date - 1

rstParamChange！id1 = id1

rstParamChange！id2 = id2

如果a = 3那么rstParamChange！id3 = id3

如果a = 4那么rstParamChange！id3 = id3

如果a = 4那么rstParamChange！id4 = id4

如果a = 5那么rstParamChange！id3 = id3

如果a = 5那么rstParamChange！id4 = id4

如果a = 5那么rstParamChange！id5 = id5

rstParamChange！Table = TableNew

rstParamChange.Update

rstParamChange.Requery

GoTo 100
Else

OldParam = rstTableOld.GetRows（1）

对于n = 0到rstTableNew.Fields.Count - 1

如果NewParam（n，0）= OldParam（n，0）然后

tx = 1


否则

如果IsNull（NewParam（n，0））和IsNull（OldParam（n，0））然后GoTo 200

rstParamChange.AddNew

rstParamChange！SectorName = rstKonf.Fields（" SectorName"）

rstParamChange！日期=日期 - 1

rstParamChange！id1 = NewParam（0,0）

rstParamChange！id2 = NewParam（1,0）

rstParamChange！id3 = NewParam（2,0）

rstParamChange！id4 = NewParam（3,0）

rstParamChange！id5 = NewParam（4,0）

rstParamChange！Table = TableNew

rstParamChange！Parameter = rstTableNew.Fields（n）.Name

rstParamChange！new = NewParam（n，0）

rstParamChange！old = OldParam（n，0）

rstPar amChange.Update

rstParamChange.Requery

结束如果


$

下一页n&br >
结束如果


100

Loop


$

下一页

下一页

结束Sub

解决方案

我提前道歉，因为我还没读过通过你的代码详细了解。但我想首先回答几个问题。

• 你能发布表定义吗？我想我们需要了解索引，特别是。
• 您是在尝试查找整个记录是否相同，或者您是否有兴趣识别具有相同关键字段但是具有相同关键字段的记录在其他领域有所不同吗？
• 您是否尝试过使用随Access提供的向导来构建查找重复查询？

我要重新发布您的带有标签的代码，以提高可读性。我已经强调代码中的一些If / ElseIf条件似乎没有意义。你可以检查一下吗？

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

我提前道歉，因为我还没有详细阅读你的代码。但我想首先回答几个问题。

• 你能发布表定义吗？我想我们需要了解索引，特别是。
• 您是在尝试查找整个记录是否相同，或者您是否有兴趣识别具有相同关键字段但是具有相同关键字段的记录在其他领域有所不同吗？
• 您是否尝试过使用随Access提供的向导来构建查找重复查询？

我要重新发布您的带有标签的代码，以提高可读性。我已经强调代码中的一些If / ElseIf条件似乎没有意义。你可以检查一下吗？

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

也许我需要找另一个脚本来执行此操作......？！

Hi everyone!

I have some questions, maybe someone can help me...
I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this process faster and if it is possible in VB.
The idea is to compare two tables and if there is some distinction, then is written in third table. Some tables contains more than 200 000 rows and each row contain approximatelly 30 parameeters (columns).
************************************************** *******************************************
Option Compare Database

Sub Parametru_Izmainas()
Dim bssSiemens As Database
Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
Dim rstKonf As Recordset
Dim n As Long, tx As Integer, a As Integer
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim TableNew As Variant, TableOld As String, TableList As Variant
Dim strCriteria As String, strCriteriaSect As String
Dim NewParam As Variant, OldParam As Variant
Set bssSiemens = CurrentDb
Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)

For a = 2 To 5
''If a = 1 Then
'' TableList = Array("Bsc")
ElseIf a = 2 Then
TableList = Array("Btsm")
ElseIf a = 3 Then
TableList = Array("Bts")
ElseIf a = 4 Then
TableList = Array("AdjC")
ElseIf a = 5 Then
TableList = Array("Chan")
End If

For Each TableNew In TableList
TableOld = TableNew & "Old"
Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
If rstTableNew.RecordCount = 0 Then GoTo 300
rstTableNew.MoveFirst
fname1 = rstTableNew.Fields(0).Name
fname2 = rstTableNew.Fields(1).Name
fname3 = rstTableNew.Fields(2).Name
fname4 = rstTableNew.Fields(3).Name
fname5 = rstTableNew.Fields(4).Name
Do While rstTableNew.EOF = False
NewParam = rstTableNew.GetRows(1)
id1 = NewParam(0, 0)
id2 = NewParam(1, 0)
id3 = NewParam(2, 0)
id4 = NewParam(3, 0)
id5 = NewParam(4, 0)

If a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2
ElseIf a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4 & " AND [" & fname5 & "]=" & id5
Else
strCriteria = "[" & fname1 & "]=" & id1

End If
strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
rstKonf.FindFirst strCriteriaSect
rstTableOld.FindFirst strCriteria

If rstTableOld.NoMatch Then
rstParamChange.AddNew
rstParamChange!Date = Date - 1
rstParamChange!id1 = id1
rstParamChange!id2 = id2
If a = 3 Then rstParamChange!id3 = id3
If a = 4 Then rstParamChange!id3 = id3
If a = 4 Then rstParamChange!id4 = id4
If a = 5 Then rstParamChange!id3 = id3
If a = 5 Then rstParamChange!id4 = id4
If a = 5 Then rstParamChange!id5 = id5
rstParamChange!Table = TableNew
rstParamChange.Update
rstParamChange.Requery
GoTo 100
Else
OldParam = rstTableOld.GetRows(1)
For n = 0 To rstTableNew.Fields.Count - 1
If NewParam(n, 0) = OldParam(n, 0) Then
tx = 1

Else
If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
rstParamChange.AddNew
rstParamChange!SectorName = rstKonf.Fields("SectorName")
rstParamChange!Date = Date - 1
rstParamChange!id1 = NewParam(0, 0)
rstParamChange!id2 = NewParam(1, 0)
rstParamChange!id3 = NewParam(2, 0)
rstParamChange!id4 = NewParam(3, 0)
rstParamChange!id5 = NewParam(4, 0)
rstParamChange!Table = TableNew
rstParamChange!Parameter = rstTableNew.Fields(n).Name
rstParamChange!new = NewParam(n, 0)
rstParamChange!old = OldParam(n, 0)
rstParamChange.Update
rstParamChange.Requery
End If

200
Next n
End If

100
Loop

300
Next
Next a
End Sub

解决方案

I apologise in advance, as I haven''t read through your code in detail yet. But I would like to get a couple of questions answered first.

• Can you post the table definition? I think we''ll need to know about indexes, especially.
• Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
• Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?

I''m going to re-post your code with tags around it for readability. I''ve underlined some of the If/ElseIf conditions in the code which don''t seem to make sense. Can you check them please?

Expand|Select|Wrap|Line Numbers

I apologise in advance, as I haven''t read through your code in detail yet. But I would like to get a couple of questions answered first.

• Can you post the table definition? I think we''ll need to know about indexes, especially.
• Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
• Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?

I''m going to re-post your code with tags around it for readability. I''ve underlined some of the If/ElseIf conditions in the code which don''t seem to make sense. Can you check them please?

Expand|Select|Wrap|Line Numbers

Maybe I need to find another script to do this...?!

这篇关于数据库比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！