Does T-SQL support some type of loop that can reference one Field at a time out of a single record - converting VBA to TSQ
-
Currently, I have been coding in Access VBA to get design approval, then converting the code to TSQL for a Rule Engine.
Thanks to this site, I have converted over 40 functions from VBA to TSQL. This process is challenging me.
This process pulls one record at a time. The WellsRecordResult Record is the result of a group of functions in a rule engine that return a True/False. A Status ID is used to determine what record to use on the 2nd table. The process looks up the 2nd table record Matrix Key (the expected values). Then field-by-field, they are compared to output the output.
1. The single WellsRecordResult record includes a Well ID (Primary key), a Status ID, and dozens of T/F fields with either True or False (no nulls).
On the image below, that would be a single record shown on the top. The Status is #13.
2 There is a Key Matrix table that defines the Expectation for each Status. Think of it as a grading key. It contains T, F, or N.
T means that True was expected for Status 13 in some fields, F means False was expected. N means either one was expected.
On the image below, one Key Matrix record (for 13) is shown. There are actually a dozen Key Matrix Status.
3. There is a VBA function that I would like to rewrite into TSQL.
The function takes #1 above, uses the Status ID, then evaluates the #1 Actual field with the #2 Expected Key and the function returns True or False for Each Field.
In the image, the result is on the bottom is shown in Excel. The resulted True is green, the resulted is Red.
A Row that is Green all the way across means that the record for that Status ID passes all the expectations.
In VBA it was easy to reference #1 and #2 record's field by its numeric location. Put the values through a Case Statement and harvest the answer for that field. Then just use a loop to increment the number and get the value for the next field in the record.
I can 't find any example like this in TSQL. Is there a process equal to the For Next loop that can reference the value of each column in a single row?
For MatrixKeyLoopCounter = 1 To MatrixKeyTableRecordCountStatusPassedAllRules = False ' Set to false this must be true by end of all columns
ID_StatusofMatrix = objRS2.Fields("ID_WellsStatus1").Value ' get Matrix key Status to check actual data against
'*** Start Loop for each Column in both Recordsets and evaluate with Case statement
For MatrixRuleColumn = 4 To 24 ' Read each objRS2 rules column '
MatrixKeyColumnName = objRS2.Fields(MatrixRuleColumn).Name
MatrixKeyValue = objRS2.Fields(MatrixRuleColumn).Value
WellsRuleColumnName = objRS.Fields(MatrixRuleColumn).Name
ID_Wells_Record_Result = objRS.Fields(MatrixRuleColumn).Value ' Increment field 4 to 24 columns per row
Select Case MatrixKeyValue ' looked up the Status - now look at the column expected value for the data in Excel for that column
' N is Neither True or False - so either value in Excel passes for this profile key
Case "N"
FinalAnswer = -1
StatusPassedAllRules = True ' one Rule column passed
Case "T"
If ID_Wells_Record_Result = True Then
' T matches with True - turn background Green
FinalAnswer = -1
StatusPassedAllRules = True ' one Rule column passed
Else
' T expected true - turn background Red
FinalAnswer = 0
StatusPassedAllRules = False ' one Rule column failed
Exit For ' one rule failed for this Status ID, stop and go check the next one
End If
Case "F"
If ID_Wells_Record_Result = False Then
' F matches with False - this passes expectations turn background Green
FinalAnswer = -1
StatusPassedAllRules = True ' one Rule column passed
Else
FinalAnswer = 0
StatusPassedAllRules = False ' one Rule column failed
Exit For ' one rule failed for this Status ID, stop and go check the next one
End If
End Select
.... rest of function
This is a graphical representation of the process
-
You can absolutely loop through rows and columns like a matrix in T-SQL. If you have any significant number of rows to do this with then make sure you have something else to entertain yourself with while this is happening cause T-SQL is without fail the most miserably slow interpreter in existance today bar none!
edit/ just a disclaimer here, if you only have a few rows or a small amount of data then you can certainly treat T-SQL as a regular procedural programming language, but any significant increase in the bulk amount of rows you're dealing with and you'll be hating it!
/edit
You get your best speed out of T-SQL if you make sure each programming statement works on as many rows as possible, you'll see this referred to as "set oriented" programming. If you can work on one column of many rows during each iteration, your code might not be terribly slow, I haven't really looked very deeply into your code, but I've looped through columns and as long as I did many rows at once for each column iteration, the execution was tolerable.
You'll be much happier if you keep this in VBA (or maybe move to .NET), and use SQL as your row or dataset store. VBA doesn't seem to have nearly the interpretive penalty that T-SQL does, and of course .NET is excellent for this sort of stuff. With T-SQL and this sort of stuff you're wanting to do, T-SQL is just going to get in the way unless you can transform your algorithm into something more closely aligning with how T-SQL works best.
Just an opinion after a very cursory look at your post, but I could be completely off base here so don't take my post as the last word :hehe::hehe::hehe:
-
patrickmcginnis59 10839 (2/10/2015)
...You get your best speed out of T-SQL if you make sure each programming statement works on as many rows as possible...
I would say "that depends." But the rest of the advice from Patrick is sound.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply