Accelerating MS SQL Client

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.Joe (josephtermine@.hotmail.com) writes:
> 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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108413195.029686.70090@.z14g2000cwz.googlegro ups.com...
> 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.

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|||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?|||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.|||Joe (josephtermine@.hotmail.com) writes:
> 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.

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Feb 2005 10:33:18 -0800, Joe wrote:

> 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?

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.|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108492398.045929.319930@.l41g2000cwc.googlegr oups.com...
>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.

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.

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

Well... that's a mistake someone's made right there.

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

No idea mate.

--
Regards,
Andy O'Neill

Accelerating first call of Reports

Hi,
is there a possibility to accelerate the first call of a report?
It could take up to 1 minute on my Server. After I called a report the first
time, its going faster (~5 seconds).
Thanks and regards,
StefanThis is due to IIS, not Report Server specifically.
Go to IIS Manager, under Applocation Pools node, right click
"DefaultAppPool" in which the Reporting Server work process is running,
select properties. On "Performace" tag, you will see, by default, the app
pool will shut down if > being idle for 20 min. You can extend this time to
8x60min 480min, so that the app pool will not shut down for a regular
working day. However, the first report reader of the day, will hit the
delay. You may schedule a dummy report at beginning of a work day for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan" <stefan@.nospam.nospam> wrote in message
news:eIt4iTroHHA.1476@.TK2MSFTNGP03.phx.gbl...
> Hi,
> is there a possibility to accelerate the first call of a report?
> It could take up to 1 minute on my Server. After I called a report the
> first time, its going faster (~5 seconds).
> Thanks and regards,
> Stefan
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Bruce, hi Wei Lu,
thanks for your support.
Can you please give me an example, how to call a dummy report from a
batch-File.
Thanks and regards,
Stefan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:eZ%23UFCsoHHA.4516@.TK2MSFTNGP05.phx.gbl...
> This is due to IIS, not Report Server specifically.
> Go to IIS Manager, under Applocation Pools node, right click
> "DefaultAppPool" in which the Reporting Server work process is running,
> select properties. On "Performace" tag, you will see, by default, the app
> pool will shut down if > being idle for 20 min. You can extend this time
> to 8x60min 480min, so that the app pool will not shut down for a regular
> working day. However, the first report reader of the day, will hit the
> delay. You may schedule a dummy report at beginning of a work day for
> this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stefan" <stefan@.nospam.nospam> wrote in message
> news:eIt4iTroHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> is there a possibility to accelerate the first call of a report?
>> It could take up to 1 minute on my Server. After I called a report the
>> first time, its going faster (~5 seconds).
>> Thanks and regards,
>> Stefan
>|||Hello Stefan,
I did not have a sample yet. I would like to suggest you create a
application to use the web services to call the reports.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I run the following script in planned tasks and now everything runs fine.
Thanks,
Stefan
Option Explicit
Dim objIEA
Set objIEA = CreateObject("InternetExplorer.Application")
objIEA.Navigate
"http://localhost/reportserver?%2fReports2fMyDummyReport&rs:Command=Render"
objIEA.visible = true
While objIEA.Busy
Wend
objIEA.Quit
Set objIEA = Nothing
"Stefan" <stefan@.nospam.nospam> schrieb im Newsbeitrag
news:O%23d1KDopHHA.4632@.TK2MSFTNGP04.phx.gbl...
> Hi Bruce, hi Wei Lu,
> thanks for your support.
> Can you please give me an example, how to call a dummy report from a
> batch-File.
> Thanks and regards,
> Stefan
>
>

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause