All fields in the where clause contain indices and constrains for validation of business logic.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Martijn Tonies
Sent: 20 January 2006 09:13
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Slow SQl execution

Hello Kevin,

> 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,
> HPC_DESPATCH VARCHAR(3),
> RUNTYPE VARCHAR(11)
> )
>
> CREATE TABLE CD$$__ABB__MASTER_8 (
> BATCH_NO INTEGER NOT NULL,
> CUSTOMER_FILE_ID VARCHAR(100),
> HPC_FILE_INDEX INTEGER
> )
>
>
> CREATE TABLE LIVERY (
> CARDTYPE VARCHAR(25) NOT NULL,
> ISIMAGECARD VARCHAR(1),
> ISCHIPCARD VARCHAR(1),
> C_CENTRE VARCHAR(4)
> )
>

No indices or constraints?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links