
If you were logged in you would be able to see more operations.
|
|
|
Environment:
|
Server Version: LI-V2.1.3.18185 Firebird 2.1
Server Implementation: Firebird/linux Intel
Service Version: 2
Server Version: LI-V2.1.3.18185 Firebird 2.1
Server Implementation: Firebird/linux Intel
Service Version: 2
|
|
First, meta data is given:
CREATE TABLE "Wool_InSheet" (
"StorageName" VARCHAR(12),
"InDate" DATE,
"InCode" VARCHAR(10),
"InVoiceNo" VARCHAR(10),
"KeeperCode" VARCHAR(10),
"InTypeCode" CHAR(2),
"DeptCode" VARCHAR(15),
"DeptName" VARCHAR(40),
"Kind" VARCHAR(10),
"PackNumber" VARCHAR(10),
"YarnCount" VARCHAR(10),
"LotCode" VARCHAR(20),
"ArticleCode" VARCHAR(20),
"ColorCode" VARCHAR(20),
"Trait" VARCHAR(12),
"MixColor" VARCHAR(20),
"IsColor" CHAR(1),
"WQty" NUMERIC(18,2),
"NQty" NUMERIC(18,2),
"SQty" NUMERIC(18,3),
"Amount" INTEGER,
"Note" VARCHAR(100),
"Status" VARCHAR(10),
"Mark" VARCHAR(10),
"SubCompanyName" VARCHAR(20),
"Check" CHAR(1),
"Sub" VARCHAR(10),
"Place" VARCHAR(20),
"PArea" VARCHAR(10),
"MLotCode" VARCHAR(20),
"PackMode" VARCHAR(10),
"Packages" INTEGER,
"Account" CHAR(1),
"Wet" FLOAT,
"Canal" VARCHAR(10),
"ArticleName" VARCHAR(30),
"TwistWay" VARCHAR(10),
"Kind2" VARCHAR(10),
"Mix" VARCHAR(20),
"TInDate" DATE
);
CREATE INDEX "Wool_InSheet_IDX1" ON "Wool_InSheet" ("LotCode");
CREATE INDEX "Wool_InSheet_IDX2" ON "Wool_InSheet" ("InDate");
CREATE DESCENDING INDEX "Wool_InSheet_IDX3" ON "Wool_InSheet" ("InDate");
Second,insert into 2M records.
Wool_InSheet_IDX1 selectivity 0.000037
Wool_InSheet_IDX2 selectivity 0.000587
Wool_InSheet_IDX3 selectivity 0.000587
Result:
statement 1: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate"
statement 2: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate"+0
statement 1 is about five to ten times slower then statement 2, both return 9 records
statement 3: select max("InDate") from "Wool_InSheet" where "LotCode"='D99054'
statement 4: select max("InDate"+0) from "Wool_InSheet" where "LotCode"='D99054'
statement 3 is about five to ten times slower then statement 4
|
Description
|
First, meta data is given:
CREATE TABLE "Wool_InSheet" (
"StorageName" VARCHAR(12),
"InDate" DATE,
"InCode" VARCHAR(10),
"InVoiceNo" VARCHAR(10),
"KeeperCode" VARCHAR(10),
"InTypeCode" CHAR(2),
"DeptCode" VARCHAR(15),
"DeptName" VARCHAR(40),
"Kind" VARCHAR(10),
"PackNumber" VARCHAR(10),
"YarnCount" VARCHAR(10),
"LotCode" VARCHAR(20),
"ArticleCode" VARCHAR(20),
"ColorCode" VARCHAR(20),
"Trait" VARCHAR(12),
"MixColor" VARCHAR(20),
"IsColor" CHAR(1),
"WQty" NUMERIC(18,2),
"NQty" NUMERIC(18,2),
"SQty" NUMERIC(18,3),
"Amount" INTEGER,
"Note" VARCHAR(100),
"Status" VARCHAR(10),
"Mark" VARCHAR(10),
"SubCompanyName" VARCHAR(20),
"Check" CHAR(1),
"Sub" VARCHAR(10),
"Place" VARCHAR(20),
"PArea" VARCHAR(10),
"MLotCode" VARCHAR(20),
"PackMode" VARCHAR(10),
"Packages" INTEGER,
"Account" CHAR(1),
"Wet" FLOAT,
"Canal" VARCHAR(10),
"ArticleName" VARCHAR(30),
"TwistWay" VARCHAR(10),
"Kind2" VARCHAR(10),
"Mix" VARCHAR(20),
"TInDate" DATE
);
CREATE INDEX "Wool_InSheet_IDX1" ON "Wool_InSheet" ("LotCode");
CREATE INDEX "Wool_InSheet_IDX2" ON "Wool_InSheet" ("InDate");
CREATE DESCENDING INDEX "Wool_InSheet_IDX3" ON "Wool_InSheet" ("InDate");
Second,insert into 2M records.
Wool_InSheet_IDX1 selectivity 0.000037
Wool_InSheet_IDX2 selectivity 0.000587
Wool_InSheet_IDX3 selectivity 0.000587
Result:
statement 1: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate"
statement 2: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate"+0
statement 1 is about five to ten times slower then statement 2, both return 9 records
statement 3: select max("InDate") from "Wool_InSheet" where "LotCode"='D99054'
statement 4: select max("InDate"+0) from "Wool_InSheet" where "LotCode"='D99054'
statement 3 is about five to ten times slower then statement 4
|
Show » |
made changes - 26/Feb/10 05:21 AM
Field |
Original Value |
New Value |
Assignee
|
|
Dmitry Yemanov
[ dimitr
]
|
made changes - 08/Mar/10 07:44 AM
Status
|
Open
[ 1
]
|
In Progress
[ 3
]
|
made changes - 17/Mar/13 02:43 PM
Status
|
In Progress
[ 3
]
|
Open
[ 1
]
|
|