DAO too slowww with SQL Backend

[love4god]

I have an accounting/order/inventory application written in Access97 with a SQL 6.5 back end. The problem is, all of the programming was done in DAO with a small set of test data. Once we went live, after data converting 100's of thousands of records from an old FoxPro DOS system, the app crawls.

I've been brushing up on ADO and Transact-SQL but I'm very new to it. I can access records quicker for reports and snapshot type queries. But what do I do when a form is bound to a giant table (100,000+ records)? Or, more likely, a dynamically created query based on that table. Is an ADO recordset updateable? Should I pass through a stored procedure to create a SQL view and link to that?

I don't have many answers but I'm capable of learning the code if I'm pointed in the right direction. Any ideas on what I should be trying to do here?

Thanks

[JHausmann]

Whee, this one can be fun. There's so many places where things can affect you.

1)Indexing on the server. Make sure you have the fewest amount of indices possible that reflect the way you retrieve the data. Make sure you take advantage of clustered indexes.

2) Networking. Make sure your Access database(s) ( I assume you've got linked SQL Server tables in it) resides on the user's machine. It will make a big difference.

3) SQL Server. Make sure your server has tons of memory. Make sure there's plenty of disc space for tempdb (or better yet, put tempdb in memory). Make sure you update statistics regularly (if they're not, queries that were once fast get slow in an instant) on the tables you change.

Your specific questions:

Bound controls on a big table. Your best bet is to populate the fields yourself -or- make sure the datacontrol for the fields uses specific SQL to control the amount of data retrieved (there's no point getting 100,000 records if the user only needs 10).

Yes, an ADO recordset is updatable.

I'd need to know more on why you think you need a view before I'd advise using one.