Rx_
Nothing In Moderation
- Local time
- Today, 01:38
- Joined
- Oct 22, 2009
- Messages
- 2,803
2 Part question based on reading and analysis this weekend.
The Rule Engine has around 100 rules in a framework. It works, but takes too long. Currently, I am in the process of re-design.
So, Saturday, I had the SQL Server all to myself.
The new design consist of a base table with two outer joins to two other tables. It returns 85,000 records needed for the next step.
up until now, a discrete rule passed in SQL statements to a set of Linked Tables (on SQL Server). A discrete rules always returns a T/F. Example:
The Setup:
The new Outer Join is a pure SQL statement. It took exactly 10 seconds for Access to return 85,000 records.
Opening up SQL Management Studio and pasting the Access SQL statement in a new query and running it, it took exactly 10 seconds to run.
Changed the sort order on both Access and SQL Server queries, they both took exactly the same amount of time.
Reading several articles on SQL Server Central, the foreign keys(FK) for the outer joined tables were Non-Clustered (per MS standard) on that index. Changed both of them to Clustered Index. Rebuilt queries.
Re-run both Access SQL and SQL Server SQL - both still took exactly 10 seconds.
The articles in SQL Server Central argued both cases. Bottom Line - try it.
In my case with a Database under 1 GB, it didn't make a difference.
Leaving both the FK as Clustered.
In SQL Server, the exact SQL statement was used to create a View.
On Access, the new View was linked (as a linked table) as vRE_1Seg.
On SQL Server, created a new Query wiht vRE_1Seg. On Access, created a query with vRE_1Seg.
Both of these queries ran in under 1 second (both Access and SSMS query). They returned the same 85,000 records much faster (about 10 times faster).
Typically, the preference has been to use the
SQLMisc = "SELECT Wells_SHLBHL.ID_Wells, Wells_SHLBHL.SHLBHL FROM Wells_SHLBHL WHERE (((Wells_SHLBHL.ID_Wells)=" & ID_Well & "));"
format becaue it is easier to maintain, search and troubleshoot.
However, if the query windows from Access and SQL SSMS Query are an indicator, this might not be a preference.
The preference would be to query VRE_1Seg with a filter (where clause).
The where clause brings back between 0 and 20 records in miliseconds.
A few miliseconds might not seem like a lot, but for each record (ID_Well), there are the equal of 5 VRE_1Seg with about 100 functions running for each record. That really starts adding up.
Does anyone else have observations about using SQL statements vs Views or with Clustered Index? For speedy repetitive used functions, is passing a SQL string really this much slower?
The Rule Engine has around 100 rules in a framework. It works, but takes too long. Currently, I am in the process of re-design.
So, Saturday, I had the SQL Server all to myself.
The new design consist of a base table with two outer joins to two other tables. It returns 85,000 records needed for the next step.
up until now, a discrete rule passed in SQL statements to a set of Linked Tables (on SQL Server). A discrete rules always returns a T/F. Example:
Code:
Public Function Has_SHLorBHL(ID_Well) As Boolean ' Navagator Header created on Home must be completed
' Rule 11
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_SHLorBHL = False ' false until proven true
90 SQLMisc = "SELECT Wells_SHLBHL.ID_Wells, Wells_SHLBHL.SHLBHL FROM Wells_SHLBHL WHERE (((Wells_SHLBHL.ID_Wells)=" & ID_Well & "));"
'
100 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
110 On Error Resume Next
120 rstMisc.MoveLast ' should only be one in this construct, just in case there are two
130 If rstMisc.RecordCount > 0 Then
140 Has_SHLorBHL = True
150 Else
160 Has_SHLorBHL = False
170 End If
180 If Err.Number <> 0 Then
190 Err.Clear
200 Exit Function
210 End If
End FunctionThe new Outer Join is a pure SQL statement. It took exactly 10 seconds for Access to return 85,000 records.
Opening up SQL Management Studio and pasting the Access SQL statement in a new query and running it, it took exactly 10 seconds to run.
Changed the sort order on both Access and SQL Server queries, they both took exactly the same amount of time.
Reading several articles on SQL Server Central, the foreign keys(FK) for the outer joined tables were Non-Clustered (per MS standard) on that index. Changed both of them to Clustered Index. Rebuilt queries.
Re-run both Access SQL and SQL Server SQL - both still took exactly 10 seconds.
The articles in SQL Server Central argued both cases. Bottom Line - try it.
In my case with a Database under 1 GB, it didn't make a difference.
Leaving both the FK as Clustered.
In SQL Server, the exact SQL statement was used to create a View.
On Access, the new View was linked (as a linked table) as vRE_1Seg.
On SQL Server, created a new Query wiht vRE_1Seg. On Access, created a query with vRE_1Seg.
Both of these queries ran in under 1 second (both Access and SSMS query). They returned the same 85,000 records much faster (about 10 times faster).
Typically, the preference has been to use the
SQLMisc = "SELECT Wells_SHLBHL.ID_Wells, Wells_SHLBHL.SHLBHL FROM Wells_SHLBHL WHERE (((Wells_SHLBHL.ID_Wells)=" & ID_Well & "));"
format becaue it is easier to maintain, search and troubleshoot.
However, if the query windows from Access and SQL SSMS Query are an indicator, this might not be a preference.
The preference would be to query VRE_1Seg with a filter (where clause).
The where clause brings back between 0 and 20 records in miliseconds.
A few miliseconds might not seem like a lot, but for each record (ID_Well), there are the equal of 5 VRE_1Seg with about 100 functions running for each record. That really starts adding up.
Does anyone else have observations about using SQL statements vs Views or with Clustered Index? For speedy repetitive used functions, is passing a SQL string really this much slower?
