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
And Here is my code for reading the same data from SQL Server
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?
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
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
ASKER
So if your data is more than 4 000, it's getting too slow to use a INI file.
untitled.PNG