Issue while querying using maxRows property in Teiid's JDBC driver

[pranavk]

Hi,

I'm facing issues while querying data using Teiid's JDBC driver (connecting to a SQL DB) when the maxRows property of the prepared statement I created is set to a low value. The scenario here is, that I want to show a snapshot of a large dataset (100 rows out of a total 6mil in the table). For this purpose, I set the maxRows property of the prepared statement to 100, and fetch size remains the default 2048, as is in Teiid. When I run the query on this statement i.e

resultSet = preparedStatement.executeQuery("select * from Table");

it takes a very long time to execute; maxRows property does not have the same limiting effect as it does with other standard JDBC drivers.

When I increase the maxRows count to a value of say, 10000, it runs rather quickly (which is exactly the opposite behavior compared to other standard JDBC drivers). Could someone please point as to why this could be occurring?

Thanks,

Pranav

[shawkins]

> it takes a very long time to execute; maxRows property does not have the same limiting effect as it does with other standard JDBC drivers.

Do you mean that your result set contains more than 100 rows, or that it seems to take longer than you expect?  If it's the latter, that could be due to the particulars of the query plan - are you going straight against a source table or are there other steps involved? 

With a Teiid prepared statement, the affect of the max rows is entirely on the client side.  As far as I know that is typical typical with drivers.

> When I increase the maxRows count to a value of say, 10000, it runs rather quickly (which is exactly the opposite behavior compared to other standard JDBC drivers). Could someone please point as to why this could be occurring?

I'm not sure what you are seeing.  You wouldn't expect much difference as the limit will simply be enforced on the client either way.

If you use a limit that will be enforced on the server side.

[pranavk]

Steve,

> Do you mean that your result set contains more than 100 rows, or that it seems to take longer than you expect?

No, the result set contains 100 rows, but yes setting this maxRows property makes the code take a long time on the execute statement mentioned in my question above. Below, the connection is made to VDB having a source model which connects to the T_6mil table (6mil X 44 cols).  The code snippet is as follows:

this.preparedStatement = connection.prepareStatement("select * from T_6mil");

this.preparedStatement.setMaxRows(maxRows);

resultSet = preparedStatement.executeQuery();

The difference here is quite notable. When I set a value of 0 or a high value like 100000, the third line runs instantly giving me a resultset with 128 records. But setting a value of 100 makes this 3rd line take a very long time time to execute and gives a resultset with the 100 records as set. This behavior gets reversed when I make this connection object directly using the JTDS MsSql JDBC driver.

[shawkins]

> The difference here is quite notable. When I set a value of 0 or a high value like 100000, the third line runs instantly giving me a resultset with 128 records. But setting a value of 100 makes this 3rd line take a very long time time to execute and gives a resultset with the 100 records as set. This behavior gets reversed when I make this connection object directly using the JTDS MsSql JDBC driver.

I'm still unsure of what you are seeing.  The server side execution will be the same in either case.  Can you post the full code for your scenario and the query plan (that is unless it is simply direct to a source table)?

[pranavk]

Yes I am observing this contrasting behavior while directly querying the source through Teiid, and no federation scenario is involved.

[shawkins]

We'll either need to see the full scenario and/or a profiler run to confirm what is taking the time.  I cannot reproduce what you are seeing locally using a couple of simple scenarios, so more details would be needed.

[pranavk]

Could you tell me the scenarios that you tried? I'm able to consistently see the behavior, in one of the scenarios I was querying an MS SQL server running on my local machine using its jtds driver, the table had 44 columns and 6 million rows. Running it without Teiid using maxRow value of 100 gave an instant result whereas it ran through Teiid the same way, only by giving a value like 100000. (There was no federation involved, just direct source query).

[shawkins]

I ran against a source producing several hundred thousand rows with setting the max rows as 2^n with n from 0 to 16.  The result was a proportional increase in runtimes.

However when running against a database like mysql through Teiid, the max rows setting on a preparedstatement will have very little impact on the overall execution time.  This is because the mysql driver by default is not using a cursor and instead materializing the whole result set. 

Also with the default configuration 2 batches will at least be obtained from the source (as the execution will be multi-threaded and we'll prefetch a batch), but otherwise you shouldn't see Teiid reading beyond that for a small max rows. 

Again we would need to start with your exact scenario.  If you can use a profiler to analyze the wait, or provide the code you are using for a representative scenario.

And again if you are using a prepared statement it would be best to use the limit clause rather than setting the max rows.

[pranavk]

Setting the limit as a part of the query doesnt fit my scenario. At the end are snapshots from the profiler, the driver here was jtds using which I connected to MS SQL server and queried a table having 3-3.5 mil rows and around 44 cols. Let me know if any more information would be helpful. Here is the sample program based on the Embedded Porfolio example:

  private static void buildDeployVDB(EmbeddedServer teiidServer) throws Exception

  {

    // model for the jdbc source

    ModelMetaData jdbcModel = new ModelMetaData();

    jdbcModel.setName("a");

    jdbcModel.setSchemaSourceType("native");

    jdbcModel.addSourceMapping("mssql-connector", "sqlserver", "a");

    teiidServer.deployVDB("example1", jdbcModel);

  }

  public static void main(String args[])

  {

    try

    {

      EmbeddedConfiguration ec = new EmbeddedConfiguration();

      ec.setUseDisk(true);

      EmbeddedServer embeddedServer = new EmbeddedServer();

      embeddedServer.start(ec);

      JtdsDataSource sqlServerDataSource = new JtdsDataSource();

      sqlServerDataSource.setUser("user");

      sqlServerDataSource.setPassword("pass");

      sqlServerDataSource.setServerName("host");

      sqlServerDataSource.setPortNumber(1433);

      sqlServerDataSource.setDatabaseName("a");

      EmbeddedServer.ConnectionFactoryProvider<DataSource> sqlServerJdbcProvider = new EmbeddedServer.SimpleConnectionFactoryProvider<DataSource>(

              sqlServerDataSource);

      embeddedServer.addConnectionFactoryProvider("a", sqlServerJdbcProvider);

      embeddedServer.addTranslator(SQLServerExecutionFactory.class);

      buildDeployVDB(embeddedServer);

      TeiidDriver td = embeddedServer.getDriver();

      Connection c = null;

      c = td.connect("jdbc:teiid:example1", null);

      //manual execution

      PreparedStatement statement = c.prepareStatement("select * from \"dbo\".\"3M\"");

      statement.setFetchSize(0); // to use the default 2048

      statement.setMaxRows(100);

      ResultSet rs = statement.executeQuery();

    }

    catch(SQLException | TranslatorException e)

    {

      e.printStackTrace();

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

  }

  private static void execute(Connection connection, String sql, boolean closeConn) throws Exception {

    try (Statement statement = connection.createStatement()){

      boolean hasResults = statement.execute(sql);

      if (hasResults) {

        ResultSet results = statement.getResultSet();

        ResultSetMetaData metadata = results.getMetaData();

        int columns = metadata.getColumnCount();

        System.out.println("Results");

        for (int row = 1; results.next(); row++) {

          System.out.print(row + ": ");

          for (int i = 0; i < columns; i++) {

            if (i > 0) {

              System.out.print(",");

            }

            System.out.print(results.getString(i+1));

          }

          System.out.println();

        }

        results.close();

      }

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      if (connection != null && closeConn) {

        connection.close();

      }

    }

  }

[shawkins]

When the max rows is not set we will issue a cancel to the source query prior to close, but this does not appear to happen in the max rows case.  The default behavior then for SQL Server (as it seems with both jtds and the ms driver) is to fully read the results on close if the query has not been cancelled.  So an issue would be needed to ensure that we call cancel in the max rows case as well.

[shawkins]

See also https://sourceforge.net/p/jtds/bugs/566/

[shawkins]

This was logged as: [TEIID-3275] remaining work should always be canceled - JBoss Issue Tracker to be addressed for 8.10 Beta1.

[pranavk]

Thanks.