Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Медленный запрос из MS SQL  [new]
salex13
Member

Откуда:
Сообщений: 5
Добрый вечер.

В чем проблема:
Очень долгая обработка запросов, на бд размером 4.5гб запрос вида:

SELECT TOP 1 dbo.FloatTable.Val 
FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) 
WHERE dbo.TagTable.TagName LIKE '%200400.db_V_Tanks.V1%' AND DateAndTime >= '1.1.2017 0:00:00' AND DateAndTime < '1.2.2017 0:00:00' order by val desc


выполняется ~30 секунд в ssms. В данном случае запрашивается последнее значение параметра за день. А если учесть, что формируем месячный отчет, да еще и по нескольким параметрам, времени уходит астрономически много. Раньше, когда тестировал, уходило на подобный запрос максимум по 500мс, но и база была в разы меньше. На данный момент, нужно узнать в какую сторону двигаться, чтобы получить адекватное время обработки запроса в ssms, а с vba уже буду сам разбираться.

Задача:

Есть система (factorytalk view se), которая сохраняет архивные значения параметров оборудования в MSSQL Express . В системе свои подобия юзерформ с поддеркой VBA. Нужно через VBA выгрузить данные для отчета (почасового, суточного, месячного). Данные хранятся в двух таблицах dbo.FloatTable и dbo.TagTable. В первой значения, время, индекс и т.п., во второй имя параметра и индекс.

DateAndTimeMillitmTagIndexValStatusMarker
2017-05-23 00:00:00.000000068164UB
2017-05-23 00:00:00.000000068258UB
2017-05-23 00:00:00.000000068354UB
2017-05-23 00:00:10.000000068151UB
2017-05-23 00:00:10.000000068249UB


TagNameTagIndexTagTypeTagDataType
/DATA_AREA::200200.db_ASensor.CIT01.Val2920
/DATA_AREA::200200.db_ASensor.CIT03.Val3020
/DATA_AREA::200200.db_ASensor.FIT06.Val3120
/DATA_AREA::200200.db_ASensor.FIT07.Val3220


Вроде бы структура простая, но из-за скудных знаний mssql чувствую, что что-то делаю не так.

Начал делать все это дело на процедурах в mssql и уже вызывал в программе, но потом выяснилось что нужно будет в будущем переходить на оракл, так что я решил сделать более универсальное решение, которое похоже вышло мне боком. Запросы практически все идентичны первому, иногда возвращаю первое или последнее значение, иногда среднее. Все запросы возвращают одно значение. Завтра попробую сделать через процедуру, если результат будет неудовлетворительным, попробую поиграться со временем в запросах, т.е. брать не день, а последние несколько минут.

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

Ниже листинг кода в vba, на всякий случай
+
Sub ShowXLOnTop(ByVal OnTop As Boolean, exc As Excel.Application)
    Dim xStype As Long
    #If Win64 Then
        Dim xHwnd As LongPtr
    #Else
        Dim xHwnd As Long
    #End If
    If OnTop Then
        xStype = HWND_TOPMOST
    Else
        xStype = HWND_NOTOPMOST
    End If
    Call SetWindowPos(exc.Application.hwnd, xStype, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)
End Sub

Private Sub btnDataToExcel_Released()
    
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
 
    Set oXLApp = New Excel.Application  'Create a new instance of Excel
 
    Set oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
 
    Dim clipboard As MSForms.DataObject
        Set clipboard = New MSForms.DataObject

        clipboard.Clear
        With gridReportData
            .col = 0
            .Row = 1
            .ColSel = .Cols - 1
            .RowSel = .Rows - 1
            clipboard.SetText .Clip
        End With

        clipboard.PutInClipboard
        clipboard.Clear
        Set clipboard = Nothing
 
        Dim Indent As Integer
        Indent = 1
 
        With oXLBook.Worksheets(1)
            .Range("A" & 1 + Indent).Select
            .Paste
            .PageSetup.Orientation = xlLandscape
            .Range("B:Z").NumberFormat = "###0.00"
            .Columns("A:Z").AutoFit
            
            .Range("A" & 1 + Indent & ":Z" & 1 + Indent).Font.FontStyle = "Bold"
            .Range("B" & 1 + Indent & ":Z" & 1 + Indent).ColumnWidth = 10
            .Range("A" & 1 + Indent & ":Z" & 1 + Indent).WrapText = True
            .Range("A" & 1 + Indent & ":Z" & 1 + Indent).VerticalAlignment = xlTop
            .Range("A1").Value = "&#204;&#229;&#241;&#255;&#247;&#237;&#251;&#233; &#238;&#242;&#247;&#229;&#242; &#239;&#238; &#208;&#195;&#209;"
            .PageSetup.RightFooter = "&#209;&#242;&#240;&#224;&#237;&#232;&#246;&#224; &P &#232;&#231; &N"
            .Range("A1").Font.FontStyle = "Bold"
            .Range("A" & 1 & ":L" & 1).Merge
            .Range("A1").HorizontalAlignment = xlCenter
        End With
        
        
        With oXLApp.Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With oXLApp.Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With oXLApp.Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With oXLApp.Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With oXLApp.Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With oXLApp.Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        
        oXLApp.Visible = True               'Show it to the user
        ShowXLOnTop True, oXLApp
        
        Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
        Set oXLApp = Nothing

End Sub



Private Function GetTagName(Description As String) As String
    Select Case Description
        Case "LT21a - &#204;&#224;&#234;&#241;&#232;&#236;&#224;&#235;&#252;&#237;&#251;&#233; &#243;&#240;&#238;&#226;&#229;&#237;&#252; &#209;&#211;&#195; &#226; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229; &#200;1"
            GetTagName = "200400.db_ASensor.LT21a.Val"
        Case "LT22a - &#204;&#224;&#234;&#241;&#232;&#236;&#224;&#235;&#252;&#237;&#251;&#233; &#243;&#240;&#238;&#226;&#229;&#237;&#252; &#209;&#211;&#195; &#226; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229; &#200;2"
            GetTagName = "200400.db_ASensor.LT22a.Val"
        Case "LT23a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;1"
            GetTagName = "200400.db_ASensor.LT23a.Val"
        Case "LT24a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;2"
            GetTagName = "200400.db_ASensor.LT24a.Val"
        Case "LT25a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;3"
            GetTagName = "200400.db_ASensor.LT25a.Val"
        Case "LT26a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;4"
            GetTagName = "200400.db_ASensor.LT26a.Val"
        Case "PT10a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229;&#233; &#200;1,&#200;2"
            GetTagName = "200400.db_ASensor.PT10a.Val"
        Case "PT1a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#225;&#224;&#231;&#238;&#233; 1"
            GetTagName = "200400.db_ASensor.PT1a.Val"
        Case "PT2a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#225;&#224;&#231;&#238;&#233; 2"
            GetTagName = "200400.db_ASensor.PT2a.Val"
        Case "PT3a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#241;&#235;&#232;&#226;&#237;&#251;&#236;&#232; &#234;&#238;&#235;&#235;&#238;&#237;&#224;&#236;&#232;"
            GetTagName = "200400.db_ASensor.PT3a.Val"
        Case "PT4a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#224;&#236;&#232; &#204;1,&#204;2"
            GetTagName = "200400.db_ASensor.PT4a.Val"
        Case "PT5a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;1"
            GetTagName = "200400.db_ASensor.PT5a.Val"
        Case "PT6a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;1"
            GetTagName = "200400.db_ASensor.PT6a.Val"
        Case "PT7a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;2"
            GetTagName = "200400.db_ASensor.PT7a.Val"
        Case "PT8a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;2"
            GetTagName = "200400.db_ASensor.PT8a.Val"
        Case "PT9a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#228;&#238; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229;&#233; &#200;1,&#200;2"
            GetTagName = "200400.db_ASensor.PT9a.Val"
        Case "FlowGas - &#195;&#208;&#207; &#240;&#224;&#241;&#245;&#238;&#228;"
            GetTagName = "200500.DB_FlowGas_1.FlowGas"
        Case "V1 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P1"
            GetTagName = "200400.db_V_Tanks.V1"
        Case "V2 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P2"
            GetTagName = "200400.db_V_Tanks.V2"
        Case "V3 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P3"
            GetTagName = "200400.db_V_Tanks.V3"
        Case Else
            GetTagName = ""
    End Select
End Function

Private Sub ButtonsOn()
    btnGetData.Enabled = True
    btnDataToExcel.Enabled = True
    btnMenuMain.Enabled = True
    btnMenuBack.Enabled = True
    btnMenuTrends.Enabled = True
    btnMenuReports.Enabled = True
    btnMenuAlarms.Enabled = True
    tvTags.Enabled = True
End Sub

Private Sub ButtonsOff()
    btnGetData.Enabled = False
    btnDataToExcel.Enabled = False
    btnMenuMain.Enabled = False
    btnMenuBack.Enabled = False
    btnMenuTrends.Enabled = False
    btnMenuReports.Enabled = False
    btnMenuAlarms.Enabled = False
    tvTags.Enabled = False
End Sub

Private Sub btnGetData_Released()
    
    ButtonsOff
    
    Dim i As Integer
    Dim CountChoosedTags As Integer
    
    CountChoosedTags = 0
    For i = 1 To tvTags.Nodes.Count
        If (tvTags.Nodes(i).Checked And tvTags.Nodes(i).Children = 0) Then
            CountChoosedTags = CountChoosedTags + 1
        End If
    Next i
    
    If (CountChoosedTags > 11 And cbTagsLimit.Value) Then
        MsgBox "&#209;&#242;&#238;&#232;&#242; &#238;&#227;&#240;&#224;&#237;&#232;&#247;&#229;&#237;&#232;&#229; &#237;&#224; &#234;&#238;&#235;&#232;&#247;&#229;&#241;&#242;&#226;&#238; &#226;&#251;&#225;&#240;&#224;&#237;&#237;&#251;&#245; &#242;&#229;&#227;&#238;&#226;, &#247;&#242;&#238;&#225;&#251; &#239;&#229;&#247;&#224;&#242;&#252; &#237;&#229; &#226;&#251;&#245;&#238;&#228;&#232;&#235;&#224; &#231;&#224; &#239;&#240;&#229;&#228;&#229;&#235;&#251; &#235;&#232;&#241;&#242;&#224;. (&#194;&#251; &#236;&#238;&#230;&#229;&#242;&#229; &#243;&#225;&#240;&#224;&#242;&#252; &#238;&#227;&#240;&#224;&#237;&#232;&#247;&#229;&#237;&#232;&#229;, &#241;&#237;&#255;&#226; &#227;&#224;&#235;&#234;&#243; &#237;&#224; &#241;&#238;&#238;&#242;&#226;&#229;&#242;&#226;&#243;&#254;&#249;&#229;&#236; &#239;&#238;&#235;&#229;, &#234;&#238;&#242;&#238;&#240;&#238;&#229; &#237;&#224;&#245;&#238;&#228;&#232;&#242;&#241;&#255; &#239;&#238;&#228; &#238;&#225;&#235;&#224;&#241;&#242;&#252;&#254; &#226;&#251;&#225;&#238;&#240;&#224; &#239;&#224;&#240;&#224;&#236;&#229;&#242;&#240;&#238;&#226;)", vbOKOnly, "&#211;&#241;&#242;&#224;&#237;&#238;&#226;&#235;&#229;&#237;&#238; &#238;&#227;&#240;&#224;&#237;&#232;&#247;&#229;&#237;&#232;&#229;"
        btnGetData.Enabled = True
        Exit Sub
    End If
    
    Dim DateIndex As Date
    Dim Count As Integer
    Dim CountDate As Integer
    
    With gridReportData
            .Cols = 1
            .Rows = 1
            .AddItem ""
            '.AddItem ""
            .FixedRows = 1
    
    For DateIndex = DateStart To DateAdd("d", -1, (DateAdd("m", 1, DateStart)))
        .AddItem DateIndex 'Day(DateIndex) & "." & Month(DateIndex) & "." & Year(DateIndex) ' & " 00:00-23:59" & vbTab
    Next DateIndex
    
    
    pbGetDataProgress.Visible = True
    
    pbGetDataProgress.Max = CountChoosedTags
    pbGetDataProgress.Min = 0
    
    
    Dim rsValues As Recordset
    Set dbConnect = CreateObject("ADODB.Connection")
    
    dbConnect.ConnectionString = ConnectString
    dbConnect.Open
    
    Dim tempStr As String
    
    
    Count = 0
    For i = 1 To tvTags.Nodes.Count
        If (tvTags.Nodes(i).Checked) Then
            If (GetTagName(tvTags.Nodes(i).Text) <> "") Then
                .Cols = .Cols + 1
                Count = Count + 1
                pbGetDataProgress.Value = Count
                CountDate = 1
                gridReportData.TextMatrix(1, Count) = tvTags.Nodes(i).Text
                For DateIndex = DateStart To DateAdd("d", -1, (DateAdd("m", 1, DateStart)))
                    CountDate = CountDate + 1
                    If tvTags.Nodes(i).Text = "V1 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P1" Or tvTags.Nodes(i).Text = "V2 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P2" Or tvTags.Nodes(i).Text = "V3 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P3" Then
                        tempStr = GetSqlQueryForTagV(GetTagName(tvTags.Nodes(i).Text), DateIndex, DateAdd("d", 1, DateIndex))
                    Else
                        tempStr = GetSqlQueryForTag(GetTagName(tvTags.Nodes(i).Text), DateIndex, DateAdd("d", 1, DateIndex))
                    End If
                    Set rsValues = dbConnect.Execute(tempStr)
                    If Not (rsValues.EOF Or rsValues.BOF Or IsNull(rsValues(0))) Then
                        gridReportData.TextMatrix(CountDate, Count) = FormatNumber(rsValues(0), 2, vbUseDefault, vbUseDefault, vbUseDefault)
                    Else
                        gridReportData.TextMatrix(CountDate, Count) = "-"
                    End If
                    
                Next DateIndex
                
            End If
            Set nodY = tvTags.Nodes(i)
        End If
    Next i
    
    End With
    
    ButtonsOn
    
End Sub

Private Sub btnMenuAlarms_Released()

End Sub

Private Sub cbMonth_Change()
    DateStart = DateSerial(cbYear.Value, GetMonthByName(cbMonth.Value), 1)
End Sub

Private Sub cbTagsLimit_Click()

End Sub

Private Sub cbYear_Change()
    DateStart = DateSerial(cbYear.Value, GetMonthByName(cbMonth.Value), 1)
End Sub

Private Function GetMonthByName(monthName As String) As Integer
    Select Case monthName
        Case "&#223;&#237;&#226;&#224;&#240;&#252;"
            GetMonthByName = 1
        Case "&#212;&#229;&#226;&#240;&#224;&#235;&#252;"
            GetMonthByName = 2
        Case "&#204;&#224;&#240;&#242;"
            GetMonthByName = 3
        Case "&#192;&#239;&#240;&#229;&#235;&#252;"
            GetMonthByName = 4
        Case "&#204;&#224;&#233;"
            GetMonthByName = 5
        Case "&#200;&#254;&#237;&#252;"
            GetMonthByName = 6
        Case "&#200;&#254;&#235;&#252;"
            GetMonthByName = 7
        Case "&#192;&#226;&#227;&#243;&#241;&#242;"
            GetMonthByName = 8
        Case "&#209;&#229;&#237;&#242;&#255;&#225;&#240;&#252;"
            GetMonthByName = 9
        Case "&#206;&#234;&#242;&#255;&#225;&#240;&#252;"
            GetMonthByName = 10
        Case "&#205;&#238;&#255;&#225;&#240;&#252;"
            GetMonthByName = 11
        Case "&#196;&#229;&#234;&#224;&#225;&#240;&#252;"
            GetMonthByName = 12
        Case Else
            GetMonthByName = 1
    End Select
End Function

Private Sub Display_Activate()

End Sub

Private Sub Display_Load()
    
    cbMonth.AddItem "&#223;&#237;&#226;&#224;&#240;&#252;"
    cbMonth.AddItem "&#212;&#229;&#226;&#240;&#224;&#235;&#252;"
    cbMonth.AddItem "&#204;&#224;&#240;&#242;"
    cbMonth.AddItem "&#192;&#239;&#240;&#229;&#235;&#252;"
    cbMonth.AddItem "&#204;&#224;&#233;"
    cbMonth.AddItem "&#200;&#254;&#237;&#252;"
    cbMonth.AddItem "&#200;&#254;&#235;&#252;"
    cbMonth.AddItem "&#192;&#226;&#227;&#243;&#241;&#242;"
    cbMonth.AddItem "&#209;&#229;&#237;&#242;&#255;&#225;&#240;&#252;"
    cbMonth.AddItem "&#206;&#234;&#242;&#255;&#225;&#240;&#252;"
    cbMonth.AddItem "&#205;&#238;&#255;&#225;&#240;&#252;"
    cbMonth.AddItem "&#196;&#229;&#234;&#224;&#225;&#240;&#252;"
    cbMonth.Style = fmStyleDropDownList
    cbYear.Style = fmStyleDropDownList
    cbMonth.Value = "&#223;&#237;&#226;&#224;&#240;&#252;"
    
    
    Dim yearInt As Integer
    
    For yearInt = 17 To 99
        cbYear.AddItem "20" & yearInt
    Next yearInt
    cbYear.Value = "" & Year(Now)
    
    DateStart = DateSerial(cbYear.Value, GetMonthByName(cbMonth.Value), 1)
    
    cbTagsLimit.Value = True
    btnDataToExcel.Enabled = False
    btnGetData.Enabled = False
    
    ConnectString = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=DB_DataLog;Data Source=SERVER\FTVIEWX64TAGDB"
    'ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=FTView"
    'dtpStart.Second = 0
    'dtpStart.Minute = 0
    'dtpStart.Hour = 0
    tvTags.Enabled = False
    ''''''''''''''''''''''''''''''''''''
    Dim nodX As Node
       
    With tvTags
        .Style = tvwTreelinesPlusMinusPictureText
        .Indentation = 0
        .LineStyle = tvwRootLines
    
        .Nodes.Clear
        
        .Nodes.Add , tvwChild, "Group1", "&#208;&#224;&#241;&#245;&#238;&#228; &#226;&#238;&#228;&#251;"
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_1", "LT21a - &#204;&#224;&#234;&#241;&#232;&#236;&#224;&#235;&#252;&#237;&#251;&#233; &#243;&#240;&#238;&#226;&#229;&#237;&#252; &#209;&#211;&#195; &#226; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229; &#200;1")
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_2", "LT22a - &#204;&#224;&#234;&#241;&#232;&#236;&#224;&#235;&#252;&#237;&#251;&#233; &#243;&#240;&#238;&#226;&#229;&#237;&#252; &#209;&#211;&#195; &#226; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229; &#200;2")
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_3", "LT23a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;1")
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_4", "LT24a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;2")
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_5", "LT25a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;3")
            Set nodX = .Nodes.Add("Group1", tvwChild, "1_6", "LT26a - &#211;&#240;&#238;&#226;&#229;&#237;&#252; &#226; &#240;&#229;&#231;&#229;&#240;&#226;&#243;&#224;&#240;&#229; &#208;4")
        .Nodes.Add , tvwChild, "Group2", "&#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#226;&#238;&#228;&#251;"
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_1", "PT10a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229;&#233; &#200;1,&#200;2")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_2", "PT1a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#225;&#224;&#231;&#238;&#233; 1")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_3", "PT2a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#225;&#224;&#231;&#238;&#233; 2")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_4", "PT3a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#236;&#229;&#230;&#228;&#243; &#234;&#238;&#236;&#239;&#229;&#240;&#229;&#241;&#241;&#238;&#240;&#224;&#236;&#232; &#232; &#241;&#235;&#232;&#226;&#237;&#251;&#236;&#232; &#234;&#238;&#235;&#235;&#238;&#237;&#224;&#236;&#232;")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_5", "PT4a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#224;&#236;&#232; &#204;1,&#204;2")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_6", "PT5a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;1")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_7", "PT6a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;1")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_8", "PT7a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#229;&#240;&#229;&#228; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;2")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_9", "PT8a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#239;&#238;&#241;&#235;&#229; &#237;&#224;&#241;&#238;&#241;&#238;&#236; &#204;2")
            Set nodX = .Nodes.Add("Group2", tvwChild, "2_10", "PT9a - &#196;&#224;&#226;&#235;&#229;&#237;&#232;&#229; &#228;&#238; &#232;&#241;&#239;&#224;&#240;&#232;&#242;&#229;&#235;&#229;&#233; &#200;1,&#200;2")
        .Nodes.Add , tvwChild, "Group3", "&#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224;"
            Set nodX = .Nodes.Add("Group3", tvwChild, "3_1", "V1 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P1")
            Set nodX = .Nodes.Add("Group3", tvwChild, "3_2", "V2 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P2")
            Set nodX = .Nodes.Add("Group3", tvwChild, "3_3", "V3 - &#206;&#225;&#250;&#229;&#236; &#227;&#224;&#231;&#224; &#226; P3")
    End With
  
    Dim i As Integer
    Dim nodY As Node
    'Dim S
    
    
    
    tvTags.Enabled = True
End Sub

Private Function CheckTime() As Boolean
    If (HourBegin * 3600 + MinuteBegin * 60 + SecondBegin <= HourEnd * 3600 + MinteEnd * 60 + SecondEnd) Then
        CheckTime = True
    Else
        CheckTime = False
    End If
End Function

Private Function GetSqlQueryForTag(tagName As String, DateStart1 As Date, DateEnd1 As Date) As String
    GetSqlQueryForTag = _
        "SELECT AVG(dbo.FloatTable.Val) " & vbCl & _
        "FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) " & vbCl & _
        "WHERE dbo.TagTable.TagName LIKE '%" & tagName & _
        "%' AND DateAndTime >= '" & FormatDateToSql(DateStart1) & _
        "' AND DateAndTime < '" & FormatDateToSql(DateEnd1) & "'"
End Function

Private Function GetSqlQueryForTagV(tagName As String, DateStart1 As Date, DateEnd1 As Date) As String
    GetSqlQueryForTagV = _
        "SELECT TOP 1 dbo.FloatTable.Val " & vbCl & _
        "FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) " & vbCl & _
        "WHERE dbo.TagTable.TagName LIKE '%" & tagName & _
        "%' AND DateAndTime >= '" & FormatDateToSql(DateStart1) & _
        "' AND DateAndTime < '" & FormatDateToSql(DateEnd1) & "' order by val desc"
End Function

Private Function FormatDateToSql(inputDate As Date) As String
    FormatDateToSql = Month(inputDate) & "." & Day(inputDate) & "." & Year(inputDate) & " " & TimeValue(inputDate)
End Function

Private Sub tvTags_NodeCheck(ByVal Node As MSComctlLib.Node)
    btnGetData.Enabled = True
    tvTags.Enabled = False
    
    CheckChildren Node
    
    Dim nodX As Node
    
    Set nodX = Node
    Do Until nodX.Parent Is Nothing
        Set nodX = nodX.Parent
        If (CheckParent(nodX)) Then
            nodX.Checked = True
        Else
            nodX.Checked = False
        End If
    Loop
    
    tvTags.Enabled = True
End Sub

Private Sub CheckChildren(nd As Node)
    
    
    Dim i As Integer
    Dim nodX As Node
    
    
    If nd.Children <> 0 Then
        Set nodX = nd.Child
       
        For i = 1 To nd.Children
            nodX.Checked = nd.Checked
            CheckChildren nodX
            Set nodX = nodX.Next
        Next
    End If
End Sub

Private Function CheckParent(nd As Node)
    Dim i As Integer
    Dim nodX As Node
    Dim nodeChild As Node
    
    Set nodX = nd
    
        
    If nodX.Children <> 0 Then
        Set nodeChild = nodX.Child
       
        For i = 1 To nodX.Children
            If nodeChild.Checked Then
                CheckParent = True
                Exit Function
            End If
            
            Set nodeChild = nodeChild.Next
        Next
    End If
    
    CheckParent = False
End Function

17 авг 17, 18:42    [20732297]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Такой запрос:
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT TOP 1 dbo.FloatTable.Val 
FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) 
WHERE dbo.TagTable.TagName LIKE '%200400.db_V_Tanks.V1%' AND DateAndTime >= '1.1.2017 0:00:00' AND DateAndTime < '1.2.2017 0:00:00' order by val desc
OPTION(RECOMPILE);

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
что выдаёт?
17 авг 17, 18:50    [20732310]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
dies irae
Member

Откуда:
Сообщений: 78
индексы?

например, это может помочь:
create clustered index ixFloatTable on  dbo.FloatTable(DateAndTime,TagIndex,Val)

ессно, insert, update и delete - операции это слегка замедлит

ну а для более точного ответа - приводите скрипт таблиц с индексами и план запроса
17 авг 17, 18:53    [20732323]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Таки да, нужны скрипты таблиц, количество записей в каждой таблице, план запроса.
17 авг 17, 19:01    [20732341]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
salex13
Member

Откуда:
Сообщений: 5
Dmitry V. Liseev
что выдаёт?

К сожалению нет доступа к серверу сейчас, завтра попробую, отпишусь.
17 авг 17, 19:27    [20732400]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
salex13
Member

Откуда:
Сообщений: 5
dies irae
индексы?

например, это может помочь:
create clustered index ixFloatTable on  dbo.FloatTable(DateAndTime,TagIndex,Val)

ессно, insert, update и delete - операции это слегка замедлит

ну а для более точного ответа - приводите скрипт таблиц с индексами и план запроса


С индексами пока не сталкивался, сейчас читаю документацию по ним. Завтра после тестов напишу, получилось что или нет.
17 авг 17, 19:35    [20732427]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
salex13,

и даты как-то странно задаёте. К примеру, '1.2.2017' - это первое февраля или второе января?
17 авг 17, 19:37    [20732437]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
salex13
С индексами пока не сталкивался, сейчас читаю документацию по ним.
Такими темпами Вы запрос не оптимизируете.
17 авг 17, 19:39    [20732440]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
iii2
Member

Откуда:
Сообщений: 202
salex13,
SELECT TOP 1 dbo.FloatTable.Val 
FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) 
WHERE dbo.TagTable.TagName LIKE '%200400.db_V_Tanks.V1%' AND DateAndTime >= '1.1.2017 0:00:00' AND DateAndTime < '1.2.2017 0:00:00' order by val desc


Предполагаю, что запросу не хватает индексов.
Создайте покрывающий индекс по DateAndTime include (TagName), если эти поля у вас в одной таблице, из текста запрса не видно.
18 авг 17, 08:17    [20733008]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
iii2
salex13,
SELECT TOP 1 dbo.FloatTable.Val 
FROM dbo.FloatTable join dbo.TagTable on (dbo.FloatTable.TagIndex = dbo.TagTable.TagIndex) 
WHERE dbo.TagTable.TagName LIKE '%200400.db_V_Tanks.V1%' AND DateAndTime >= '1.1.2017 0:00:00' AND DateAndTime < '1.2.2017 0:00:00' order by val desc


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

Дождаться Количества записей в таблицах, чтобы прикинуть, каким может быть оптимальный план запроса. Возможно, проще сначала искать фулсканом по LIKE во второй таблице, а потом поиском по индексу взять данные первой таблицы, объединив по JOIN MERGE. А возможно, сначала поиском по индексу первой таблицы, потом NESTED LOOP и просмотр второй таблицы по LIKE. Учитывая, что есть TOP 1, и нам достаточно найти первую попавшуюся запись. То есть, необязательно делать полные выборки из двух таблиц и мержить их, чтобы выбрать всего одну запись. Тут NESTED LOOP может быть полезнее, поскольку надо остановиться после первого же шага. Для этого надо в индексе предусмотреть order by val desc.

Посмотреть
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
для точных числовых характеристик. Подозреваю, что количество логических, да и физических чтений блоков там запредельное. И надо посмотреть план запроса.

Далее предлагать разные индексы, смотреть получающиеся планы запросов, оценивать результат по
SET STATISTICS TIME ON;
SET STATISTICS IO ON;


Надо с помощью индексов уменьшить количество логических чтений. Корректировкой размера памяти сервера уменьшить количество физических чтений.

Как-то так.

З.Ы. Насколько я понял, это серьёзная система автоматизации технологических процессов. Меня смущает MSSQL Express. Автор, эта штука случайно не управляет каким-то опасным химическим производством? Что за завод и в каком городе? Я бы противогаз заранее купил, если это под Питером.
18 авг 17, 08:49    [20733086]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Dmitry V. Liseev,

автор
Учитывая, что есть TOP 1, и нам достаточно найти первую попавшуюся запись

советы впечатляют
18 авг 17, 08:55    [20733111]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
Dmitry V. Liseev,

автор
Учитывая, что есть TOP 1, и нам достаточно найти первую попавшуюся запись

советы впечатляют
Что Вас смущает? Часто в таких случаях полезно смотреть, что в запросе указано в ORDER BY. И иметь соответственно отсортированный индекс. Иначе реально бывает, что обрабатываются лярды записей, скидываются в темповую базу, потом сортируются и вуаля, из лярдов получается всего одна.
18 авг 17, 10:44    [20733427]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Dmitry V. Liseev,

угу, первую попавшуюся... хватит пороть чушь, она и так тут рыдает
18 авг 17, 10:46    [20733431]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
Dmitry V. Liseev,

угу, первую попавшуюся... хватит пороть чушь, она и так тут рыдает
То есть, аргументированной позиции не будет? Будем гнать порожняк?
18 авг 17, 15:28    [20734559]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Поскольку автор всё равно пропал, попробую пованговать.
18 авг 17, 15:35    [20734580]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Сейчас план выглядит примерно так (на проценты внимания на обращайте):

К сообщению приложен файл. Размер - 24Kb
18 авг 17, 15:36    [20734588]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Это в предположении, что обе таблицы - кучи и нет индексов. Если оборудование тупо льёт данные, то самое быстрое - лить их в кучи. Т.е. предположение разумное.

Как работает план: фулскан FloatTable, потом сортировка по Val по убыванию, потом фильтрация по DateAndTime. Получаем столбцы TagIndex и Val. Всё это подаётся на Nested Loop. Который берёт сверху (они отсортированы по Val по убыванию) по одной записи (точнее, поле TagIndex) из полученной таблицы и лезет в TagTable с целью проверки, а не соответствует ли запись по указанному TagIndex выражению LIKE в поле TagName. Как только обнаружена "первая попавшаяся", то останавливаемся.
18 авг 17, 15:51    [20734680]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
o-o
Guest
какая ценная картинка.
плиз, не взыщите, я ее перетащу в перлы перевода на русский.
"верх" это ценно.
хотя лучше бы "вершок".
"вершки, корешки" и прочие русские народные сказки
18 авг 17, 15:53    [20734688]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Давайте поможем этому плану работать быстрее:

CREATE INDEX IX_FloatTable_ForTest_01 ON FloatTable
(
	Val DESC,
	DateAndTime
) INCLUDE(TagIndex);

CREATE INDEX IX_TagTable_ForTest_01 ON TagTable
(
	TagIndex
);


Заранее отсортируем по Val по убыванию, по времени (не важно, по убыванию или возрастанию, нам всё равно диапазон нужен) и включим в индекс интересующий нас TagIndex.

А для второй таблицы построим индекс по TagIndex. Включать ничего в него не будем, ибо предполагаю, что самое большое поле в таблице - TagName, а размер остальных незначителен. Потому включение поля в индекс только приведёт к тому, что индекс будет размером с таблицу и мы не уменьшим количество необходимых дисковых операций. Только зря потратим место. Включение полей в индекс обычно делается с целью получения маленького компактного индекса, который быстрее прочитать, чем лезть в таблицу за данными.
18 авг 17, 15:59    [20734720]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
o-o
какая ценная картинка.
плиз, не взыщите, я ее перетащу в перлы перевода на русский.
"верх" это ценно.
хотя лучше бы "вершок".
"вершки, корешки" и прочие русские народные сказки
Собака лает - караван идёт.
18 авг 17, 16:00    [20734721]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
В итоге получается примерно такой план:

К сообщению приложен файл. Размер - 21Kb
18 авг 17, 16:01    [20734725]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
То есть, от сортировок и обращения к таблице FloatTable мы избавились полностью. Индекс по таблице TagTable у меня не используется, потому что тестовый набор данных маленький. Серверу проще фулсканом всю таблицу зачитать. А делать таблицу на гигабайты мне лень.
18 авг 17, 16:04    [20734730]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36695
Dmitry V. Liseev,

В данном случае для приведенного запроса в зависимости от данных нужен такой набор индексов:

TagTable ( TagIndex )
FloatTable ( TagIndex, DateAndTime ) include ( TagName, Val )
или FloatTable ( DateAndTime ) include ( TagIndex, TagName, Val )

Включать в ключ индекса val нет вообще никакого смысла, ибо после фильтрации по диапазону DateAndTime все равно придется по нему пересортировывать.

Сообщение было отредактировано: 18 авг 17, 16:12
18 авг 17, 16:09    [20734744]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
o-o
Guest
Dmitry V. Liseev
Собака лает - караван идёт.

ну, лайте дальше, не буду вам мешать
18 авг 17, 16:11    [20734749]     Ответить | Цитировать Сообщить модератору
 Re: Медленный запрос из MS SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Гавриленко Сергей Алексеевич,

автор
Включать в ключ индекса val нет вообще никакого смысла, ибо после фильтрации по диапазону DateAndTime все равно придется по нему пересортировывать.

так он Val включил только для сортировки :) да ещё и первым... ну да ладно, пусть лает, караван пошёл :)
18 авг 17, 16:14    [20734758]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить