MSSQL Text type column

[Alexander Turlov]

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.

[Doroteya]

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.


Regards,
Doroteya
Telerik