Professional Web Applications Themes

Calculations - VB to SQLServer plunge - Microsoft SQL / MS SQL Server

Using VB to perform functions that can be done in SQL is usually the slowest possible way to do it. As a rule of thumb, have the DBMS do as much of the work as practical/possible. Using Seek in VB indicates that you haven't used a well designed SQL statement to obtain the proper data (the data you need, and nothing more). I'm not sure what you think is convoluted or impossible in T-SQL. You wouldn't ONLY use T-SQL - your application would still be in VB. But T-SQL can easily calculate things like sums, averages, counts, minimums, maximums, etc. ...

  1. #1

    Default Re: Calculations - VB to SQLServer plunge

    Using VB to perform functions that can be done in SQL is usually the slowest
    possible way to do it. As a rule of thumb, have the DBMS do as much of the
    work as practical/possible. Using Seek in VB indicates that you haven't
    used a well designed SQL statement to obtain the proper data (the data you
    need, and nothing more).

    I'm not sure what you think is convoluted or impossible in T-SQL. You
    wouldn't ONLY use T-SQL - your application would still be in VB. But T-SQL
    can easily calculate things like sums, averages, counts, minimums, maximums,
    etc. (aggregate functions) across multiple rows. You can also do string
    manipulations and calculations involving multiple fields in a record.

    Your business logic and user interface would be done in VB.

    Cubes are for On-Line ytical Processing (OLAP), which is typically what
    you need in order to yze historical data from a combination of
    "dimensions". This is somewhat different from requesting information that
    satisfies different criteria - it basically means that you are looking for
    aggregates along different, individual dimensions, and want to
    cross-reference them. OLAP is the type of processing applied to Data
    Warehouses and Data Marts, usually not against on-line production data.

    MS Access doesn't have temporary tables. A Temporary Table in SQL Server is
    a table that is only available during the life of your database session, and
    only visible to your session. To create a temporary table, use a CREATE
    TABLE statement and prefix the table name with a # sign (CREATE TABLE #MyTmp
    (...)). SQL Server 2000 also implements table variables, in which case you
    use a DECLARE MyTblVar TABLE (...) statement. (Table variables are limited
    to the session and context in which they were created). In either case you
    can use it as if it was a table. You can use INSERT INTO #MyTmp (...)
    SELECT FROM ..., you can JOIN them with each other or with "real" tables and
    views, etc.

    If you could describe exactly what you are having trouble with or what your
    existing application does that you don't understand how to do, maybe I could
    be more specific.

    HTH,
    Tore.

    "russ" <com.au> wrote in message
    news:google.com... 
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconcreatingtestingobjects03.asp”, 


    Tore Guest

  2. #2

    Default Re: Calculations - VB to SQLServer plunge

    Thanks for your help so far Tore, it’s given me a clearer idea
    of where to start,

    I wanted to keep things general to guide my options and not bog anyone
    down in detail.

    Offshore platforms have components broken down into “Tags”
    in this case to monitor their maintenance history and thus attempt to
    balance “planned maintenance” against “corrective
    maintenance” (breakdowns) to optimise extremely high maintenance
    costs.

    This database extracts Tag maintenance history from SAP. ysis is
    based on the “time between maintenance”(TBM). Users ask
    questions like “What were the worst TBM actors by a particular
    grouping for a given data range?”

    Tags are organised by hierarchical finance and engineering methods
    (both different and unrelated)

    When a user asks “ How did Subsea Tags on asset A fare in the
    past year versus the past maintenance cycle of 6 years”, two
    types of data are required; population stats and sample stats.

    Population stats, gather all the planned and corrective maintenance
    “Work Orders” against a Tag. These are then sorted by
    data and the number of days between maintenance calculated. This can
    be done when the data is imported.

    Sample stats are provided at runtime and involve grabbing and sorting
    Work Orders for a Tag, calculating sample TBM, then sample mean etc,
    then writing these to each rows.

    With VBA, I create a Recordset and an array, calculate the stats I
    want, jump between rows as I please, with temporary variables and
    custom functions to help me out, write data to each row, dump the
    array and then move on to the next Tag’s data.

    I’m sure that TSQL is a more powerful tool to achieve this, but
    am worried about just where to start with these types of calculations.

    Below is a (tedious)code sample that calculates TBM and the meanTBM
    and populates them to every row returned for that Tag.


    Public Sub TBPMCalcs()
    'Public Function DaysBetweenMaintenanceCalcsUpdate2(strMaintenanceT ype
    As String, strDBMX As String)

    'NEED TO BE ABLE TO SET TYPE IN SQL - Only Does One Type At A Time
    'If Data Imported > Once - CALCULATIONS WRONG!
    'May Need To Run Update Query To Set Values To Null Before
    Populating With New Data
    '_________________________________________________ ___________________________
    'UPDATE QUERIES ASSUME ROWS ETC IDENTICAL - MUST HAVE EXCLUSIVE
    ACCESS!!!!!

    Dim db As Database
    Dim rst As Recordset
    Dim rsttbladmUpdate As Recordset
    Dim qdf As QueryDef
    Dim lngCounter As Long
    Dim intMean As Integer
    Dim dtm1 As Date
    Dim dtm2 As Date
    Dim byt1 As Byte
    'Boolean For Adding Most Current Date
    Dim intTBM As Integer 'intTBM
    (Mean) Is Integer = NO DECIMALS!
    Dim lngSumTBM As Long
    Dim str1 As String
    '---------------------------------------------------------
    'Array Section
    Dim lngMainArray() As Long
    Dim lngtblEquipmentArray() As Long 'Data Type For 2nd
    Array Type Is Incorrect !
    Dim lngRow As Long

    Set db = CurrentDb()
    Set qdf = db.CreateQueryDef("", "SELECT tblWorkOrderVariable.UID,
    tblEquipment.EquipmentID, tblWorkOrderStatic.Type,
    tblWorkOrderStatic.CompleteDate, tblWorkOrderVariable.TBPM,
    tblEquipment.MeanPM FROM tblWorkOrderStatic INNER JOIN (tblEquipment
    INNER JOIN tblWorkOrderVariable ON tblEquipment.EquipmentID =
    tblWorkOrderVariable.EquipmentID) ON tblWorkOrderStatic.WkOrder =
    tblWorkOrderVariable.WkOrder WHERE
    (((tblWorkOrderStatic.Type)='PMRC')) ORDER BY
    tblEquipment.EquipmentID, tblWorkOrderStatic.Type,
    tblWorkOrderStatic.CompleteDate;")
    Set rst = qdf.OpenRecordset

    byt1 = 1

    With rst
    lngRows = .RecordCount
    '************************************************* ************************************************** *************
    'Array Section
    ReDim lngMainArray(0 To lngRows, 1)
    ReDim lngtblEquipmentArray(0 To lngRows, 1)

    '************************************************* ************************************************** *************
    'If There Are Any Records Move To The First
    If .RecordCount > 0 Then
    .MoveFirst
    lngRow = 0

    Line1: If lngRow <> 0 Then
    'Must Loop Back Here To Calculate Statistics
    intMean = MeanCalc(lngSumTBM, lngCounter - 1)
    ' -1 Used To Remove First Zero From Mean Calculations
    .MovePrevious
    .Edit
    ![MeanPM] = intMean
    .Update
    .MoveNext
    If lngRow = .RecordCount Then
    GoTo Line2
    End If
    End If

    str1 = ![EquipmentID]
    dtm1 = ![CompleteDate]
    dtm2 = ![CompleteDate]
    lngCounter = 0
    lngSumTBM = 0

    Do Until .EOF
    'If EquipmentID = To Previous (Or First)
    Value, Continue Calculations For This Piece Of Equipment"
    If ![EquipmentID] = str1 Then
    If byt1 = 1 Then
    byt1 = 0
    dtm1 = ![CompleteDate]
    intTBM = DateDiff("d", dtm2, dtm1)
    .Edit
    ![TBPM] = intTBM
    .Update
    '******************
    ' "lngMainArray" Records TBM Data To
    Be Updated Back To Query Field TBxM
    '"lngtblEquipmentArray" Records TBM
    Data For This EquipmentID And Then Will Update "tblEquipment"
    ' With Statistical Calculations
    Performed On These Values
    ' "lngCounter" Is A Line Counter For
    "lngtblEquipmentArray"
    lngCounter = lngCounter + 1
    lngMainArray(lngRow, 0) = lngCounter
    lngMainArray(lngRow, 1) = intTBM
    '******************
    ElseIf byt1 = 0 Then
    byt1 = 1
    dtm2 = ![CompleteDate]
    intTBM = DateDiff("d", dtm1, dtm2)
    .Edit
    ![TBPM] = intTBM
    .Update
    '******************
    lngCounter = lngCounter + 1
    lngMainArray(lngRow, 0) = lngCounter
    lngMainArray(lngRow, 1) = intTBM
    '******************
    End If
    Else:

    GoTo Line1 'If A New Piece Of Equipment, Then
    Reset EquipmentID And Completion Dates
    End If
    lngSumTBM = lngSumTBM + intTBM
    'Debug.Print "Row Number: " & lngRow & Chr(9)
    & " Inner Loop: " & lngCounter & Chr(9) & " TBM: " & intTBM & Chr(9) &
    " Sum: " & lngSumTBM
    lngRow = lngRow + 1
    'If lngRow > lngRows Then Exit Do
    If lngRow = lngRows Then Exit Do
    .MoveNext
    Loop
    Else: MsgBox strNoRecords
    Exit Sub
    End If

    'This Is Used To Calculate The Mean Of The Last Row In The
    Recordset
    intMean = lngSumTBM \ lngCounter 'Should Do Better
    Than Repeat This Code
    'Debug.Print "Mean Is: "; intMean
    .Edit
    ![MeanPM] = intMean
    .Update

    Line2: .MoveFirst
    lngRow = 0

    End With

    Set rst = Nothing

    '************************************************* ************************************************** *************
    'Record When TBPMRC And TBPMRC Population Mean

    Set rsttbladmUpdate = db.OpenRecordset("tbladmUpdate")

    With rsttbladmUpdate

    .AddNew
    ![TableName] = "tblWorkOrderVariable"
    ![UpdateType] = "TBPMUpdate"
    ![UpdateDate] = Now()
    .Update

    End With

    Set rsttbladmUpdate = Nothing

    End Sub







    Public Function MeanCalc(intSum As Long, intCount As Long) As Long

    If intCount = 0 Then

    intCount = 1
    End If

    MeanCalc = intSum / intCount

    End Function
    russ Guest

  3. #3

    Default Re: Calculations - VB to SQLServer plunge

    This does indeed sound like an OLAP type application, so looking at Data
    Warehousing (or cubes as you mentioned) may indeed be a good approach. That
    may require some aggregation to be done up front. I'm not an expert in this
    area, but it sounds like you may possibly have three or four dimensions to
    the particular (sample) question you mentioned - TBM, actors, "grouping",
    date.

    Of course, to get started, you could continue to use the same logic you used
    with an MS Access database.

    HTH,
    Tore.

    "russ" <com.au> wrote in message
    news:google.com... 
    '_________________________________________________ __________________________

    '************************************************* **************************
    ************************************* 
    '************************************************* **************************
    ************************************* 
    '************************************************* **************************
    ************************************* 


    Tore Guest

Similar Threads

  1. [Macromedia][SQLServer JDBC Driver][SQLServer]Internal
    By gsmadman in forum Coldfusion Database Access
    Replies: 1
    Last Post: February 9th, 04:53 AM
  2. Calculations with date
    By Danield in forum PERL Beginners
    Replies: 3
    Last Post: January 19th, 12:00 AM
  3. date calculations
    By Fredg in forum Microsoft Access
    Replies: 4
    Last Post: July 25th, 02:53 PM
  4. Finally took digital plunge
    By Gorham in forum Photography
    Replies: 23
    Last Post: July 19th, 02:45 AM
  5. Replies: 5
    Last Post: July 6th, 10:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139