Пытаясь ускорить отчет долбанные навсегда


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

Управление имеет доступ к отчету, используемые для определения соответствия. Отчет имеет несколько параметров, которые должны быть выбраны:

  • Диапазон дат, чтобы посмотреть в (например, с 11 марта 2010 по 11 марта 2011 года)
  • Одну или несколько онлайн-тестов (это больница, так что некоторые темы имеют клинические и неклинические версия)
  • Одно или несколько подразделений (VPS может увидеть все свои отделы, менеджеры могут увидеть только их отдел и т. д.)
  • Есть несколько вариантов для фильтрации результатов (показывает все результаты, показывая только "пропавших без вести" результаты - как бы сломан на данный момент, кроме "отсутствия" результатов)
  • В отчете могут быть выведены в виде таблицы Excel или в виде веб-страницы (формат Excel денется из-за некоторых проблем с Excel 2007, у меня просто не было времени, чтобы удалить его)

2011-03-17 добавление:

Как за щедрый совет (и, видимо, раньше меня это отмечает) Я http://aspprofiler.sourceforge.net/ чтобы получить немного более точные данные.

Получение данных из SQL Server 2005, экспресс (который работает на той же машине), кажется, самой большой проблемой.

Набор objResultsRS = objSQLDB.Казнить(,,котором предложение) заняла где-то от 33% до 50% от времени обработки.

Если не objResultsRS.Затем БОФ objResultsRS.MoveFirst берет еще 20% - 26% времени - будь я проклят, если я могу выяснить, почему его даже... поскольку данные бросила в массив, я, вероятно, не нужно проверить BoF и EOF, чтобы увидеть, если я получаю пустой набор записей - мне просто нужно проверить, если массив пуст. Я тест, чтобы увидеть, если это дает какие-либо улучшения для репортажей.

Ничего другого не принимать более 3% от времени обработки. Я бежал довольно типичный протокол проверки системы на соответствие с онлайн-тест с 11 марта 2010 по 11 марта 2011 года. (отчет, постоянно работать в этом месяце почти каждый вице-президент, и источник половина звонков в службу поддержки я принимаю.)

В SQL для этого бегите заканчивается глядя, как:

SELECT LEmp.LawsonID, LEmp.LastName, LEmp.FirstName, LEmp.MidInit, LEmp.winUserName, LPos.Position, LEmp.AccCode, LDept.DisplayName, LEmp.EmpStatus, 
    Log.VerificationID, Log.WinLogon, Log.Name, Log.Position, Log.Pass, Log.DoneTime, Log.Title 
FROM ((Lawson_Employees AS LEmp LEFT JOIN Lawson_DeptInfo AS LDept ON LEmp.AccCode = LDept.AccCode) 
    INNER JOIN Lawson_PositionCodes AS LPos ON LEmp.PosCode = LPos.PosCode) 
    LEFT JOIN (
            SELECT V.VerificationID, V.WinLogon, V.LawsonID, V.Name, V.Position, V.Pass, V.DoneTime, C.Title 
            FROM Testing_TestLogs AS V LEFT JOIN Testing_Content AS C ON V.PresID = C.PresID 
            WHERE V.PresID IN (284,285) 
            AND V.DoneTime >= '20100311000000' AND V.DoneTime <= '20110311235959' AND V.Name <> 'program check'
        ) AS Log ON LEmp.LawsonID = Log.LawsonID 
WHERE LEmp.CurrentEmp = 1 AND LEmp.AccCode <> '106000' 
AND LEmp.EmpStatus NOT IN ('CT','EN','R1','R2','T1','T2','T3') 
ORDER BY LEmp.AccCode ASC, LEmp.LastName ASC, LEmp.FirstName ASC, Log.DoneTime ASC;

2011-03-23 добавление:

Столько, сколько я хотел бы посвятить больше времени этому питу, у меня другие сроки, нажав на. Суб-запрос, кажется, то, что вызывает больше всего огорчений из SQL сервера (спасибо Mongus), но мне не хватило времени, чтобы уделять слишком много внимания.

По duming все равно возвращаются в массив, а затем проверить, если массив был пуст, я был в состоянии получить ~20% улучшение. Так что раздел теперь выглядит так:

Dim objSQLDB : Set objSQLDB = CreateObject("ADODB.Command")

objSQLDB.ActiveConnection = strTestingConn
objSQLDB.CommandText = strSQL

Set objResultsRS = objSQLDB.Execute(,,adCmdText)
arrResults = objResultsRS.GetRows(adGetRowsRest)
intTotResults = UBound(arrResults,2)

objResultsRS.Close
Set objResultsRS = Nothing
Set objSQLDB = Nothing

If Not IsArray(arrResults) Then
    'No results, show the page then end the script'         

objResultsRS.Close
    Set objResultsRS = Nothing
    Set objSQLDB = Nothing
    Response.End()
End If

Я оставляю код (немного подрезали) внизу Для справки/контекст.

%><!-- #include virtual="/educationtesting/inc_testingstuff.asp" --><%
'--> Removed some variable declarations that are used by the library below, but not in the main code below <--'
%><!-- #include virtual="/forum/inc_func_common.asp"--><% 

'--> Removed declarations and validation of incoming data <--'

strSQL = "SELECT LEmp.LawsonID, LEmp.LastName, LEmp.FirstName, LEmp.MidInit, LEmp.winUserName, LPos.Position, LEmp.AccCode, LDept.DisplayName, LEmp.EmpStatus, "
strSQL = strSQL & "Log.VerificationID, Log.WinLogon, Log.Name, Log.Position, Log.Pass, Log.DoneTime, Log.Title "
strSQL = strSQL & "FROM ((Lawson_Employees AS LEmp LEFT JOIN Lawson_DeptInfo AS LDept ON LEmp.AccCode = LDept.AccCode) "
strSQL = strSQL & "INNER JOIN Lawson_PositionCodes AS LPos ON LEmp.PosCode = LPos.PosCode) "
strSQL = strSQL & "LEFT JOIN "
strSQL = strSQL & "(SELECT V.VerificationID, V.WinLogon, V.LawsonID, V.Name, V.Position, V.Pass, V.DoneTime, C.Title "
strSQL = strSQL & "FROM Testing_TestLogs AS V LEFT JOIN Testing_Content AS C ON V.PresID = C.PresID "
strSQL = strSQL & "WHERE V.PresID IN (" & intPresID & ") "
strSQL = strSQL & "AND V.DoneTime >= '" & strFrom & "' AND V.DoneTime <= '" & strTo & "' "
strSQL = strSQL & "AND V.Name <> 'program check') "
strSQL = strSQL & "AS Log ON LEmp.LawsonID = Log.LawsonID "
strSQL = strSQL & "WHERE LEmp.CurrentEmp = 1 "
Select Case LCase(strAccCode)
    Case "all"
    Case "mgmt"
        strSQL = strSQL & "AND (LDept.VP = '" & Session(strCookieURL & "strWinFullName") & "' "
        strSQL = strSQL & "OR LDept.SLD = '" & Session(strCookieURL & "strWinFullName") & "' "
        strSQL = strSQL & "OR LDept.DeptMgr = '" & Session(strCookieURL & "strWinFullName") & "') "
    Case Else
        strSQL = strSQL & "AND LEmp.AccCode IN (" & strAccCode & ") "
End Select

'Filter out the suspense account'
strSQL = strSQL & "AND LEmp.AccCode <> '106000' "

'Filter out LOA, FMLA, etc.'
strSQL = strSQL & "AND LEmp.EmpStatus NOT IN ('CT','EN','R1','R2','T1','T2','T3') "

Dim strRptHead : strRptHead = ""
Select Case UCase(Trim(Request.Form("Filter")))
    Case "C"
        strSQL = strSQL & "AND Log.VerificationID IS NOT NULL "
        strSQL = strSQL & "ORDER BY LEmp.AccCode ASC, LEmp.LastName ASC, LEmp.FirstName ASC, Log.DoneTime ASC;"
        strRptHead = "Only completed records"
    Case "NC"
        strSQL = strSQL & "AND Log.VerificationID IS NULL "
        strSQL = strSQL & "ORDER BY LEmp.AccCode ASC, LEmp.LastName ASC, LEmp.FirstName ASC;"

        strRptHead = "All missing records"
    Case Else
        strSQL = strSQL & "ORDER BY LEmp.AccCode ASC, LEmp.LastName ASC, LEmp.FirstName ASC, Log.DoneTime ASC;"
        strRptHead = "All compliance results"
End Select

Dim objResultsRS, arrResults, intTotResults
Dim objSQLDB : Set objSQLDB = CreateObject("ADODB.Command")
objSQLDB.ActiveConnection = strTestingConn
objSQLDB.CommandText = strSQL
Set objResultsRS = objSQLDB.Execute(,,adCmdText)

If Not objResultsRS.BOF Then objResultsRS.MoveFirst
If objResultsRS.EOF Then
    'No results, show the page then end the script'
    '--> Snipped code that showed message re: no records, and what may be at issue <--'
    objResultsRS.Close
    Set objResultsRS = Nothing
    Set objSQLDB = Nothing
    Response.End()
Else
    'Results, drop the recordset into an array and close out the DB connection'
    arrResults = objResultsRS.GetRows(adGetRowsRest)
    intTotResults = UBound(arrResults,2)
    objResultsRS.Close
    Set objResultsRS = Nothing
    Set objSQLDB = Nothing
End If

'send mime type et al for headings'
Select Case strDisplay
    Case "page"
        '--> Snipped code to write out HTML header info <--'
    Case "excel"
        'Returns the result as an Excel spread sheet.'
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader "content-disposition","attachment;filename=AllResults_" & strFrom & "_To_" & strTo & ".xls"
        '--> Snipped code to write out Excel header info <--'
    Case Else
        Response.Redirect("oops.asp?Err=BadDisplay")
End Select

Select Case strDisplay
    Case "page"
        Response.Write("<table class=""fcc iffc c tc tbc"" cellspacing=""0"" style=""font-size:8pt;"">" & vbNewLine)
        If LCase(strAccCode) = "mgmt" Then
            '--> Show a note to managers re: what to do if a department is/not on the list that should/not be there <--'
        End If
        '--> Write out the main report header, formatted for a web page <--'
        strExcelExtra = ""
    Case "excel"
        '--> Write out the main report header, formatted for an Excel sheet <--'
        strExcelExtra = " style=""mso-number-format:\@;"""
    Case Else
        Response.Redirect("oops.asp?Err=BadDisplay")
End Select

'--> Removed some declarations for variables used below <--'

'Write out each row of data'

'--> Removed the horridly long chunk of code for writing out each row - profiler showed it was not that big of a time suck <--'

'Done, close up table'
Response.Write("</table>" & vbNewLine)
If strDisplay = "page" Then Response.Write("<h3 class=""c""><a href=""#"" onClick=""window.close()"">Close this window</a></h3>" & vbNewLine & "</center>" & vbNewLine)
Response.Write("</body>" & vbNewLine & "</html>")
%>

Сноска: Не думаю, что это важно, но я буду заливать в формате Excel в ближайшем будущем. Как команда разработчиков на 1 (и администратора сервера БД, админ, дизайнер, тестер, сопровождающий и т. д.), есть несколько других проектов, которые нужно закончить, прежде чем я смогу сделать это.



405
3
задан 15 марта 2011 в 10:03 Источник Поделиться
Комментарии
3 ответа

Это заставляет меня грустить, как мало людей знают этот основной совет: Если у вас есть программа, которая медленно профиля. Это занимает пять минут, чтобы загрузить пробную версию профайлер, подключить его к вашему приложению, и видеть точно , где узкие места. Компьютеры являются слишком сложными, чтобы больше о работе из первых принципов.

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

6
ответ дан 16 марта 2011 в 02:03 Источник Поделиться

Как большие данные, которые вы вытягиваете в?

Если таблица Testing_TestLogs массивный (название заставляет меня думать, что это может быть), у вас есть индексы на столбце LawsonID?

Если Testing_Content массивная - у вас есть индекс по столбцу прези?

Как обе эти таблицы оказаться втянутым в запрос базы данных сортировать данные в этих столбцах. Чем больше данных, тем медленнее эта операция будет.

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

4
ответ дан 16 марта 2011 в 01:03 Источник Поделиться

Поскольку другие ответили снова: убедитесь, что у вас есть правильные индексы (столбец " ФК " бы и любой столбец, по которому выполняется фильтрация/отбор довольно много), и чтобы проверить план выполнения - я вижу пару проблем.

Просто беглый взгляд через прок - я могу видеть, что есть большое количество non-sargable заявления. Это не позволяет эффективно использовать ваши индексы и значительно замедлить запросов на больших объемах данных.

За раз, вы должны использовать встроенный в "между" сайта.

http://www.dotnet4all.com/snippets/factsheet%20SQL%20Server.pdf

Посмотрите на sargability, и контрольный список для индексов на ссылку выше. Это большая быстрая-ресурса.

Также так как вы сделали это, вы должны проверить для BoF и EOF в противном случае вы получите 500 при попытке открыть набор записей [пустой].

2
ответ дан 20 марта 2011 в 02:03 Источник Поделиться