Hi,
I transported the data from my old MaxDB server (Kernel 7.6.00 build 037-121-149-748) to a new server (Kernel 7.6.06 build 003-123-202-135) using the current version of Database Studio. Now I am having massive performance problems. For instance, a query that used to run in 40ms on the old and slow server now takes 15000ms. The query and the table structure are included at the end of this post. Both servers are running WindowsXP SP2 32bit with 2GB RAM. The new server is dual core.
As a test, I disabled the index on the new server, and the query still ran in 15000ms. Even though EXPLAIN says the index is being used, I suspect the query is not using the index. I also used the database analyzer to search for a problem, but I cannot find anything suspicios. Could it be the sample size or other parameters are different in both databases and thus the optimizer chooses different query execution plans?
Can anyone help me with my problem?
Thank you and kind regards,
Leonardo
// This table has 1.000.000 rows
CREATE TABLE "FIELDTEST-100-1k"
("HEX" Varchar (100) ASCII,
"READER" Varchar (10) ASCII)
CREATE INDEX "FIELDTEST-100-1k-HEX,READER"
ON "FIELDTEST-100-1k" ("HEX" ASC,"READER" ASC )
ALTER INDEX "FIELDTEST-100-1k-HEX,READER"
ON "FIELDTEST-100-1k" ENABLE
SELECT HEX,
SUM(CASE WHEN F."READER" = 'RACK1' THEN Event ELSE 0 END) AS EventOK,
SUM(CASE WHEN F."READER" != 'RACK1' THEN Event ELSE 0 END) AS EventWrong
FROM
(SELECT HEX, READER, COUNT(*) AS Event
FROM "FIELDTEST-100-1k"
WHERE SUBSTR(HEX,4,11) = '10442001784'
AND "READER" != 'POS'
AND "READER" != 'Lager'
GROUP BY HEX, READER) AS F
GROUP By HEX