Print Page | Close Window

BOS performance worse than direct connection

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1764
Printed Date: 28-Mar-2024 at 8:02am


Topic: BOS performance worse than direct connection
Posted By: agrogers
Subject: BOS performance worse than direct connection
Date Posted: 28-Apr-2010 at 12:54am
We implemented the BOS on our server not long ago expecting to see a speed improvement when retrieving data.  Up until then we had been connecting directly to the remote SQL server.  We have done some tests now between the two connection methods and it turns out that it is taking longer to grab the data *with* the BOS.

For example, a fairly complex form takes 20 seconds without the BOS to load and 29 seconds with the BOS.
Another much simpler form (when pulling back a lot of records) takes 25 seconds without the BOS and 26 second with it.

What we were expecting to see was a significant improvement due to the 10 to 1 compression of data that was coming from the BOS. 

So I am guessing we have configured something quite badly/wrongly and wondering whether someone could give us some pointers on where to start looking.  For starters, is there a switch to enable/disable compression when using the BOS?  Or is this likely to be a server performance issue rather than a data issue?

Thanks
Andrew



Replies:
Posted By: ting
Date Posted: 28-Apr-2010 at 5:45pm
What kind of connection do you have to the database?  Over local area networks, the BOS is unlikely to reduce latency because the bandwidth is already very high, the network latency is low, and there is an extra step of object hydration, population, and compression which has to be performed on the server in order to send it to the client.
 
A common source of performance problems actually has little to do with bandwith, but with the per query latency for a large number of small queries performed serially.  This happens freqently when you have a master-detail grid with a nested binding.
 
For example, if I'm showing a grid of Orders, and I want to display the Customer Name for each Order, I will bind to Order.Customer.Name in one of the columns (in a well normalized schema).  When the grid is displayed, it will navigate one-by-one from each Order to its Customer to get the Name.  That's one query for each row.  The situation gets worse if I have other nested properties that I want to display (such as the Shipping Address).
 
So, while you may have only issued one query explicitly (that for the Orders).  You may be causing hundreds (or thousands) of queries to be issued in order to get the properties on the related objects.
 
To solve this, use a span query, and when you fetch the Orders, you get the Customers and ShippingAddresses in the same trip to the server.  This can make huge differences in performance.
 
Another common problem is query inversion.  I won't go into detail here, but if you have a large subquery, in order to cache the query, DevForce has to perform a query inverson and retrieve the objects from the subquery as well as the primary query.  If the subquery results in a large number of objects (e.g. all products < $100) this will be a problem.  You can suppress query inversion which will prevent this from being cached and speed it up.
 
Hope this helps!
 
 


Posted By: agrogers
Date Posted: 28-Apr-2010 at 9:20pm
Thanks Ting for your detailed reply.  I am talking with my developers about your suggestions.  The database is running over a WAN connection so we are only getting Australian ADSL speeds - a lot slower than 10Mb.

Is it possible to disable the compression that the BOS is doing to eliminate that as a cause of the slowness?

Given that the BOS and the Non BOS connections are both over the wire, i would have guessed that the spanning  issues with small queries would be common to both scenarios.

Thanks
Andrew


Posted By: ting
Date Posted: 29-Apr-2010 at 2:55pm
Based on our experience, compression has never been a bottleneck.  However, if you look at the client and server configuration files, you may be able to remove the compression provider from the network stack.  This will be different depending on whether you are using remoting or WCF, and needs to be done to both the client and server-side configs.
 
The first thing I would do is to look at the issued queries in the traceviewer, debug log, or SQL Profiler.  You may see something obvious that will lead you to the problem (e.g. hundreds of unexpected queries - one each for a Customer of a given Order, or an expansive query inversion that would result in 100,000 rows being returned).  You can also write additional timing information to the debuglog to help narrow down the source of the problem.
 
It is also extremely useful to isolate the operation in a small test case with no/minimal UI to identify the cause.
 
Once you have something isolated, if you give me a description of the operation and the number & size of objects being returned, I can suggest some other best practices or optimizations.
 



Print Page | Close Window