Медленный запрос с поиском cfqueryparam по индексируемому столбцу, содержащему хэши

У меня есть следующий запрос, который выполняется в 16 мс - 30 мс.

<cfquery name="local.test1" datasource="imagecdn">
    SELECT hash FROM jobs WHERE hash in(
        'EBDA95630915EB80709C69089315399B',
        '3617B8E6CF0C62ECBD3C48DDF8585466',
        'D519A38F09FDA868A2FEF1C55C9FEE76',
        '135F94C3774F7719CFF8FF3A275D2D05',
        'D58FAE69C559273D8427673A08193789',
        '2BD7276F209768F2FCA6635659D7922A',
        'B1E3CFBFCCFF6F5B48A849A050E6D424',
        '2288F5B8A797F5302E8CA24323617236',
        '8951883E36B5D38A4643DFAA0396BF13',
        '839210BD564E30BE1355D1A6D4EF7081',
        'ED4A2CB0C28B608C29576819CF7BE19B',
        'CB26925A4874945B810707D5FF0B91F2',
        '33B2FC229F0CC797A02AD163CDBA0875',
        '624986E7547DBAC0F47B3005CFDE0A16',
        '6F692C289BD805CEE41EF59F83F16F4D',
        '8551F0033C617BD9EADAAD6CEC4B3E9E',
        '94C3C0A74C2DE085FF9F1BBF928821A4',
        '28DC1A9D2A69C2EDF5E6C0E6368A0B3C'
    )
</cfquery>

Если я выполняю тот же запрос, но использую cfqueryparam, он выполняется через 500 - 2000 мс.

<cfset local.hashes = "[list of the same ids as above]">
<cfquery name="local.test2" datasource="imagecdn">
    SELECT hash FROM jobs WHERE hash in(
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#local.hashes#" list="yes">
    )
</cfquery>

Таблица содержит около 60000 строк. Столбец hash имеет тип varchar(50) и имеет уникальный некластеризованный индекс, но не является первичным ключом. Сервер БД - MSSQL 2008. На веб-сервере установлена ​​последняя версия CF9.

Любая идея, почему cfqueryparam заставляет производительность взрываться? Он ведет себя так каждый раз, независимо от того, сколько раз я обновляю страницу. Если я спариваю список только до 2 или 3 хешей, он все равно работает плохо на 150-200 мс. Когда я удаляю cfqueryparam, производительность соответствует ожидаемой. В этой ситуации существует возможность внедрения SQL-кода, поэтому использование cfqueryparam, безусловно, предпочтительнее, но не нужно 100 мс, чтобы найти 2 записи из индексированного столбца.

Редактирование:

  1. Мы используем хеши, сгенерированные hash() не UUIDS или GUIDS. Хеш генерируется hash(SerializeJSON({ struct })) который содержит план для набора операций для выполнения над изображением. Цель этого состоит в том, что это позволяет нам знать перед вставкой и перед запросом точного уникального идентификатора для этой структуры. Эти хеши действуют как "индекс" того, какие структуры уже хранятся в БД. В дополнение к хешам та же структура будет хешить к тому же результату, что не верно для UUIDS и GUIDS.

  2. Запрос выполняется на 5 разных серверах CF9, и все они работают одинаково. Для меня это исключает идею, что CF9 что-то кеширует. Все серверы подключаются к одной и той же БД, поэтому, если бы происходило кэширование, это был бы уровень БД.

Источник

3 ответа

Решение

Ваша проблема может быть связана с VARCHAR против NVARCHAR. Эти 2 ссылки могут помочь в запросе G/UUID MS SQL Server из ColdFusion и nvarchar против varchar в SQL Server, ВНИМАНИЕ

Может случиться так, что в ColdFusion есть настройка, если cfqueryparam отправляет varchars как юникод или нет. Если этот параметр не соответствует настройке столбца (в вашем случае, если этот параметр включен), то MS SQL не будет использовать этот индекс.

Источник

Я не думаю, что cfqueryparam вызывает проблему. Поскольку вы упомянули о значительном повышении производительности, возможно, индекс не будет использоваться для вашего запроса при попытке использования cfqueryparam. Я создал такой же сценарий на своем компьютере для разработки, но у меня было одинаковое время выполнения с и без cfqueryparam. Использование списка может быть связано с некоторыми дополнительными затратами, поскольку в первом запросе вы передаете его непосредственно в качестве теста, а во втором Coldfusion необходимо создать параметр запроса из предоставленного списка, но, опять же, это не так уж много. Я предлагаю запустить "SQL Server Profiler" и отслеживать запросы, выполняемые на сервере, это поможет вам лучше, кто стоит еще 500 мс.

Источник

Как отмечает Марк, это, вероятно, неверный план выполнения в кеше. Одним из преимуществ cfqueryparam является то, что когда вы передаете разные значения, он может повторно использовать кэшированный план, который он имеет для этого оператора. Вот почему, когда вы попробуете это с меньшим списком, вы не увидите никаких улучшений. Если вы не используете cfqueryparam, SQL Server должен каждый раз разрабатывать план выполнения. Обычно это плохо, если в кеше нет оптимального плана. Попробуйте очистить кеш, как описано здесь http://www.devx.com/tips/Tip/14401 это, надеюсь, будет означать, что в следующий раз, когда вы запустите ваш оператор с cfqueryparam, он кеширует лучший план.

Есть смысл?

Источник
Другие вопросы по тегам