[AccessD] Access & SQL 2005 Performance: .Recordset clone search

Christopher Hawkins clh at christopherhawkins.com
Thu Sep 13 02:26:10 CDT 2007


Hey all.

I've got a real performance issue here.  A client of mine has an Access (front and back end) application.  We have migrated to a SQL 2005 back-end.  Most things work as well as the Access version did, but there is one thing in particular that has become markedly slower - a  jump-to navigation control using the old .RecordsetClone trick.  You know the one:  you lay a combobox on the form and bindit to a list of IDs, then add this code to the OnClick:

        Forms("frmBlah").Requery
        Forms("frmBlah").RecordsetClone.FindFirst "[RecID] = " & Me![cboFindRec]
        Forms("frmBlah").Bookmark = Forms("frmBlah").RecordsetClone.Bookmark

Anyway.  The table that this works against is bad news; it has about 200 fields and 5,000 records.  It's indexed for the most commonly-searched fields, but still.  The jump-to takes about 3 seconds in Access - which, IMO, is too slow to start with - but it now takes a whopping EIGHT SECONDS against SQL 2005.  

I'm not sure why this is.  Out of all the things that have stayed the same performance-wise, why is this the thing that slows down?

Has anyone had any similar experiences with migrating to SQL 2005 (or SQL in general)?

-Christopher-



More information about the AccessD mailing list