• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Optimizing Record Set reading

Started by Marty Francom, April 09, 2010, 02:01:35 PM

Previous topic - Next topic

Marty Francom

     I am doing some testing in remote mode rather than in local mode. And comparing the speed.  What I have found so far is that record look ups for in a single tables is almost as fast as in local mode.
    But the look up of the record set for an Rx takes about 4 seconds with Remote Mode and is almost instantaneous in local mode.  Quite a noticeable delay.   
    If I have a small LAN setup 3 or so computers,  and one of the computers is being used as a "server"  can the program on the "server" be attached to the DB in "local mode" while the "workstation" computers be attached to the DB in "remote mode" ?   

    Is it possible to read data from several different tables all in a single call to the SQL-DB ?

   Here's the code that I use to get the Rx Set of records.   The GetRxRecords Function gets one record from each of the many TABLES and puts the record in a global.  All the fields being used are indexed fields.   The GetRecord Function actually gets the individual record requested.  The GetAllRfRecsFor Rx Function gets all of the RfRecords linked to the specific Rx Record.
    Is it possible to combine all the individual calls to get each record from each of the tablets into a single call to SQLitening?    My hope is that this would significantly increase the speed of getting the Rx Set of records.   
...Marty




'--------------------------------------------------------------
Function GetRxRecords (RxN As String) As Long

    Local SQL1 As String
    Local sFld As String
    Local direction As String
    Local sTxt As String
    Local dtaRec As String
    Local c As Long
    Local i As Long 
    Local res As Long 
    Local lErr     As String
     
    ClearRxForm      'Clears form and all Rx globals
      '? "Step1 Rx"
      'res = RxLock (RxN)         'Set a Lock on that Rx Number
       res =  EditLock (RxN , "RxInUse", 0 )
      If res <> 0 Then
          If res = -1 Then Function = -1 : Exit Function
          MsgBox "Sorry Rx Record In Use" & $CrLf & _
                 "    Try again Later", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
          Function = -1
          Exit Function       
      End If           
      ' ? "End Step1" 
      gCurRxRxRec = GetRecord (RxN, "RxNum", "RxRecs", 1)
      If Len(gCurRxRxRec) < 1  Then
          MsgBox "Sorry Rx Record Not Found" & $CrLf & _
                 "    Try Again ", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
          res =  EditLock (RxN , "RxInUse", 1 )       
          Function = -1   
          Exit Function       
     
      End If   
      ' ? "step2 Rf"
       gCurRxRfRec = GetAllRfRecsForRx (RxN)
      If Len(gCurRxRfRec) < 1  Then
          MsgBox "Sorry Rf Record Not Found" & $CrLf & _
                 "    Database Error ", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
          Function = - 1   
          Exit Function       
      End If   
       
       'Get Doctor Record
       '   ? "step3 Dr"
       sTxt = Parse$(gCurRxRxRec, Chr$(127), 6)   
       gCurRxDrRec = GetRecord (sTxt, "DrNum", "Doctors", 1)   
      If Len(gCurRxDrRec) < 1  Then
          MsgBox "Sorry Dr Record Not Found" & $CrLf & _
                 "    Database Error ", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
      End If   
       
       'Get Drug Record
       '   ? "step4 Dg"
       sTxt = Parse$(gCurRxRxRec, Chr$(127), 7)   
       gCurRxDgRec = GetRecord (sTxt, "DgNum", "Drugs", 1)   
      If Len(gCurRxDgRec) < 1  Then
          MsgBox "Sorry Dr Record Not Found" & $CrLf & _
                 "    Database Error ", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
      End If   

       'Get Patient Record
        '  ? "step5 Pt"
       sTxt = Parse$(gCurRxRxRec, Chr$(127), 5)   
       gCurRxPtRec = GetRecord (sTxt, "PtNum", "Patients", 1)   
      If Len(gCurRxPtRec) < 1  Then
          MsgBox "Sorry Pt Record Not Found" & $CrLf & _
                 "    Database Error ", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
      End If   
       
       'Get Patient Insurance1 Record
       '  ? "step5a PtIns1"       
       sTxt = Parse$(gCurRxPtRec, Chr$(127), 32) 
       If Val(sTxt) > 1 Then           ' val (sTxt) <= "0"   'cash plan
          gCurRxPtIns1Rec = GetRecord (sTxt, "InsNum", "Insurances", 1)   
          If Len(gCurRxPtIns1Rec) < 1  Then
             MsgBox "Sorry Pt Insurance Not Found" & $CrLf & _
                    " Add Insurance to Patient", _
                    %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                    "WARNING"
          End If   
      End If
         
       'Get Patient Insurance2 Record
       '   ? "step5b PtIns2"         
      sTxt = Parse$(gCurRxPtRec, Chr$(127), 44)
      If Val(sTxt) > 1 Then                   'sTxt = "0"   'cash plan
         gCurRxPtIns2Rec = GetRecord (sTxt, "InsNum", "Insurances", 1)   
         If Len(gCurRxPtIns2Rec) < 1  Then
            MsgBox "Sorry Pt Insurance Not Found" & $CrLf & _
                   " Add Insurance to Patient", _
                   %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                   "WARNING"
         End If   
     End If
                           
       'Get Sig Record
       '  ? "step6 Sg"
       sTxt = Parse$(gCurRxRxRec, Chr$(127), 8)   
       gCurRxSgRec = GetRecord (sTxt, "SgNum", "SigRecs", 1)
      If Len(gCurRxSgRec) < 1  Then
          MsgBox "Sorry Sg Record Not Found" & $CrLf & _
                 " You need to fix this.", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
      End If   

       'Get Price Table Record
      ' ? "step6 Prc"
       sTxt = Parse$(gCurRxRxRec, Chr$(127), 9)   
       gCurRxPrcRec = GetRecord (sTxt, "PrcNum", "PriceTbls", 1)
      If Len(gCurRxPrcRec) < 1  Then
          MsgBox "Sorry Pricing Record Not Found" & $CrLf & _
                 " You need to fix this.", _
                 %MB_OK Or %MB_TASKMODAL Or %MB_ICONERROR, _
                 "WARNING"
      End If
       '? "Step7 End of get records" 
      Function = 0
     
End Function

Function GetRecord (sTxt As String, sFld As String, sTbl As String, sTyp As Long) As String

          Local SQL1 As String
          Local direction As String
          Local dtaRec As String
          Local c As Long
          Local i As Long 
          Local res As Long 
          Local lErr     As String   
           slCloseSet         
          dtaRec = ""
          direction = "="
          If sTyp = 0 Then   ' Character search
             SQL1 = "SELECT RowID, * FROM " & sTbl & " WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]"
          Else   
             SQL1 = "SELECT RowID, * FROM " & sTbl & " WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]"
          End If     
          res = slSel (Sql1,0,"E1") 
          lErr = slGetError
          '? "step1"
          If res <> 0 Then
             MsgBox lErr & Str$(res) & "-Getting " & sTbl & " Record", ,"Error"
             Function = ""
             slCloseSet : Exit Function   
          End If 
          res = slGetRow
          If res = 0 Then
          '  ? "Rec= " & sTxt & " Not Found" & $CrLf & _
          '          "in Table= " & sTbl & " Field= " & sFld
            slCloseSet 
            Function = ""     
            Exit Function      'no matching record found
          End If 
          For i = 1 To slGetColumnCount
             dtaRec = dtaRec & slF(i) & Chr$(127)
          Next
          Function = dtaRec      'keep current record in global memory
          If Parse$(dtaRec, Chr$(127), 2) <> sTxt Then Function = ""
          slCloseSet

End Function

Function GetAllRfRecsForRx (RxNumber As String) As String

          Local SQL1 As String
          Local sFld As String 
          Local sTxt As String 
          Local direction As String
          Local dtaRec As String
          Local dtaRecords As String   
          Local c As Long
          Local i As Long 
          Local res As Long 
          Local lErr     As String   

       For c = 199 To 100 Step -1
          dtaRec = ""
          direction = "="
          sTxt = RxNumber & "~" & Trim$(Right$(Str$(c),2))
          sFld = "RfNum"
          SQL1 = "SELECT RowID, * FROM RfRecs WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]"
          'SQL1 = "SELECT RowID, * FROM RfRecs WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]" 
          'slOpen gDBName
          slSel Sql1,0,"E1" 
          lErr = slGetError
          If Val(lErr) Then
             MsgBox lErr & "-Getting Rf Records", ,"Error"
             slCloseSet : Exit Function   
          End If 
          res = slGetRow
          If res = 0 And c = 100 Then Exit Function   'no matching record found
          If res <> 0 Then
             For i = 1 To slGetColumnCount
                dtaRec = dtaRec & slF(i) & Chr$(127)   ' add group separator for ease of parsing string
             Next
             dtaRecords = dtaRecords & dtaRec & Chr$(128)       'keep current record in global memory   
             '? dtaRecords
         End If
         slCloseSet
       Next
       gRxRfTop = 1
       Function = dtaRecords

End Function


Bern Ertl

Quote from: Marty Francom on April 09, 2010, 02:01:35 PMIs it possible to read data from several different tables all in a single call to the SQL-DB ?

Use an INNER JOIN or a LEFT OUTER JOIN to pull data from two or more tables as long as the data is related.

http://www.sqlite.org/lang_select.html

http://www.shokhirev.com/nikolai/abc/sql/joins.html

Fred Meier

QuoteIf I have a small LAN setup 3 or so computers, and one of the computers is
being used as a "server" can the program on the "server" be attached to
the DB in "local mode" while the "workstation" computers be attached to
the DB in "remote mode" ? 
Yes, the "server" could run in local mode.

QuoteIs it possible to read data from several different tables all in a single call to the SQL-DB ?
It appears that for each RX you are doing 8 trips to the server.  As Bern
said, using joins should reduce that number.   A DBProc could also be used.



Marty Francom

Fred,
    What is a DBProc ?    Can you provide an example?.
Thanks
...Marty

Fred Meier

There is a sample DBProc included -- check out SQLiteningProcsA.Bas. 
Below is from that source file. 

Quote=====================================================
|                                                                        |
| SQLitening Procs Suffix A                                     |
|                                                                        |
=========================================================
Procs will allow you to write your own code which will run at the server
accessing SQLite in local mode.  These procs talk to SQLite directly
without going thru SQLitening. Not using SQLitening is required cause
SQLitening.Dll is not thread safe and the server starts a new thread for
each connection.  Procs will also work fine in local mode. This concept is
similar to database procedures in Oracle and MySQL except here you get to
code in Basic rather that their proprietary language. Using procs can
greatly improve response time by reducing messages to/from server.
CAUTION1:  This Dll is running as part of the server so all sets must be
           finalized ASAP so locks are released. (don't return to user
           with un-finalized sets)
CAUTION2:  This Dll must be thread safe so careful with globals or use
           Threaded global variables.  NEVER call any of the functions
           in SQLitening.Dll because it is not thread safe.

Fred Meier

Another way to do multi selects in one statement is to compound them. 
Below is quote from SQLite doc:

QuoteA compound SELECT is formed from two or more simple SELECTs connected by
one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In a
compound SELECT, all the constituent SELECTs must specify the same number
of result columns.  There may be only a single ORDER BY clause at the end
of the compound SELECT.  The UNION and UNION ALL operators combine the
results of the SELECTs to the right and left into a single big table.  The
difference is that in UNION all result rows are distinct where in UNION
ALL there may be duplicates.  The INTERSECT operator takes the
intersection of the results of the left and right SELECTs.  EXCEPT takes
the result of left SELECT after removing the results of the right SELECT. 
When three or more SELECTs are connected into a compound, they group from
left to right. 
Below is a simple example of compounding two selects.  Note that the first
column of each is a ID number.  Since table TA has fewer columns we must
add a filler columne to it's select. 

  slExe "Create table T1(F1,F2,F3)
   slExe "Create table TA(FA,FB)
   slSel "Select 1, * from T1 union select 2, *, '' from TA"


Using compounding/joins you should be able to reduce you trips to the
server to one.