Hello,

complete metadata of Booking & Client

here Start date is "Required CO Date"
and End date is "Required CI Date"

/* Table: Booking */

CREATE TABLE "Booking" (
"Booking No" VARCHAR (10) CHARACTER SET WIN1251
NOT NULL COLLATE WIN1251,
UPDATEDAT TIMESTAMP,
UPDATEDSITEID CHAR (2) CHARACTER SET WIN1251
COLLATE WIN1251,
"Status" VARCHAR (12) CHARACTER SET WIN1251
COLLATE WIN1251,
"Rental No" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"Client No" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"Client Not Driver" T_YESNO NOT NULL,
"Source Abrev" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Tariff No Used" VARCHAR (10) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Name" VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
"Contact Telephone No" VARCHAR (15) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Ext No" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Order Ref" VARCHAR (20) CHARACTER SET WIN1251
COLLATE WIN1251,
"Own Insurance" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"Second Id" VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
"Client Proposals Declined" CHAR (1) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Client Accidents/Claims" CHAR (1) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Client Previous Convictions" CHAR (1) CHARACTER
SET WIN1251 COLLATE WIN1251,
"Client Physical/Mental Defects" CHAR (1)
CHARACTER SET WIN1251 COLLATE WIN1251,
"Question 5" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"Question 6" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"Comments" VARCHAR (100) CHARACTER SET WIN1251
COLLATE WIN1251,
"Required Group" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Required Rate" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"Required CO Date" TIMESTAMP,
"Required CO Time" VARCHAR (5) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Required CI Date" TIMESTAMP,
"Required CI Time" VARCHAR (5) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Fuel_No_Litres" SMALLINT,
"Fuel_Rate_Per_Litre" T_CURRENCY,
"Fuel_Sub_Total" NUMERIC (15, 4),
"Fuel_Surcharge_Percent" NUMERIC (15, 4),
"Fuel_Surcharge_Amount" NUMERIC (15, 4),
"Fuel_Net" NUMERIC (15, 4),
"Fuel_VATRATE" T_CURRENCY,
"Fuel_VAT" NUMERIC (15, 4),
"Fuel_Gross" NUMERIC (15, 4),
"Sub Rental Total" NUMERIC (15, 4),
"Less %" NUMERIC (15, 4),
"Less Amount" NUMERIC (15, 4),
"Rental Total" NUMERIC (15, 4),
"Rental_VATRATE" T_CURRENCY,
"Rental_VAT" T_CURRENCY,
"Sub Total" NUMERIC (15, 4),
"Charge VAT" T_YESNO NOT NULL,
"Vat Rate" T_CURRENCY,
"Vat Amount" NUMERIC (15, 4),
"Gross Total" NUMERIC (15, 4),
"HasSecondaryInvoice" T_YESNO NOT NULL,
"Required Deposit" NUMERIC (15, 4),
"Total Payment" NUMERIC (15, 4),
"Total Deposit" NUMERIC (15, 4),
"Posted to Accounts" T_YESNO NOT NULL,
"Excess Payment No CDW" NUMERIC (15, 4),
"Excess Payment With CDW" NUMERIC (15, 4),
"Cash Sheet Id" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"Cust is an Account" T_YESNO NOT NULL,
"Insurance_XS" NUMERIC (15, 4),
VOR T_YESNO NOT NULL,
DRIVER_NAME VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
BILLING_ADDRESS VARCHAR (130) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Use_Client_INS" T_YESNO NOT NULL,
"RetainRateChanges" T_YESNO NOT NULL,
"RetainPeriodChanges" T_YESNO NOT NULL,
"Required ARET Date" TIMESTAMP,
"Required ARET Time" VARCHAR (5) CHARACTER SET
WIN1251 COLLATE WIN1251,
"TotalAmountInvoiced" NUMERIC (15, 4),
CONTRACTHIRE T_YESNO NOT NULL,
"PartBill" T_YESNO NOT NULL,
"PartBillRAAction" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"LastBillDayTime" VARCHAR (5) CHARACTER SET
WIN1251 COLLATE WIN1251,
"InvoiceInAdvance" T_YESNO NOT NULL,
"PBByBKProportion" T_YESNO NOT NULL,
"WeekEndsAsGradceDays" T_YESNO NOT NULL,
"InvoiceSite" VARCHAR (2) CHARACTER SET WIN1251
COLLATE WIN1251,
"CResBooking" T_YESNO,
"CResAssignStatus" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"CResAssignOrriginalTime" TIMESTAMP,
"CResAssignLastTime" TIMESTAMP,
BKREASON VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
BKREASONSTATUS VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
GARAGE_NAME VARCHAR (20) CHARACTER SET WIN1251
COLLATE WIN1251,
GARAGE_TOWN VARCHAR (20) CHARACTER SET WIN1251
COLLATE WIN1251,
GARAGE_TELNO VARCHAR (15) CHARACTER SET WIN1251
COLLATE WIN1251,
"AddDrivers" VARCHAR (200) CHARACTER SET WIN1251
COLLATE WIN1251);



/* Indices definition */

CREATE INDEX "IDX_Booking" ON "Booking" ("Required CO
Date");
CREATE INDEX "IDX_Booking1" ON "Booking" ("Required CI
Date");
CREATE INDEX "IDX_Booking2" ON "Booking" ("Rental
No");
CREATE INDEX "IDX_Booking3" ON "Booking" ("Client
No");
CREATE INDEX "IDX_Booking4" ON "Booking"
("CResBooking");
CREATE INDEX "IDX_Booking5" ON "Booking"
("InvoiceSite");
CREATE INDEX "IDX_Booking7" ON "Booking" ("Order
Ref");
CREATE UNIQUE INDEX "PK_Booking" ON "Booking"
("Booking No");

SET TERM ^ ;

/* Triggers definition */



/* Trigger: "Booking_BI" */
CREATE TRIGGER "Booking_BI" FOR "Booking" ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
/* Trigger body */

new."Status" = UPPER(new."Status");

END
^


/* Trigger: "Booking_BU" */
CREATE TRIGGER "Booking_BU" FOR "Booking" ACTIVE
BEFORE UPDATE POSITION 0
AS
DECLARE DRIVERNAMES VARCHAR (200);
DECLARE BOOKINGNO VARCHAR (10);
BEGIN
/* Trigger body */
new."Status" = UPPER(new."Status");
BOOKINGNO = new."Booking No";
EXECUTE PROCEDURE GETDRIVERNAMES(:BOOKINGNO)
returning_values DRIVERNAMES;
new."AddDrivers" = DRIVERNAMES;
END
^


SET TERM ; ^
------------------------------------------

/* Table: Client */

CREATE TABLE "Client" (
"Client No" VARCHAR (10) CHARACTER SET WIN1251 NOT
NULL COLLATE WIN1251,
UPDATEDAT TIMESTAMP,
UPDATEDSITEID CHAR (2) CHARACTER SET WIN1251
COLLATE WIN1251,
"Client Type" CHAR (1) CHARACTER SET WIN1251
COLLATE WIN1251,
"Account No" VARCHAR (9) CHARACTER SET WIN1251
COLLATE WIN1251,
"Tariff Rate" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"NoVATClient" T_YESNO NOT NULL,
"Name" VARCHAR (50) CHARACTER SET WIN1251 COLLATE
WIN1251,
"Source Abrev" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Source Site" CHAR (2) CHARACTER SET WIN1251
COLLATE WIN1251,
"Name2" VARCHAR (30) CHARACTER SET WIN1251 COLLATE
WIN1251,
"Title" CHAR (4) CHARACTER SET WIN1251 COLLATE
WIN1251,
"Sex" VARCHAR (6) CHARACTER SET WIN1251 COLLATE
WIN1251,
DOB TIMESTAMP,
"Licence No" VARCHAR (16) CHARACTER SET WIN1251
COLLATE WIN1251,
"Issued By" VARCHAR (8) CHARACTER SET WIN1251
COLLATE WIN1251,
"IssueNo" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Date of Test" TIMESTAMP,
"Expiry Date" TIMESTAMP,
"Held Years" INTEGER,
"Validity Groups" VARCHAR (20) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Address1" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Address2" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Town" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Contact County" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Post Code" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Telephone No" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Ext No" VARCHAR (5) CHARACTER SET WIN1251
COLLATE WIN1251,
"Contact Name" VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
"Contact Fax No" VARCHAR (15) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Contact Mobile No" VARCHAR (15) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Temporary Contact" T_YESNO NOT NULL,
"Address 1" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Address 2" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Town" VARCHAR (31) CHARACTER SET WIN1251 COLLATE
WIN1251,
"County" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Post Code" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Telephone No" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Ext No" VARCHAR (5) CHARACTER SET WIN1251 COLLATE
WIN1251,
"Fax No" VARCHAR (15) CHARACTER SET WIN1251
COLLATE WIN1251,
"Occupation" VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
"Card Type" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"CCName" VARCHAR (30) CHARACTER SET WIN1251
COLLATE WIN1251,
"Card No" VARCHAR (20) CHARACTER SET WIN1251
COLLATE WIN1251,
"CCExpiry Date" VARCHAR (8) CHARACTER SET WIN1251
COLLATE WIN1251,
"CCIssue No" CHAR (3) CHARACTER SET WIN1251
COLLATE WIN1251,
"Mandatory" T_YESNO NOT NULL,
"Zero Authorised Drivers" T_YESNO NOT NULL,
"ID2 Not Mandatory" T_YESNO NOT NULL,
"Mug Shot" BLOB sub_type 0 segment size 80,
"VOR Type" T_YESNO NOT NULL,
"DefField_Text_1" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_2" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_3" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_4" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_5" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_6" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_7" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_8" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_9" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_10" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_11" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_12" VARCHAR (50) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_13" VARCHAR (100) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_14" VARCHAR (100) CHARACTER SET
WIN1251 COLLATE WIN1251,
"DefField_Text_15" VARCHAR (100) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Need_CRES" T_YESNO NOT NULL,
"ClientStatus" T_YESNO NOT NULL,
"Employer Name" VARCHAR (50) CHARACTER SET WIN1251
COLLATE WIN1251,
"Employer Contact" VARCHAR (30) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Address1" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Address2" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Town" VARCHAR (31) CHARACTER SET WIN1251
COLLATE WIN1251,
"Employer County" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Post Code" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Telephone No" VARCHAR (31) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Ext No" VARCHAR (5) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Employer Fax No" VARCHAR (15) CHARACTER SET
WIN1251 COLLATE WIN1251,
"Address_ID" VARCHAR (15) CHARACTER SET WIN1251
COLLATE WIN1251,
"InvoiceInAdvance" T_YESNO NOT NULL,
"Occupation_ID" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"O_Licence" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"OL_Internal_ReviewDate" TIMESTAMP,
"OL_TrafficOffice_ReviewDate" TIMESTAMP,
"OL_Exempt" T_YESNO NOT NULL,
"PBByBKProportion" T_YESNO NOT NULL,
"BVRLARiscDate" VARCHAR (20) CHARACTER SET WIN1251
COLLATE WIN1251,
"BVRLARiscMSG" VARCHAR (10) CHARACTER SET WIN1251
COLLATE WIN1251,
"WeekEndsAsGradceDays" T_YESNO NOT NULL);



/* Indices definition */

CREATE INDEX "IDX_Client" ON "Client" ("Client Type");
CREATE INDEX "IDX_Client1" ON "Client" ("Account No");
CREATE INDEX "IDX_Client2" ON "Client" ("Name");
CREATE INDEX "IDX_Client3" ON "Client" ("Name2");
CREATE INDEX "IDX_Client4" ON "Client" ("Contact Post
Code");
CREATE INDEX "IDX_Client5" ON "Client" ("Licence No");
CREATE INDEX "IDX_Client6" ON "Client" ("Contact
Town");
CREATE UNIQUE INDEX "PK_Client" ON "Client" ("Client
No");

the SQL that i send you is simpler version
actual SQL has lot of conditions and in inner query it
has aggregate fields like SUM of total invoice for
each client, COUNT of booking for each client.

so i tried the simple version to test the speed.
Client and Booking table has more that 300, 000
record. To run this query without index on "Start
date" and "End date" it took 1-3 minutes but with
index it took more that 30 minutes.

i doubt the plan analyzer doesn't use "end date" may
be the problem? i'm not sure.

Thanks
Anand





--- Svein Erling
<svein.erling.tysvaer@...> wrote:
---------------------------------

--- In firebird-support@yahoogroups.com, anand wrote:
> select count(*)
> FROM "Client" WHERE "Client"."Client No" in
> (
> SELECT DISTINCT "Booking"."Client No" FROM "Booking"
> WHERE "Booking"."End Date" >= '02-feb-2001' AND
> "Booking"."Start Date" <= '02-may-2001'
> )
> Plan:
> PLAN (Booking INDEX (IDX_Booking3,IDX_Booking))
> PLAN (Client NATURAL)

??? Why doesn't client use an index for "Client No"?
Have you defined
several indexes covering "Client"."Client No" so that
Arnos optimizer
gets confused?

By the way, if "Client No" is distinct for any record
in client, then
you can simply do

select count(distinct "Client"."Client No")
FROM "Client"
JOIN "Booking" on "Booking"."Client No" =
"Client"."Client No"
WHERE "Booking"."End Date" between '02-feb-2001' AND
'02-may-2001' and
"Booking"."Start Date" '02-feb-2001' AND
'02-may-2001'

> |> the DISTINCT is unneeded here.
>
> there can be more than one booking for one client so
> distinct reomove the duplicate rec in the sub query.

When Arno says you can remove the distinct, you can
remove the
distinct. No-one knows the optimizer better than him.

> i tried Exists it works similar to in statement very
> slow.

Show us the definition of all indexes and keys in
these tables, exists
should be fast if your indexes are properly defined
(though slow and
fast are relative terms - how slow is slow?).

Set



---------------------------------
Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo!
Terms of Service.






___________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html