Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

>Have you tested using a transaction over your combined queries?

>Even for simple read operations this may give a huge performance difference.

Yes, we did. Not only for performance reasons but for "automatic cleanup" in 
case of an error as well.

--

Peter

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Bert Huijben]

> -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 16:46
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] group_concat query performance
> 
> Thanks Bert,
> 
> You are right. Now the C++ example runs equaly fast. Looks like I have to
> redesign the Db schema.

Have you tested using a transaction over your combined queries?

Even for simple read operations this may give a huge performance difference.


In our use case (where we use SQLite as Subversion working copy database)
the performance problems are usually in the total number of transactions for
an operation, not in the queries itself.

Bert
> 
> With regards,
> 
> Peter
> 
> >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
> 
> ___
> 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

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Thanks Bert,

You are right. Now the C++ example runs equaly fast. Looks like I have to 
redesign the Db schema.

With regards,

Peter

>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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Bert Huijben]

> -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 
> #include 
> #include 
> #include 
> 
> 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();
> 
>   int error = sqlite3_open("test.db",);
> 
>   if (error)
>   {
>   std::cout << "Could not open DB" << std::endl;
>   sqlite3_close(db);
> 
>   system("Pause");
>   return 1;
>   }
> 
>   int cnt;
> 
>   for (cnt = 0; cnt < 5; 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(),
> , );
> 
>   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();
> 
>   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 5
> 
> _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

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

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 

With regards,

Peter

> C++ example code: <

#include "stdafx.h"
#include 
#include 
#include 
#include 

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();

int error = sqlite3_open("test.db",);

if (error)
{
std::cout << "Could not open DB" << std::endl;
sqlite3_close(db);

system("Pause");
return 1;
}

int cnt;

for (cnt = 0; cnt < 5; 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(), 
, );

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();

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 5

_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

Re: [sqlite] group_concat query performance

[Joe Mistachkin]

Kraijenbrink - FixHet - Systeembeheer wrote:
> 
> Sorry for the delay, I had to solve an server problem first. This is the
> sql query I've been testing with
>

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?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi Joe,

Sorry for the delay, I had to solve an server problem first. This is the sql 
query I've been testing with

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 =  1817 
AND 
Node.fkintFolderID  = 1937926;

And this is the table:

CREATE TABLE tblFolderNestedSets (
  pkintFolderNestedSetID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  fkintSessionID  integer NOT NULL,
  fkintFolderID   integer NOT NULL,
  intLeft integer,
  intRightinteger
);

It runs perfectly with the C++ test sample and very slow on ADO.Net SQLite 
library. I don't know why.

Thank you for your time and effort.

With regards,

Peter


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Joe Mistachkin
>Sent: maandag 13 oktober 2014 20:31
>To: 'General Discussion of SQLite Database'
>Subject: Re: [sqlite] group_concat query performance
>
>
>Kraijenbrink - FixHet - Systeembeheer wrote:
>>
>> 1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9
>seconds;
>>
>> 2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8
>seconds.
>>(Without the GROUP_CONCAT function that is;)
>> 
>> 3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6
>minutes;  
>> 
>
>Are you able to share the query and the schema of the database involved?
>
>If you have sample code, that might reveal important details as well.
>
>--
>Joe Mistachkin
>
>___
>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

Re: [sqlite] group_concat query performance

[Joe Mistachkin]

Kraijenbrink - FixHet - Systeembeheer wrote:
>
> 1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9
seconds;
>
> 2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8
seconds.
>(Without the GROUP_CONCAT function that is;)
> 
> 3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6
minutes;  
> 

Are you able to share the query and the schema of the database involved?

If you have sample code, that might reveal important details as well.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi,

I've created 3 test samples. A C++ (sqlite-amalgamation-3080600 .lib) and two 
VB.NET variants (sqlite-netFx40-binary-x64-2010-1.0.94.0). 

1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9 seconds;

2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8 seconds. 
(Without the GROUP_CONCAT function that is;)

3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6 minutes; 
 

It looks like the .Net variant handles the GROUP_CONCAT function differently.

With regards,

Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi Rchard,

I've created 3 test samples. A C++ and two VB.NET variants. 

1. "SQLitePerfTest - C++ " runs very fast. 50.000 queries in 8 or 9 seconds;

2. "SQLitePerfTest - VB.net  runs very fast. 50.000 queries in 7 or 8 seconds. 
(Without the GROUP_CONCAT function that is;)

3. "SQLitePerfTest - VB.net  which runs slow, 50.000 queries in 5 or 6 minutes; 
 

Thank you for your time and effort and hope to hear from you.

With regards,

Peter Kraijenbrink



FixHet - Systeembeheer
Geestdorp 22-II
3444 BD  Woerden
Tel: 0348-410220
Gsm: 06 - 27231926
Web: www.fixhet.nl
Mail:  kraijenbr...@fixhet.nl

=== Disclaimer = 
 
Dit e-mailbericht is vertrouwelijk en uitsluitend bedoeld voor de 
geadresseerde. Indien u niet de geadresseerde bent wordt u verzocht de afzender 
hiervan in kennis te stellen en dit bericht te vernietigen. 
 
FixHet - Systeembeheer aanvaardt geen enkele aansprakelijkheid voor enigerlei 
schade voortvloeiend uit het gebruik en/of acceptatie van de inhoud van het 
bericht. Bij twijfel verzoeken wij u een kopie op te vragen. 
 
Deze e-mail is gescand op virussen met Kaspersky Antivirus. 
=

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: donderdag 25 september 2014 17:46
To: General Discussion of SQLite Database
Subject: Re: [sqlite] group_concat query performance

On Thu, Sep 25, 2014 at 9:34 AM, Richard Hipp <d...@sqlite.org> wrote:

> Thanks for the schema.  Unfortunately, that was insufficient to 
> reproduce the problem.  Are you able to send me the actual database 
> file, via private email?
>


Thanks for sending the data.  But I'm still not able to reproduce the problem.  
I tried running this script against your database:

.open /home/drh/Downloads/Journal.dat
.tables
PRAGMA integrity_check;
.timer on
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 =  1817 AND 
Node.fkintFolderID  = 1937926; .print 
---
SELECT 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 =  1817 AND 
Node.fkintFolderID  = 1937926;

The output I get back is:

tblCabinets  tblFiles tblSessions
tblDataParts tblFolderNames   vw_FileParts
tblFileNames tblFolderNestedSets  vw_Files
tblFileParts tblFolders   vw_Folders
ok

Run Time: real 0.000 user 0.84 sys 0.00
---
Run Time: real 0.000 user 0.61 sys 0.00

As you can see, neither query is returning any result and neither query is 
taking an measurable amount of time.

Do you have any suggestions on what I can do differently in order to recreate 
your problem?

--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] group_concat query performance

[Richard Hipp]

On Thu, Sep 25, 2014 at 9:34 AM, Richard Hipp  wrote:

> Thanks for the schema.  Unfortunately, that was insufficient to reproduce
> the problem.  Are you able to send me the actual database file, via private
> email?
>


Thanks for sending the data.  But I'm still not able to reproduce the
problem.  I tried running this script against your database:

.open /home/drh/Downloads/Journal.dat
.tables
PRAGMA integrity_check;
.timer on
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 =  1817
AND Node.fkintFolderID  = 1937926;
.print ---
SELECT 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 =  1817
AND Node.fkintFolderID  = 1937926;

The output I get back is:

tblCabinets  tblFiles tblSessions
tblDataParts tblFolderNames   vw_FileParts
tblFileNames tblFolderNestedSets  vw_Files
tblFileParts tblFolders   vw_Folders
ok

Run Time: real 0.000 user 0.84 sys 0.00
---
Run Time: real 0.000 user 0.61 sys 0.00

As you can see, neither query is returning any result and neither query is
taking an measurable amount of time.

Do you have any suggestions on what I can do differently in order to
recreate your problem?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Clemens Ladisch]

Richard Hipp wrote:
> Note that the use of AUTOINCREMENT has nothing to do with your
> problem - I just see people using it a lot and I'm wondering why
> it is so popular

MySQL needs it.  Every search for "autoincrement" will find it.  This
keyword's name appears to imply that you do _not_ get autoincrementing
if you omit it.  Finally, when you use it, there is no noticeable
downside with typcial tests.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Richard Hipp]

Thanks for the schema.  Unfortunately, that was insufficient to reproduce
the problem.  Are you able to send me the actual database file, via private
email?

Aside:  Why are you using AUTOINCREMENT?  Do you really need it?  Are you
aware that there are space and time penalties for using AUTOINCREMENT even
if you never actually use the features it provides?  Are you aware that
INTEGER PRIMARY KEY values will be assigned automatically even without the
AUTOINCREMENT keyword?  See http://www.sqlite.org/autoinc.html for
additional information?  Note that the use of AUTOINCREMENT has nothing to
do with your problem - I just see people using it a lot and I'm wondering
why it is so popular and whether or not people really need it.

On Thu, Sep 25, 2014 at 8:59 AM, Kraijenbrink - FixHet - Systeembeheer <
kraijenbr...@fixhet.nl> wrote:

> Hi,
>
> Thank you for the fast response. Below this line you'll find sqlite3.exe's
> output:
>
> CREATE TABLE tblFolderNames (
> pkintFolderNameID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>   txtNametext NOT NULL UNIQUE COLLATE nocase
> );
> CREATE TABLE tblFolders (
>   pkintFolderIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL,
>   fkintParentIDinteger NOT NULL,
>   fkintNameID  integer NOT NULL,
>   dtmCreationTime  datetime NOT NULL
> );
> CREATE TABLE tblFolderNestedSets (
>   pkintFolderNestedSetID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>   fkintSessionID  integer NOT NULL,
>   fkintFolderID   integer NOT NULL,
>   intLeft integer,
>   intRightinteger
> );
> CREATE TABLE tblSessions (
>   pkintSessionID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>   dtmStartDatedatetime NOT NULL,
>   dtmEndDate  datetime
> );
> CREATE UNIQUE INDEX tblFolderNames_Name
>   ON tblFolderNames
>   (txtName COLLATE nocase);
> CREATE UNIQUE INDEX tblFolders_ParentID_NameID
>   ON tblFolders
>   (fkintParentID, fkintNameID);
> CREATE UNIQUE INDEX tblFileParts_FileID_DataPartID_DataPartPos
>   ON tblFileParts
>   (fkintFileID, fkintDataPartID, intDataPartPos);
> CREATE UNIQUE INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left
>   ON tblFolderNestedSets
>   (fkintSessionID, fkintFolderID, intRight, intLeft);
> /* No STAT tables available */
>
> --
>
> Peter Kraijenbrink
>
>
>
> >Please run the ".fullschema" command on your database and send us the
> output.
> >
> >I mean by this:  (1) Download the latest version of sqlite3.exe from the
> website.  (2) Run "sqlite3 YOURDATABASE .fullschema >out.txt"  (3) Include
> the text of out.txt in the body of a follow-up email.
> >
> >That information will assist us in answering your question.
> >
> >On Thu, Sep 25, 2014 at 6:54 AM, Kraijenbrink - FixHet - Systeembeheer <
> kraijenbr...@fixhet.nl> wrote:
> >
> >> Hi all,
> >>
> >> I've searched through this forum but couldn't find any related topic
> >> regarding my question. I'm having serious performance problems
> >> (queries up to 20/sec) while running a SQLite query since i added a
> group_concat clause.
> >>
> >> The query looks like:
> >>
> >> 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 =
> >> 1817 AND Node.fkintFolderID  = 1937926;
> >>
> >> Query result:
> >>
> >> 1927916\1934826\1936323\1937926
> >>
> >> Changing the first line "SELECT GROUP_CONCAT(" into "SELECT
> >> Parent.fkintFolderID..." increased performance by an order of magnitude.
> >>
> >> explain query plan returns:
> >>
> >> selectid orderfrom detail
> >> 0  0  0  SEARCH TABLE tblFolderNestedSets AS
> Node
> >> USING COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left
> >> (fkintSessionID=? AND fkintFolderID=?) (~9 rows)
> >> 0  1  1  SEARCH TABLE tblFolderNestedSets AS
> >> Parent USING COVERING INDEX
> >> tblFolderNestedSets_SessionID_FolderID_Right_Left (fkintSessionID=?)
> >> (~5
> >> rows)
> >>
> >> My question is: how can I improve performance and keep using
> >> GROUP_CONCAT at the same time?
> >>
> >> Thanks in advance.
> >>
> >> Peter
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> >--
> >D. Richard Hipp
> >d...@sqlite.org
> >___
> >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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing 

Re: [sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi,

Thank you for the fast response. Below this line you'll find sqlite3.exe's 
output:

CREATE TABLE tblFolderNames (
pkintFolderNameID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  txtNametext NOT NULL UNIQUE COLLATE nocase
);
CREATE TABLE tblFolders (
  pkintFolderIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL,
  fkintParentIDinteger NOT NULL,
  fkintNameID  integer NOT NULL,
  dtmCreationTime  datetime NOT NULL
);
CREATE TABLE tblFolderNestedSets (
  pkintFolderNestedSetID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  fkintSessionID  integer NOT NULL,
  fkintFolderID   integer NOT NULL,
  intLeft integer,
  intRightinteger
);
CREATE TABLE tblSessions (
  pkintSessionID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  dtmStartDatedatetime NOT NULL,
  dtmEndDate  datetime
);
CREATE UNIQUE INDEX tblFolderNames_Name
  ON tblFolderNames
  (txtName COLLATE nocase);
CREATE UNIQUE INDEX tblFolders_ParentID_NameID
  ON tblFolders
  (fkintParentID, fkintNameID);
CREATE UNIQUE INDEX tblFileParts_FileID_DataPartID_DataPartPos
  ON tblFileParts
  (fkintFileID, fkintDataPartID, intDataPartPos);
CREATE UNIQUE INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left
  ON tblFolderNestedSets
  (fkintSessionID, fkintFolderID, intRight, intLeft);
/* No STAT tables available */

--

Peter Kraijenbrink



>Please run the ".fullschema" command on your database and send us the output.
>
>I mean by this:  (1) Download the latest version of sqlite3.exe from the 
>website.  (2) Run "sqlite3 YOURDATABASE .fullschema >out.txt"  (3) Include the 
>text of out.txt in the body of a follow-up email.
>
>That information will assist us in answering your question.
>
>On Thu, Sep 25, 2014 at 6:54 AM, Kraijenbrink - FixHet - Systeembeheer < 
>kraijenbr...@fixhet.nl> wrote:
>
>> Hi all,
>>
>> I've searched through this forum but couldn't find any related topic 
>> regarding my question. I'm having serious performance problems 
>> (queries up to 20/sec) while running a SQLite query since i added a 
>> group_concat clause.
>>
>> The query looks like:
>>
>> 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 =  
>> 1817 AND Node.fkintFolderID  = 1937926;
>>
>> Query result:
>>
>> 1927916\1934826\1936323\1937926
>>
>> Changing the first line "SELECT GROUP_CONCAT(" into "SELECT 
>> Parent.fkintFolderID..." increased performance by an order of magnitude.
>>
>> explain query plan returns:
>>
>> selectid orderfrom detail
>> 0  0  0  SEARCH TABLE tblFolderNestedSets AS Node
>> USING COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left
>> (fkintSessionID=? AND fkintFolderID=?) (~9 rows)
>> 0  1  1  SEARCH TABLE tblFolderNestedSets AS
>> Parent USING COVERING INDEX
>> tblFolderNestedSets_SessionID_FolderID_Right_Left (fkintSessionID=?) 
>> (~5
>> rows)
>>
>> My question is: how can I improve performance and keep using 
>> GROUP_CONCAT at the same time?
>>
>> Thanks in advance.
>>
>> Peter
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>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

Re: [sqlite] group_concat query performance

[Richard Hipp]

Please run the ".fullschema" command on your database and send us the
output.

I mean by this:  (1) Download the latest version of sqlite3.exe from the
website.  (2) Run "sqlite3 YOURDATABASE .fullschema >out.txt"  (3) Include
the text of out.txt in the body of a follow-up email.

That information will assist us in answering your question.

On Thu, Sep 25, 2014 at 6:54 AM, Kraijenbrink - FixHet - Systeembeheer <
kraijenbr...@fixhet.nl> wrote:

> Hi all,
>
> I've searched through this forum but couldn't find any related topic
> regarding my question. I'm having serious performance problems (queries up
> to 20/sec) while running a SQLite query since i added a group_concat clause.
>
> The query looks like:
>
> 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 =  1817
> AND Node.fkintFolderID  = 1937926;
>
> Query result:
>
> 1927916\1934826\1936323\1937926
>
> Changing the first line "SELECT GROUP_CONCAT(" into "SELECT
> Parent.fkintFolderID..." increased performance by an order of magnitude.
>
> explain query plan returns:
>
> selectid orderfrom detail
> 0  0  0  SEARCH TABLE tblFolderNestedSets AS Node
> USING COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left
> (fkintSessionID=? AND fkintFolderID=?) (~9 rows)
> 0  1  1  SEARCH TABLE tblFolderNestedSets AS
> Parent USING COVERING INDEX
> tblFolderNestedSets_SessionID_FolderID_Right_Left (fkintSessionID=?) (~5
> rows)
>
> My question is: how can I improve performance and keep using GROUP_CONCAT
> at the same time?
>
> Thanks in advance.
>
> Peter
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat query performance

[Dominique Devienne]

On Thu, Sep 25, 2014 at 12:54 PM, Kraijenbrink - FixHet - Systeembeheer <
kraijenbr...@fixhet.nl> wrote:
>
> 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 =  1817
> AND Node.fkintFolderID  = 1937926;
>
> Query result:
>
> 1927916\1934826\1936323\1937926
>

from http://www.sqlite.org/lang_aggfunc.html: The order of the concatenated
elements is arbitrary.

So it's likely just chance that you get the result you expect, no?
Aren't recursive CTEs supposed to be used for such hierarchical queries?

Regarding group_concat performance, you could always try to write your own
aggregate function and compare. Could be group_concat does not pre-allocate
enough capacity into its buffer, and must realloc too many times. I doubt
it accounts for a 10x slow down though, assuming my hypothesis even holds
of course.

SQLite is one of those SQL engines where you can use aggregate functions
w/o an explicit group-by clause, so maybe w/o group_concat, that's no
longer a group-by by just a join, ending up being faster. All speculations
though, especially since I don't understand how your query works :). --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group_concat query performance

[Kraijenbrink - FixHet - Systeembeheer]

Hi all,

I've searched through this forum but couldn't find any related topic regarding 
my question. I'm having serious performance problems (queries up to 20/sec) 
while running a SQLite query since i added a group_concat clause.

The query looks like:

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 =  1817
AND Node.fkintFolderID  = 1937926;

Query result:

1927916\1934826\1936323\1937926

Changing the first line "SELECT GROUP_CONCAT(" into "SELECT 
Parent.fkintFolderID..." increased performance by an order of magnitude.

explain query plan returns:

selectid orderfrom detail
0  0  0  SEARCH TABLE tblFolderNestedSets AS Node USING 
COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left 
(fkintSessionID=? AND fkintFolderID=?) (~9 rows)
0  1  1  SEARCH TABLE tblFolderNestedSets AS Parent 
USING COVERING INDEX tblFolderNestedSets_SessionID_FolderID_Right_Left 
(fkintSessionID=?) (~5 rows)

My question is: how can I improve performance and keep using GROUP_CONCAT at 
the same time?

Thanks in advance.

Peter

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users