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-