[METAMODEL-1132] Metamodel doesn't support a native paging on SQL Server and Oracle database

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.5.4
• Fix Version/s: 4.5.5
• Labels: performance
• Environment: Metamodel with SQL Server or Oracle database
• Assignee: Unassigned
• Reporter: Radek Poskočil
• Created: 06/Dec/16 21:33
• Updated: 21/Dec/16 07:57
• Resolved: 21/Dec/16 07:57

Description

Problem description

If a database contains a lot of records, let say a million of records, and user wants to execute a query with a good performance then he will probably use a native paging constucts of the database dialect. Without this feature the user cannot efficiently execute select queries.

The MetaModel supports it just in MySQL and PostgreSQL. In this case a query properties firstRow and maxRows are rewritten to OFFSET and LIMIT constructs. See MySQLQueryRewriter which extends LimitOffsetQueryRewriter where you can find the method with it.

SQLServerQueryRewriter supports the special construct TOP which equals maxRows but says nothing about firstRow. Althrough the user set the firstRow attribute. It will be IGNORED and couse a big performance issue.

A similar issue you can find in OracleQueryRewriter.

Current behavior

OracleQueryRewriter and SQLServerQueryRewriter ignores Query.setFirstRow(). OracleQueryRewriter ignores maxRows, too.

Expected behavior

There are possibilities to rewrite queries to SQL Server and Oracle dialects.

for example: SQL Server 2012+ or Oracle 12c
SELECT fieldA,fieldB
FROM table
ORDER BY fieldA
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY;

Issue Links

• links to: GitHub Pull Request #137