Accelerating MS SQL Client

[Joe]

Does a product exist that might solve the following problem?

I have an application developed in VB6 that accesses data residing on a
central Microsoft SQL server in our datacenter. While I would argue
that this application stinks, I have been assigned to deploy it to the
laptops of about 100 people. These people travel to sites and connect
to the SQL server using a VPN connection and MDAC 2.8. In testing, I
found that the performance of this application suffers when connecting
to SQL via VPN, and since this application seems to hammer SQL when
loading menus in the least optimum way possible, the program will hang
while waiting for data.

Is there software I can install on the users' laptops that might
"accelerate " the connection to the SQL server? Perhaps this software
might be an alternative to the Microsoft-provided SQL client? I saw a
product called ZCache that seems to provide some caching functionality
for Oracle and MySQL databases, but nothing for MS SQL.

[Erland Sommarskog]

Re: Accelerating MS SQL Client

Joe (josephtermine@ hotmail.com) writes:[color=blue]
> Does a product exist that might solve the following problem?
>
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
>
> Is there software I can install on the users' laptops that might
> "accelerate " the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.[/color]

That sounds like about an impossible thing for me. Then again, if there is
such a product for Oracle or MySQL, I guess it should be possible for
MS SQL Server. Yet then again, the accellerating effect of that product
may apply only to certain usage patterns.

Of course, if one analyses what this product is upto, it is possible
that you could implement a local cache, but it would certainly be
far cheaper to fix the application itself. It sounds if there are
tons of roundtrips and server-side cursors in it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Andy O'Neill]

Re: Accelerating MS SQL Client

"Joe" <josephtermine@ hotmail.com> wrote in message
news:1108413195 .029686.70090@z 14g2000cwz.goog legroups.com...[color=blue]
> Does a product exist that might solve the following problem?
>
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
>
> Is there software I can install on the users' laptops that might
> "accelerate " the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.
>[/color]

If you just connect VB6 bound datagrids to sql server and that's your lot
then you'll potentially get a hell of a lot of IO.
Plus the ones in the box didn't work so well, maybe they're patched or third
party or whatever.
It does sound that the app was developed without considering what's likely
over something like a 28k connection.

I've used vpn.
It can be very slow.
It can also have a limit on concurrent users, depending on how you're
vpn-ing.

Personally, I would have thought caching some data in a local database of
some sort a must.
At least for the sort of app this sounds like.
Whether that would best be an access mdb, full msde installation or whatever
would depend on the details.
vb.net and xml on the laptops would be another alternative but quite likely
involving a complete rewrite.

--
Regards,
Andy O'Neill

[Joe]

Re: Accelerating MS SQL Client

I suspect that this application does use the VB6 grids out of the box,
and these are known to be resource-intensive. The newer VB.NET grids
seem to perform better in disconnected environments.

Anyway, there doesn't seem to be much we can do if we don't have access
to the VB6 source code.

Might there be a VPN accelerator we could try? Would this help us much?

[Joe]

Re: Accelerating MS SQL Client

There are tons of round-trips with this application, but as we don't
have access to the VB6 source (this is an off-the-shelf product) there
might not be much we can do to fix the inefficiencies or implement a
local cache. There's a lot to think about.

[Erland Sommarskog]

Re: Accelerating MS SQL Client

Joe (josephtermine@ hotmail.com) writes:[color=blue]
> There are tons of round-trips with this application, but as we don't
> have access to the VB6 source (this is an off-the-shelf product) there
> might not be much we can do to fix the inefficiencies or implement a
> local cache. There's a lot to think about.[/color]

The one thing I can think would be replication. Assuming that you can
update from the app, that would be merge replication.

And, no, I have experience of merge replication. (And not much of other
sorts of replication either.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: Accelerating MS SQL Client

On 15 Feb 2005 10:33:18 -0800, Joe wrote:
[color=blue]
> I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.
>
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.
>
> Might there be a VPN accelerator we could try? Would this help us much?[/color]

Perhaps this could help?

http://sqlrelay.sourceforge.net/

It looks complicated, but it might suit you.

Replication of the database to a local MSDE might be another choice.

[Andy O'Neill]

Re: Accelerating MS SQL Client

"Joe" <josephtermine@ hotmail.com> wrote in message
news:1108492398 .045929.319930@ l41g2000cwc.goo glegroups.com.. .[color=blue]
>I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.[/color]

I used to use flexgrid to display data, user clicks on a row and gets a
bunch of text/combo boxes to work on it .
I worte my own code to update.
This was because of the bugs in the datagrid.
You might want to investigate those.
I forget but there are circs when updates just didn't happen.

WIth dotnet I use the datagrids and they're good.
The dataadaptor and (disconnected) dataset functionality is very useful.
There would be issues meant added code in an app where a salesman wants to
use the thing completely disconnected from the database.
[color=blue]
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.[/color]

Well.... that's a mistake someone's made right there.
[color=blue]
>
> Might there be a VPN accelerator we could try? Would this help us much?
>[/color]

No idea mate.

--
Regards,
Andy O'Neill