-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: 6.2.X EE, 7.0.1 CE GA2
-
Fix Version/s: 6.2.X EE, 7.0.1 CE GA2
-
Component/s: DM
-
Branch Version/s:6.2.x
-
Backported to Branch:Committed
-
Story Points:1.75
-
Fix Priority:3
-
Git Pull Request:
When we migrated from Liferay 6.0 to Liferay 6.1 automatically the update script ( "update-6.0.6-6.1.0.sql" ) generates new Fields in DLFileEntry Table:
....
alter table DLFileEntry add smallImageId LONG;
alter table DLFileEntry add largeImageId LONG;
alter table DLFileEntry add custom1ImageId LONG;
alter table DLFileEntry add custom2ImageId LONG;
....
Looking for a better performance we observed in AWR and ADDM Oracle DB that the following sql query behaves too slowly:
SELECT dlfileentry.fileentryid AS fileentr1_85_0_,
dlfileentry.uuid_ AS uuid2_85_0_, dlfileentry.groupid AS groupid85_0_,
dlfileentry.companyid AS companyid85_0_,
dlfileentry.userid AS userid85_0_,
dlfileentry.username AS username85_0_,
dlfileentry.versionuserid AS versionu7_85_0_,
dlfileentry.versionusername AS versionu8_85_0_,
dlfileentry.createdate AS createdate85_0_,
dlfileentry.modifieddate AS modifie10_85_0_,
dlfileentry.repositoryid AS reposit11_85_0_,
dlfileentry.folderid AS folderid85_0_, dlfileentry.NAME AS name85_0_,
dlfileentry.extension AS extension85_0_,
dlfileentry.mimetype AS mimetype85_0_, dlfileentry.title AS title85_0_,
dlfileentry.description AS descrip17_85_0_,
dlfileentry.extrasettings AS extrase18_85_0_,
dlfileentry.fileentrytypeid AS fileent19_85_0_,
dlfileentry.VERSION AS version85_0_, dlfileentry.size_ AS size21_85_0_,
dlfileentry.readcount AS readcount85_0_,
dlfileentry.smallimageid AS smallim23_85_0_,
dlfileentry.largeimageid AS largeim24_85_0_,
dlfileentry.custom1imageid AS custom25_85_0_,
dlfileentry.custom2imageid AS custom26_85_0_
FROM dlfileentry
WHERE (dlfileentry.smallimageid = :1)
OR (dlfileentry.largeimageid = :2)
OR (dlfileentry.custom1imageid = :3)
OR (dlfileentry.custom2imageid = :4)
Looking the update scripts of later versions ( 6.2, 7.0 ) no actions about it we saw.
With the unique creation of the indexes we have noticed through performance indicators it has improved considerably:
create index LPS_DLENT_SMALLIMGID on DLFileEntry (smallimageid);
create index LPS_DLENT_LARGEIMGID on DLFileEntry (largeimageid);
create index LPS_DLENT_CUSTOM1IMGID on DLFileEntry (custom1imageid);
create index LPS_DLENT_CUSTOM2IMGID on DLFileEntry (custom2imageid);
We recommend add as improvement.