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, > txtName text NOT NULL UNIQUE COLLATE nocase > ); > CREATE TABLE tblFolders ( > pkintFolderID integer PRIMARY KEY AUTOINCREMENT NOT NULL, > fkintParentID integer 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, > intRight integer > ); > CREATE TABLE tblSessions ( > pkintSessionID integer PRIMARY KEY AUTOINCREMENT NOT NULL, > dtmStartDate datetime 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 order from 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 list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
