Icon View Incident Report

Serious Serious
Reported By: Jesper Peterson
Reported On: 11/2/2004
For: Version 4.14 Build 1
# 1899 Large Tables Causing Integer Overflows in SQL Optimization Cost Calculations

We have a database with 11.000.000 records. Table.dat is 5GB. Table idx is 2 GB. The table are used for telephone calls and there are 18 indexes:

Example

Localnumber, CallDate, CallTime
CallDate, CallTime, LocalNumber

If we send the first SQL statement below it take 2-3 sec. However, if we change the CallDate to 30 days like in the second SQL statement below, it takes ca. 60 sec. There are ca. 50.000 records pr day distributed on ca. 1000 LocalNumber.

First
-----

SELECT *.
FROM Tabel
WHERE CallDate BETWEEN '2004-10-10' AND '2004-10-10'
AND (LocalNumber = '58303200')
ORDER BY CallDate, CallTime

Second
------
SELECT *.
FROM Tabel
WHERE CallDate BETWEEN '2004-08-10' AND '2004-10-10'
AND (LocalNumber = '58303200')
ORDER BY CallDate, CallTime



Comments Comments
The problem was caused by the larger number of candidate rows in the second query, which ultimately caused the integer overflow.


Resolution Resolution
Fixed Problem on 11/4/2004 in version 4.15 build 1
Image