How to parameterize array with activerecord

[Faisal Mansoor]

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?

[Walther Diechmann]

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 ;)

> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/3edc1ebe-8d1b-4b42-9229-75934110b26b%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> Denne besked er blevet skannet af
> ALCO Stopspam, og menes at være fri for vira og spam.
> Klik her for at rapportere denne besked som spam.

[Faisal Mansoor]

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.

[Walther Diechmann]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/6ad9443b-3fd6-4e8c-b934-f8c83760ea26%40googlegroups.com.