SQL Server Performance Forum – Threads Archive

Problems Updating Data to SQL Server 2000

Hi, i’ve developed a Inventory System in Visual Basic 6 with MS Access and works fine, and i’ve a server with windows 2003 server and SQL Server 2000 to update the data from MS Access Data Base (of the same Inventory System) using SQL by ODBC, my SQL database has 30mb of used space and like 14,000 records in one table (and growing up). I feel the connection too too too too slow, to update 1 record the system delay about 20 min lately <img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />. Im confused becuase i need the connection make faster. All other things like querys and all other is working fine, onlye the Local Application-ODBC SQL connection is too slow. My server is connected in a T1 Internet Connection. Please I need help, what i need to check or change? my vb code? Thanks a lot!

Slow updates through ODBC – which in Access means linked tables, right? – couple of items: (1) Bandwidth of connection between workstation and server needs to be pretty wide. Unless you use ADO or a pass-through query in DAO, the processing is done by the Jet engine, so all the data has to be retrieved from the server to the workstation. If you have insufficient bandwidth this just takes up a hell of a lot of time. (2) I would double-check whether the SQL Server table has a Primary Key – neither SQL Sever nor Jet can do much without one. While you’re at it, check if you have indexes for columns on which you’re filtering for the update. (3) If you are processing through a client-side recordset, you will get a terrible response time – I know, I’ve seen it arrive with the introduction of SQL 2000 – seems it wasn’t quite so bad with SQL 7.0, but that’s progress for you. Upload the ‘new values’ to a working table on SQL Server, and have a stored procedure handle the processing for you.

thanks guys! i found the problem in my vb code. Corvera Networks Mexico

]]>