Teiid - MSSQL Pagination

[mtawk]

I'm using Teiid 8.11.3 with mssql translator.

I have a huge table over sql server 2008 on which i'm applying pagination.

I noticed slowliness in the query execution over this table at each time I increase the pagination.

I monitored the JDBC queries execute by teiid and found out that it is using top n according to the limit used into the query.

For example:

in teiid : Select * from mytable LIMIT 90 , 10

is translated in JDBC: select top 100 from mytable

Since my table contains millions of records, when fetching the last page, the executed jdbc query is retrieving the whole table top n, to return in result the last 10 records. And the execution is taking too much time.

Is there a more optimized way to write my query with pagination in teiid for MSSQL?

[shawkins]

OFFSET isn't marked as supported by the translator.  It looks like it can be done using a window function in SQL Server 2005+ or with a more standard FETCH option starting with SQL Server 2012, so you can log an enhancement request for this.

[mtawk]

Will teiid be able to execute the query if written in native MSSQL synthax? If yes, can you show me a small example how to write my query in teiid?

[shawkins]

> Will teiid be able to execute the query if written in native MSSQL synthax?

If you are using the direct query procedure feature.  If submitting a regular Teiid SQL query, then the translator will need an enhancement.

[mtawk]

I'll add an enhancement request

[shawkins]

Thanks for logging https://issues.jboss.org/browse/TEIID-4021

It may make it into 9.0.