June 20, 2013 at 3:34 pm
An Access Programmer - with Linked SQL Tables - Any ideas on the best way to approach this design would be welcome?
Here is a simple function that returns a True/False written in MS Access.
It is part (1 of 180) of a Rules Engine. In a MS Access Query for example, the query can call
Column1 [ID_Building] Comun2 StakingStatus: Building_Status_Staking_Does_Building_FieldWorkDate_Land([ID_Building])
The output would be something like 343434 True
For small pulls of a dozen records, it cost a few mili-seconds. The SQL Server Native Client actually does convert it to some sloppy T-SQL.
The problem is - I will soon have about 120 columns with more complex rules.
Times around 100 to 500 concurrent users.
Should I use a Stored Procedure, a TSQL pass-through query, or other method?
This is a Rules Engine under development. The rules are run when one of many key fields across many forms are changed.
Each user passes in a single [ID_Building] for a session that has around 30 forms (not all open of course) If they change one value, the rules engine evaluates all of the rules and updates a dashboard that needs to give the appearance of "real-time". (e.g. within 1 to 10 seconds).
Think about a tax form: change a dependent from 1 to 2, you are a homeowner, 2 incomes, with rental income, .... - the change from 1 to 2 activates a Dashboard that "recommends you file separately"
Public Function Building_Status_Staking_Does_Building_FieldWorkDate_Land(ID_Building) As Boolean
' Rule Building Status - Staking Staking Status Can NOT have an a Field Work Date of type LAND
Dim rstMisc As DAO.Recordset
Dim SQLMisc As String ' NOTE Added IP Date afterwards
10 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False ' set function to false until proven true
SQLMisc = "SELECT APD_FieldWorkDate_2.ID_Buildings, APD_FieldWorkDate_2.ID_Bio_Svy_Type FROM APD_FieldWorkDate_2 " & _
"WHERE (((APD_FieldWorkDate_2.ID_Buildings)=" & ID_Building & ") AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type) In (15,18)));"
30 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
40 On Error Resume Next
50 rstMisc.MoveLast
60 If rstMisc.RecordCount > 0 Then
70 Building_Status_Staking_Does_Building_FieldWorkDate_Land = True
80 Else
90 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False
100 End If
110 If Err.Number <> 0 Then
120 Err.Clear
130 Exit Function
140 End If
' A Case statement would be OK too.
End Function
June 21, 2013 at 9:15 am
Pretty tough to tell what you are trying to do here but it seems like all the VBA is doing is running a query and seeing how many rows it has? You could turn this into an iTVF pretty easily like this:
create function Building_Status_Staking_Does_Building_FieldWorkDate_Land
(
@ID_Building int --Not sure of the datatype here???
)
returns table
return
select cast(COUNT(*) as bit) as MyResult
FROM APD_FieldWorkDate_2 fwd
WHERE fwd.ID_Buildings = @ID_Building
AND fwd.ID_Bio_Svy_Type In (15, 18)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 9:32 am
Yes it is an integer.
What I am attempting to return is a True / False (meta data) - not the actual records.
THANKS!! A function - why didn't I think of that?
Have not thought of a function, that is a great solution that matches my Access side.
Next question: Can a function be called directly in a TSQL Query?
My goal is to have many functions that return a True/False.
They all use the identical ID_Building as the argument.
The idea is to receive a single record with the ten columns of True/False
e.g. 29992 T T T F F F T T F T
Each of these function results feed a Dashboard about regulatory data in the user interface.
Can SQL functions be called directly in a SQL TSQL query statement?
A simple example would probably get me up and running.
June 21, 2013 at 9:55 am
Mile Higher Than Sea Level (6/21/2013)
Yes it is an integer.What I am attempting to return is a True / False (meta data) - not the actual records.
THANKS!! A function - why didn't I think of that?
Have not thought of a function, that is a great solution that matches my Access side.
Next question: Can a function be called directly in a TSQL Query?
My goal is to have many functions that return a True/False.
They all use the identical ID_Building as the argument.
The idea is to receive a single record with the ten columns of True/False
e.g. 29992 T T T F F F T T F T
Each of these function results feed a Dashboard about regulatory data in the user interface.
Can SQL functions be called directly in a SQL TSQL query statement?
A simple example would probably get me up and running.
It is important to understand that what I put together is not a scalar function. It is an inline table valued function. That means you can join to it just like a table. You would have to join to it multiple times in your query.
You might want to check BOL for table valued functions. It will provide far better details than I can rattle off.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 10:52 am
Thanks again! I was on the phone talking to my friend, a very senior SQL programmer who was waiting for his flight.
Was reading the article above and coming to the conclusion.
He told me the evils of scalar - per my question. Wow, what a difference.
I said "Now I understand why the SQL ServerCentral post was pointing me into the right direction.
It totally makes sense now. I will read your articles over the weekend.
This is very exciting. Will do my homework and start on this.
If there are any tools that would help a Newbie, please let me know.
Thank you again for your time and help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply