[teiid-issues] [JBoss JIRA] (TEIID-4021) MSSQL Pagination

[Mark Tawk (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4021?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13284306#comment-13284306 ] 

Mark Tawk commented on TEIID-4021:
----------------------------------

I have implemented the fix, it works perfectly with SQL Server 2012.
But with SQL Server 2008 and 2005, i'm getting an exception whenever i use pagination in Teiid query.

org.teiid.jdbc.TeiidSQLException: TEIID30504 implify_dataModel_YfVDB: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."DbTRX20112015LRed1006_Amoun" AS c_0, g_0."DbTRX20112015LRed1006_Creat" AS c_1, g_0."DbTRX20112015LRed1006_Lastt" AS c_2, g_0."DbTRX20112015LRed1006_RIM" AS c_3, g_0."DbTRX20112015LRed1006_TRX20" AS c_4, g_0."ID" AS c_5 FROM "implify_data"."dbo"."DbTRX20112015LRed1006" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY]

> MSSQL Pagination
> ----------------
>
>                 Key: TEIID-4021
>                 URL: https://issues.jboss.org/browse/TEIID-4021
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: JDBC Connector
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>             Fix For: 9.1
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)