DBClean for Guest Users does not perform well when there are many Guest Users in the Database

Technote (troubleshooting)


Problem(Abstract)

When a WebSphere Commerce database contains a large number of guest users, DBClean may take an extremely long time to run when used to clean out those guest users. This can be the case even if the database statistics and tables are in a well-maintained state.

Cause

The SQL that is used to delete guest users is extremely complicated and contains several unnecessary predicates.

Resolving the problem

The SQL has been re-written in order to remove the unnecessary predicates. A different version of the new query is needed, depending on whether a DB2 or an Oracle database is being used.

DB2 Database:

The new version of the SQL that is used to delete guest users is this:

delete from member where member_id in ( select users_id from users T1 where registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ? And not Exists (select 1 from orders where orders.member_id=t1.users_id and status != 'Q') and (users_id > 0))

For comparison purposes, here is the old version:

delete from member where member_id in (select users_id from users where registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ? and (users_id not in (select member_id from orders where status != 'Q')) and (users_id > 0) and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null and status != 'Q') or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null and status != 'Q') or address_id in (select address_id from orders where address_id is not null and status !='Q'))))

Oracle Database:

The new version of the SQL that is used to delete guest users is this:

delete from member where member_id in (select users_id from users T1 where registertype='G' and (sysdate - ?) >= prevlastsession And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') and (users_id > 0))

For comparison purposes, here is the old version:

delete from member where member_id in (select users_id from users where registertype='G' and (sysdate - ?) >= prevlastsession And (users_id not in (select member_id from orders where status != 'Q')) and (users_id > 0) and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null and status != 'Q') or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null and status != 'Q') or address_id in (select address_id from orders where address_id is not null and status !='Q'))))

Contact WebSphere Commerce Support and request an APAR for this new query. Alternatively, the new query is included in both the WebSphere Commerce 6.0.0.2 Fix Pack and the WebSphere Commerce 5.6.1.3 Fix Pack.


Cross Reference information
Segment Product Component Platform Version Edition
Commerce WebSphere Commerce Professional Edition Performance AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux zSeries, Solaris, Windows 5.6.1, 6.0 Professional Edition
Commerce WebSphere Commerce Business Edition Performance AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux zSeries, Solaris, Windows 5.6.1 Business Edition
Commerce WebSphere Commerce - Express Performance AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux zSeries, Solaris, Windows 5.6.1, 6.0 Express

Rate this page:

(0 users)Average rating

Document information


More support for:

WebSphere Commerce Enterprise
Performance

Software version:

6.0

Operating system(s):

AIX, Linux, Solaris, Windows, i5/OS

Software edition:

Enterprise

Reference #:

1283886

Modified date:

2009-08-02

Translate my page

Machine Translation

Content navigation