Вопрос по performance, cfqueryparam, sql-server, coldfusion-9, coldfusion – Медленный запрос с поиском cfqueryparam по индексируемому столбцу, содержащему хэши

4

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

<code><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>
</code>

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

<code><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>
</code>

Таблица содержит около 60000 строк. & Quot; хэш & quot; Аргумент column равен varchar (50) и имеет уникальный некластеризованный индекс, но не является первичным ключом. Сервер БД - MSSQL 2008. На веб-сервере установлена последняя версия CF9.

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

Edits:

We are using hashes generated by hash() not UUIDS or GUIDS. The hash is generated by a hash(SerializeJSON({ struct })) which contains the plan for a set of operations to execute on an image. The purpose for this is that it allows us to know before insert and before query the exact unique id for that structure. These hashes act as an "index" of what structures have already been stored in the DB. In addition with hashes the same structure will hash to the same result, which is not true for UUIDS and GUIDS.

The query is being executed on 5 different CF9 servers and all of them exhibit the same behavior. To me this rules out the idea that CF9 is caching something. All servers are connecting to the exact same DB so if caching was occurring it would have to be the DB level.

Это может быть связано с тем, что он не может повторно использовать оператор prepare для списков, поэтому он перекомпилируется каждый раз .... Если вы уверены, что используете собственную логику для предотвращения внедрения SQL, возможно, для этого целесообразно пропустить cfqueryparam. Henry
Вот несколько способов улучшить производительность SELECT INflorianreischl.blogspot.ca/2012/03/… Henry
Вы пытались использовать только cf_sql_char вместо varchar? Это может заставить MSSQL более внимательно посмотреть на массив и дать вам лучший план выполнения. Очевидно, что план, который он разрабатывает из кэша, не так эффективен, как план, который он составляет в реальном времени. Также попробуйте добавить подсказку. Запустите на нем анализатор трассировки, если вы можете изолировать его в dev - это может дать вам некоторые подсказки в плане выполнения. это все, что у меня есть :) Mark A Kruger

Ваш Ответ

3   ответа
0

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

8

ки могут помочь Запрос G / UUID MS SQL Server из ColdFusion а также nvarchar против varchar в SQL Server, ВНИМАНИЕ

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

решение о том, какой тип столбца использовать, зависит от данных, которые вы храните. просто убедитесь, что они соответствуют настройкам в CF Administrator :)
Я собираюсь написать это в блоге. Хорошее решение Yisroel - люблю это!
Знаете ли вы, где бы я изменил этот параметр или проверил, так ли это? На этом конкретном источнике данных у меня нет-- Enable High ASCII characters and Unicode for data sources configured for non-Latin characters проверено. Нужно ли мне? Колонна varchar. Nucleon
Похоже, это должно быть не проверено. Можете ли вы попробовать изменить настройку, чтобы увидеть, если она имеет значение?
БУМ! Это Изроэль, большое спасибо. На моем локальном устройстве разработчика это было снято. На наших серверах dev / live это было проверено. Как только я снял галочку, производительность сразу улучшилась. Если бы в будущем я столкнулся с этой же проблемой, но мне нужно было проверить (потому что unicode был необходим), я бы хотел, чтобы мои столбцы были nvarchar таким же образом, как и индекс в unicode? Nucleon
0

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

Есть смысл?

Я полностью согласен с вами, что утверждение IN не является одним списком. Но я не думаю, что он идентифицирует его как новый подготовленный оператор и заставит рассчитать новый план запроса. Если подумать, то количество элементов в операторе IN не должно иметь никакого отношения к тому, использовать ли сканирование таблицы, индекс и т. Д.
Я не уверен, что это актуально, но один и тот же запрос выполняется на 5 разных блоках CF9. Каждый из них демонстрирует одинаковое поведение, которое, я думаю, исключает любую форму кэширования запросов Coldfusion. Все блоки соединяются с одной и той же БД, поэтому, если кэширование находится на уровне SQL, это может быть возможно. Nucleon
Я подозреваю - хотя я не уверен на 100% - хотя для списков разного размера будет другой план. CF не передает SQL какSELECT * FROM table WHERE col IN (:listParam) (т.е. есть единственный параметр связывания), он передает его какSELECT * FROM table WHERE col IN (:each, :element, :separately), Таким образом, сервер увидит, что запрос для списка из трех элементов отличается от запроса со списком из десяти элементов, и я сильно подозреваю, что каждый будет скомпилирован отдельно.

Похожие вопросы