Simple entity list performs two queries?

Support
#1

I have a simple entity browser, when I enable the eclipselink.sql logging, I see that it issues two queries. The first gets the first N records, but then it reads it again passing each of the IDs returned from the first ??? This seems unnecessarily inefficient.

The operation I am doing is clicking the header to sort by the first column.

Here is the logs:

2018-06-19 17:53:56.961 DEBUG [http-nio-8080-exec-38/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=grocery$Item, view=com.company.grocery.entity.Item/item-view, query=select e from grocery$Item e order by e.upc desc, max=50
2018-06-19 17:53:56.973 DEBUG [http-nio-8080-exec-38/app-core/admin] eclipselink.sql - SELECT ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, NAME AS a4, UPC AS a5, VERSION AS a6 FROM GROCERY_ITEM WHERE (DELETE_TS IS NULL) ORDER BY UPC DESC LIMIT ? OFFSET ?
 bind => [50, 0]
2018-06-19 17:53:56.975 DEBUG [http-nio-8080-exec-38/app-core/admin] eclipselink.sql - [2 ms] spent
2018-06-19 17:53:56.977 DEBUG [http-nio-8080-exec-38/app-core/admin] eclipselink.sql - SELECT ID, COST, DELETE_TS, DELETED_BY, VENDOR_ITEM_NUMBER, VERSION, ITEM_ID, VENDOR_ID FROM GROCERY_VENDOR_ITEM WHERE ((ITEM_ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND (DELETE_TS IS NULL))
 bind => [21cd0cc3-9f62-0bd5-dd86-0a6f2cab5870, edecc3cd-733d-85c7-2b2d-89394e419cb6, f342646c-ee62-2244-3fc5-2ad6e5247ed2, 28fe521c-91bf-ed3f-288f-3d0d900be9c3, 463c3521-5f4a-75ac-b456-1ec21ce26e29, eaedbb23-0402-2462-929a-a74977037de1, 847926e0-4300-43c5-44c8-7c1f696476ab, 4e070a66-79e6-5ca4-ba03-1f1248c02395, ff1cbf79-f101-457d-d5fb-eff435dc94a5, da7392b9-4625-306b-f576-962868918a44, fcf4915b-e942-d9ca-d2d8-1dcc1d9fc4df, 697a5639-65e7-171b-5f6d-997f0c6965a8, b7baaa21-d2aa-6cad-91e9-f5c45a4d2a7f, e3b803f5-892b-9df5-ec80-10fd90a520d4, d46a492d-87c1-9d96-bf45-51a9b34aa15f, fb60f452-bc8c-cd05-4aa2-238969b7ef73, b9fe9717-e088-3580-88c3-31d8fd3a8037, 55fef520-69db-47db-e3db-63a19d66112a, b1dcb514-d3d0-16e4-5401-25a5ac13e53b, fdc02124-03b5-da60-860d-a796a7403fb8, f4c554c5-7de1-d2d4-4f66-a8f378f43ffb, be9f406d-3eab-013f-c725-5b5cc9f173d2, 74b0e01a-cbf6-3076-67ac-64c0c0eaa320, 6ba5b431-353b-066b-89b7-9ef1c349c632, b81b5f14-2419-bc7e-504e-4bda6ce6f603, d9663bbe-acc6-e553-cfc4-6e9a665ed009, 4f7ed15b-3e7e-e43a-3784-e598c6d94239, 40c05908-d6c2-4e8b-c4ba-19dfa190306f, 9bd47222-d8f5-f72a-fddf-afa0f949399a, 5ccbb87c-e1d0-99c5-026b-96a865d706ec, 8d59bd4b-09e7-98d5-19fa-9062b63821c6, 3fc814b5-ed38-1b7d-ecf6-8866a9c0bf3a, aa7af62b-fa53-3411-c3e2-7dde5a5cca0a, 6eac212d-e82e-e6f8-542b-bc2eb47f522e, 0853870b-b91d-c9a4-7049-3f3621914be1, 4f43ecd2-2d10-d572-0479-c74d0be8efbe, 385200d6-b65b-ac3c-8e0e-46bb269e8961, 584875c9-5a13-4d20-59f3-88fb2f0e045e, 97ba2240-1500-c159-7b3a-1fbbbb6175fa, ac29bfbb-c4d6-0f6b-9131-a02ac2f25883, 680c31a6-3c48-b07e-b377-b160278fa9b2, 6300a5bd-4e5c-4475-24e9-def7c0d545f6, 13e6352f-58f1-d33f-eb7c-be3e3820cdb1, 15d9fc5b-910a-ef49-0bd0-80aa89866071, 967f8be7-19ef-944a-f2d4-152f2d89ab22, 9f796105-2ea0-0cdc-0b73-d413d6792cb1, 605ebd6f-b254-62f7-a09a-fdd511d506a3, dd74a7a7-8142-8c62-2d89-9f6c0743273f, ff00a399-7526-c394-b036-31f4d0910603, 5517fbd3-1631-e832-a283-3b091c4ad7d3]
2018-06-19 17:53:56.978 DEBUG [http-nio-8080-exec-38/app-core/admin] eclipselink.sql - [1 ms] spent

Here is a screen shot of the browser:

image

#2

As you can see, the second SQL selects another entity (mapped to GROCERY_VENDOR_ITEM). So your list of entities is not that simple - you are actually loading a graph of entities. Probably your view for the Item entity includes a nested collection, which is loaded with BATCH fetch mode (see about view fetch modes here).

#3

Ah, I see that the browser was configured to use a view, that had the relation - even though I was not displaying any of those fields.

Thanks for the linked documentation - makes sense now.

It would seem that for most ‘views’ the mode should be ‘join’ not ‘batch’, is there some reason that batch is the default ?

Still, I changed it to use ‘join’ and it is much better, but it still issues 2 queries - I guessed the second is because I have a ‘selected row’, but when I select a different row, no query is issued, so I am still confused as to the second query:

2018-06-20 07:35:00.654 DEBUG [http-nio-8080-exec-6/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=grocery$Item, view=com.company.grocery.entity.Item/item-view, query=select e from grocery$Item e order by e.upc, max=50
2018-06-20 07:35:00.662 DEBUG [http-nio-8080-exec-6/app-core/admin] eclipselink.sql - SELECT t1.ID AS a1, t1.DELETE_TS AS a2, t1.DELETED_BY AS a3, t1.NAME AS a4, t1.UPC AS a5, t1.VERSION AS a6, t0.ID AS a7, t0.COST AS a8, t0.DELETE_TS AS a9, t0.DELETED_BY AS a10, t0.VENDOR_ITEM_NUMBER AS a11, t0.VERSION AS a12, t0.ITEM_ID AS a13, t0.VENDOR_ID AS a14, t2.ID AS a15, t2.DELETE_TS AS a16, t2.DELETED_BY AS a17, t2.NAME AS a18, t2.VERSION AS a19 FROM GROCERY_ITEM t1 LEFT OUTER JOIN GROCERY_VENDOR_ITEM t0 ON ((t0.ITEM_ID = t1.ID) AND (t0.DELETE_TS IS NULL)) LEFT OUTER JOIN GROCERY_VENDOR t2 ON (t2.ID = t0.VENDOR_ID) WHERE (t1.DELETE_TS IS NULL) ORDER BY t1.UPC LIMIT ? OFFSET ?
 bind => [50, 0]
2018-06-20 07:35:00.664 DEBUG [http-nio-8080-exec-6/app-core/admin] eclipselink.sql - [2 ms] spent
2018-06-20 07:35:00.668 DEBUG [http-nio-8080-exec-6/app-core/admin] eclipselink.sql - SELECT ID, COST, DELETE_TS, DELETED_BY, VENDOR_ITEM_NUMBER, VERSION, ITEM_ID, VENDOR_ID FROM GROCERY_VENDOR_ITEM WHERE ((ITEM_ID = ?) AND (DELETE_TS IS NULL))
 bind => [17d9db38-370d-7a12-78ec-6e2d0f088441]
2018-06-20 07:35:00.668 DEBUG [http-nio-8080-exec-6/app-core/admin] eclipselink.sql - [0 ms] spent
#4

It would seem that for most ‘views’ the mode should be ‘join’ not ‘batch’, is there some reason that batch is the default ?

BATCH is the default when multiple root entities are selected or if there is more than one nested collection in the view. Otherwise JOIN is used, so if a view with one nested collection is used in an entity editor, the framework will execute one SQL select. We find this scheme the most optimal for database performance.

I changed it to use ‘join’ and it is much better, but it still issues 2 queries

I’m not sure about the cause of the second select. If it’s a standard browse screen without any modifications, selection of a row does not cause any DB operations. Could you provide a test project?

#5

Is there some easy way to zip a project? The directory structure doesn’t support this easily as there are lots of lower level ‘build’ directories.

Maybe this should be added as a gradle task to the default build file ? I see the ‘distribution’ tasks but have no idea where the output goes ???

#6

Never mind, I found the ‘zip project’ in the studio menu - not sure why the gradle task doesn’t perform the same way… anyway, I am attaching the project.

grocery.zip (338.7 KB)
grocery.zip

#7

Thank you for the test project (BTW, there is zipProject Gradle task that is invoked from the Studio menu).

We have created an issue for the problem with the extra SQL selects.