Skip to Content
0
Former Member
Feb 18, 2009 at 12:38 PM

Performance Problems after transporting database to a new server

32 Views

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