SmartGWT SQL generation for Oracle enhancement suggestion: Parameter binding

[Blama]

Hi Isomorphic,

I'm using current SmartGWT 4.1p and have a SQLDataSource-subclass, that adds a tenant_id (and sometimes a user_id) whereClause to every request.

This means that even the simplest display of a databound component results in a different SQL statement sent to the database.

With respect to the Oracle 11.2 docs these queries result in different sqlIds in Oracle, each needing a hard parse. This is true even if they result in the same execution plan afterwards. As these requests go to complex views in my case, they take some time (3 sec) the first time executed, while subsequent executions take only some milliseconds for my testdata.

In order to make queries like

SELECT col1, col2, col3 FROM employees WHERE tenant_id = 123;
SELECT col1, col2, col3 FROM employees WHERE tenant_id = 456;

use the same sqlId, they must be changed to

SELECT col1, col2, col3 FROM employees WHERE tenant_id = :a;

Is there already an option in server.properties that enables parameter binding? Do you plan to support this for Oracle/JDBC in general?

Thank you & Best regards,
Blama

[Isomorphic]

We don't currently use parameter binding for most cases (binary is an exception) because it's an area with lots of bugs, and makes SQL customization more complicated.

A SQL parse is normally a trivial amount of overhead, and your results of a faster query are probably explained by caching of the data itself or other factors.

Also, it would be relatively uncommon that the SQL string would end up exactly the same for several reasons:

1. the only way to do efficient paging in Oracle requires wrapping the main query with a row range query that necessarily differs every time (row start/end)

2. with an advanced UI like Smart GWT, people tend to query on a different set of fields each time, or modify the sort or other parts of the query each time

3. we would have to turn parameter binding off for many cases of SQL customization

That all said, if you can put some numbers together that clearly show a large benefit from avoiding the SQL parse as such (without conflating the effects of data caching), we can consider adding some kind of flag that would cause parameter binding to be used in edges cases where it could have an impact.

[Blama]

Hi Isomorphic,

I'm still on an old Oracle XE, but will get Oracle SE1 today or next week. I had this problem before and found out that it must be related to parsing.
Please see this OTN forums post, perhaps it helps.

Otherwise I'll dive deep into it once I have the time and try to generate a test case and some numbers for you. But the linked thread should give you hint about what I mean.

Best regards,
Blama

[ssequeira]

Hello. Has this been addressed in the latest smartGWT release(s)?
Related to your comments about why you don't consider support for it, please allow me to counter argue.
1. each page would be assigned a plan. If the user doesn't get his results on the first 3 or 4 pages then he is doing something wrong. So having 3, 4 or even 20 execution plans is still fine. The problem is that without parameter binding support we end up having almost as many plans as records%page on the table.
2. same as 1. Still ok to have 1 plan for each field search combination. Multiply this by the number of different values that can be put on each field and we can easily reach the thousands of plans in a very short period of time.
3. still ok, I would assume this feature could be toggled on/off

Our application provides many search pages which show a form, grid and details panel. After search results are displayed on the grid, the user may click any record on the grid and the details for that record are displayed. The details are queried by an ID. So the query has always the same format, the only thing that differs is obviously the ID. The query itself to get the details might be actually quite complex, with multiple joins, etc.. So even for this sole use case there would be a noticeable performance increase, since for all these type of queries there would be just one execution plan, instead of thousands of execution plans.

Thanks.

[Isomorphic]

You talk about having 3 or 4 plans for the first 3-4 pages of data, but that wouldn't happen: the pages aren't fixed, they are dynamically determined based on the viewport, which is based on the user's screen size, and even for a single user can differ according to which component does the query (eg grid using most of the screen vs smaller comboBox) and differ as the user resizes the browser or resizes components to give grids more or less space.

Then, take the many many plans for different ranges of rows, and multiple that by all the combinations of fields and sort order that might come up.

Your proposal would basically take a bunch of ad-hoc queries and *explicitly ask the DB to cache plans for them*. Since the likelihood of being able to reuse a plan is very low, it could easily backfire. Of course, as far as the row range issue, there could be an algorithm that tries to find a plan that has the next largest row range and simply returns extra rows, but then would that even be an efficiency gain?

That's why we said:

... if you can put some numbers together that clearly show a large benefit from avoiding the SQL parse as such (without conflating the effects of data caching), we can consider adding some kind of flag that would cause parameter binding to be used in edges cases where it could have an impact

Optimization attempts based on guesses usually fail, and often backfire. So to even consider implementing this, we would need to see real-world data from thousands of users triggering real-world queries, and based on that large query set, large gains from using parameter binding.

Also note: *if* it could be determined that a setting to use parameter binding would optimize certain edge cases, adding such a setting would require a Feature Sponsorship.

[ssequeira]

Hi. Thanks for your comments.

Nevertheless you assume many things which are business/application dependent.
For example when you say the page size depends on the user's screen size, that might be true if you keep the default behavior. Our page sizes are fixed on application/feature level and not dependent on screen real estate. I feel it is safe to assume there might be others following the same approach.
The same applies for sorting. There may be some fields on which you can sort, but majority might not be sortable. It also depends a lot on the feature.
Again, not saying this is a setting that could be used across all features/datasources, but surely on many of them.
Like I have mentioned the search by ID (for details) is a very obvious use case, if we could enable such a feature on all our screens it would already improve a lot those queries.

I will gather some statistics and figure out real numbers.
Rgds.

[Isomorphic]

We *just mentioned* that page sizes could be made uniform, but that that would require fetching extra rows, so no, we weren't assuming that.

However, that's another area where we would highly recommend looking at real-world data to see if you've actually made an improvement. Setting fixed page sizes creates an awkward balance between trying to avoid multiple requests vs loading lots of rows unnecessarily. With a highly dynamic UI where a given grid appears initially small but where users can fill the screen with the grid as needed, and considering a mobile phone vs 30" portrait display, there is just is no good single number for a page size, so we would intuitively expect that optimization strategy to backfire - real world data would again, answer definitively.

[Blama]

Hi Isomorphic,

coming back to this post, as I read it again today.
I still have the problem with the hard parses (alsough somewhat mitigated already with simpler queries). It's for sure not data-caching related, as also a space in the query triggers the "1st slow, then fast"-phenomenon.
W.r.t to your points:

• I agree with your point 2.
• I don't know about 3.
• Point 1 does not apply here IMHO, at least not for Oracle, where you do the startRow/endRow via JDBC.

But point 2 alone is unfortunately already enough that one needs to ensure that hard parses are always fast, making this a developer issue and not a framework issue.

Best regards
Blama

We don't currently use parameter binding for most cases (binary is an exception) because it's an area with lots of bugs, and makes SQL customization more complicated.

A SQL parse is normally a trivial amount of overhead, and your results of a faster query are probably explained by caching of the data itself or other factors.

Also, it would be relatively uncommon that the SQL string would end up exactly the same for several reasons:

1. the only way to do efficient paging in Oracle requires wrapping the main query with a row range query that necessarily differs every time (row start/end)

2. with an advanced UI like Smart GWT, people tend to query on a different set of fields each time, or modify the sort or other parts of the query each time

3. we would have to turn parameter binding off for many cases of SQL customization

That all said, if you can put some numbers together that clearly show a large benefit from avoiding the SQL parse as such (without conflating the effects of data caching), we can consider adding some kind of flag that would cause parameter binding to be used in edges cases where it could have an impact.

[Isomorphic]

Point 1 does apply - see sqlPaging property - there are multiple available modes.