Reducing Round Trips
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/roundtrips.asp>http://www.sqlservercentral.com/columnists/awarren/roundtrips.asp
-
Good article. One thing to keep in mind is this depends on your client application. We use Cold Fusion and the multiple result sets don't always work. also, sometimes two less complex queries beat one complex one, though not always.
As with most things, it depends.
Steve Jones
-
I dont know anything about CF, thanks for that! Definitely multiple resultsets is not always a good solution - just an idea to keep mind. In the app we're building now we're doing a lot of simple selects, some get unioned into one rs for one purpose, others return as separate resultsets.
Andy
-
Great Article
This however brings up a question: Is it better to let the client or the server do most of the work? If you tax the server then it becomes a bottleneck.
The one operation I had in mind for example is sorting.
Also, what about static bind? Do I really need this type of verification?
-
My vote is to push what you can to the clients AS LONG AS IT DOES NOT substantially imopact the perceived performance of the client. You can upgrade the db hardware, but I prefer to push the load to the webservers (my clients) because it's much easier to add more web servesr than more db hardware. Less $$ too.
Steve Jones
-
Definitely depends on the client. Here where I work the minimum configuration is P400 with 128m RAM, more than enough for anything we could think of so far! Doing the work on the client will let you scale to more users generally. Always a balancing act. You dont want a server idling while your clients churn away, at the same time you dont want to load the server so hard that clients are just waiting for results.
Andy
-
Great idea, providing you are using ADO.
Being a SQL Server guy it is very easy to get into the Microsoft mentality. JFDI methodology.
We use a variety of Web Content Management Systems and they can only handle the bare minimum of ODBC functionality. Certainly not multiple recordsets.
I have invented a whole new dictionary of swear words over a CMS that did not support OUTPUT parameters, including RETURN_VALUE!
-
Our whole design approach with our web applications is based on reducing round-trips and minimising the size of data being sent back and forth. We have come up with a number methods to achieve this. First and foremost we return all data from SQL Server in XML. Like the article suggests this allows us to build up mutiple 'select' statements within a single SP and return a single block of data. This means we can populate a form with one SP which includes all the data such as lookup info for combos, the parent record and related child recordsets if appropriate. Where we have to return complete recordsets - again we do this in the ADO XML format - we strip out the updates, deleted etc on the client app before posting them back to the server. If a user changes data then that changed data exists on the client - therefore in many cases there is no need to send it back to the server and then refresh the whole record - simply send back your changes and return only what you must - which may be nothing! We have found that this approach makes a huge difference to performance and even helps compartmentalise the structure of the database along with reducing the number of unnecessary SPs, not to mention the reduced impact on bandwidth and performance gain.
-
Interesting topic. However, one aspect that hasn't been mentioned is how this will impact the object oriented design of your applications. If you consider that the clients in this case are a set of application servers and the server is the DBMS server, then it seems as though you'd need to often trade off good, partitioned, object oriented design in order to reduce the number of DBMS server calls. For example, if you have a Person object with persisted data in the database and a Company that's also persisted in the same database, you'd want those objects to be in control of the structure of the underlying data. With this in mind, you'd also want those objects in control of the SQL commands that are used to instantiate/manipulate them. Granted, I could see providing some sort of control layer between the business objects and their underlying data such that you could do most of your instantiation (queries) all in one shot, but it seems that this would significantly complicate the design. Also, there are going to be other adhoc data needs that the business object will have during the course of use. I'm not sure it's practical/desirable in many cases to "funnel" those data requests through a single shot to the server. Lastly, making the individual connections should not be a concern -- connection pooling should help to reduce the overhead associated with this.
While performance is important, it usually isn't good to gain performance while compromising/complicating your software designs. As I've heard mentioned in the recent past, hardware is cheap compared to the cost of human-power required to maintain a poorly designed system.
-
Good article and comments.
Another way that applications will be able to mitigate the response issue (or lack thereof) will be delivered with .NET 2.0 (check out the article link). Though this doesn't involve reducing round trips, it is definately an improvement to perceived performance of an application.
-
This article was a light bulb going off for me back when it first came out. I had not grasped the concept of multiple recordsets in ADO, and using them improved our user-apparent speed dramatically. In our specific case, the client is a VB app that may be hundreds of miles away connecting to SQL Server over the internet. The time to make each round trip dwarfs the rest of the delays (prep time, query time, etc) combined when you are not local.
In one case, (Editing an Order), we would pull the customer info, then the trucker info, then the Order and Order Detail info in sequence. It was nicely "object oriented" with each piece of the user interface pulling it's own data and rather self contained. Nice theory, but the performance sucked. We simply rolled the actual creation of the recordsets into one pull with the NextRecordSet method and cut our "user-apparent form start time" almost by a factor of 4 to a very acceptable half second.
Thanks again, Andy.
Student of SQL and Golf, Master of Neither -
I just want to point out that the two posts by BobAtDBS & Keith, are not mutually exclusive. You could always have a generic get data object that will hold all of the data returned from the db in that one call, and have it as a sort of static object, so that your people object can "query" the object saying gimme the people data, thereby reducing the round trips (as if you keep it static (in C#) you don't have to requery the db), as well keep the readability/reusability of oop.
-
One thing that we do to reduce the round trips, is have sorting done on the webserver. I would cache the data, and then use the sort method in ADO.NET to sort it, thereby saving the round trips of each sort.
(I only cache the data for about 2 minutes so that new updates will show through)
-
Great article! I usually keep my stored procedures granular so they can be reused easier. To use the example from the article, I would implement it this way:
Create Proc usp_GetOrderTable @OrderID int as
select field1, field2, etc, from ordertable where primarykey=@OrderID
Create Proc usp_GetOrderDetails @OrderID int as
select field3, field4, etc, from orderdetails where foreignkey=@OrderID
Create Proc usp_GetMyData @OrderID int as
set nocount on
exec usp_GetOrderTable @OrderID
exec usp_GetOrderDetails @OrderID
This way, if a developers need to get just the order details or just the order header they can use the more granular procedure. I would be interested in seeing if this gets optimized any differently...
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply