How to parameterize array with activerecord

Activerecord generates parameterized queries for basic types, but, it does parameterize array types.

E.g.

Article.where(id: 1) # generates
# SELECT "articles".* FROM "articles" WHERE "articles"."id" = $1 [["id", 1]]

But,

  Article.where(id: [1,2])
# generates
# SELECT "articles".* FROM "articles" WHERE "articles"."id" IN (1, 2)
# rather than
# SELECT "articles".* FROM "articles" WHERE "articles"."id" IN ($1, $2)
[["id", 1], ["id", 2],]

Is it possible to restructure the query or use Arel to generate parameterized query for IN clauses?

Fail to see the use case Faisal - plz elaborate - and keep in mind that

Article.where id: [1,2,3,4,5,6,7,8,9,10,11, . . . , 2100]

would generate one heck of a parameterized array :wink:

Thanks walt,

Non parametric queries are polluting SQL Server plan cache, which is affecting overall database performance.

I understand that parameter count for the IN clause can vary widely, but, parameterizing these queries will substantially reduce than number of generated plans.

well faisal,

either you could http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/ now and then

or

look at your queries and try to isolate those hurting your plan cache the most;

are they recognizable and the data behind it not too dynamic by nature, build a ‘cache’ for them - either in memory or a Redis kind of thing

are they not recognizable or are the data too dynamic (like airplane tickets on popular flights), you could try redesigning the tables

or you could pull in larger datasets and reduce in-memory (if your DB is hurt more than your CPU’s)

cheers,
walt