Solved

SQL Server vs INI File

Posted on 2011-09-15
5
245 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:koossa
  • 4
5 Comments
 

Author Comment

by:koossa
ID: 36548334
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
0
 
LVL 14

Accepted Solution

by:
VBClassicGuy earned 400 total points
ID: 36549882
INI files are wonderful things, sometimes pased over for more modern methods. But they are still a good workhorse for storing reasonable amounts of data, and I still use them a lot. One example is that I use INI files to store values that are PC-specific, and use SQL for things that are global to the system (all PC's that run my programs).

Here is your problem: SQL (as great as it is) does have some overhead time-wise. Establishing the connection, opening the database, retreiving the data, closing the database, and dropping the connection amongst them. Then too, I have run into many a slow EtherNet (too much traffic), and this adds to the time taken to retreive data. INI has far less overhead if the entries in a particular "scope" are reasonable.

But, there comes a point when the number of entries in an INI's scope is so great, the INI file starts taking more time than SQL. It all boils down to how much data you are storing, as you have found out. If you can keep the number of entries small enough to beat SQL, use INI. If you think your entries will grow beyond this threshold, use SQL.
0
 

Assisted Solution

by:koossa
koossa earned 0 total points
ID: 36553601
Ok, I've done a couple of speed tests.

The tests are done searching for data on line number 1 to 1000, then line 1001 to 2000, 2001 to 3000 and a time in seconds it took for each batch to search for was recorded.
Obviously the SQL Server's search time would be fairly constant:

The interesting thing is that SQL Server is only faster than the INI file when a primary key on the search field was created and you have more than 7 000 entries.

So my conclusion is, if you have 5 000 entries or less, it is much faster to look up values in an INI file than in SQL Server.

Once again here is my code :

SEARCH IN 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
  Dim j As Long
  dTimer = Timer
  For i = 1 To 10000
    Call GetINIString("Words", "Main" & i)
    If i Mod 1000 = 0 Then
      Debug.Print i & "," & Timer - dTimer
      dTimer = Timer
    End If
  Next i
End Sub

Open in new window



SEARCH IN 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 10000
    Call GetSQLString(db, "Main" & i)
    If i Mod 1000 = 0 Then
      Debug.Print i & "," & Timer - dTimer
      dTimer = Timer
    End If
  Next i
  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


SpeedTest.png
0
 

Assisted Solution

by:koossa
koossa earned 0 total points
ID: 36553607
Here is what the INI file's format looks like

[Words]
Main1=Value1
Main2=Value2
Main3=Value3
Main4=Value4
Main5=Value5
Main6=Value6
Main7=Value7
Main8=Value8
Main9=Value9
..................
..................
..................
..................

Open in new window

0
 

Author Closing Comment

by:koossa
ID: 36578982
Thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now