Performance Increase ODBC.mdb ==> ADO.SQL Server?

[(PeteCresswell)]

I've got a little app that runs pretty well on C:.

Loads eight subforms as the user walks a TreeView and takes about
a quarter of a second to do each load.

Moving it to my dinky little home LAN (10 mb, almost zero
traffic), I see the times rise to .5 or .6 seconds per load.

Running it on the client's LAN tonite (way after COB, presumably
very low traffic), I'm getting .7 to .8 seconds per load.

But during the day, it's pretty miserable. Tomorrow will give
the numbers - since I just put the time logging code in
tonite.... but I expect times well over a second and maybe up to
2 seconds per load.

The Question:
--------------------------------------------------------------
Is there any reason to hope that moving the back end to SQL
Server will mitigate the performance issue?
--------------------------------------------------------------

ADO, one trip to the server for each screenful in a multi-file
stream.

The reason I'm asking is that my only experiences where I could
compare before/after .MDB/SQL Server have been conversions where
it was mandated that we would just stay with ODBC and pretend the
SQL Server tables were .MDB tables. In those cases, I'd say
performance diminished substantially rather than improved.

Also, my image of SQL Server and C/S DBs in general has been of
something whose performance scales well - but which aren't that
quick with a low user count. A little like a VW Golf (.MDB) vs
two-axle dump truck (SQL Server).... the Golf runs rings around
the dump truck until it's time to transport a few tons of crushed
rock.

OTOH, rightly or wrongly I'm laying a lot of my own problem on
LAN traffic - given the steep (albeit yet-to-be documented in
exact numbers) increase in response time once the business day
begins.

The way this app is coded, when we load a "Thing" and it's
subforms, we run six append queries - each of which hits the back
end to extract some rows and writes same to work tables on C: -
which the screen is bound to.

Intuitively, it doesn't sound like it would... but could using
SQL Server significantly reduce the number of bits I pull over
the LAN?

 

[AnandaSim]

But during the day, it's pretty miserable. Tomorrow will give
the numbers - since I just put the time logging code in
tonite.... but I expect times well over a second and maybe up to
2 seconds per load.

The Question:

Speaking generally, SQL Server will delegate data retrieval
computations to another machine. Not the client PC. However, once the
data is retrieved, you have to now throw the data to the client PC.
The the client PC has to digest the data and populate the screen.

You will see some improvement in speed by delegating the retrieval
computations but if you throw a volume of data larger than the
bandwidth that each of the PCs can grab during the day, you will still
have bottlenecks.

Try paging your data or partitioning each screen - I know, that means
re-design.

 

[PeteCresswell]

Try paging your data

Can somebody explain "paging"?