Solved

SQL Server vs INI File

Posted on 2011-09-15
5
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

762 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