MS-SQL query: 2100 parameters limit

Can you try to upgrade to the 3.0.x version of the PHP driver for SQL Server? This new version allows us to prepare the query at the PDO level instead of using database-provided prepared statements. This fixes a lot of issues (likely this one included) and has a better performance in practice.

Subscribing. Am running into this issue via a query in _menu_navigation_links_rebuild() in menu.inc.

I've been thinking about this issue for a while and am still puzzled.

What would be the best way to handle a query with > 2100 parameters?

1. Reject it and refuse to run it? This doesn't sound like it would solve anything.
2. Run it with the first 2100 parameters? This would return results inconsistent with what the querying code expects and could cause all sorts of follow-on problems, probably with serious security implications.
3. Try and re-write it? This appears on first glance to be very difficult:
   SELECT :col_1, :col2 FROM {foo} JOIN {:table} ON (:col_3 = :col_4) WHERE :col_5 IN (:in_1, :in_2 [...] in_1700) AND :col_8 = :val AND :COL_9 IN (:in_1701, [...], :in_2101)
   How would one begin to programmatically re-write that? UNION could work, but only for SELECT queries. But even so, re-writing something like that seems really complicated.

What do people think? Is this limit still causing problems for others?

This was causing us no end of headaches so I delved into the database class and wrote a quick patch. It's probably not the best of ways to solve this problem but it gets the job done for me.

Basically what I do, is check if arguments exceed the 2100 limit and if so, directly embed them into the query (instead of as parameters) via an existing function (replacePlaceholders()). Patch attached against HEAD.

This don't work for me. It seems like i don't have the same sqlsrv_database.inc file. Wich version you rewrite?

7.x-1.x-dev.

Working for me. Thanks, torpy!

Also, here's a drush script I was using to test:

$max = 2105 //just to make sure
$nids = array();
for ($i = 0; $i < $max; $i++) {
  $nids[] = $i;
}
$result = db_select('node', 'n')
  ->fields('n')
  ->condition('nid', $nids,'IN')
  ->execute()
  ->fetchAll();
print_r($result);

PDO SQL Server 3.0 supports PHP replacement of placeholders, and we have the architecture in place inside the Drupal driver to leverage it. Just upgrade your PDO version and this bug should go away.

Postponed until someone confirms that this bug doesn't affect the 3.x version of the PDO SQL Server extension.

Definitely still affects it. We were running into this problem with version 3.x of the PDO SQL Server extension.

One minor fix to the patch. In testing, I found that failures to still occurred if the number of parameters was 2099 or 2100. I changed the parm check to >= 2099 to account for this. I have attached the updated patch.

Good to know, thanks for that!

It work fine, but i think we don't have to wait until 2099 parameters because the process go slow procesing so many parameters. If we make the replacements whenever a 'IN' condition is used probably we never going to use more than 100 parameters and the query will work faster. Particulary i'm using the prior patch but replace the 2099 for 300 to increase performance.

In my modules development I try to dont use the 'IN' condition passing an ARRAY parameter, instead I pass an array with only one value imploding all values in him:
Ex: array("'" . implode("', '", $value_array) . "'")

@daneelcm Do you have a patch you can contribute to improve on @omegamonk's patch?

Forgive me, I'm coming to this party a bit late.

Why exactly are there any queries at all that have that many parameters instead of using temp tables to begin with? I'd think so many parameters would make performance disgustingly slow and using a simple temp table would vastly outperform any query that has more than even 500 parameters. Just populate it with a bit of XML in SQL Server. There is something equivalent in MySQL, I think.

It seems that this should be a Drupal core requirement rather than a db driver requirement so that Drupal doesn't get slapped around for being slow when it's actually the module implementation that's slow and not Drupal.

If anyone's interested I think I could pretty easily write a routine that produces a temp table from an arbitrary number of parameters (even more than 65k) and then does a join on those as a parameter. We could even optimize so that things coming out of the database never have to even see the light of PHP, they're just dumped directly into the temp table before the join.

Thoughts anyone?

@mgifford as I sed before here is the previous patch just changed the 2099 value for 300 to prevent slow processing.

@xenphibian that is a good idea, so when an 'IN' condition is used the values of the array received can be puted in a temp table and then join or simply leave IN and then SELECT that table. I think this is only an issue for MSSQL Server because in MySQL, Postgree, Oracle, etc didn't report this problem, so it most be in the SQLSRV driver and not in the Drupal Core. And respect of the use of XML to populate the table... I think it's not necesary go so far, just inserting the values it's good.

Just a question: The temp table must be deleted after been used or you gona use table variables?

I was planning to use XML to create the values because the code is trivial for an arbitrary number of parameters, although there is a string length limitation in SQL Server, as well. Anything else would either be too granular and take many, many inserts (code very slow) or else would have to do something with a bunch of nulls at the end (code too complex). MySQL could use something similar with or without XML.

A "global temp table" is something that I have used on a number of occasions when I wanted to do this exact sort of thing but didn't have the luxury of generating SQL inline because of the awful performance problems. A GT table is a table that is really just a cache. The values in the table are temporary but the table itself already exists. For cleanup the table can be cleaned routinely or just dropped and recreated. Either way, it's a simple idea. The specific values would be selected by the run time generated id used to populate the table. Then you'd see something like:

SELECT userid, username FROM user INNER JOIN gtTempCache tc ON userid = tc.uid AND tc.cacheid = :cacheid

Or alternatively:

SELECT userid, username FROM user WHERE userid IN (SELECT tc.UID FROM gtTempCache WHERE tc.cacheid = :cacheid)

I'd think that either would be VASTLY superior in performance to trying to match 64,000 individual values in a "WHERE ... IN" clause, but I have not run tests to compare actual performance.

After playing with this a bit, here's what I have come up with for our Drupal module case.

Without using XML you could easily produce a call that inserts X number of values an arbitrary number of times to fill the cache table like this:

INSERT INTO gtTempCache VALUES :cacheid, :value

But, that would be horribly slow, I'd think. Every value would require a round trip to the database.

My untested belief is that SQL Server could much, much more easily handle something that would build a string that looked something like:

<root>
<value cacheid=":cacheid" value=":value1"/>
<value cacheid=":cacheid" value=":value2"/>
</root>

Writing that to a temp XML file (named CacheXML.xml here, but you'd have to avoid collisions, so a unique name would have to be passed). Obviously, the :cacheid, :value1 and :value2 would be replaced while writing/appending to the XML file.
And then do something very, very simple to insert like:

DECLARE @CacheXML XML

SELECT @CacheXML = x.y
FROM OPENROWSET( BULK 'C:\Sandbox\CacheFile.xml', SINGLE_CLOB ) x(y)

INSERT INTO gtTempCache
SELECT
	x.y.value('@cacheid', 'VARCHAR(MAX)') AS [cacheid],
	x.y.value('@value', 'VARCHAR(MAX)') AS [value]
FROM @CacheXML.nodes('//values') AS x(y)

I haven't put a whole, whole lot of thought into this for Drupal, but this is the same idea that I'm using with a C# web service that is passed a file content type and a GUID requesting data and gets back an XML file as a response for an Android app (or a web page or any other XML aware app) to consume. I also have stored procedures that do similar things without an interim temp file and perform amazingly well, considering what they're being asked to do (such as search through many different calendars with several million appointments each to find a group of appointments with complex relationships and produce a list of results each representing a list of related appointments that can be booked in a single click -- all within only a few seconds).

An unplanned (but useful) side benefit to this approach is that you could have two or more lists at the same time being dumped into the cache table. Just use a different cacheid for each list. Also, since this is XML (and thus by default not ordered) you could build your lists asynchronously, a few from list 1, a few from list 2, a few from list 3, then a few more from list 2, etc. in any order.

Seems good, I like the idea of the cache table with cacheid. I never work before with XML on SQL so I can´t tell about but all you say before sounds good and I like to test'it. However did you think that creating a XML file and then importing to a table is faster than generate a multiple INSERT and run only one time? Something like this:

<?php
$query = "INSERT INTO gtTempCache 
                VALUES (cacheid, value1),
                            (cacheid, value2),
                            (cacheid, value3)";
db_query($query);
?>

Like I sed before never work with XML in SQL scripts but I think using XML force us to create, write, read and finally delete a file. However if all this it's better than a multi-insert, do not have words.

I have done both a separate file as well as using strings. I cannot see a performance difference between the two except when there is a problem with permissions or with networked servers (then files aren't always where you'd expect them to be). I have not tested performance between the two extensively, though. My initial application was in a system where I had a stored procedure that needed anywhere from 1 to several dozen parameters of all different types. My second one that used this approach used XML files to pass around data between systems and processes. So, while I was solving different problems, they have some interesting similarities to this one.

The problem with the INSERT...VALUES solution is that you're limited by string size with the query, so you have to do it over and over and over and/or have logic that can get a little complicated because a query as a variable has a 4000 NVARCHAR string limit or an 8000 VARCHAR limit, so you'd have to break the query up. No such limit exists in a file as far as I know (well, a 2GB file limit, or something equally ridiculous, I think).

My original solution (still being used in a very large medical system today) consisted of a trio of stored procedures. One that took an XML string containing an arbitrary number of parameters and created the GUID that also was returned to the first caller. Then a second one took that GUID and another XML string of parameters that were added to the table. The second sproc could be called multiple times with apparently no measurable performance hit so long as you maximized the length of your XML string. And, the third one cleaned everything up when we were done. You could do the same thing with PHP routines for the setup and cleanup and only use one stored procedure (advantage being that it's precompiled).

#14 Working OK. Performance of solution is not good, but queries with such a large # of parameters are very rare and execute ocasionally.

While rebuilding the D8 version of the driver, I found out that these two options:

\PDO::SQLSRV_ATTR_DIRECT_QUERY => TRUE
\PDO::ATTR_EMULATE_PREPARES => TRUE

were actually NEVER applied to the statement, thus having no effect on preventing the 2100 parameter limit error.

The D8 driver is now prepared to apply this attributes, what needs to be done:

- D8: Remove the patch from this issue, PDO PREPARE should do the job.
- D8: Detect queries with more than 2100 parameters and mark the as insecure.
- D8: Write a test....

After that we may think of backporting....

Fixed in latest 7.x and 8.x dev versions.

Tests included.

PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 2100. SQL Server supports a maximum of 2100 parameters.: SELECT * FROM {panelizer_entity} WHERE entity_type = 'node' AND entity_id IN (:ids_0, :ids_1, :ids_2, :ids_3, :ids_4, :ids_5, :ids_6, :ids_7, :ids_8, :ids_9, :ids_10, :ids_11, :ids_12, :ids_13, :ids_14, :ids_15, :ids_16, :ids_17, :ids_18, :ids_19, :ids_20, :ids_21, :ids_22, :ids_23, :ids_24, :ids_25, :ids_26, :ids_27, :ids_28, :ids_29, :ids_30, :ids_31, :ids_32, :ids_33, :ids_34, :ids_35, :ids_36, :ids_37, :ids_38, :ids_39, :ids_40, :ids_41, :ids_42, :ids_43, :ids_44, :ids_45, :ids_46, :ids_47, :ids_48, :ids_49, :ids_50, :ids_51, :ids_52, :ids_53, :ids_54, :ids_55, :ids_56, :ids_57, :ids_58, :ids_59, :ids_60, :ids_61, :ids_62, :ids_63, :ids_64, :ids_65, :ids_66, :ids_67, :ids_68, :ids_69, :ids_70, :ids_71, :ids_72, :ids_73, :ids_74, :ids_75, :ids_76, :ids_77, :ids_78, :ids_79, :ids_80, :ids_81, :ids_82, :ids_83, :ids_84, :ids_85, :ids_86, :ids_87, :ids_88, :ids_89, :ids_90, :ids_91, :ids_92, :ids_93, :ids_94, :ids_95, :ids_96, :ids_97, :ids_98, :ids_99, :ids_100, :ids_101, :ids_102, :ids_103, :ids_104, :ids_105, :ids_106, :ids_107, :ids_108, :ids_109, :ids_110, :ids_111, :ids_112, :ids_113, :ids_114, :ids_115, :ids_116, :ids_117, :ids_118, :ids_119, :ids_120, :ids_121, :ids_122, :ids_123, :ids_124, :ids_125, :ids_126, :ids_127, :ids_128, :ids_129, :ids_130, :ids_131, :ids_132, :ids_133, :ids_134, :ids_135, :ids_136, :ids_137, :ids_138, :ids_139, :ids_140, :ids_141, :ids_142, :ids_143, :ids_144, :ids_145, :ids_146, :ids_147, :ids_148, :ids_149, :ids_150, :ids_151, :ids_152, :ids_153, :ids_154, :ids_155, :ids_156, :ids_157, :ids_158, :ids_159, :ids_160, :ids_161, :ids_162, :ids_163, :ids_164, :ids_165, :ids_166, :ids_167, :ids_168, :ids_169, :ids_170, :ids_171, :ids_172, :ids_173, :ids_174, :ids_175, :ids_176, :ids_177, :ids_178, :ids_179, :ids_180, :ids_181, :ids_182, :ids_183, :ids_184, :ids_185, :ids_186, :ids_187, :ids_188, :ids_189, :ids_190, :ids_191, :ids_192, :ids_193, :ids_194, :ids_195, :ids_196, :ids_197, :ids_198, :ids_199, :ids_200, :ids_201, :ids_202, :ids_203, :ids_204, :ids_205, :ids_206, :ids_207, :ids_208, :ids_209, :ids_210, :ids_211, :ids_212, :ids_213, :ids_214, :ids_215, :ids_216, :ids_217, :ids_218, :ids_219, :ids_220, :ids_221, :ids_222, :ids_223, :ids_224, :ids_225, :ids_226, :ids_227, :ids_228, :ids_229, :ids_230, :ids_231, :ids_232, :ids_233, :ids_234, :ids_235, :ids_236, :ids_237, :ids_238, :ids_239, :ids_240, :ids_241, :ids_242, :ids_243, :ids_244, :ids_245, :ids_246, :ids_247, :ids_248, :ids_249, :ids_250, :ids_251, :ids_252, :ids_253, :ids_254, :ids_255, :ids_256, :ids_257, :ids_258, :ids_259, :ids_260, :ids_261, :ids_262, :ids_263, :ids_264, :ids_265, :ids_266, :ids_267, :ids_268, :ids_269, :ids_270, :ids_271, :ids_272, :ids_273, :ids_274, :ids_275, :ids_276, :ids_277, :ids_278, :ids_279, :ids_280, :ids_281, :ids_282, :ids_283, :ids_284, :ids_285, :ids_286, :ids_287, :ids_288, :ids_289, :ids_290, :ids_291, :ids_292, :ids_293, :ids_294, :ids_295, :ids_296, :ids_297, :ids_298, :ids_299, :ids_300, :ids_301, :ids_302, :ids_303, :ids_304, :ids_305, :ids_306, :ids_307, :ids_308, :ids_309, :ids_310, :ids_311, :ids_312, :ids_313, :ids_314, :ids_315, :ids_316, :ids_317, :ids_318, :ids_319, :ids_320, :ids_321, :ids_322, :ids_323, :ids_324, :ids_325, :ids_326, :ids_327, :ids_328, :ids_329, :ids_330, :ids_331, :ids_332, :ids_333, :ids_334, :ids_335, :ids_336, :ids_337, :ids_338, :ids_339, :ids_340, :ids_341, :ids_342, :ids_343, :ids_344, :ids_345, :ids_346, :ids_347, :ids_348, :ids_349, :ids_350, :ids_351, :ids_352, :ids_353, :ids_354, :ids_355, :ids_356, :ids_357, :ids_358, :ids_359, :ids_360, :ids_361, :ids_362, :ids_363, :ids_364, :ids_365, :ids_366, :ids_367, :ids_368, :ids_369, :ids_370, :ids_371, :ids_372, :ids_373, :ids_374, :ids_375, :ids_376, :ids_377, :ids_378, :ids_379, :ids_380, :ids_381, :ids_382, :ids_383, :ids_384, :ids_385, :ids_386, :ids_387, :ids_388, :ids_389, :ids_390, :ids_391, :ids_392, :ids_393, :ids_394, :ids_395, :ids_396, :ids_397, :ids_398, :ids_399, :ids_400, :ids_401, :ids_402, :ids_403, :ids_404, :ids_405, :ids_406, :ids_407, :ids_408, :ids_409, :ids_410, :ids_411, :ids_412, :ids_413, :ids_414, :ids_415, :ids_416, :ids_417, :ids_418, :ids_419, :ids_420, :ids_421, :ids_422, :ids_423, :ids_424, :ids_425, :ids_426, :ids_427, :ids_428, :ids_429, :ids_430, :ids_431, :ids_432, :ids_433, :ids_434, :ids_435, :ids_436, :ids_437, :ids_438, :ids_439, :ids_440, :ids_441, :ids_442, :ids_443, :ids_444, :ids_445, :ids_446, :ids_447, :ids_448, :ids_449, :ids_450, :ids_451, :ids_452, :ids_453, :ids_454, :ids_455, :ids_456, :ids_457, :ids_458, :ids_459, :ids_460, :ids_461, :ids_462, :ids_463, :ids_464, :ids_465, :ids_466, :ids_467, :ids_468, :ids_469, :ids_470, :ids_471, :ids_472, :ids_473, :ids_474, :ids_475, :ids_476, :ids_477, :ids_478, :ids_479, :ids_480, :ids_481, :ids_482, :ids_483, :ids_484, :ids_485, :ids_486, :ids_487, :ids_488, :ids_489, :ids_490, :ids_491, :ids_492, :ids_493, :ids_494, :ids_495, :ids_496, :ids_497, :ids_498, :ids_499, :ids_500, :ids_501, :ids_502, :ids_503, :ids_504, :ids_505, :ids_506, :ids_507, :ids_508, :ids_509, :ids_510, :ids_511, :ids_512, :ids_513, :ids_514, :ids_515, :ids_516, :ids_517, :ids_518, :ids_519, :ids_520, :ids_521, :ids_522, :ids_523, :ids_524, :ids_525, :ids_526, :ids_527, :ids_528, :ids_529, :ids_530, :ids_531, :ids_532, :ids_533, :ids_534, :ids_535, :ids_536, :ids_537, :ids_538, :ids_539, :ids_540, :ids_541, :ids_542, :ids_543, :ids_544, :ids_545, :ids_546, :ids_547, :ids_548, :ids_549, :ids_550, :ids_551, :ids_552, :ids_553, :ids_554, :ids_555, :ids_556, :ids_557, :ids_558, :ids_559, :ids_560, :ids_561, :ids_562, :ids_563, :ids_564, :ids_565, :ids_566, :ids_567, :ids_568, :ids_569, :ids_570, :ids_571, :ids_572, :ids_573, :ids_574, :ids_575, :ids_576, :ids_577, :ids_578, :ids_579, :ids_580, :ids_581, :ids_582, :ids_583, :ids_584, :ids_585, :ids_586, :ids_587, :ids_588, :ids_589, :ids_590, :ids_591, :ids_592, :ids_593, :ids_594, :ids_595, :ids_596, :ids_597, :ids_598, :ids_599, :ids_600, :ids_601, :ids_602, :ids_603, :ids_604, :ids_605, :ids_606, :ids_607, :ids_608, :ids_609, :ids_610, :ids_611, :ids_612, :ids_613, :ids_614, :ids_615, :ids_616, :ids_617, :ids_618, :ids_619, :ids_620, :ids_621, :ids_622, :ids_623, :ids_624, :ids_625, :ids_626, :ids_627, :ids_628, :ids_629, :ids_630, :ids_631, :ids_632, :ids_633, :ids_634, :ids_635, :ids_636, :ids_637, :ids_638, :ids_639, :ids_640, :ids_641, :ids_642, :ids_643, :ids_644, :ids_645, :ids_646, :ids_647, :ids_648, :ids_649, :ids_650, :ids_651, :ids_652, :ids_653, :ids_654, :ids_655, :ids_656, :ids_657, :ids_658, :ids_659, :ids_660, :ids_661, :ids_662, :ids_663, :ids_664, :ids_665, :ids_666, :ids_667, :ids_668, :ids_669, :ids_670, :ids_671, :ids_672, :ids_673, :ids_674, :ids_675, :ids_676, :ids_677, :ids_678, :ids_679, :ids_680, :ids_681

i am seeing this error from recent log message. When i Clear cache and and close once i am done clearing the cache, it gives me Eror : the website encountered undexpected error, ...... then when i reload the page it load for me. Can you help.

What version of the SQL Server driver are you using?

i am using sqlsrv 7.x-1.3

And what version of the PDO extension? Make sure to run the latest version from MS.

You can debug what is happening easily. To overcome the 2100 parameter limit issue the driver must set that query to insecure (in database.inc):

$stmt->RequireInsecure();

If that line is not being called, then you must debug the preceeding logic to see why it's not being detected by the driver as requiring insecure settings.

And make sure that PDO::SQLSRV_ATTR_DIRECT_QUERY is being set on the statement before it's execute.

Greetings,

Thank you David_garcia. many thanks. i ended up solving this issue by adding filter, to query a node created upto 30 days. but i look at this. again thanks.