Description:
I have an application the runs with MS-SQL 2000, and I have ported it to use MySQL 4 for a univiersity.
The following SQL query was generated by Crystal Reports 9, which I use for reporting.
SELECT Account.sFinanceCode, TxTypes.sTxShortName, Transactions.dtTransactionDate, Transactions.cAmount, Transactions.iProduct1Usage, Transactions.iProduct2Usage, Transactions.iProduct3Usage, Transactions.iProduct4Usage, Transactions.iProduct5Usage, Transactions.iProduct6Usage, Transactions.iProduct7Usage, Transactions.iProduct8Usage, Users.sName1, Users.sName2, Transactions.lAccountId, Account.cBalance, Account.sDescription
FROM (((Account Account INNER JOIN Transactions Transactions ON Account.cntAccountId=Transactions.lAccountId) INNER JOIN UserAccount UserAccount ON Account.cntAccountId=UserAccount.lAccountId) INNER JOIN TxTypes TxTypes ON Transactions.iTransactionType=TxTypes.cntTxTypes) INNER JOIN Users Users ON UserAccount.sBarCode=Users.sBarcodeNumber
WHERE (Transactions.dtTransactionDate>={ts '2003-06-01 07:11:06'} AND Transactions.dtTransactionDate<{ts '2003-06-03 07:11:07'})
ORDER BY Account.sFinanceCode, Transactions.lAccountId, Transactions.dtTransactionDate
When I run the query against MS-SQL 2000 (P4-1.8, 256M), I get a result in approx 25 secs (7009 rows).
The data from the MS-SQL 2000 db has been transferred into MySQL and uses the same table structure. When I run the same query against MySQL (using the MySQL Control Centre 0.9.3-beta), the query takes 350+ secs to complete. MySQL 4 is running an AMD Athlon 2000+ with 512M RAM, Win2000 SP4.
How to repeat:
I can repeat the result every time. I have tried increasing the various buffer variables to take full advantage of the 512M RAM in the PC, but it seems to make very little effect (1-2 secs faster).
I have changed my.ini back to:
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld-nt.exe
[mysqld]
port=3306
set-variable = key_buffer_size=64M
set-variable = table_cache=256
set-variable = sort_buffer_size=4M
set-variable = read_buffer_size=1M
set-variable = max_allowed_packet=1M
set-variable = read_rnd_buffer_size=512
If I watch the CPU usage when the query is running, it is sitting on 100% for almost then entire 350 secs.
Description: I have an application the runs with MS-SQL 2000, and I have ported it to use MySQL 4 for a univiersity. The following SQL query was generated by Crystal Reports 9, which I use for reporting. SELECT Account.sFinanceCode, TxTypes.sTxShortName, Transactions.dtTransactionDate, Transactions.cAmount, Transactions.iProduct1Usage, Transactions.iProduct2Usage, Transactions.iProduct3Usage, Transactions.iProduct4Usage, Transactions.iProduct5Usage, Transactions.iProduct6Usage, Transactions.iProduct7Usage, Transactions.iProduct8Usage, Users.sName1, Users.sName2, Transactions.lAccountId, Account.cBalance, Account.sDescription FROM (((Account Account INNER JOIN Transactions Transactions ON Account.cntAccountId=Transactions.lAccountId) INNER JOIN UserAccount UserAccount ON Account.cntAccountId=UserAccount.lAccountId) INNER JOIN TxTypes TxTypes ON Transactions.iTransactionType=TxTypes.cntTxTypes) INNER JOIN Users Users ON UserAccount.sBarCode=Users.sBarcodeNumber WHERE (Transactions.dtTransactionDate>={ts '2003-06-01 07:11:06'} AND Transactions.dtTransactionDate<{ts '2003-06-03 07:11:07'}) ORDER BY Account.sFinanceCode, Transactions.lAccountId, Transactions.dtTransactionDate When I run the query against MS-SQL 2000 (P4-1.8, 256M), I get a result in approx 25 secs (7009 rows). The data from the MS-SQL 2000 db has been transferred into MySQL and uses the same table structure. When I run the same query against MySQL (using the MySQL Control Centre 0.9.3-beta), the query takes 350+ secs to complete. MySQL 4 is running an AMD Athlon 2000+ with 512M RAM, Win2000 SP4. How to repeat: I can repeat the result every time. I have tried increasing the various buffer variables to take full advantage of the 512M RAM in the PC, but it seems to make very little effect (1-2 secs faster). I have changed my.ini back to: [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe [mysqld] port=3306 set-variable = key_buffer_size=64M set-variable = table_cache=256 set-variable = sort_buffer_size=4M set-variable = read_buffer_size=1M set-variable = max_allowed_packet=1M set-variable = read_rnd_buffer_size=512 If I watch the CPU usage when the query is running, it is sitting on 100% for almost then entire 350 secs.