> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Kraijenbrink - FixHet - Systeembeheer
> Sent: vrijdag 17 oktober 2014 12:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] group_concat query performance
>
>
> Joe Mistachkin wrote:
> >
> >Thanks for the query. It's difficult to track down performance issues
with
> System.Data.SQLite without seeing the C# (or VB.NET) example code as
> there are a variety of ways to query and process data using it.
> >
> >Is there any chance we could see the code that is using
> System.Data.SQLite?
>
> Yes, sure. Here they are, the first one is C++, the second one VB.net .Net
> Framework 4
Where do you perform the query in the C++ code?
Your C++ program shows how you prepare the statement 5000 times, but not how
you execute it.
The VB.Net code prepares the statement once, and then executes it 5000
times.
It looks like you are testing completely different things.
Bert
>
> With regards,
>
> Peter
>
> >>>>>>>>> C++ example code: <<<<<<<<<<<<<<<<<<<<<
>
> #include "stdafx.h"
> #include <iostream>
> #include <string>
> #include <sqlite3.h>
> #include <time.h>
>
> int main()
> {
> sqlite3 *db;
> sqlite3_stmt *res;
> time_t execStart, execStop;
>
> const char *errMSG;
> const char *tail;
>
> std::cout << "Running GROUP_CONCAT function test..." << std::endl;
>
> time(&execStart);
>
> int error = sqlite3_open("test.db",&db);
>
> if (error)
> {
> std::cout << "Could not open DB" << std::endl;
> sqlite3_close(db);
>
> system("Pause");
> return 1;
> }
>
> int cnt;
>
> for (cnt = 0; cnt < 50000; cnt++)
> {
> std::string query = "SELECT
> GROUP_CONCAT(Parent.fkintFolderID,':') FilePath FROM
> tblFolderNestedSets Node, tblFolderNestedSets Parent "
> "WHERE Node.intLeft
> BETWEEN Parent.intLeft AND Parent.intRight AND Parent.fkintSessionID =
> Node.fkintSessionID "
> "AND Node.fkintSessionID
=
> 1824 AND Node.fkintFolderID = 2913318;";
>
> error = sqlite3_prepare_v2(db,query.c_str(), query.length(),
> &res, &tail);
>
> if (error != SQLITE_OK)
> {
>
> std::cout << "Could not prepare sql" << std::endl;
> sqlite3_close(db);
>
> system("Pause");
> return 1;
> }
> }
>
> sqlite3_finalize(res);
> sqlite3_close(db);
>
> time(&execStop);
>
> double timeDiff = difftime(execStart, execStop);
>
> printf("Elapsed time is %.2lf seconds. ", timeDiff);
>
> system("Pause");
> return 0;
>
> }
>
> >>>>>>>>>>>>> System.Data.SQLite example code:
> <<<<<<<<<<<<<<<<<<<<<<
>
> Module Module1
>
> Sub Main()
>
> Dim _stopwatch As New Stopwatch()
>
> Dim _dbConn As New System.Data.SQLite.SQLiteConnection()
> Dim _dbPath As String = "Data Source=test.db"
>
> _stopwatch.Start()
>
> Console.WriteLine("Running GROUP_CONCAT function test...")
>
> _dbConn.ConnectionString = _dbPath
> _dbConn.Open()
>
> Dim _selCmd As System.Data.SQLite.SQLiteCommand
> _selCmd = New System.Data.SQLite.SQLiteCommand(_dbConn)
>
> _selCmd.CommandText = "SELECT
> GROUP_CONCAT(Parent.fkintFolderID,':') FilePath FROM
> tblFolderNestedSets Node, tblFolderNestedSets Parent " & _
> "WHERE Node.intLeft BETWEEN Parent.intLeft
AND
> Parent.intRight AND Parent.fkintSessionID = Node.fkintSessionID " & _
> "AND Node.fkintSessionID = 1824 AND
Node.fkintFolderID =
> 2913318;"
>
> Dim _cnt As Integer
> Dim _result As String
>
> For _cnt = 1 To 50000
>
> _result = _selCmd.ExecuteScalar().ToString()
> Next
>
> _dbConn.Close()
>
> _stopwatch.Stop()
>
> Console.WriteLine("Elapsed time is {0} seconds.",
_stopwatch.Elapsed)
> Console.WriteLine("Press any key to continue...")
> Console.ReadKey()
>
> End Sub
>
> End Module
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
