SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reducing Round Trips


Reducing Round Trips

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11615 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62699 Visits: 19111
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
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11615 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
ramosj
ramosj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
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?



Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62699 Visits: 19111
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
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11615 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7613 Visits: 3285
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!

LinkedIn Profile

Newbie on www.simple-talk.com
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11615 Visits: 2730
Fair point!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Guy Avery
Guy Avery
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 9
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.
Keith Raker
Keith Raker
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search