I created an index on TMPTBL(record_id) and reran the EXPLAIN:
TBL COLUM/INDEX STRATEGY PAGECNT
D deviceinfo_idx index scan 379618
Only index accessed
T tmpidx join via indexed column 1
Result copied, costvalue 346439
Still the update takes forever.
Keith
-----Original Message-----
From: Keith [mailto:[EMAIL PROTECTED]
Sent: Friday, March 31, 2006 5:20 PM
To: 'Kakhandiki, Ashwath'; 'Keith Arnold'; 'maxdb@lists.mysql.com'
Subject: RE: Horrible Query Performance on Update
TBL COLUM/INDEX STRATEGY PAGECNT
T table scan 42
D deviceinfo_idx join via indexed column 379618
No temp results created
Result copied, costvalue 862630
-----Original Message-----
From: Kakhandiki, Ashwath [mailto:[EMAIL PROTECTED]
Sent: Friday, March 31, 2006 4:47 PM
To: Keith Arnold; maxdb@lists.mysql.com
Subject: RE: Horrible Query Performance on Update
Hi Keith,
What is the result of this explain statement?
Explain
SELECT T.SUBLOTNO FROM TMPTBL T, DEVICEINFO D
WHERE T.REPORT_ID = D.REPORT_ID
I think it might help if you create an index on TMPTBL (report_id,
sublotno).
Thanks,
Ashwath Kakhandiki
MAXDB Dev. Support
SAP Labs LLC, Palo Alto
-----Original Message-----
From: Keith Arnold [mailto:[EMAIL PROTECTED]
Sent: Friday, Mar 31, 2006 14:36 PM
To: maxdb@lists.mysql.com
Subject: Horrible Query Performance on Update
Friendly and Helpful List Members,
We are running MaxDB 7.5.00.24 on an X86 machine w/ 2x3Ghz CPU, 2G ram,
SCSI Mirrored drives, Win2K3 Std. Data cache and catalog hit rates are
above 99.9%.
I have 2 tables:
DEVICEINFO (report_id int, groupnum int) approx 27,000,000 records
(2.8GB), no keys but an index on report_id There are approx 1000 records
per unique report_id.
TMPTBL (report_id int, sublotno int) approx 200 records, no keys, no
index
When I run the SQL
UPDATE DEVICEINFO D
SET GROUPNUM = (SELECT SUBLOTNO FROM TMPTBL T
WHERE T.REPORT_ID = D.REPORT_ID)
The data base drops to it's knees and eventually times out. I tried
adding WHERE REPORT_ID IN (SELECT REPORT_ID FROM TMPTBL) To the bottom
the update query but it makes no difference.
Does anyone have any thoughts on why this query is so horribly slow, how
I might rewrite it, or what I might do to tune the DB?
Your assistance and insight will be greatly appreciated.
Regards,
Keith Arnold
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
