• Welcome, Guest. Please login.
 
April 10, 2020, 01:17:12 am

News:

Welcome to the SQLitening support forums!


Possibility of Speeding Things Up?

Started by Jeffrey Smith, July 06, 2010, 02:06:21 pm

Previous topic - Next topic

Jeffrey Smith

First of all I got to say that SQLitening rocks!  It took me some time to wrap my brain around the SQL calls.

I've created a charting tool to display results for a fisheries habitat model.  One of my tables in the database is over 1.6 million records.  It takes about 0.75 seconds to retrieve 300 records into an array.  I then have to sort them into new arrays for the charting portion. 

Have I created an efficient method to retrieve the records?  Is it faster to use the SlGetRow command instead of loading the results into an array?  Thank you for any help.

Jeff


    Global EDQry() As String

      ReDim EDQry(0)
      A = "Select * from Exceedence Where "
      A = A & "NodeNum =" & Str$(CurNodeNum)
      A = A & " AND WYType =" & Str$(CurWYType)
      A = A & " AND MonthNum =" & Str$(CurMonthNum)
      A = A & " AND Percentile >=" & Str$(ExMin)
      A = A & " AND Percentile <=" & Str$(ExMax)
      A = A & " AND (HydSet < 3"
      A = A & " OR HydSet = 3 AND AltFlow =" & Str$(AFLOW) & ")"
      slSelAry A, EDQry(), "E2"



Fred Meier

Don't use slSelAry except when you really NEED the returning array of all the selected records.

      slSel "Select * from Parts"
      do while slGetRow
         lsA = slF(1)
         lsA = slF(2)
         lsA = slF(3)
         lsA = slF(4)
         lsA = slF(5)
         lsA = slF(6)
         lsA = slF(7)
         lsA = slF(8)
         lsA = slF(9)
      loop

      slSelAry "Select * from Parts", lsaA()
The first snippet is approx 35% faster than the second.


Also don't use "Select *" except when you are sure you will ALWAYS want
all the columns (even after new columns are added). 

Jeffrey Smith

Thanks Fred.  I took your suggestions.  The first snippet used to take 0.75 seconds to parse out 300 records from 1.69 million; now it takes less than a measurable amount of time.  The second snippet takes about 0.68 to 0.70 seconds to divide the data into chart series.  A gain of about 6% to 10%.  Oh, and I did discover the BETWEEN parameter from a previous post.


      b = "HydSet,Percentile,PV" & LTrim$(Str$(CurSpecNum))
      A = "Select " & b & " from Exceedence Where "
      A = A & "NodeNum =" & Str$(CurNodeNum)
      A = A & " AND WYType =" & Str$(CurWYType)
      A = A & " AND MonthNum =" & Str$(CurMonthNum)
      A = A & " AND Percentile BETWEEN" & Str$(ExMin) & " AND" & Str$(ExMax)
      A = A & " AND (HydSet < 3"
      A = A & " OR HydSet = 3 AND AltFlow =" & Str$(AFLOW) & ")"
      slSel A



Do While SlGetRow
    Perc = Val(Slfn("Percentile"))*100
    PValue = Val(slfn(PVstr))
    H = Val(slfn("HydSet"))
    AUC(H) = AUC(H) + PValue
    Select Case H
          Case 1
    Incr XP1
    Series1Y(XP1) = PValue
    Series1X(XP1) = Perc
    Case 2
    Incr XP2
    Series2Y(XP2) = PValue
    Series2X(XP2) = Perc
    Case 3
    Incr XP3
    Series3Y(XP3) = PValue
    Series3X(XP3) = Perc
    End Select
    MinY = Min(MinY,PValue)
    MaxY = Max(MaxY, PValue)
  Loop


Fred Meier

Your welcome.  Doesn't the first snippet need to also divide the data into the chart series like snippet two is doing?  Wouldn't that increase the time beyond .75 seconds?

Bern Ertl

Quote from: Fred Meier on July 06, 2010, 07:17:59 pm
Don't use slSelAry except when you really NEED the returning array of all the selected records.
...
The first snippet is approx 35% faster than the second.


Good stuff.  I was just wondering about this, this morning.