Try to update statistics: update statistics TMPTBL estimate sample 100 percent
Alexei Novakov Keith Arnold wrote: > 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]
