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

Reply via email to