Loading related data executes parameterized queries? Sql Server can't cache the plan

ServiceStack.OrmLite
1

We love ORMLite, but are running into our query plan cache compiling a TON - so I started digging into the queries being executed without a plan being reused and ran across any type of Include executes the first query correct, but then any of the included data execute without a parameter, just inlined sql where clauses.

Example:

db.LoadSelect<CompanyUser>(a => a.CompanyUserID == someGuid, a => a.User);

The resulting SQL run within Sql Server is:

exec sp_executesql N'SELECT "CompanyUserID", "CompanyID", "UserID", "CrewContactID" FROM "CompanyUsers" WHERE "CompanyUserID" = @CompanyUserID',N'@CompanyUserID uniqueidentifier',@CompanyUserID='87222E7E-2508-48CA-A2D1-659ABD17D9B7'

And then:

SELECT "UserID", "Email", "CreationDate", "IsDeleted", FROM "Users" WHERE "UserID"=CAST('5a7115ce-77f1-42d0-a2c1-8f287c1f2d08' AS UNIQUEIDENTIFIER)

So SQL server then creates an execution plan for the first one and the second one. The first one get’s re-used all day long, but the 2nd one never get’s used. Every time that user id is changed it creates a new plan. Is this expected behavior? If not, do we have an idea on how to fix this without rebuilding our whole app? :slight_smile:

Thanks!

2

That is how it LoadSelect is implemented where it merges the params into SQL to create the sub query.

There is an opportunity to modify the Sub query used with OrmLiteConfig.LoadReferenceSelectFilter otherwise you could select the queries independently and merge the disconnected results, something like:

var companyUsers = db.Select<CompanyUser>(...);
var users = db.Select<User>(...);

companyUsers.Merge(users);
3

Is it an option to make this ‘smarter’ by using a single SQL statement with a left join loading the referenced objects?

4

You can try create a query that does it that way if it works for your use-case.

References can contain multiple load references inc. multiple self references with ambiguous columns that I’m not convinced will work. I’d expect there to be a lot of issues like how to control paging for just the main table generically across all RDBMS’s but ensure all referenced data is loaded or how to handle computed custom SQL expressions if you have to try shuffle ambiguous columns around.

If you can put together a working solution that works for all RDBMS’s I’d like to see it.

5

Paging on the main table shouldn’t be an issue with a left join on 0.1 db references.

Using [referencepropertyname] syntax should make each field unique and matches the default behavior to match a datareader column with a class.propertyname

6

This doesn’t address any of the problems I’ve mentioned, I’m obviously not referring to the issue of paging about only being able to query 1:1 references and the fields in custom select expressions would still be ambiguous. I’m sure there are other problems but that are some I can think of.

You should be able to implement a solution that works for your use-case, which you can add as a custom extension method.

7

I think the multiple resultset feature from dapper can be used to optimize this, see this example

8

OrmLite also has a SelectMultiple tables feature and can also the SQL Expression that can be used with Dapper’s QueryMultiple:

It still doesn’t address the 2 issues I’ve mentioned above. Not to mention Dapper doesn’t understand any of OrmLite’s Table customizations. Again if it works for your use-case feel free to create your own custom extension method that uses it.

9

sorry for bothering you again, but do you think this solution will solve your issues?

public class MultiSelfCustomer
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }

    [References(typeof(SelfCustomerAddress))]
    public int? HomeAddressId { get; set; }

    [References(typeof(SelfCustomerAddress))]
    public int? WorkAddressId { get; set; }

    [Reference]
    public SelfCustomerAddress HomeAddress { get; set; }

    [Reference]
    public SelfCustomerAddress WorkAddress { get; set; }
}

Executing this statement

var results = db.LoadSelect(q => q.HomeAddressId != null && q.WorkAddressId != null);

would result in the following single SQL command:

select Id,Name,HomeAddressId,WorkAddressId from MultiSelfCustomer where HomeAddressId is not null and WorkAddressId is not null;
select Id,AddressLine1,AddressLine2,City,State,Country from SelfCustomerAddress where Id In (
select HomeAddressId from MultiSelfCustomer where HomeAddressId is not null and WorkAddressId is not null
union
select WorkAddressId from MultiSelfCustomer where HomeAddressId is not null and WorkAddressId is not null
);

Pseudo code:
select * from [mainsql]
for each referenced type add a sql statement
select * from [referencedtype] where [primarykey] in (select [referencedfieldid] from [mainsql])

load the first resultset
load the other resultsets into an array
merge the array in the first resultset

Thanks
Marco

10

I don’t have any issue with LoadSelect, it works as intended and I still don’t see the paging or computed select expressions addressed in the example, if you had something that works that I can run I’ll be able to take a look.

11

Indeed it works as intended, except if I assign “;Drop table XXX;GO” as an unique primary key value to a reference I have a nasty side effect :grin:

Is there some documentation how to run the unit test locally (for a specific driver)?

12

It’s the same as normal OrmLite, create a factory with the provider you want to test and create your connection from that.

What code are you using that has this side effect?

13

I want to change the logic of LoadReferences to use SQL parameters (to prevent SQL injection) and reuse query plan on the database server.

For that I want to run the current LoadReferences tests in ormlite on my local machine (for now postgres/ sqlserver only).

Next step will be to use a single dbcommand with multiple result sets to load the references

Next step to support it with multiple base instances (loadbysingleid with multiple primary keys)

Final step use LoadReferences with any base query…

14

You’ve suggested that SQL injection is possible, so please confirm that you were actually able to enable SQL injection and please provide the code that shows an example of an SQL injection.

If string parameters are escaped it still doesn’t allow SQL injection, it’s not mandatory to use DB parameters.

15

You are correct. But it’s definitely not best practice (someone who scan’s the code (like I did) could raise the same conclusion) and leads to query plan issues on the server at mentioned at the first post.

16

So this didn’t actually happen? Then am I assuming correctly that you’ve also made no attempt to verify your 2nd claim of SQL Injection you’ve done in this post either?

A cursory look at the implementation will see that the arguments are indeed quoted. I don’t know anyone else that has made a serious claim like SQL Injection without any attempt at verification.

Most of OrmLite API’s do use db parameters, but there are times when combining queries where it’s simpler to rasterize the SQL with inline db parameters so it can be used in different queries - which is what LoadReferences does.