Is Passthrough Query - best way to call SQL Server Parameter Function?

[Rx_]

Note: I was not getting good results - Please see new post in DB Theory & Practice area
http://www.access-programmers.co.uk/forums/showthread.php?p=1283919#post1283919

See attathment. Created a Table valued function that allows a parameter. This returns a recordset to Access so some business logic can evaluate it to a True or False.
1. How do I call the vRule_001 (prefer DAO) and pass the parameter
2. How does the small Recordset get returned?

In Access, there is a function: Public Function R_001(ID_Well) As Boolean
Here is working code to call the SQLServer View R_01. But, it is slow.
Here is what I want to replace with the Table-Valued Function and the records it returns.
SQLExclude = "SELECT R_01.ID_Wells, R_01.State_Abrv from R_01 " & _
" WHERE (((R_01.ID_Wells)=" & ID_Well & ") AND ((R_01.State_Abrv) In('UT')));"
Set rstExclude = CurrentDb.OpenRecordset(SQLExclude, dbOpenForwardOnly)

The R_01 is a linked View in SQL Server. The Where clause is taking way too long to return data from a view.
The goal is to return the Table Value View and treat it like the rstExclude recordset inside my function.

The problem is that about 20 Functions like this one, must be run on about 10,000 rows. It is a Rule Based Report. Lots of processing. So Every milisecond counts.

I know that if I could write a Stored Procedure. A SP would all run faster. Problem is, it will take me more time to write SP.
Some of the logic is very involved. The Rule definition is a work in progress.
So, having the Rule Logic in VBA allows me to put off learning combinations of nested TSQL case statements (for now).
20 Columns of different rules x 10,000 rows is taking about 45 mintues to run.

So, my hope is that a Table-Valued Function in SQL Server and some type of Pass-through query will speed things up.

 

[Pat Hartman]

If the query is running slowly with criteria, perhaps you should add an index or two. I doubt that a function will help since without a usable index, SQL Server will still have to do a full table scan.

 

[Rx_]

I really appreciate all inputs. This is not the normal Access Question. It is a hybrid MS Access and SQL Server relationship.
The index is a primary autocounter key, clustered. I rebuilt the index. Darn, it actually ran slower. That might be because the SQL Server is re-set each night and I came in early to be the first to log in.

Here are some test on 1,000 rows (top query). The table is about 20,000 rows.
The simplest rule is shown (out of 4 rules)
Field in a Query: AS9: Has_NavigatorHeader([ID_Wells])
Has_NavigatiorHeader is a custom function

Public Function Has_NavigatorHeader(ID_Well) As Boolean ' Navagator Header created on Home must be completed
      ' Rule 9   Required - this should return True
      ' Rule Well Status - NavHeader Has the date
      Dim rstMisc                         As DAO.Recordset
      Dim rstExclude                      As DAO.Recordset ' excluded states
      Dim SQLMisc         As String  ' NOTE Added IP Date afterwards
      Dim SQLExclude      As String  ' use to exclude states
10      Has_NavigatorHeader = False  ' false until proven true
90      SQLMisc = "SELECT Wells.ID_Wells, Wells.DtProgSentToField, States.State_Abrv FROM Wells INNER JOIN States ON Wells.ID_State = States.ID_State " & _
        "WHERE (((Wells.ID_Wells = " & ID_Well & ")) AND ((Wells.DtNavigatorHeadersCreated) Is Not Null ));"
      '
100   Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
' Once record is returned - business rule evaluate and return a True or False

For 1,000 Records returned times 4 business rules
The above select style takes 70 Seconds
Using line 90 to create a View on SQL Server (with the joins) and then running a Access select statement against that SQL Server View (using the Where clause - using a Linked Table)
The 1,000 rows (times 4 rules) take 23 seconds
Of interest, a DBOpenForwardOnly against the same SQL Server View takes 21 seconds.

' Rule 9 callse SQL View R_09 - Tables pre-joined in SQL Server View
Public Function R_009(ID_Well) As Boolean ' Navagator Header created on Home must be completed
      ' Rule 9   Required - this should return True
      ' Rule Well Status - NavHeader Has the date
      Dim rstMisc                         As DAO.Recordset
      Dim rstExclude                      As DAO.Recordset ' excluded states
      Dim SQLMisc         As String  ' NOTE Added IP Date afterwards
      Dim SQLExclude      As String  ' use to exclude states
10      R_009 = False  ' false until proven true
90      SQLMisc = "SELECT R_01.ID_Wells, R_01.DtProgSentToField, R_01.State_Abrv FROM R_01  " & _
        "WHERE (((R_01.ID_Wells = " & ID_Well & ")) AND ((R_01.DtNavigatorHeadersCreated) Is Not Null ));"
      'Note: the SQL Server View (as linked table) used here is 3 times faster
100   Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenForwardOnly)
' same Business Rules Logic taken from here

According to several people on the SQL Server forums
A Table Valued Function with the parameter then run a select statement against it - will run in parallel to the next select request. Otherwise I am running each column, row sequentially.
They show that it is counter intuitive that more code runs faster, but this is the industry standard. SQL Server seems to handle this much better.

By running a Table-value Function, then running a Select Query against it, there are articles that show SQL Server will allocate more threads and drastically increase the speed.

Now, that said, none of the SQL Server advisers have ever touched MS Access. They are running the Select statement from scripts inside SQL Server. That is what the attachment shows on the first post.
Select * from vRule_0001(552)

Eventually, this Rule Engine will only run 1 single Well_ID.
But, the design tool must run not 4 rules x 1000, but 62 rules times 20,000.
Normally speed would not be such an issue.

Of course the other question that plagues me is: How much time is saved from the SQL Server vs my Business Rule logic constantly creating a recordsset, running various IF/Then or Case statements and destroying variables? Looking at my Task Manager - it doesn't appear to be all that busy.
If I was a verbose TSQL logic programmer, this would be moved to SQL Server. In the Compliance industry, one of these rules will change weekly.
Access is such a great Rapid Prototype and lends itself to managed updates.
So, I am trying to keep the detailed Business Logic in MS Access if possible.

 

[Pat Hartman]

"Access" definitely adds overhead but unless your query contains something such as VBA or a UDF that cannot be sent to the server, Access will pass-through the query to the server and only the requested rows will be returned.

Try pasting the SQL string in the query window of SSMS. How long does it take?

You can also turn on the profiler to view communication between Access and the server and that might give you some clue as to what is causing the problem if it turns out to be between Access and the server rather than at the server itself.

ID_Wells needs an index and probably state_abbr in both tables since you are joining on state. 70 seconds is beyond the pale for 20,000 rows. I have totals queries that run against 11 MILLION rows in only a 1-2 seconds. I have queries that select a half million rows that take less than 10 seconds to get all that data AND download it.