RESOURCES

4D V11 SQL IN A REAL APPLICATION

warning: file_get_contents(http://www.telize.com/geoip/54.82.112.193) [function.file-get-contents]: failed to open stream: HTTP request failed! HTTP/1.1 404 Not Found in /var/www/www.4d.com/docs/includes/common.inc(1762) : eval()'d code on line 4.

 

4D v11 SQL in a Real Application

4D v11 SQL performance tests under real-world conditions.

 

Is the whole greater than the sum of its parts?

 

This is the question we had in mind when creating some of the new algorithms in 4D v11 SQL. Although every command has received individual attention, and while we know how to assess their improvements through unit tests, it’s the perception of the user that determines the total improvement in performance.

 

So with this in mind, we thought it would be interesting to run our tests against the most realistic application possible, subjecting it to a constant flux of simultaneous requests in a Client/Server environment. We invite you to discover some results.

 

Application Structure

 

Methodology

To illustrate certain behaviors of 4D SQL v11, we are going to establish some requests from a simple structure – in this case a subset of an application for call center management. The database tracks communications between telephone exchanges, and different statistical requests are made by operators on client machines.

 

Volumetrics

Exchanges

1,000

Customers

10,000

Telephone lines

100,000

Communications

1,000,000

Bills

Variable

 

Test Configuration

  • 4D Server v11 SQL (11.2) vs. 4D Server 2004.7 r3
  • 8 clients connected

 

Results

Below are the results of 4 typical operational sequences for this type of activity:

 

Results chart

 

Comments

Sequence 1: Number of communications by region

To get a geographical overview of communications, we want to establish the number of offices by region. For this, we will go by postal code.

 

For instance, customers located in the Maritime Alps of France have a postal code between 06000 and 06999. The query is thus performed on a dependent field, since we as we are performing a search on the table [communication] with a search criterion on the linked field codepostal[customer].

 

4D v11 SQL’s engine optimizations make for a performance improvement of 20%.
 
Sequence 2: Monthly customer turnover

 

Sequence 3: Real-time turnover

It’s generally not useful to query data that won’t be used. 4D v11 SQL anticipates the execution of code and pre-loads information needed for the engine to access data and speed up the operations that follow.

 

In these two examples, we query either all the communications of a given day, or the monthly communications of a  customer. Then, we calculate the sum of the field "price."

 

Optimizations in 4D v11 SQL allow us to perform this calculation  2-4 times faster than before.

 

Sequence 4 : Listing of communications

We want to establish a detailed list of communications made each day:

 

Communications made

 

In order to do that we return links automatic in purple in the schema above.

 

For each communication, we use the command RELATE ONE to select and load the related records.
In Client/Server, the records for the tables [phone_exchange], [customer] and [line] must then be transferred to the client end.

 

An optimization in 4D v11 SQL allows the load and transfer of the three related records in a single request, reducing the number of requests passing over the network.

 

In the conditions under which we performed this test, 4D v11 SQL proves to be eight times faster than 4D 2004.


 

 

More resources