Sproc performance over vpon

[Mark]

Hi...

This may not be the perfect group for this, but we're having performance
problems calling a sproc through ADO.Net when on the office vpn.

The sproc returns 3 result sets (8 rows, 1 row, and 12 rows) The total size
of the result set is ~150k, with the last result set being ~148k of it. I
exported the results from Sql Server Management Studio express to a
spreadsheet and then saved the spreadsheet. Not a precise measurement but
accurate enough to give a sense of scale.

Anyway, in the office, the whole sproc takes < 1 second. Over the vpn from
home, the same sproc takes > 1.2 minutes. This is causing a lot of timeouts
in our app.

The dba turned on tracing on the sql server side, and 99%+ of the time is
going to produce that last result set, but the stats he sees aren't breaking
it down between the query execution and network serialization.

The whole size is pretty small; we're just both flummoxed why the query is
taking so long.

Over the same vpn connection, I can browse 150k of websites in a small
fraction of that time.

Any hints on trying find out where the bottleneck is?

Thanks
Mark

 

[sloan]

Google
"Parameter Sniffing" "Sql Server"

and try one of the workarounds.

This is a guess, not a "Yeah, I know what it is exactly".

Also disable your anti virus "auto protect" as a test on the client as well.

 

[Cowboy \(Gregory A. Beamer\)]

What is causing the problem? My first guess would be resolving the server.
This could be sped up in a couple of ways. One is through an admin push of
the entries. Another is adding tables on the router.

If you cannot fix name resolution, consider putting the query on the
opposite side of the connection. What I mean by this is change the data
access methodology from a query in a windows form app to a call to a web
service. I think you will find that this works faster, as resolution of
servers is at a different level. The sproc can also be exposed as a web
service in SQL Server, if you are using 2005 or newer.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

 

[Mark]

Thanks to you and sloan for your answers. I hadn't heard of the "parameter
sniffing" issue before.

I should have made it more clear - the same sproc call with the same
parameters is behaving so differently depending on the network. I tried a
number of things in the parameter sniffing results, but it didn't make any
difference. I also tried disabling virus check, but that didn't help.

I'm using the ip address, not the server name, to connect so name resolution
isn't part of it.

If it *is* a data serialization issue (though ~150k doesn't sound like
*that* much data), how would it help to convert it to a web service?

Thanks
Mark

 

[Colbert Zhou [MSFT]]

Hello Mark,

1. If you install and use SQL Management Studio to connect to the server,
and run the sproc, do you still face the same performance issue?

2. If you write another little application using a very simple Select
statement, and test it against the server via VPN, do you have the same
performance issue?

These can help to troubleshoot whether the slow performance is caused by
ADO.NET or the codes implementation.

Based on my research on the net at this time, the following discussions
seem to be very similar issue as you have.
http://www.windows-tech.info/15/012e4200c358f09f.php
There, Garry said setting the max packet size to 1 had improved his ADO.NET
connection while on a VPN. So I think it is worth of a try.
http://support.microsoft.com/kb/244474


Best regards,
Colbert Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

 

[Mark]

Hi Colbert...

Yes, running the sproc from Sql Management Studio does have the same
performance degradation. Based on sloan's recommendation I looked up the
Parameter Sniffing articles (and proposed cures). I took the body of the
sproc, created a local variable and ran that, and I still faced the
performance degradation.

The sproc returns 3 result sets; the first 2 are very small (<1k each) and
they finish quite quickly. It's the last one with the ~148k result set that
takes so long.

I'm in the office today, so I'll have to try the kerberos setting over the
weekend.

Thank you for looking into this.

Mark

 

[sloan]

Well you can ~try this. Its a total guess.


Create a #temp table in the procedure.
Push your results into the #temp table.
Select from the #temp table


This is a total guess. You might want to post the query to the complexity
of the query cannot be seen.
But the below is worth a 15 minute attempt .... this would be for your query
#3.
Again. a total guess.


Here is a generic Northwind example to help you out:



set nocount on

if object_id('tempdb..#OrdersHolder') is not null

BEGIN

DROP TABLE #OrdersHolder

END

CREATE table #OrdersHolder

(

OrderID int,

CustomerID varchar(32) ,

OrderDate smalldatetime

)

INSERT INTO #OrdersHolder ( OrderID, CustomerID , OrderDate )

Select OrderID, CustomerID , OrderDate from dbo.Orders

Select OrderID, CustomerID , OrderDate from #OrdersHolder



if object_id('tempdb..#OrdersHolder') is not null

BEGIN

DROP TABLE #OrdersHolder

END

 

[Mark]

Thanks to you and Colbert for your answers.

I've tried both of them to no avail.

I probably should add in the detail that our office vpn is using CheckPoint
SecuRemote. I should also add that changing from my home office wireless to
a hard wire cut the execution time by about 25% (so now down to 45 seconds to
a minute, still about 40 times the speed of in office, but indicating the
bulk of this may just be in serialization and that 148k through this vpn
client is just more than it can handle...

Thanks
Mark

 

[sloan]

If its a "must have" situation and performance is critical..... then I'll
suggest WCF and passing serialized objects over the wire.

Here is my example I wrote up:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!158.entry

Its a dot net to dot net example. If you have a different kind of client,
WCF is good, but my example is not.

 

[Cowboy \(Gregory A. Beamer\)]

Thanks to you and sloan for your answers. I hadn't heard of the
"parameter
sniffing" issue before.

I should have made it more clear - the same sproc call with the same
parameters is behaving so differently depending on the network. I tried a
number of things in the parameter sniffing results, but it didn't make any
difference. I also tried disabling virus check, but that didn't help.

I'm using the ip address, not the server name, to connect so name
resolution
isn't part of it.

If it *is* a data serialization issue (though ~150k doesn't sound like
*that* much data), how would it help to convert it to a web service?

It would only help with the database, in that you could control connection
libraries and make sure things were working.

And, the app is less likely to have a problem hitting a web server (port 80,
which is almost always open AND easier name resolution, at least in
general).

The downside is you add an extra latency here, but the same web service can
be used for any variety of apps. And, it is extremely easy to wire into the
application.

As for the issue I was primarily focused on, it is probably name resolution,
which is different from parameter sniffing.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Blog: http://gregorybeamer.spaces.live.com
Twitter: @gbworld

*************************************************
| Think outside the box! |
*************************************************

 

[Paul]

First of a VPN is encypted so any data you send has to be encrypted. This
has a huge overhead. So if its fine on the local LAN maybe this is the
problem.

 

[Paul]

But this will perfrom far slower than direct DB access? Serialization?

Great for access over Port 80/443 rather than VPN but still much slower.

 

[sloan]

//but still much slower.//

You don't know until you try.

Especially if you use binary serialization.