Saturday, December 11, 2010

The Cost of Round Trips To The Server

A database is not much without the applications that connect to it, and one of the most important factors that affects the application's performance is how it retrieves data from queries. In this essay we are going to see the effect of round trips on application performance.

This blog has two tables of contents, the Complete Table of Contents and the list of Database Skills.

Pulling 15,000 Rows

The test will pull 15,000 rows from a table. We do it three different ways and see which is faster and by how much.

Getting a Lot of Rows

The script below creates a table and puts 1 million rows into it. We want far more rows in the table than we will actually pull so that we can pull fresh rows on every pass through the test. It is deliberately crafted to spread out the adjacent values of the integer primary key. This is because, inasmuch as can control what is going on, we want every single row to be on a different page, so that in all tests the cost of retrieving the row is roughly the same and we are measuring only the effect of our retrieval methods.

The script can be run without modification in pgAdmin3, and with slight mods on MS SQL Server.

create table test000 (
    intpk int primary key
   ,filler char(40)
)


--  BLOCK 1, first 5000 rows
--  pgAdmin3: run as pgScript
--  All others: modify as required  
--
declare @x,@y;
set @x = 1;
set @y = string(40,40,1);
while @x <= 5000 begin
    insert into test000 (intpk,filler)
    values ((@x-1)*200 +1,'@y');

    set @x = @x + 1;
end

-- BLOCK 2, put 5000 rows aside 
--
select  * into test000_temp from test000

-- BLOCK 3, Insert the 5000 rows 199 more
--          times to get 1million altogether
--  pgAdmin3: run as pgScript
--  All others: modify as required  
--  
declare @x;
set @x = 1;
while @x <= 199 begin
    insert into test000 (intpk,filler)
    select intpk+@x,filler from test000_temp;

    set @x = @x + 1;
end

Test 1: The Naive Code

The simplest code is a straight loop that pulls 15,000 consecutive rows by sending an explicit query for each one.

# Make a database connection
$dbConn = pg_connect("dbname=roundTrips user=postgres");

# Program 1, Individual explicit fetches
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo "\nTest 1, using $x1 to $x2";
$timeBegin = microtime(true);
while ($x1++ <= $x2) {
    $dbResult = pg_exec("select * from test000 where intpk=$x1");
    $row = pg_fetch_array($dbResult);
}
$elapsed = microtime(true)-$timeBegin;
echo "\nTest 1, elapsed time: ".$elapsed;
echo "\n";

Test 2: Prepared Statements

The next command asks the server to prepare a statement, but it still makes 15,000 round trips, executing the prepared statement with a new parameter each time. The code looks like this:

# Make a database connection
$dbConn = pg_connect("dbname=roundTrips user=postgres");

# Program 2, Individual fetches with prepared statements
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo "\nTest 2, using $x1 to $x2";
$timeBegin = microtime(true);
$dbResult = pg_prepare("test000","select * from test000 where intpk=$1");
while ($x1++ <= $x2) {
    $pqResult = pg_execute("test000",array($x1));
    $row = pg_fetch_all($pqResult);
}
$elapsed = microtime(true)-$timeBegin;
echo "\nTest 2, elapsed time: ".$elapsed;
echo "\n";

Test 3: A single round trip

This time we issue a single command to retrieve 15,000 rows, then we pull them all down in one shot.

# Make a database connection
$dbConn = pg_connect("dbname=roundTrips user=postgres");

# Program 3, One fetch, pull all rows
$timeBegin = microtime(true);
$x1 = rand(0,199)*5000 + 1;
$x2 = $x1 + 14999;
echo "\nTest 3, using $x1 to $x2";
$dbResult = pg_exec(
    "select * from test000 where intpk between $x1 and $x2"
);
$allRows = pg_fetch_all($dbResult);
$elapsed = microtime(true)-$timeBegin;
echo "\nTest 3, elapsed time: ".$elapsed;
echo "\n";

Results

I ran this five times in a row, and this is what I got:

Naive 15,000 Prepared 15,000 One Round Trip
~1.800 seconds ~1.150 seconds ~0.045 seconds

Compared to the naive example, the set-oriented fetch of al 15,000 rows in a single shot ran 40 times faster. This is what set-oriented code does for an application.

While the prepared statement option ran faster than the naive option, the set oriented example still ran 25 times faster than the repeated prepared statements.

I also re-arranged the order of the tests, and the results were the same.

Does Server or Language Matter?

So this test was done using PHP against PostgreSQL, will other servers and client languages get different results? Given the same hardware, a different client language or server is going to have a different spread but the shape will be the same. Fetching all rows in a single shot beats the living frack out of round trips inside of loops in any client language against any server.

Putting It Into Use

The most obvious conclusion is that any query returning more than 1 row should return all rows as a set. The advantage is so stark with large row counts that it is worthwhile making this the default for our applications, unless we can find a very good reason not to. So what would the objections be?

One objection might go something like, "Ken, I see the numbers, but I know my app very well and we never pull more than 10-20 rows in a pop. I cannot imagine how it would matter at 10-20 rows, and I do not want to recode." This makes sense so I ran a few more tests with 20 and 100 rows, and found that, on my hardware, you need about 100 rows to see a difference. At 20 rows all three are neck-in-neck and at 100 the set is pulling 4 times faster than the prepared statement and 6 times faster than the naive statement. So the conclusion is not an absolute after all, some judgment is in order.

Another thing to consider is how many simultaneous reads and writes might be going on at any given time. If your system is known to have simultaneous transactions running regularly, then the complete fetch may be a good idea even if you do some tests for best-guess row count and the tests are inconclusive. The reason is that the test is a single user case, but multiple simultaneous users put a strain on the database, even when they are not accessing the same tables. In this case we want the application to play the "good citizen" and get in and out as quickly as possible to reduce strain on the server, which will improve the performance of the entire application, not just the portions optimized for complete fetches.

Another objection might be, "Well, my code needs to pull from multiple tables, so I cannot really do this. When we do -PROCESS-X- we go row by row and need to pull from multiple tables for each row." In this case you *definitely* need to go set oriented and pull all associated quantities down in a query with a JOIN or two. Consider this, if on your particular hardware the ratio of naive row-by-row to single fetch is 10, and you must pull from 2 other tables for each row, that means you are really running 30 times slower (ratio is 10 x 3 reads) than you could be.

A Final Note About PHP, Data Structures, and Frameworks

Back when dinosaurs ruled the Earth and there was no internet (outside of Universities, etc), the languages we used had specialized data structures that were tuned to database use. Compared to those older systems the newer languages born on the internet are more or less starving for such a data structure.

PHP gets by fairly well because its associative array can be used as a passive (non object-oriented) data structure that comes pretty close to what we had before.

I bring this up because the choice of a language and its support for a "fetch all" operation obviously impacts how well the conclusions of this post can be implemented. If your mapping tool has an iterator that absolves you of all knowledge of what is going on under the hood, it may be worthwhile to see if it is doing a complete fetch or a row-by-row.

67 comments:

Markus Winand said...

hi!

There is an excellent video describing that roundtrip issue to non-programmers.

The video covers some more performance issues, however,
here's a youtube link to that particular scene: Weekend shopping

I have covered that in my last year's post "Latency: Security vs. Performance"

luxury travel Spain said...

Very useful all these information you are sharing with us because i was looking for something like that, it would help us a lot in our business for sure, thanks!

BroddyAdams said...

I’m not that much of a online reader to be honest but your blogs really nice, keep it up! I'll go ahead and bookmark your website to come back later on. Many thanks oracle platinum support

Yashasvi said...

Microsoft office 365 setup with key is an application software. It is used in most of the businesses and organizations Install office setup on your Pc's to get best advantage of it. mostly used tasks are ms-word, Ms-excel and Ms-powerpoint.

Yashasvi said...

Microsoft office 365 setup is the complete bundle of Microsoft programs as it takes to the a variety of jobs, servers, and affiliations like PowerPoint, Excel, Word, Outlook, Publisher, OneNote, and Access.

Yashasvi said...

office setup is the best programming which is broadly utilized in globe .It is a dazzling association that causes you release your best contemplations, complete things, and stay related on the go.for more subtleties visit:office setup with product key today.

Yashasvi said...

Roku enables you to watch free and paid video content on your TV by means of the Internet. For Activating Roku, go to roku account setup record enter Roku com connection code showed on Roku TV.

Yashasvi said...

AVG Retail is the best Antivirus programming which is accessible on download and install avg ultimat , gives the total suite of highlights which can verify your computerized on the web and disconnected work of the framework or gadgets.

Yashasvi said...

Avg is a well-known in the protection fields in the software world. install avg with license number The reason behind that it is commonly used among various users its is the tool against the malwares and viruses. It scans for virus and malware.

Yashasvi said...


Norton prevents your computer from virus-infected emails and instant messages from spreading viruses and malwares. For installation of norton install with product key go through the link.

Yashasvi said...

Norton security software is used broadly and it provides tha simplest ways to useand it is the most effective protection for yur pc's and computers. Install it in your PC's and stay free from viruses.Do visit Norton 360 setup for more details.

Yashasvi said...

It is safe to say that you are on the Hulu (No Ads) or Hulu (No Ads) + Live TV plan? Assuming this is the case,enter activation code for hulu presently you can download a great many TV shows and motion pictures on your bolstered iOS gadget to watch disconnected later.

Yashasvi said...

McAfee thing in citation is an antivirus program. this program secure your PC with tainting so you can display it. to see significantly more please visit mcafee my account our site .

Yashasvi said...

McAfee Activate! you can start incitation with enter thing code/key at and visit mcafee activate download and activate for more nuances.

Yashasvi said...

Norton keeps your PC's free from various malicious activities and threats.to make your PC's viruses free download norton install with product key.

Yashasvi said...

Norton Security gives you the best of Norton for the numerous ways you interface. With a solitary basic arrangement,install norton security setup with product key you get complete insurance that is made to verify your preferred gadgets—PCs, Macs®, cell phones and tablets—so you can be sheltered in any case and any place you associate.

Yashasvi said...

Endeavor which is amazingly easy to present, download and recoup. office setup and enter 25 character Usage of it is moreover fundamental and the customer can pick up capability with the use of it adequately. Online Support&help elective is in like manner open in all application which gives a minute guideline.

Yashasvi said...

office it the pile of office gadgets to make your working smooth and effective.visit: install microsoft office 365 with product key to downloaded in your PC with the smart help.

Yashasvi said...


Be more innovative and accomplish what is important with Outlook, OneDrive, Word, Excel, PowerPoint, OneNote, SharePoint, Microsoft Teams, Yammer, and that's only the tip. For more details go through the links:enter your product key 25 character

Yashasvi said...

Enact Roku connect, go to roku link activate roku tv account enter Roku interface code showed on Roku TV. My roku com interface not working utilize new Roku code.To download click for more details..

Yashasvi said...

Roku spilling players need web access to stream content. roku link code They utilize remote to interface with your home system.

Yashasvi said...


Roku gadgets are easy to set-up and simple to-utilize. roku activation code entry They accompany a straightforward remote, and incredible highlights like Roku Search which makes it easy to discover what you need to watch.

Yashasvi said...

Roku Streaming players are an increasingly advantageous and financially savvy approach to sit in front of the TV. Simply plug it into your TV, associate with the web, set up a Roku record, and start spilling your top choices. For more options do visit the site:roku activation enter link code

Yashasvi said...

Webroot digital security is an extreme web security suite for complete assurance against the present different scope of danger on windows. key highlights are 100% secure shopping, 1 snap infection examining, pernicious site separating, unblock antivirus.if you need to introduce it at that point visit our site:
webroot download with key code

Yashasvi said...

Webroot AntiVirus is a not too bad, secure program that successfully sees and shields you from Mac malware.install webroot with key code It has safe program remembers that single work for Safari, which also makes Webroot's less practical at perceiving a couple of Windows perils.

Yashasvi said...

Webroot cyber security is a ultimate internet security suite for complete protection against today's diverse range of threat on windows. key features are 100% secure secure shopping, 1 click virus scanning, malicious website filtering, unblock antivirus.if you want to install it then visit our site: webroot download with key code best buy

Yashasvi said...

Present mcafee thing with no issue. We give best foundation advantage. visit:enter mcafee activation codee today.

Yashasvi said...

Activate mcafee antivirus with the highly professional tech team and get rid of antivirus from computer cilck the link mcafee activate with activation code for more details.

Yashasvi said...

Microsoft Office Setup is the full suite of Microsoft utmost programming that joins a mix of livelihoods, affiliations, and server like Excel, PowerPoint, Word, OneNote, Publisher, and Access. Get to know more details visit the links:install office 365 personal with product key

Yashasvi said...

Microsoft office setup isn't open for Windows contraption, yet in like a way for Macs, and phones. The Microsoft Office 2016 suite looks as strong and major as its harbingers and a marvelous arrangement has changed. Having any doubts visit the links: activate office 2016 with a product key

Yashasvi said...

Webroot AntiVirus is a decent, secure program that effectively perceives and shields you from Mac malware. install webroot secureanywhere with key code It has safe program includes that solitary work in Safari, which additionally makes Webroot's less viable at recognizing a few Windows dangers.

Yashasvi said...


Microsoft Office A Series Of Subscription-Based Services Which Includes Client Software As Well As Server Software. It Is Generally Offered As A Component Of The Microsoft Office Product Line Which Can Be Installed By Office setup. Any question regarding this visit our site:office 2019 activate product key

Yashasvi said...

Office Setup To begin with your Microsoft Office Installation you should require substantial item key code and visit our webpage and we can likewise assist you with your whole procedure to arrangement office item on the web. Here is the link you need to visit: download office 2016

Yashasvi said...

Norton web security is commonly used antivirus gives the least requesting to use and most intutive affirmation for your PC and your mobiles .present it and dismissal viruses,spyware,root-units, hackers.for more nuances visit: enter norton product key code to activate today.

Yashasvi said...

Get your Office Setup Installed with the help of the best help team. activate office on this computer You may install the complete office 365 package without any complicated work.

Yashasvi said...

mcafee antivirus is broadly utilized antivirus recognizes and kill PC infection,enter mcafee key code the infected mail ,the malware programs,and enables your framework to free of infection and other malware is a serious every day challenge.

Yashasvi said...

office setup is the best software which is widely used in globe.It is a powerful service that helps you unleash your best ideas , get things done, and stay connected on the go.for more details visit: activate office 365

Yashasvi said...

Norton is the most prescribed antivirus programming, broadly utilized for its unwavering quality in verifying your online experience.norton security setup with product key It gives total security to your significant information and gadget, and with security progressions, you need not stress over the security of your gadgets.

Yashasvi said...

Would you like to set up hulu antivirus in your PC?...activate hulu account at that point click here for more subtleties.

Yashasvi said...

Webroot SecureAnywhere Antivirus is more than antivirus programming. It is antimalware. enter webroot keycode It stops the infections and worms that exist so as to contaminate different PCs, yet it additionally ensures you during web surfing sessions by blocking destructive sites.

Yashasvi said...

McAfee thing in citation is an antivirus program. this program secure your PC with tainting so you can display it. to see significantly more please visit Activate Mcafee with product key our site .

Yashasvi said...

Webroot offers total security from infections and fraud for every one of your gadgets, webroot install without backing you off.

Yashasvi said...

Roku Streaming players are an increasingly advantageous and financially savvy approach to sit in front of the TV. Simply plug it into your TV, My Roku Activation Code associate with the web, set up a Roku record, and start spilling your top choices.

Yashasvi said...

mcafee antivirus is broadly utilized aantivirus distinguishes and kill PC infection, the mail worms,the trojan programs,and likewise helps enter mcafee activation code- your framework free of infection and other malware is a significant every day challenge. for more subtleties visit today.

Yashasvi said...

Norton Security Premium gives all what Norton Security does, in addition to it backs up the individual records, photographs, recordings and different recollections on your PC.Go to the link for more information: norton internet security

Yashasvi said...

Norton arrangement offers an expense based suite of antivirus items which give different malware recognition technique. Client can ensure their gadgets by consistently filtering the PC with Norton antivirus. norton com setup with product key against infection suite utilizes infection definitions, suspicious conduct, imitating and the sandbox technique to identify infections.

Yashasvi said...

AVG is a known name among the users for providing the best security features against any malware. It provides online security, web security, offline security, family security and many other features to its users. avg activation code For AVG Activate, Download & complete installation online to get rid of harmful viruses.

Yashasvi said...

mcafee antivirus is widely used aantivirus helps to detect and neutralize computer virus, the mail worms,the trojan programs,and also helps your system free of virus and other malware is quite a daily challenge. for more details visit Activate Mcafee with product key here.

Yashasvi said...

To enact the Norton setup, select the Activate Now option at the base. To recharge the membership for Norton, select the Help choice and snap on Enter item key. Download Norton with key Cautiously type the right Norton item key in the clear. Snap on the Next catch.Go through with for more details.

Yashasvi said...

Norton Setup Internet and Device Sercurity. Norton give total seurity to web and device.Every business constantly expected to send the record and subtleties material download norton have product key and everybody needs to beyond any doubt that the archives that are sending the best possible and unique setup.

Yashasvi said...

McAfee Activate is a certain shot way you can deal with the issues with the initiation procedure. enter your product key 25 character Our McAfee group endeavors to acquire the best tech help for your benefit.

Yashasvi said...

Much obliged to you for picking Webroot web security. install webroot safe You're nearly secured! Essentially complete the accompanying strides beneath to finish your introduce.

Yashasvi said...

Steps to Activate Avg retail enlistment Once you install AVG retail activation on your framework, you require to activate it as without activation it is extraordinary to get its feature. avg activation code In this way, enact AVG antivirus by following given advances carefully.

Yashasvi said...

Introduce mcafee antivirus in your PC with high class experts and best tech group. Simply ring us and we are prepared to help you till the last moment of establishment - Activate mcafee product key visit here.

Yashasvi said...

After you buy Norton Antivirus visit Norton 360 setup sign in to norton account then enter norton product for Norton Setup or Install Norton Antivirus

Yashasvi said...

Presently your norton retail card has been reclaimed and you can introduce the norton item and run the setup.Firstly go to download option and follow the directions. norton security will provide you easy and simple way for download, install and activation process.

Yashasvi said...

norton web security is generally utilized antivirus gives the simplest to utilize and most intutive assurance for your PC and your mobiles .introduce it and disregard viruses,spyware,root-units, hackers.for Download Norton more subtleties visit.

ROSHIYA said...

Download norton.com/setup and Antivirus application that can shield your records from getting influenced from any online malware or defilement.

ROSHIYA said...

webroot digital security is an extreme web security suite for complete insurance against the present differing scope of danger on windows. Key highlights are 100% secure shopping, webroot.com/safe 1 snap infection examining, malevolent site sifting, unblock antivirus. Visit our site on the off chance that you need to introduce it.

ROSHIYA said...
This comment has been removed by the author.
webdesign01 said...


If you want to Use Cortana in the Microsoft Edge Browser then you are at the right website. Here our company provide a various software and application according to the requirement of the user for more details you can contact us.

webdesign01 said...


Our Web Development Company in Jaipur offers all type of services such as Web Design, Mobile App Development, software that turns webcams into eye trackers , Android App Development and various programming framework according to the requirement of the user for more details you can visit our site.

webdesign01 said...

Our Web Development Company in Jaipur offers all type of services such as Web Design, Mobile App Development, Software Development, Android App Development and various programming framework according to the requirement of the user for more details you can visit our site.

Alexais said...

Thanks for sharing your inspiring blog the information was very useful to my project..

For more visit on: norton.com/setup
norton.com/setup
office.com/setup

Alexais said...

With McAfee, you can perform and customize real-time protection scans for your system data and various folders and files, which provides you an incredible experience while working on your PC, either online or offline mode. If you want to avail of such esteemed features, then visit

: mcafee.com/activate | mcafee.com/activate

Edward Lewis said...

Nice one! thank you so much! Thank you for sharing this post. Your blog posts are more in
How to Fix No Audio After Windows 10 Update
How to Delete All Facebook Posts Without Deleting the Account

Jaxsonharry said...


The antivirus software provides an added layer of protection and that too without conflicting with other security subscriptions.

Visit us for more information :
Web Application

Gmail
Latest Android Processor