is there more efficient way to use multiple Likes? (1 Viewer)

[Rx_]

A facility name generally ends with WSW, SWD, or WIW
By generally, there can be spaces or an extra character following it. It is a Proper Names might have these three letters as part of the name.

Taking the LEFT 6 of the full name of the Facility field, could end up with WSW, or WSW , or WSW X, or WSW 1 for example.

the goal is a purse SQL statement that can eventually move over to TSQL. Just wondering if there is a fancy combination of the IN clause that can use wildcards?

' this doesn't work with a wild card
SELECT Facility.ID_Facility, Wells.WDesc, Right([Facility_Name],6) AS RightFacilityName
FROM Facility
WHERE (((Facility.ID_Facility)=<variable>) AND ((Right([RightFacilityName],5)) In ('wsw','swd','wiw'))); (can't do wild card here)

VS ' this works
SELECT Facility.ID_Wells, Wells.WDesc, Right([Facility_Name],6) AS RightWellName
FROM Wells
WHERE ((((Facility.ID_Facility)=<variable>) AND Right([Facilityl_Name],6)) Like "*WSW*" Or (Right([Facility_Name],6)) Like "*WIW*" Or (Right([Facility_Name],6)) Like "*SWD*"));

This is part of a rules engine that needs to run very efficiently.
The Or statements have me concerned that it could take several times longer.

 

[BlueIshDan]

Would passing the value to a function take longer or shorten it ?
(disregarding the efficiency of the code)

 

[Rx_]

It isn't a query, there are 100 rules like this one that run on one ID_Facility at at time. I run this from Access query now for rapid prototype to validate the rule engine. After that, this code will be moved to T-SQL in a function. However, it still requires a query component.
The maintenance where there are multiple OR always worries me since rules change over time. In(.,.,.) don't worry me as much. But, they don't seem to support wild-cards. Just wondering if there is a SQL statement that combines the IN(*SWS*, *WIW*) type of thing for wild cards.

Here is an example of an extremely simple discrete rule.
Run about 100 selected rules against a single facility, then a 2nd tier rule-engine takes all of the true/false reslults and makes a work-flow-progress status evaluation. This all works real-time during data entry in a multi-user environment.
So, each column is a rule. The prototype, evaluation, and QA is in Access. The rules will eventually move over to SQL Server. The rules work real-time in an Access front-end.

' Rule 711 Rule711  Well is facility - has WSW, SWD, WIW
Function Rule711(ID_Wells As Integer) As Boolean
      ' Rule 711  Well is a Facility - Wells Greater than Drilling don't apply to these facilities evaluation
      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
      Dim RuleResult As Integer
10    On Error GoTo errTrap
20        Rule711 = False  ' false until proven true
30        SQLMisc = "SELECT Wells.ID_Wells, Wells.WDesc FROM Wells WHERE (((Wells.ID_Wells)=" & ID_Wells & ") AND ((Wells.WDesc) In ('wsw','swd','wiw')));"
40        Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)
50             If Not (rstMisc.EOF And rstMisc.BOF) Then rstMisc.MoveLast    ' should only be one in this construct, just in case there are two
60              If rstMisc.RecordCount > 0 Then
70                  Rule711 = True
80              Else
90                  Rule711 = False
100             End If
110   Exit Function
errTrap:
120             If Err.Number <> 0 Then
130               Debug.Print "Function Rule71 has problem with well " & ID_Wells
140                 Err.Clear
150             End If
End Function

 

[Rx_]

Well, the SQL translation of multiple Likes with wildcards looked ugly and non-efficient. My connection is SQL Server Native Client 11, and ODBC driver.

After searching, this article (about half way down) was interesting:
http://www.codemag.com/Article/0712172
For example: "At the time of its release, experts at Microsoft believed OLE DB would supersede ODBC. This is no longer the case …"
There are some great links under that with more on ODBC.

My conclusion is that a pure Access SQL statement will be tranlated into a efficient TSQL executable by ODBC. This means that absolutely no Access or custom Functions can be used!

 

[namliam]

well you can use access functions, but if they cant be translated they wil be omitted from the sql in the server and only be actioned once the data hits Access.
Which simply means you may (or will) pull more records from the SQL database to validate them in Access, not the most efficient but it works.

Your brackets in the second where clause are going to break your sql statement since they are uneven between ( and ) and in the wrong places.
My guess its sloppy copy paste work, but if you would want a working version (without the extra brackets access always intents on creating, it would be something like:

WHERE 
      Facility.ID_Facility=<variable>
  AND  ( Right([Facility_Name],6) Like "*WSW*" 
      Or Right([Facility_Name],6) Like "*WIW*" 
      Or Right([Facility_Name],6) Like "*SWD*")

Would also see silly to actually search for an ID <variable> and have it return only if it is like ....

You can also use the instr function, add the different instr together if that yields > 0 one of the values has been found, but that is more clunky (at least in my oppinion).
I personaly dont think the OR like will hurt more than a single like.

If you want to improve this performance wize, there is a way.... since you have 5 chars which need to hold a 3 letter 'word' you can create 3 indexes on your column that "cut" the 3 letter 'words' from your Facility_name and search on IN .... but on 3 indexes...

   Left(right(facility,5),3) in ( )
OR Left(right(facility,4),3) in ( )
OR Left(right(facility,3),3) in ( )

Not sure if query wize that would be that much more readable but performance wize, particular with INDEXES added to this on table level.... it should speed it up.

NOTE: Offcourse on the last 3 characters only the right(,3) should suffice but kept it in one syntax

As for your Rule711, Using such a function to (refetch) the well record can cause considerable drag on your process... Instead cant you either send the Well Description into a function or even do it in a simple function like so:

Instr(right(Wells.WDesc,5), "wsw") +Instr(right(Wells.WDesc,5), "swd") + Instr(right(Wells.WDesc,5), "wiw") > 0

That should relatively simply return if its a well or not.