I have a large VB 6.0 application running with Jet 4.0 Access DB. I wish to move the DB to MSDE and then SQL. For testing, I installed MSDE on another computer in a peer to peer network running XP sp1, all firewalls turned off, loaded up the DB from Access into MSDE2000A -- all without problem. (I should add the obvious fact I am new to using SQL server.) MSDE seems to be running fine, SQL database seems clean.
What I find is that with a relatively small test DB, running just a single instance of the application (no issue of MSDE throttle), query response from MSDE is taking several seconds (4-5 second lag) longer than response from the Access DB, which runs extremely fast. The lag seems to come with establishing connection. I am using SQL password instead of NT security. It seems the process is perhaps lagging in the user access authorization. But maybe I am missing someting else.
Test are with the Access DB and MSDE installed on the same network drive, running the same application from the same peer networked computer, and the same queries syntax -- only changing configuration of connection for each (SQL vs Jet 4).
Any clues as to what may be going on? I would appreciate any pointers on what to try fixing/changing.