Link to home
Start Free TrialLog in
Avatar of koossa
koossa

asked on

SQL Server vs INI File

Good day

I'm working on a project that will be in 2 different languages, I have done a speed test to see if the INI file option would be fast enough.
I have compared the speed of SQL Server vs an INI File with 3 000 entries in both of them.

Here is my code for the INI File


Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Private Function GetINIString(ByVal sApp As String, ByVal sKey As String) As String
  Dim sFile As String
  Dim sBuf As String * 256
  Dim lBuf As Long
  
  sFile = App.Path & "\MyData.ini"
  lBuf = GetPrivateProfileString(sApp, sKey, "", sBuf, Len(sBuf), sFile)
  GetINIString = Left$(sBuf, lBuf)
End Function

Private Sub cmdFromINIFile_Click()
  Dim dTimer As Double
  Dim i As Long
  dTimer = Timer
  For i = 1 To 3000
    Debug.Print GetINIString("Words", "Lang_" & i)
  Next i
  MsgBox "Finished : " & Timer - dTimer
End Sub

Open in new window



And Here is my code for reading the same data from SQL Server

Private Sub cmdFromSQL_Click()
  Dim dTimer As Double
  Dim i As Long
  Dim db As ADODB.Connection
  Set db = CreateObject("ADODB.Connection")
  db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\SQLEXPRESS;Initial Catalog=master"
  
  dTimer = Timer
  For i = 1 To 3000
    Debug.Print GetSQLString(db, "Lang_" & i)
  Next i
  MsgBox "Finished : " & Timer - dTimer
  
  db.Close
  Set db = Nothing
End Sub

Private Function GetSQLString(ByVal db As ADODB.Connection, ByVal sKey As String) As String
  Dim rs As ADODB.Recordset
  Set rs = db.Execute("Select [Value] from Test_Table Where [MasterField] = '" & sKey & "'")
  If rs.EOF = False Then
    GetSQLString = rs("Value")
  End If
  Set rs = Nothing
End Function

Open in new window





The INI file is taking 6.97 seconds and the SQL Server is taking 16.63 seconds.

Is this possible or am I doing something wrong with the SQL Server function?
Avatar of koossa
koossa

ASKER

Very interesting, when I check the time it took to look up values of row from 1 to 1000, 1001 to 2000, 2001 to 3000 it starts to slowdown to the end (see attachment)
So if your data is more than 4 000, it's getting too slow to use a INI file.

untitled.PNG
ASKER CERTIFIED SOLUTION
Avatar of VBClassicGuy
VBClassicGuy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of koossa

ASKER

Thank you