Keith Arnold wrote: > > 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?
In your case a dbproc would be really helpful, doing the following (not correct syntax, just the way of doing) Create dbproc my_update .... Var sublotno ...; report_id ...; Declare cursor_of_tmptbl cursor for select * from tmptbl; Fetch cursor_of_tmptbl into :sublotno, :report_id; While ($RC = 0)DO BEGIN UPDATE DEVICEINFO set GROUPNUM = :sublotno Where report_id = :report_id; (perhaps some return-code-check here or a try..catch around the whole stuff) Fetch cursor_of_tmptbl into :sublotno, :report_id; END; If you want to update with one statement you always will have the trouble of not having a nice restriction without subquery. And a subquery-restriction together with a correlated subquery (as the subquery in your set-clause is) will never be fast, unfortunately. Elke SAP Labs Berlin > > 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]