Skip to content

Select Performance #160

ssteiner opened this Issue Feb 13, 2013 · 0 comments

1 participant


After INSERTs there are selects, so I used my stress test tool to insert a large number of records (up to 50k) into a single table, then run selects on it.

conn.Table().Where(f => f.FrameworkUserUid == u.FrameworkUserUid).Select(x => x.FrameworkUserUid).FirstOrDefault();

If I'd be writing this as SQL, I'd write

select FrameworkUserUid from FrameworkUser where FrameworkUserUid = ?

Here's what sqlite-net makes of the linq statement (taken directly from the debugger)

"select * from \"FrameworkUser\" where (\"FrameworkUserUid\" = ?) limit 1"

So, the generated statement is a mixed bag.. it applies the FirstOrDefault directly to the query thus ensuring we're not parsing all objects, but it still parses the entire object even though with the select I'm limiting the return value to a single table field. Things do get a lot worse if instead of FirstOrDefault you run Any(). Now the generated SQL is

"select * from \"FrameworkUser\""

So the entire table is parsed, then any is performed. There's no direct SQL translation for the ANY statement, but I figure it should either be

SELECT COUNT(FrameworkUserUid) FROM FrameworkUser WHERE FrameworkUserUid = ?

SELECT CASE WHEN (SELECT COUNT(FrameworkUserUid) FROM FrameworkUser WHERE FrameworkUserUid = ?) > 0 THEN 1 ELSE 0 END

For a direct "binary" return.

Either way, the query generation should take into account SELECT or ANY statements for performance reasons (things do get quite bad if you have a couple thousand records to deal with).

@edit: I did a bit of experimentation on other syntax... lookups seem to do what they're supposed to if you're using Linq's WHERE, but First(OrDefault) with a predicate, just as Any, is out.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.