Not Responding - Open Recordset

[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

 

[Norman Yuan]

Why do you use "TOP 100 PERCENT"?
Why you need SELECT more than 8 million rows in one shot?

 

[Guest]

Let me take the last comment one step futher...

Not only are you requesting nearly nine million records in the recordset but
you are requesting it be sorted as well? Given that its the primary key,
it's already sorted in that sequence because of the clustered index however I
think Sql Server will sort it anyway. Only ask for a sort if you relly need
it!

The main advise I can give you is to rethink the problem. Add indexes where
appropriate, perform selections based on indexes AND on smaller subsets. Put
the request in a stored procedure. Check the generated execution plan to see
where you can save yourself time.

I can think of very few cases where you would need/want an entire recordset
passed back to an Access ADP consisting of 9 million records. If it is batch
processing you are performing this is better handled in a stored procedure.
If it is for user access, perhaps allowing users search screens and execute a
stored procedure based upon the entered search criteria might help your cause.

If this is a shared network you are killing everyone with this data request.

I hope this helps.

 

[Robert Morley]

Not only are you requesting nearly nine million records in the recordset but

you are requesting it be sorted as well? Given that its the primary key,
it's already sorted in that sequence because of the clustered index however I
think Sql Server will sort it anyway. Only ask for a sort if you relly need
it!

I thought that as well, but someone proved to me adequately that despite being stored in the same order as the clustered index, it
won't always be returned that way.


Rob

 

[Robert Morley]

As written, I'm not surprised you're having problems, though I wouldn't expect timeout problems in this instance. I think what you
probably meant to code for your loop was:

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
End If
rst1.MoveNext
rst2.MoveNext
Loop

An easier way to do something like this might be to use an OUTER JOIN instead. It's not quite the same functionality, technically,
but I believe it might be what you're actually after...especially if you make it a FULL OUTER JOIN...and probably do it a LOT faster
than trying to bring every last record over the network.

One of the uses of a FULL OUTER JOIN is to identify all non-matching field values in two recordsets. So in your case, it would
ignore all records that had misys.Field1 = misys4.Field1 and only return the remainder...this is what I suspect you're trying to
accomplish (but your existing code won't quite do), but I might be wrong in that assumption.

I don't remember the exact syntax for a FULL OUTER JOIN off the top of my head (cuz I've only used it about two or three times), but
if you think that approach would be useful, let me know and I can set up some test tables tomorrow and get back to you.



Rob

 

[Guest]

Elegant! Even better than the SP route. Thanks a million...

 

[Robert Morley]

Glad I could be of service.