
VERY URGENT - Visual basic 4.0 - ODBC(SQL Server) SPEED issues
A couple questions arise:
1) How large is the access database?
2) Are you using the data control or RDO?
3) How are you accessing the access database, are you using Query
Defs?
4) How are you writing to the SQL server, using stored procedures or
dymanic sql?
5) How much memory is in the box that's running your app?
My inital guess is the problem lies in accessing the access database.
Access is NOT sql server, all processing of data must occur on the box
in which your app is running. What I think is happening is that your
app is pulling down ALL the rows from the table to the local box,
processing your query, then giving you the results. Since your
running on a 9600 baud link, if the table is of any size your going to
see pathetic performance. This sounds like the most plausible
explaniation i can think of, based on the information at hand.
Another possible problem could be the box your app is running on. DAO
requires a very large memory footprint, at least 16mb should be on the
box the app is running on. When your app runs is it swapping alot?
Quote:
>Hi Guys,
> I am using VB 4.0 , ODBC (Sql Server Driver) and Sql Server 6.5,
>Windows NT 3.51. I am using Database and Recordset(dynaset) objects in
>my program. The performance of my apllication is not upto the mark.
>The program running on windows NT 3.51 client gets data from the MS
>Access Database in the another Windows NT 3.51 client situated in a
>geographically different location throught the RAS-leased
>Phone-line(baud rate- 9600 Kbps), inserts data into SQL server 6.5 which
>is on Windows NT 3.51 Server which is on a different machine in the
>same LAN as that of the machine in which my program is running.The whole
>process takes more than a minute for each of the record(of size 200
>bytes). Since the software is ready for launch in the next few days I
>request you guys to consider this problem immediately and give me some
>solution.
>We suspected the baud rate to be the cause for the sloweness and we
>bench marked by pulling a 500k file from the remote machine using the
>win nt file manager, the result was satisfactory(4 minutes). So this
>implies that the slowness is not because of the slow baud rate and just
>beacause of the way we access the database or ODBC/ JET issues.
>Thanking you guys in advance,
>R.Anand