search

LinqToDB4iSeries / Linq2DB4iSeries

LinqToDB provider for the DB2 database on IBM iSeries
MIT License
18 stars 5 forks source link

Skip performance #46

Closed jwakelim closed 3 years ago

jwakelim commented 3 years ago

Our iSeries is on 7.3 and supports OFFSET n ROWS, but the SQL generated always contains ROW_NUMBER() OVER() if Skip is specified. This causes the execution time of the query to go from ~50 ms to 5 seconds.

From what I can tell the SqlOptimizer always calls ReplaceTakeSkipWithRowNumber so it will always convert to use ROW_NUMBER().

Is there a way to prevent the conversion of Skip to ROW_NUMBER() or is there something else we can do to increase the performance?

nsarris commented 3 years ago

This wasn't supposed to happen, I'll take a look at it

nsarris commented 3 years ago

Ok good catch @jwakelim, it was regression issue from merging in the current DB2 implementation as it doesn't support OFFSET yet. It's fixed on 3.1.6.1, please check it out and let me know.

jwakelim commented 3 years ago

Yes, that fixed it. Thank you!