SQLite Forum

Performance issues with query parameters vs. static strings

Performance issues with query parameters vs. static strings

(1) By Florian (Flohack) on 2020-06-19 07:43:23 [link] [source]

Hi folks,

we are currently changing a lot of code in a big solution to use parameters instead of using concatenated strings for the parameters in large IN queries, like so:

Old: SELECT surrogate, assoc, partner FROM tcAggrValueC WHERE surrogate IN ('CBC96E58-0CF1-4EA8-81C6-6A17A9F20913') ORDER BY sequence ASC

New: SELECT surrogate, assoc, partner FROM tcAggrValueC WHERE surrogate IN (:I0) ORDER BY sequence ASC

Now the number of IN parameters can go up to 1000 at a time, quite a lot, I know. But we were happy with the performance up to now After the first round of changes however, our automated performance tests broke, with up to 30% degradation. We wonder now, how can using parameters be that much slower than using static strings.

Basically we believe that in the transition code from managed to unmanaged something seems to not efficiently use the existing command, can this be? Another idea is that SqLite in general is slower in this mode due to the need of parsing and mapping of the parameters.

Another thing is that we need those queries to run in transactions, meaning that the command needs to be explicitly moved on the new connection and transaction like this for every execution:

//Old connection and transaction are disposed, refresh them command.Connection = Connection; command.Transaction = Transaction;

But somehow I feel thats a bad way to do it, any ideas on that as well? Any good example how to use a prepared command together with transactions?

Thanks BR Florian

(2) By anonymous on 2020-06-19 08:49:29 in reply to 1 [link] [source]

Now the number of IN parameters can go up to 1000 at a time

Passing arrays of arbitrary size to SQL IN is always a problem. Perhaps the carray extension could help you? The usual recommendation is to create a temporary table and JOIN with it, but I don't expect it to be faster.

(3) By ddevienne on 2020-06-19 09:27:35 in reply to 2 [link] [source]

The problem is that you cannot pass arrays in SQLite :)

OK, lets backup a little. When you use text substitution in a WHERE-IN
clause, you're force to reparse each time, which prevents the use of
prepared statements. But then, the SQLite parser and query planner is fast.

Because SQLite does not have a native array-type you can bind, you
typically have to resort to work-arounds, as outlined by anonymous
above, i.e. the equivalent of a join with a table containing the RHS
of the WHERE-IN, be it a temp-table, or the carray-extension (which
is an eponymous virtual table)

If you changed your queries to read WHERE ... IN (:1, :2, ..., :N) you
can then use prepared statements, but a different one for each N, taking
more memory, and you're still parsing and planning many queries, and
need lots of binding now too. And apparently that's 30% slower :)

Your best bet is probably carray, since your uuids are in text form,
and carray supports strings. So pack your N text uuids, with the null-
terminator, into a block of memory (an std::string in C++ would do, can
have embedded nulls), and pass the address and size to carray. Pseudo-code:

std::string packed_uuids;
std::vector<std::string> uuids; // that you got from somewhere
packed_uuids.reserve(uuids.size() * 37); // 36 + 1 for null-terminator
for (const std::string& uuid : uuids) {
  packed_uuids.append(uuid.c_str(), uuid.size() + 1); // Note +1 to include null-terminator

prepare("SELECT ... FROM ... WHERE .. IN carray(:1, :2)");
sqlite3_bind_pointer(stmt, 1, packed_uuids.c_str(), "carray");
sqlite3_bind_int(stmt, 2, static_cast<int>(uuids.size())); // NOT packed_uuids.size()

I've not used carray in code yet, but I believe the speudo-code above
might work. It at least represents my current understanding of its workings.

See also https://www.sqlite.org/bindptr.html and https://www.sqlite.org/carray.html of course.

You'll need a recent version of SQLite for bind-pointer, and carray compiled-in too of course.
If it works out, please share, and tells us if it's any faster. If not, use the more traditional
fill-temp-table-then-join-with-it work-around.

(4) By Florian (Flohack) on 2020-06-19 09:56:16 in reply to 1 [link] [source]

I should mention we are using the .NET wrapper System.Data.SQLite version ... Sorry if I confused someone ;)

(5) By Florian (Flohack) on 2020-06-19 10:53:42 in reply to 3 [link] [source]

Hi thanks for the verbose reply,

yeah I see where we are coming here. Now the only question is, how this could be done through the .NET wrapper? I think those guys would have to find a way how to optimize this.

UPDATE: We just lowered the number of IN parameters and found a sweet spot at 100. It seems we can fight the problem with that a little bit, until we have a proper solution :)

Thanks BR

(6) By ddevienne on 2020-06-19 12:47:28 in reply to 5 [link] [source]

You should probably read https://www.sqlite.org/np1queryprob.html though.

WHERE-IN is essential in client-server RDBMSs, to send all IDs across the
wire in one network-trip, and get all results (or chunks of them via
Oracle's row prefetching setting) in as few network-trips as possible as well.

But because SQLite is embedded, individual queries are just as fast. So instead
of 1x WHERE col in (:1, ..., :N) using Nx WHERE col = :1 (which is a
single prepared query) is simpler and probably just fast or faster.

(7) By David Jones (vman59) on 2020-06-19 15:01:56 in reply to 1 [link] [source]

I did an EXPLAIN on those 2 statements and the only difference it displayed was a String8 opcode in the first became a Variable opcode in the second. I assume the vdbe skips a lot of sanity checks when processing a literal versus a variable binding (are mutexes involved?).

(8.1) By David Jones (vman59) on 2020-06-19 15:17:53 edited from 8.0 in reply to 7 [link] [source]

And why are you using named parameters and not just binding by index number? The parser has lookup each parameter name to see if it has a previous reference, which could possibly be quite costly when the number of names gets large.

(9) By Florian (Flohack) on 2020-06-19 15:26:33 in reply to 8.1 [link] [source]

Good point we will eradicate the names, they are not needed at all, the order will never change. :)

(10) By Neal (_Neal_) on 2020-06-19 19:32:26 in reply to 9 [link] [source]

Sending parameters into SQLite engine is not essential to do the “in” test. You may be able to avoid all the parameter passing overhead by simply writing a function to do the “in” check in your .NET code, register the function with SQLite and call it from the SQL query:

Let’s say you write a function called “my_in(String) in .NET and register it with SQLite as described here: https://stackoverflow.com/a/172845

Then just use it:

Select * from table where my_in(agg)

Ps: Mailing list was so much better; Hate composing replies on the forum on iPhone - cannot even see the whole thread while composing.

(11) By Warren Young (wyoung) on 2020-06-19 19:49:47 in reply to 10 [link] [source]

Hate composing replies on the forum on iPhone

Composing anything of consequence is miserable on a smartphone. Factor that out of your complaint about this forum, first of all.

cannot even see the whole thread while composing.

You've posted four messages to this forum over its 100 day history, so keep that in mind also: you're speaking of an irritant that affects you about once a month on average.

Given all of that, the solution, while awkward, is something you need only rarely, and then only on a platform that isn't well-suited to the task anyway:

  1. When you find a message you want to reply to, and where you think you might need thread context, click Back instead of Reply.

  2. Tap-and-hold on the thread title, and say Open in Background.

  3. Click Forward, then Reply.

Now you have the thread context in the background tab and the reply box in the foreground.

(12.1) By David Jones (vman59) on 2020-06-19 20:13:17 edited from 12.0 in reply to 9 [link] [source]

I did a quick test and sqlite3_bind_parameter_index() has a big O complexity of N squared. If you are calling that before every bind, I think that is your problem. Just processing the named parameters in the prepare is also N squared, even omitting the sqlite3_bind_parameter_index() call.

(13) By Neal (_Neal_) on 2020-06-19 21:04:05 in reply to 11 [link] [source]

miserable on a smartphone. Factor that out

Sorry but no. Everyone has moved to smartphones and its a general expectation now that things will preferably be done on smartphones and to the extent possible things are designed and optimized for smartphones first. Sqlite forum experience has clearly regressed as far as composing replies on today's devices is concerned - especially when the same was easier with older system of mailing list. I am otherwise OK with forum. Just FYI - I have been with SQLite since 04.

You've posted four messages to this forum over its 100 day history,

Can you imagine why? And can you imagine how many others might also be finding the experience disappointing and, unlike me, giving up on it silently

Given all of that, the solution, while awkward...

Why give awkward solutions to straightforward problems? Doesn't it just reinforce my assertion that composing replies has become a headache.

(14) By Warren Young (wyoung) on 2020-06-19 21:35:42 in reply to 13 [link] [source]

its a general expectation now that things will preferably be done on smartphones and to the extent possible things are designed and optimized for smartphones first.

People have all kinds of crazy expectations that don't line up with reality.

No amount of work on the Fossil forum software is going to change the fact that it takes me 3 taps to get to a square bracket on the iOS on-screen keyboard, whereas it's a dedicated key on any hardware keyboard.

And it's 3 taps only when I remember the exact sequence of shifts and alt-keys to get to it, and when iOS doesn't guess wrong when my fingertip covers 9 different on-screen keys. When it decides I really wanted that Emoji keyboard instead of the Shift key, I might need to cycle through 6-10 on-screen keyboards to get to that one key.

I do often read this forum on my iPhone, but when it comes time to reply, it's usually easier and faster to just go find a real computer and type my reply.

Why give awkward solutions to straightforward problems?

Because the only workable alternative is that someone has to write code for an open source software system to create the UI features you're wanting. Are you volunteering?

(15) By Neal (_Neal_) on 2020-06-19 22:17:03 in reply to 14 [link] [source]

People have all kinds of crazy expectations that don't line up with reality.

And yet, let me repeat, "Everyone has moved to smartphones". They are even flying spaceships with those.

... 3 taps ... 3 taps ...alt-keys ... Emoji ... Shift key

fingertip covers 9 different on-screen keys

:-) Boy, we are getting old.

the only workable alternative ... Are you volunteering?

Well, keeping the issue open is another. At least we are now acknowledging it.

(16) By Larry Brasfield (LarryBrasfield) on 2020-06-19 22:34:44 in reply to 14 [link] [source]

Without suggesting this ought to be done for Fossil, I can say that I was impressed with the way Subversion was designed, from its inception (nearly), to be amenable to being driven by 3rd party tools. For that reason, there is a wide variety of interfaces for interacting with Subversion. On Windows, for example, there are Subversion interfaces available for most IDEs and the GUI file manager.

That said, the web-based GUI is pretty respectable and usable. Yet I still wonder if it might be improved by folks whose forte is UI design.

(17) By Richard Hipp (drh) on 2020-06-19 23:07:47 in reply to 15 [link] [source]

"Everyone has moved to smartphones"

This statement is false. Please stick to facts in any future communications you make on this forum.

(18) By Neal (_Neal_) on 2020-06-20 00:04:18 in reply to 17 [source]

"Everyone has moved to smartphones"

OK, maybe I should have added ‘figuratively’ but ...

This statement is false.

This statement is not going to age well. Case in point: even Warren who initially pushed back on my issue acknowledges that he has to jump back and forth between iPhone and desktop to use this forum. If it becomes easy to compose on iPhone/iPad do you think he will ?

(19) By Stephan Beal (stephan) on 2020-06-20 06:33:47 in reply to 18 [link] [source]

If it becomes easy to compose on ...

FWIW, most of my not inconsiderable fossil/sqlite forum time is on an Android tablet, and it works just fine from there. A tiny (phone-sized) screen will never be a great platform for forum-style messaging (as opposed to micro-messaging like chats and tweets and similar one-liners).

That said: the ability to edit a post inline within the thread, including a preview, is pending. The code-level infrastructure is in place, and as soon as my RSI-plagued hands are up for implementing it, it will be done. Not because you asked for it, but because it's been on my TODO list for several weeks.

(Sent from my Android tablet.)

(20) By Tim Streater (Clothears) on 2020-06-20 07:35:00 in reply to 17 [link] [source]

Worse than false. It's misleading, patronisng nonsense. Having tried an iPhone, thinking I could do email and Twitter on it, I've removed the Twitter app and and stopped with the email as it's not secure. I'm quite likely to scrap the iPhone altogether and go back to a cheap clamshell with a much better battery life.

We see the same with cars: too many models with only a touch-screen to operate the heating controls, for example. These lack tactile feedback so I have to look at a screen to see what I'm doing as I drive along. You'd think the safety people would have forbidden that.

(21.1) By Wout Mertens (wmertens) on 2020-06-20 08:35:50 edited from 21.0 in reply to 20 [link] [source]

My take on this:

  • Since we moved to the forum, we seem to be getting a lot more traffic. All good content, so that's a win
  • I read the forum in my email and reply on the forum. About 50-50 on my phone.
  • Global internet traffic is about 50% mobile (ok I just picked some random site, I couldn't find a more reputable source offhand, I'm on my phone)

So IMHO the people throwing absolutes around are wrong:

  • clearly there is a significant portion of phone users (drh should have the exact proportion in the Fossil logs)
  • clearly a desktop keyboard is better suited to writing about SQL
  • clearly a big screen can show more information than a small screen
  • clearly being able to read the forum anywhere is something people choose to do.

(22) By Florian (Flohack) on 2020-06-20 10:21:50 in reply to 21.1 [link] [source]

Not complaining but maybe its better to open a separate thread for this, I think my question has been answered well and now its kinda thread-hijack and OT.