MSSQL Text type column

Thread is closed for posting
2 posts, 0 answers
  1. Alexander Turlov
    Alexander Turlov avatar
    4 posts
    Member since:
    May 2010

    Posted 08 Jan 2014 Link to this post

    Is there an implicit limitation on the size of a text type column anywhere in OpenAccess?

    I have stumbled upon a weird case when if a text column content is larger than a certain number OpenAccess throws a command time-out exception even though there is no time-out. The threshold number (length in bytes) seems to be somewhere between 12,000,000 and 15,000,000. I could not figure out the exact number because of the actual data I have in the DB. The problem did not exist in OpenAccess 2012Q3 but started happening after upgrading to 2013Q3. Problem cannot be solved by increasing a command time-out setting but one not obvious workaround was found.

    The following query will consistently fail for the larger TEXT column content as described above:

    var data = (from data in db.Data.WithOption(new QueryOptions {CommandTimeout = 100})
                            where data.FileId == id
                            select data).FirstOrDefault();

    If however modified as below it will succeed:

    long[] ids = new[] { id };
    var data = (from data in db.Data.WithOption(new QueryOptions {CommandTimeout = 100})
                             where ids.Contains(data.FileId)
                             select data).FirstOrDefault();

    Tried to analyse the queries in SQL Profiler but now difference in execution plans showed up and obviously both queries execute successfully in Management Studio. Any help or advise will be much appreciated.
  2. Doroteya
    Doroteya avatar
    498 posts

    Posted 13 Jan 2014 Link to this post

    Hello Alexander,

    The behaviour you are experiencing is expected after an update to the Q3 2013 version of Telerik OpenAccess ORM. In general, it is due to a bug that was fixed with the introduction of the Command Timeout feature and a performance optimization, which concerns the usage of primary key properties as a sole condition in the where clause of a query (my guess is that FieldId is a primary key).

    The optimization, however, does not take into account the custom value provided to the CommandTimeout option. Making the formal change in the where clause of the workaround you found, ensures the command timeout value is respected, because it turns off the optimization. Here is a suggestion of another way you could modify the query:
    int one = 1;
    var data = (from data in db.Data.WithOption(new QueryOptions {CommandTimeout = 100})
                            where data.FileId == id && 1 == one
                            select data).FirstOrDefault();
    Regarding the limitation in the size of the Text type columns, let me assure you that Telerik OpenAccess ORM does not impose any.

    I hope this helps. If you have additional questions, do not hesitate to get back to us.

    OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
Back to Top