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]

Reply via email to