Hi all,

Some information that may help you to help me:
Firebird version: 1.0 (upgrading this version is not a viable option at the moment)
Page Size: 4096
SQL Dialect: 1
Sweep Interval: 2000
ODS version: 10.1
Buffer | Pages 2048


The problem: Executing the following SQL on a DELL power Edge server with 1Gb of ram is taking up-to 2 hours to execute. As you can see the SQL itself is not that complicated and on the surface of it should not take as long as it does to execute. Give that assumption, and after ensuring that there are sufficient indexes on the tables and fields involved in producing the results . I am at something of a lost, as to where to turn next except for upgrading to a later version of the software (which is not currently an option available to me)

Any help you can provide leading to a solution of a better understanding of the problem would be greatly appreciated. Time is critical on this one but, I suppose that time is critical for everyone that email you with their dull Firebird/SQL problem.

Again, if you can help in any way it would be much appreciated. I have tried to provide as much information as possible, if you required any more, please don't hesitate in posting a reply.

select
'U'||D.Batch_No || '-' || D.Sub_Batch_No as Batch_No, d.Batch_No as FBatch_No,
d.Sub_Batch_No as FSub_Batch_No, L.Display_Name, L.C_Centre,d.reasoncode,
count(d.sub_batch_no) as carrier_count
from
cd$$__abb__master_8 M,Livery L, cd$$__abb__detail_8 D
where
(M.Card_Type = L.CardType) and (M.Batch_No = D.Batch_No) and d.carrier_required = 'T'
and d.pullout_code = 0 and d.runtype in ('NEW/REISSUE','REPLACEMENT') and
((m.Batch_No = 7 and d.Sub_Batch_No = 1) or (m.Batch_No = 7 and d.Sub_Batch_No = 2)
or (m.Batch_No = 8 and d.Sub_Batch_No = 1))
group by
D.Batch_No,D.Sub_Batch_No,L.Display_Name,d.reasoncode,l.C_Centre
order by
d.Batch_No, d.Sub_Batch_No , L.Display_Name

CREATE TABLE CD$$__ABB__DETAIL_8 (
BATCH_NO INTEGER NOT NULL,
SUB_BATCH_NO INTEGER,
FILE_SEQ INTEGER NOT NULL,
SUB_BATCH_SEQ INTEGER,
JOB_CODE INTEGER,
CARD_VISUAL_ID VARCHAR(64),
PULLOUT_CODE INTEGER DEFAULT 0,
CUSTOMER_BATCH_ID VARCHAR(50),
CAN_SPLIT_HERE VARCHAR(1),
CARRIER_REQUIRED VARCHAR(1),
SUB_BATCH_CARRIER_SEQ INTEGER,
REASONCODE VARCHAR(3),
ABBEYSEQUENCE VARCHAR(6),
PANSPACES VARCHAR(19),
PAN VARCHAR(16),
ACCOUNTNUMBER VARCHAR(8),
VALIDFROM VARCHAR(5),
EXPIRYDATE VARCHAR(5),
SORTCODE VARCHAR(8),
EMBOSSNAME VARCHAR(27),
ISSUENUMBER VARCHAR(2),
TRACK1 VARCHAR(80),
TRACK2 VARCHAR(40),
INDENT VARCHAR(20),
ADDRESS1 VARCHAR(36),
ADDRESS2 VARCHAR(36),
ADDRESS3 VARCHAR(36),
ADDRESS4 VARCHAR(36),
ADDRESS5 VARCHAR(36),
ADDRESS6 VARCHAR(36),
ADDRESS7 VARCHAR(36),
BRANCHNUMBER VARCHAR(4),
CREATIONDATE VARCHAR(8),
TRACEMAILNUMBER INTEGER,
CHIPDATA BLOB SUB_TYPE 0 SEGMENT SIZE 80,
IMAGEID VARCHAR(10),
HPC_STATUS VARCHAR(1),
HPC_IGNORED VARCHAR(1),
HPC_DESPATCH VARCHAR(3),
RUNTYPE VARCHAR(11)
)

CREATE TABLE CD$$__ABB__MASTER_8 (
BATCH_NO INTEGER NOT NULL,
CUSTOMER_FILE_ID VARCHAR(100),
DOWNLOAD_DATE TIMESTAMP,
CARD_TYPE VARCHAR(40),
RETURN_FILE_DUPLICATES INTEGER,
HPC_FILE VARCHAR(30),
HPC_FILE_INDEX INTEGER
)


CREATE TABLE LIVERY (
CARDTYPE VARCHAR(25) NOT NULL,
ISIMAGECARD VARCHAR(1),
ISCHIPCARD VARCHAR(1),
NEEDSAUDITLIST VARCHAR(1),
NEEDSTRACEMAIL VARCHAR(1),
NEEDSAUDITFILE VARCHAR(1),
EMBOSS_FORMAT VARCHAR(5),
USE_GENERIC_TBP VARCHAR(1),
DESPATCH_CALC_METHOD VARCHAR(5),
APPLICATION_ID VARCHAR(20),
DISPLAY_NAME VARCHAR(30),
ARTISTA_TYPE VARCHAR(5),
TRACE_CODE VARCHAR(5),
C_CENTRE VARCHAR(4)
)


ps: Si: if you read this, how about a little of your expert help (Kevin also formerly of Euro Telecom).



Tia, Kevin



[Non-text portions of this message have been removed]