Solved

SQL Server vs INI File

Posted on 2011-09-15
5
252 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month7 days, 9 hours left to enroll

632 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