sql: statement with parameters much slower than with string concatenation #21463
Description
Cockroach version:
Build Tag: v1.1.4
Build Time: 2018/01/08 17:32:42
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.3
C Compiler: gcc 6.3.0
Build SHA-1: b794b52cbfffa2340cdaabf1c33be716ebde1db4
Build Type: release-gnu
I'm using hibernate (5.2.10) together with cockroachdb for a proof-of-concept. While re-writing some queries from mysql to postgres/crdb I noticed a weird behavior. While calling crdb with a native query which I optimized in DBeaver (takes about 20ms) I used parameters in the query using hibernate like this:
NativeQuery<Right> query =
session.createNativeQuery("select ri.pkrightid, ri.subject "
+ " from rights ri "
+ " where ri.pkrightid in ( "
+ " select rr.fkrightid "
+ " from role_rights rr "
+ " where rr.fkroleid in ( "
+ " select distinct ur.fkroleid from user_role ur "
+ " where ur.fkuserid = :userid "
+ " intersect "
+ " SELECT distinct tr.fkroleid FROM tenant_role tr "
+ " WHERE tr.fkTenantId = :tenantid "
+ " ) "
+ ")", Right.class);
query.setReadOnly(true);
query.setParameter("userid", userid);
query.setParameter("tenantid", tenantid);
List<Right> rights = query.list();
This query took almost 5 seconds! However, when I put the parameters directly into the statement using string concatenation it's fast again.
NativeQuery<Right> query =
session.createNativeQuery("select ri.pkrightid, ri.subject "
+ " from rights ri "
+ " where ri.pkrightid in ( "
+ " select rr.fkrightid "
+ " from role_rights rr "
+ " where rr.fkroleid in ( "
+ " select distinct ur.fkroleid from user_role ur "
+ " where ur.fkuserid = " + userid
+ " intersect "
+ " SELECT distinct tr.fkroleid FROM tenant_role tr "
+ " WHERE tr.fkTenantId = " + tenantid
+ " ) "
+ ")", Right.class);
query.setReadOnly(true);
List<Right> rights = query.list();
I would have expected the query with the parameters to take the same time (approx) to what I observed in DBeaver.
See also discussion in https://forum.cockroachlabs.com/t/prepared-statement-slower-than-string-concatenated-query/1280/3