нужна помощь в оптимизации хранимой процедуры

У меня есть хранимая процедура, которая создает динамический SQL-запрос, а затем запускает его через exec@sql.

Сохраненный процесс объединяет около 12 таблиц. Как бы то ни было, он работал относительно быстро. Но тогда мне нужно было добавить дополнительное поле. Для этого я создал скалярную функцию, которая выглядит так:

SELECT @weight = @weight +COUNT(*) FROM dbo.UserPDMedication WHERE UserID = @userid
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND HoehnYarhID IS NOT null
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND DateOfBirth IS NOT NULL
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND GenderID IS NOT NULL
SELECT @weight = @weight +COUNT(*) FROM dbo.[User] WHERE UserID = @userid AND DateDiagnosed IS NOT null

По сути, это просто функция, которая возвращает целое число в зависимости от того, сколько вопросов заполнил пользователь. Таким образом, для каждого пользователя в хранимой процедуре вызывается эта функция. Сохраненный процесс выглядит так:

    SELECT DISTINCT u.UserID, u.Healthy, u.DateOfBirth, u.City, st.StateCode AS State, u.GenderID, g.Gender, u.Latitude, u.Longitude, u.PDConditionID, u.Zip, u.Distance,
    (SELECT TOP 1 EmailID FROM Messages m WHERE TrialID = ' + @trialID + ' AND ToUserID = u.userid AND LocationID = ' + @locationID + ') AS MessageID, dbo.UserWeightedValue(u.UserID) as wt
FROM [User] u
    INNER JOIN aspnet_UsersInRoles uir ON u.AspnetUserID = uir.UserId
    INNER JOIN aspnet_Roles r ON uir.RoleId = r.RoleId
    FULL JOIN UserHealthCondition uhc ON u.UserID = uhc.UserID
    FULL JOIN UserMotorSymptom ums ON u.UserID = ums.UserID
    FULL JOIN UserNonMotorSymptom unms ON u.UserID = unms.UserID
    FULL JOIN UserPDMedication updm ON u.UserID = updm.UserID
    FULL JOIN UserPDTreatment updt ON u.UserID = updt.UserID
    FULL JOIN UserSupplement us ON u.UserID = us.UserID
    FULL JOIN UserPDGeneticMarker updgm ON u.UserID = updgm.UserID
    FULL JOIN UserFamilyMember ufm ON u.UserID = ufm.UserID
    FULL JOIN State st ON u.StateID = st.ID
    FULL JOIN Gender g ON u.GenderID = g.ID
WHERE u.UserID IS NOT NULL

(я удалил некоторые куски, чтобы попытаться сделать это коротким). Это получение выполняется как динамическая строка в хранимой процедуре. Любые советы о том, как я могу оптимизировать это, чтобы ускорить работу?

Спасибо

РЕДАКТИРОВАТЬ: я получил эту работу, используя комбинацию предложений здесь. Я сохранил свою функцию как есть, хотя я объединил операторы множественного выбора в 2 оператора. Затем я взял исходный сохраненный процесс и изменил выбор на выбор в ##temp. И затем я запустил свою функцию для этой временной таблицы. Время выполнения сократилось до 3-4 секунд. Я думаю, что мне придется отдать должное Гранту за этот вопрос, поскольку именно его указание на отличие поставило меня на правильный путь. Но спасибо всем.


stored-procedures sql-server-2008 user-defined-functions scalar
person merk    schedule 10.02.2012    source источник
comment
Является ли UserID первичным ключом вашей таблицы User?   -  person Lamak    schedule 10.02.2012
comment
Да, userid — это первичный ключ.   -  person merk    schedule 11.02.2012

Ответы (3)


arrow_upward
1
arrow_downward

DISTINCT обязательно приведет к снижению производительности, поскольку он выполняет агрегацию. Вам это действительно нужно? Часто, когда вы видите DISTINCT, это указывает на проблему с данными или структурой, которая скрывается за счет возможности устранения дубликатов, которые структура должна устранять сама по себе.

После этого вместо коррелированного запроса в списке SELECT я бы хотел переместить его как JOIN. Это не беспроигрышный вариант, но часто оптимизатору удается лучше включить это в план.

Исходя из сложности того, что вы представляете, я бы также посмотрел на план выполнения. Первое, что нужно проверить, у вас полная оптимизация или тайм-аут. Если время истекло, то вы имеете дело с наилучшей догадкой, а не с полностью рассчитанным «достаточно хорошим» планом. Если это так, вам нужно подумать об упрощении этого запроса. Если у вас есть достаточно хороший план, посмотрите, где в нем есть узкие места.

person Grant Fritchey    schedule 10.02.2012
comment
Необходимо различать, иначе он возвращает слишком много повторяющихся записей. В данном случае это связано с тем, что некоторые из объединенных таблиц имеют несколько записей для каждого пользователя. Это не недостаток дизайна — каждый пользователь может выбрать несколько значений для этих конкретных таблиц. Однако для моей функции не будет дубликатов, поскольку она использует идентификатор пользователя, который является первичным ключом для пользовательской таблицы. Так что, если перемещение моей функции из отдельного поможет, я могу это сделать. Если я запускаю select myFunction(userid), user.* from [user], он завершает запрос примерно за 5 секунд. - person merk; 11.02.2012
comment
ХОРОШО. Просто проверка. Это одна из тех вещей, которые доставляют людям неприятности, особенно с точки зрения производительности. - person Grant Fritchey; 11.02.2012

arrow_upward
1
arrow_downward

Если UserID является первичным ключом таблицы User, то нет необходимости делать один SELECT для вопроса, заполненного пользователем, его можно обернуть всего одним SELECT:

SELECT @weight = @weight + COUNT(HoehnYarhID) + COUNT(DateOfBirth) + COUNT(GenderID) + COUNT(DateDiagnosed)
FROM dbo.[User] 
WHERE UserID = @userid
person Lamak    schedule 10.02.2012
comment
Спасибо. Вы правы, я должен был заметить это сам. Сейчас он может работать на несколько секунд быстрее, не уверен. Мне все еще нужно поработать над этим еще немного, чтобы ускорить его. - person merk; 11.02.2012

arrow_upward
0
arrow_downward

Преобразуйте скалярную функцию со значением во встроенную функцию с табличным значением.

person Mitch Wheat    schedule 10.02.2012
comment
Спасибо, я попробую это. Однако вопрос - я раньше не создавал функцию с табличным значением. Как я могу использовать параметр внутри выбора возврата? Если вы посмотрите на мой исходный код sql для скалярной функции, вы увидите, что я присваиваю общее значение @weight. Я упростил свой запрос до двух операторов выбора. Кажется, я не могу выбрать count(2ndtable.*), поэтому я не смог объединить все это в один выбор, поэтому мне нужен параметр. Есть ли лучший способ сделать это? Спасибо - person merk; 11.02.2012