OrmLite Data Stream to reduce memory usage

ServiceStack.OrmLite
1

Our application will need to create queries that will eventually exceed available memory.

For this reason, we used the SelectLazy method. Recently I looked at the source and found that the database still pulls the entire result set but the results are enumerated using a yield.

I found that there is a technique called a Forward-only result set. While this code is java, that same calls should be available in c#

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Is there anyway I can add this to my ormlite querys?

Is there a better way that you know of?

Thanks for the help.

Happy Holidays…

2

I think this is the class that needs to be used in mysql

Provides a means of reading a forward-only stream of rows from a MySQL database. This class cannot be inherited.

https://dev.mysql.com/doc/dev/connector-net/8.0/html/T_MySql_Data_MySqlClient_MySqlDataReader.htm

3

What MySql’s ADO .NET implementation does is what determines what behavior it performs behind-the-scenes but OrmLite doesn’t pull all the results first, it executes the reader and yields through each result one at a time.

You’re not going to be able to modify OrmLite’s reader which uses ADO .NET abstraction which doesn’t support this MySql specific feature, but if you’re able to somehow customize the MySqlCommand to enable this behavior then you’ll be able to intercept your SelectLazy queries with an Exec filter, e.g:

public class MySqlExecFilter : OrmLiteExecFilter
{
    public override IEnumerable<T> ExecLazy<T>(IDbConnection dbConn, Func<IDbCommand, IEnumerable<T>> filter)
    {
        var dbCmd = CreateCommand(dbConn);
        if (dbCmd.ToDbCommand() is MySqlCommand mysqlCmd)
        {
            // customize MySQL command...
        }
        try
        {
            var results = filter(dbCmd);

            foreach (var item in results)
            {
                yield return item;
            }
        }
        finally
        {
            DisposeCommand(dbCmd, dbConn);
        }
    }
}

MySqlDialect.Instance.ExecFilter = new MySqlExecFilter();

Otherwise my preference would be to execute individual explicit Skip/Take batch queries instead of relying on impl-specific bespoke features like this.

4

From what I’ve read

In Ado.net using a SqlDataReader class creates a cursor that reads the database one row at a time.

SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine(“\t{0}\t{1}”, myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();
}

Would something like this work? if the psudo code GetEntireRow was replaced?

public class MySqlExecFilter : OrmLiteExecFilter
{
    public override IEnumerable<T> ExecLazy<T>(IDbConnection dbConn, Func<IDbCommand, IEnumerable<T>> filter)
    {
        var dbCmd = CreateCommand(dbConn);
        var reader = dbCmd.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                T ret = reader.GetEntireRow();
                yield return ret;
            }
        }
        finally
        {
            DisposeCommand(dbCmd, dbConn);
        }
    }
}
5

SqlDataReader is an SQL Server concrete ADO .NET Provider class, MySql uses their own ADO .NET implementation classes, e.g. MySqlCommand. Yielding reading a row per reader Read() is what OrmLite’s *Lazy APIs already does, as mentioned the behavior is dependent of the ADO .NET provider implementation.

In your example the filter containing the actual query doesn’t get executed. You wont be able to intercept the Reader which is managed by OrmLite.

You can skip OrmLite for this and use MySQL’s concrete classes directly to make use of their impl-specific features. You can get the MySqlConnection with:

var mysqlConn = db.ToDbConnection() as MySqlConnection;
6

You say:

Yielding reading a row per reader Read() is what OrmLite’s *Lazy APIs already does,

So you’re saying that your implementation uses a forward-only dataset and calls Read() for every row returned. Great!

The problem is sometimes we’re seeing a 7-second delay before the first row is returned.

I guess we will keep looking…

7

I said OrmLite’s Lazy APIs yields every reader row, which is what your broken lazy Exec filter implementation is trying to do, which if worked wouldn’t achieve anything over OrmLite’s implementation (that already does this) as it makes no attempt to access MySQL specific functionality that you’re referring to which can’t be done in a filter if it needs to use MySql’s custom reader APIs.

I suggest you explicitly page queries or avoid OrmLite and use MySQL classes directly to make use of its forward only dataset feature your original post is referring to.

8

Got it. Sorry for the confusion.

Happy Holidays

1 Like