Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Server/Performance    [ Add a report in this area ]  
Report #:  120208   Status: Closed
Slower query performance than with earlier IB-version
Project:  InterBase Build #:  11.0.3.655
Version:    11.0 Submitted By:   Kjell Ljungqvist
Report Type:  Basic functionality failure Date Reported:  11/3/2013 10:32:30 AM
Severity:    Serious / Highly visible problem Last Updated: 4/1/2014 9:43:00 PM
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: Fixed (Resolution Comments) Resolved in Build: : XE3 Update4
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: 1
Description

After having used IB 2007 as our deployment version for many years we have now switched to IB XE3.

Often when we make the switch from 2007 to XE3 at a customer site there is also a new server with more powerful hardware available. So with a newer IB-version and better hardware we (and of course the customer) would like to se significant improvement in response times from the server.
Sadly this is not usually the case. After having received numerous complaints from customers saying that the performance is actually WORSE than before we had to investigate this further.

When testing we have now noticed worse performance with a number of queries and stored procedures in our databases. Many of these are quite large and complex and hard to send to you as test cases. So we have here submitted a simple database with four tables and a simple query.

The difference in response time here is in absolute terms (3.4 s vs 5.4 s) only 2 seconds but it is a big hit in relative terms and with our more complex queries and procedures the difference can be serveral minutes or for larger tasks even hours...
Steps to Reproduce:
The test case provided here has been created using a multi instance IB-install on the same server. The database is exactly the same in both cases, ie. in ODS 12-format. Changing the ODS-format to 15 doesn't help. All our XE3-customers are using databases with ODS 15 and the performance will not improve from this change.
The performance statistics provided here is from IB 2007 SP3 and IB XE3 Update 3. But it can be noted that when testing with IB XE on the same server the performance is similar as with XE3.

Observations we have made:
- The PLAN is the same in both cases
- Fetches, reads etc. are the same in both cases
- For some reason for XE3 our tool (IB Expert) is showing a number of deletes, inserts etc. for this query but this might only be an anomaly in IB Expert   or is it a sign of something else?


Attached:
- Metadata for database
- Database compressed with 7-zip
- Performance stats for IB 2007 SP3
- Performance stats for IB XE Update 3
- Comparison screen shot


This is the test query:
SELECT
S.CUSTOMER_ID,
S.CUSTOMER_CODE,
S.NAME,
S.SEARCH,
T.IS_ACTIVE,
A.ADDRESS_1,
A.ADDRESS_2,
A.ZIP,
C.EMAIL
FROM
CUSTOMER_MASTER S
LEFT JOIN CUSTOMER_STAT T ON T.CUSTOMER_ID = S.CUSTOMER_ID
LEFT JOIN SALES_ADDRESSES A ON A.CUSTOMER_ID=S.CUSTOMER_ID AND A.ISDEFAULT='T'
LEFT JOIN CUSTOMER_CONTACT C ON S.CUSTOMER_ID = C.CUSTOMER_ID
where
     T.IS_ACTIVE = 'T'
AND
     T.IS_DROP_IN_CUSTOMER <> 'T'
Workarounds
None
Attachment
Performance Test.zip
Comments

quinn wildman at 11/4/2013 10:44:28 AM -
Being discussed here:

https://forums.embarcadero.com/thread.jspa?threadID=96432

At this point, I cannot reproduce this issue.

Sriram Balasubramanian at 11/5/2013 2:12:09 PM -
Quinn says he can reproduce the issue now. Opening for further analysis by DEV team.
Internally logged as INTB-1923

Server Response from: ETNACODE01