… or, 999 the number of the sqlite beast

I’m sure that you have been in situation where you should solve some problem and you were concentrated only to that. You sketched solution, implemented it, it works, and that’s it — solved! But, something weird start’s to happen. Crash analytics begins to show positive trends, and you start to dig — you are sure that your implementation is completely aligned to best practice, you used well-known patterns, but something is failing.

One of the positive outcomes of experience with this issue that I’m going to write about is that I’m going to be more thoughtful to my wife questions about washing direction for my running shirts and gear! :)

So, here is the story…

In one of out projects, DAL layer is using parametrized queries and we were faced with the need to refactor it in a way to use dynamic sql clause like this:

S ELECT * FROM table WHERE field IN (@param1, @param2, ..., @paramN)

We first test everything by using some sqlite browsers and results were ok. But, when we implemented this in the code we started to see following error in our log:

too many SQL variables

I believe that you can imagine our horror seeing this!

Actually, we knew that we need to visit sqlite site and took a look at Limitations page. And yes, there is a SQLITE_MAX_VARIABLE_NUMBER host parameter set to 999 which prevents excessive memory allocations that could happen due to a way sqlite allocates a space to hold all host parameters. You can read more details at:

https://www.sqlite.org/limits.html

Solution for this limitation in our case was using the NextResult() method of .Net sqldatareader.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult(v=vs.110).aspx

Basically before sending this query, we knew how many parameters were there, and we split one query to as many as needed to accomodate 999 rule. So, e.g. if we got 2000 parameters that we need to send as parameters, we created these three queries:

S ELECT * FROM table WHERE field IN (@param1, @param2, ..., @param999);

SELECT * FROM table WHERE field IN (@param1000, @param1001, ..., @param1998);

SELECT * FROM table WHERE field IN (@param1999, @param2000);

The C# code was refactored to use multiple result sets by using NextResult():

do

{

while (reader.Read())

{

...

}

}

while (reader.NextResult());

So basically, we loop through multiple result sets— easy one!

I believe that other languages and sqlite or data access libraries has similiar methods.

Another way is to recompile sqlite lib and to change value for SQLITE_MAX_VARIABLE_NUMBER but we didn’t want to do that.

Sometimes, there are simple solutions to limitation that we hit with the tools and libraries that we use in our projects, but we should be aware of these limitation in early stages of developement.

—

At Telnet we take a special care about these consideration at early stages of development and trying really hard to get deep knowledge of complete technology stack which is used on the project. We handle a lot of outsource projects in which we face similar challenges on the daily basis. You can contact us to find out how we can make your business better, together.