G
Guest
On Tables, Imported from a TXT Flat file, with 8,805,141 Records - fields:
IDx (PK, Indexed), Field1 (nvarchar, 132) - this code takes 18 minutes to
open rst(n), or just errors out with a 'timeout'.
Migrated to Access 12 on Vista Ultimate, Dual Opteron, 2 GB RAM. Am used to
SQL 2000, Access 10, but they also are aborting with 'timeout'. The Vista
online debugger did not find any system errors, so 'sent me' to this site .
.. .
I have shrunk the database, and poked around with SSMS. I do notice that
Views run real slow or timeout. What techniques am I missing for working with
this large a dataset?
SAMPLE VB CODE:
Dim strSQL As String
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
strSQL = "SELECT TOP 100 PERCENT IDx, Field1 " _
& "FROM dbo.misys " _
& "ORDER BY IDx"
rst1.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
strSQL = "SELECT TOP 100 PERCENT IDx, Field1 " _
& "FROM dbo.misys4 " _
& "ORDER BY IDx"
rst2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do While Not rst1.EOF
If NOT (rst1!Field1 = rst2!Field1) Then
Debug.Print Now()
Debug.Print rst1!Field1
Debug.Print rst2!Field1
Debug.Print
Stop
rst1.MoveNext
rst2.MoveNext
End If
Loop
IDx (PK, Indexed), Field1 (nvarchar, 132) - this code takes 18 minutes to
open rst(n), or just errors out with a 'timeout'.
Migrated to Access 12 on Vista Ultimate, Dual Opteron, 2 GB RAM. Am used to
SQL 2000, Access 10, but they also are aborting with 'timeout'. The Vista
online debugger did not find any system errors, so 'sent me' to this site .
.. .
I have shrunk the database, and poked around with SSMS. I do notice that
Views run real slow or timeout. What techniques am I missing for working with
this large a dataset?
SAMPLE VB CODE:
Dim strSQL As String
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
strSQL = "SELECT TOP 100 PERCENT IDx, Field1 " _
& "FROM dbo.misys " _
& "ORDER BY IDx"
rst1.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
strSQL = "SELECT TOP 100 PERCENT IDx, Field1 " _
& "FROM dbo.misys4 " _
& "ORDER BY IDx"
rst2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do While Not rst1.EOF
If NOT (rst1!Field1 = rst2!Field1) Then
Debug.Print Now()
Debug.Print rst1!Field1
Debug.Print rst2!Field1
Debug.Print
Stop
rst1.MoveNext
rst2.MoveNext
End If
Loop