Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22
Thanks Richard! Changing the inner join to a cross join works as well in that case, though is it enough to always disable the left join optimization ? I have other variants of the query with different/more left joined tables/subqueries, and varying filtering conditions, as the query is dynamically generated from user options and filters (which can indeed lead to SQL that is not really "optimal"). Is having a cross join somewhere among the joins enough to "disable" the left join strength reduction for other joins? On Tue, Jun 26, 2018 at 5:58 PM, Richard Hipp wrote: > On 6/26/18, Eric Grange wrote: > > I am experiencing a massive performance issue on a query with a left join > > in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > > milliseconds. > > The problematic query looks like > > > > select d.key_field, count(*) nb > > from low_volume_table b > > join mid_volume_table c on c.key_b = b.id > > left join high_volume_table d on d.key_c = c.id > > where b.id >= $1 > > and b.filter1 = 0 > > and c.filter2 > 0 > > and d.filter3 > 0 > > and d.filter4 = 1 > > group by d.key_field > > order by nb desc > > Dan bisected and found the speed reduction coincides with the > introduction of the LEFT JOIN strength reduction optimization > (https://sqlite.org/optoverview.html#leftjoinreduction) in version > 3.23.0. (Dan bisected to the specific check-in > https://sqlite.org/src/info/dd568c27). > > Your work-around is to change the LEFT JOIN into CROSS JOIN, thus > forcing the query planner to preserve the same join order as it did > before the string reduction optimization. > > We (the SQLite devs) will take an action to try to improve the query > planner so that it picks a better plan for your case. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
Actually, you would probably write: SELECT aDate FROM TeachingSaturdaysInSchoolYear WHERE aDate NOT IN (SELECT aDate FROM SchoolYearTeachingDays); Since the subquery is not correlated there is no *need* for aliases ... but if you want to type more characters you are free to do so ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Tuesday, 26 June, 2018 08:56 >To: SQLite mailing list >Subject: Re: [sqlite] Understanding SELECT statement > > > >> On 26 Jun 2018, at 3:42pm, Csányi Pál wrote: >> INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate >NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) >> >> Just do not understand what are the 'T' and 'S' means out there, >after >> FROM clause. > >This is from an old stupid syntax for SQL. In more modern SQL we >would write > >SELECT aDate FROM TeachingSaturdaysInSchoolYear AS T WHERE >T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays AS S) > >It works for backwards compatibility but the "AS" makes the sense >clearer. > >Simon. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22
On 6/26/18, Eric Grange wrote: > I am experiencing a massive performance issue on a query with a left join > in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > milliseconds. > The problematic query looks like > > select d.key_field, count(*) nb > from low_volume_table b > join mid_volume_table c on c.key_b = b.id > left join high_volume_table d on d.key_c = c.id > where b.id >= $1 > and b.filter1 = 0 > and c.filter2 > 0 > and d.filter3 > 0 > and d.filter4 = 1 > group by d.key_field > order by nb desc Dan bisected and found the speed reduction coincides with the introduction of the LEFT JOIN strength reduction optimization (https://sqlite.org/optoverview.html#leftjoinreduction) in version 3.23.0. (Dan bisected to the specific check-in https://sqlite.org/src/info/dd568c27). Your work-around is to change the LEFT JOIN into CROSS JOIN, thus forcing the query planner to preserve the same join order as it did before the string reduction optimization. We (the SQLite devs) will take an action to try to improve the query planner so that it picks a better plan for your case. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
Once again, thank you all very much for your help. I understand now this query. R Smith ezt írta (időpont: 2018. jún. 26., K, 17:28): > > > On 2018/06/26 4:42 PM, Csányi Pál wrote: > > > >>> Then I get help and this code: > >>> INSERT INTO SchoolYearTeachingDays > >>>SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT > >>> IN (SELECT S.aDate FROM SchoolYearTeachingDays S) > >> This says "insert all dates that aren't already present", which of course > >> avoids UNIQUE constraint. > > Just do not understand what are the 'T' and 'S' means out there, after > > FROM clause. > > Are they CTEs? Are they virtual tables? > > > > The T and S are substitute identifiers that represent the tables > "SchoolYearTeachingDays" and "TeachingSaturdaysInSchoolYear". These > substitute names are called "Aliases" which is something like a > short-name or nick-name for something with a long name which we do not > want to type every time. Much like in school when someone was named > "Josephine" but everyone just called her "Jo" for short, then "Jo" is an > Alias for "Josephine", like in the above query where "S" is an alias for > "SchoolYearTeachingDays". > > The main reason we use aliases, which other posts have highlighted > already, is simply to spare ourselves the effort of having to type it > out many times, but it's also handy for avoiding confusion when we have > two references to the same table, or two tables with column names that > are the same. > > In the query above, the reason we need to name the table is so that we > make sure the "aDate" in the SELECT refers to the aDate from the > "SchoolYearTeachingDays" table in the correlated sub-query (the bit > inside parentheses) and not the outside query, which refers to table > "TeachingSaturdaysInSchoolYear", which may also have an "aDate" column. > So to avoid ambiguity or confusion about which aDate we are talking > about, we must specify the table it is in. But we don't want to write > the full table name every time, so in stead we use the aliases. > > One way to tell SQL we intend to use an alias is to use an "AS" clause, > so we could say: > > SELECT A.id FROM MyLongNamedTable AS A WHERE A.id > 5 > Here we say we will get data from "MyLongNamedTable" but we will use it > AS if it was named "A" so that everywhere else we will be able to refer > to A.this or A.that in stead of having to type MyLongNamedTable.this and > MyLongNamedTable.that. > > We can also do this for column names in the return header, so if I want > to get the id from MyTable but we want it to be more descriptive, we > could do: > SELECT id AS MyTableID FROM MyTable... > This will output a list of IDs under the aliased heading: "MyTableID". > > Lastly, as Simon mentioned, the SQL standard allows us to drop the word > "AS" as long as we specify the known identifier directly followed by its > Alias, so the previous query can also be written like: > SELECT id MyTableID FROM MyTable... > > There is a school of thought that prefers this "id MyTableID" aliasing > due to its brevity, and another that prefers the full "id AS MyTableID" > due to its clarity. > I don't think there is a "Right" way. Personally I use the first in my > actual queries, but the second when using a query in an explanation. > > > Hope that makes it clear. > > PS: Apologies for the overly "wordy" response, but it seems to me the OP > is not natively English, or might read this through a translator, and I > find for those to make sense in a technical translation, it is best if > they have a lot of data to work with, saying the same thing in different > ways, etc. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
On 2018/06/26 4:42 PM, Csányi Pál wrote: Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) This says "insert all dates that aren't already present", which of course avoids UNIQUE constraint. Just do not understand what are the 'T' and 'S' means out there, after FROM clause. Are they CTEs? Are they virtual tables? The T and S are substitute identifiers that represent the tables "SchoolYearTeachingDays" and "TeachingSaturdaysInSchoolYear". These substitute names are called "Aliases" which is something like a short-name or nick-name for something with a long name which we do not want to type every time. Much like in school when someone was named "Josephine" but everyone just called her "Jo" for short, then "Jo" is an Alias for "Josephine", like in the above query where "S" is an alias for "SchoolYearTeachingDays". The main reason we use aliases, which other posts have highlighted already, is simply to spare ourselves the effort of having to type it out many times, but it's also handy for avoiding confusion when we have two references to the same table, or two tables with column names that are the same. In the query above, the reason we need to name the table is so that we make sure the "aDate" in the SELECT refers to the aDate from the "SchoolYearTeachingDays" table in the correlated sub-query (the bit inside parentheses) and not the outside query, which refers to table "TeachingSaturdaysInSchoolYear", which may also have an "aDate" column. So to avoid ambiguity or confusion about which aDate we are talking about, we must specify the table it is in. But we don't want to write the full table name every time, so in stead we use the aliases. One way to tell SQL we intend to use an alias is to use an "AS" clause, so we could say: SELECT A.id FROM MyLongNamedTable AS A WHERE A.id > 5 Here we say we will get data from "MyLongNamedTable" but we will use it AS if it was named "A" so that everywhere else we will be able to refer to A.this or A.that in stead of having to type MyLongNamedTable.this and MyLongNamedTable.that. We can also do this for column names in the return header, so if I want to get the id from MyTable but we want it to be more descriptive, we could do: SELECT id AS MyTableID FROM MyTable... This will output a list of IDs under the aliased heading: "MyTableID". Lastly, as Simon mentioned, the SQL standard allows us to drop the word "AS" as long as we specify the known identifier directly followed by its Alias, so the previous query can also be written like: SELECT id MyTableID FROM MyTable... There is a school of thought that prefers this "id MyTableID" aliasing due to its brevity, and another that prefers the full "id AS MyTableID" due to its clarity. I don't think there is a "Right" way. Personally I use the first in my actual queries, but the second when using a query in an explanation. Hope that makes it clear. PS: Apologies for the overly "wordy" response, but it seems to me the OP is not natively English, or might read this through a translator, and I find for those to make sense in a technical translation, it is best if they have a lot of data to work with, saying the same thing in different ways, etc. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
> On 26 Jun 2018, at 3:42pm, Csányi Pál wrote: > >>> INSERT INTO SchoolYearTeachingDays >>> SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT >>> IN (SELECT S.aDate FROM SchoolYearTeachingDays S) > > Just do not understand what are the 'T' and 'S' means out there, after > FROM clause. This is from an old stupid syntax for SQL. In more modern SQL we would write SELECT aDate FROM TeachingSaturdaysInSchoolYear AS T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays AS S) It works for backwards compatibility but the "AS" makes the sense clearer. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
On 6/26/2018 10:42 AM, Csányi Pál wrote: Igor Tandetnik ezt írta (időpont: 2018. jún. 26., K, 16:10): On 6/26/2018 9:15 AM, Csányi Pál wrote: Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) This says "insert all dates that aren't already present", which of course avoids UNIQUE constraint. Just do not understand what are the 'T' and 'S' means out there, after FROM clause. Are they CTEs? Are they virtual tables? They are aliases. You could write TeachingSaturdaysInSchoolYear.aDate in place of T.aDate without a change in meaning; T.aDate is merely shorter and more convenient. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
> > > Then I get help and this code: > > > INSERT INTO SchoolYearTeachingDays > > > SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate > NOT > > > IN (SELECT S.aDate FROM SchoolYearTeachingDays S) > > > > This says "insert all dates that aren't already present", which of course > avoids UNIQUE constraint. > > Just do not understand what are the 'T' and 'S' means out there, after > FROM clause. > Are they CTEs? Are they virtual tables? > They are aliases for the table names. Another way of writing this is SELECT aDate FROM TeachingSaturdaysInSchoolYear AS T WHERE T.aDate The AS is optional, but makes it slightly clearer that you are saying use the table TeachingSaturdaysInSchoolYear and call it T. In this case it is a way of saving typing. Regards Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
Igor Tandetnik ezt írta (időpont: 2018. jún. 26., K, 16:10): > > On 6/26/2018 9:15 AM, Csányi Pál wrote: > > Then I get help and this code: > > INSERT INTO SchoolYearTeachingDays > > SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT > > IN (SELECT S.aDate FROM SchoolYearTeachingDays S) > > This says "insert all dates that aren't already present", which of course > avoids UNIQUE constraint. Just do not understand what are the 'T' and 'S' means out there, after FROM clause. Are they CTEs? Are they virtual tables? -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Understanding SELECT statement
I understand now a little better, how SELECT statement works. Thank you all for the explanations. Hick Gunter ezt írta (időpont: 2018. jún. 26., K, 16:23): > > There is no DATE datatype in SQLite. Your declaration assigns NUMERIC > affinity for the date column. > > It seems that you are storing TEXT values, which is allowed, but in conflict > with your declaration. > > Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE > alone would have done. > > As SQLite is asserting a UNIQUE constraint violation, you have probably > already inserted at least one saturday into your SchoolYearTeachingDays > table. You can check this with: > > SELECT rowid, aDate FROM SchoolYearTeachingDays WHERE aDate IN (SELECT aDate > FROM TeachingSaturdaysInSchoolYear); > > Or you may have duplicate dates (you did not declare aDate to be UNIQUE in > that table) in your TeachingSaturdaysInSchoolYear table, which you can check > with: > > SELECT aDate,count() from TeachingSaturdaysInSchoolYear group by 1 order by 1; > > To answer your question: INSERT INTO ... SELECT will attempt to insert each > result row of the SELECT exactly once. If you want to insert duplicated rows > of the SELECT only once, you need to SELECT DISTINCT. > > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Csányi Pál > Gesendet: Dienstag, 26. Juni 2018 15:15 > An: SQlite User > Betreff: [EXTERNAL] [sqlite] Understanding SELECT statement > > Hi, > > I have the 'SchoolYearTeachingDays' table with just one column, in which are > dates: > > CREATE TABLE SchoolYearTeachingDays ( > aDate DATE PRIMARY KEY > UNIQUE > ); > I filled it with many dates which are unique. These dates excludes dates for > Sundays and for Saturdays. I have another, the > 'TeachingSaturdaysInSchoolYear' table: > > CREATE TABLE TeachingSaturdaysInSchoolYear ( > idINT PRIMARY KEY >UNIQUE, > aDate DATE, > TimetableForTheDay TEXT > ); > This table holds just two dates. These two dates are for two Saturdays. On > these two Saturdays we have to teach students. When I do the following query > on this table, I get these two records: > > 2018-04-14 > 2018-05-05 > > I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' > table into 'SchoolYearTeachingDays' > table. > > I am trying with this query: > > INSERT INTO SchoolYearTeachingDays > SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: > Error: UNIQUE constraint failed: > SchoolYearTeachingDays.aDate > > Then I get help and this code: > INSERT INTO SchoolYearTeachingDays > SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN > (SELECT S.aDate FROM SchoolYearTeachingDays S) > > It works. But I am not understanding it at all. > I wish to know followings. > How many times want to inserts the SELECT query the one of the date from the > TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? > > That is: the how many times wants select statement to insert one record from > first table into second table? > > -- > Best, Pali > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Understanding SELECT statement
There is no DATE datatype in SQLite. Your declaration assigns NUMERIC affinity for the date column. It seems that you are storing TEXT values, which is allowed, but in conflict with your declaration. Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE alone would have done. As SQLite is asserting a UNIQUE constraint violation, you have probably already inserted at least one saturday into your SchoolYearTeachingDays table. You can check this with: SELECT rowid, aDate FROM SchoolYearTeachingDays WHERE aDate IN (SELECT aDate FROM TeachingSaturdaysInSchoolYear); Or you may have duplicate dates (you did not declare aDate to be UNIQUE in that table) in your TeachingSaturdaysInSchoolYear table, which you can check with: SELECT aDate,count() from TeachingSaturdaysInSchoolYear group by 1 order by 1; To answer your question: INSERT INTO ... SELECT will attempt to insert each result row of the SELECT exactly once. If you want to insert duplicated rows of the SELECT only once, you need to SELECT DISTINCT. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Csányi Pál Gesendet: Dienstag, 26. Juni 2018 15:15 An: SQlite User Betreff: [EXTERNAL] [sqlite] Understanding SELECT statement Hi, I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' table: CREATE TABLE TeachingSaturdaysInSchoolYear ( idINT PRIMARY KEY UNIQUE, aDate DATE, TimetableForTheDay TEXT ); This table holds just two dates. These two dates are for two Saturdays. On these two Saturdays we have to teach students. When I do the following query on this table, I get these two records: 2018-04-14 2018-05-05 I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays' table. I am trying with this query: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: UNIQUE constraint failed: SchoolYearTeachingDays.aDate Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) It works. But I am not understanding it at all. I wish to know followings. How many times want to inserts the SELECT query the one of the date from the TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? That is: the how many times wants select statement to insert one record from first table into second table? -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
On 6/26/2018 9:15 AM, Csányi Pál wrote: I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' table: CREATE TABLE TeachingSaturdaysInSchoolYear ( idINT PRIMARY KEY UNIQUE, aDate DATE, TimetableForTheDay TEXT ); This table holds just two dates. These two dates are for two Saturdays. On these two Saturdays we have to teach students. When I do the following query on this table, I get these two records: 2018-04-14 2018-05-05 I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays' table. I am trying with this query: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: UNIQUE constraint failed: SchoolYearTeachingDays.aDate This means that, despite your assumption to the contrary, at least one of those two dates is already present in SchoolYearTeachingDays Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) This says "insert all dates that aren't already present", which of course avoids UNIQUE constraint. I wish to know followings. How many times want to inserts the SELECT query the one of the date from the TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? I don't understand this question. That is: the how many times wants select statement to insert one record from first table into second table? Each row in the resultset of SELECT statement is inserted once, of course. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIMIT
Thanks Olivier, very good to know. gert Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia : > > Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > > > If I would like to use these ranking techniques in SQLite, can I do this > > with an extension? Has nobody created a Windowing.dll yet? > > Just asking. My short term need has been solved, but now that I know > this, > > I'm sure I'd like to use this technique later. But I like to stick to > > SQLite. > > Don't want to spoil any news, it is viewable by the public anyway, but > there are clearly experimental, interesting (and significant) work ongoing > by the SQLite developers around SQL windowing functions. A quick look to > the exp-window-functions branch clearly shows that. > > https://www.sqlite.org/src/timeline?n=100&r=exp-window-functions > > So it _may_ be possible that you see these features in a later version of > SQLite, or not. We'll see. I certainly wish the best for this experience > which looks good and nicely ongoing. > > From experience as a user of SQLite, not all development of features > through branches get merged to the trunk. Sometimes they stay available as > a branch for you to choose to use, sometimes they end up in the main SQLite > product (trunk branch). Sometimes they can stay significant time aside > before one day being merged. I have no specific expectations, and you > shouldn't have too, I'm just monitoring areas of development that are > interesting to my eyes and programming needs. Mainly the > 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this > 'exp-window-functions' branch. > > :) > > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding SELECT statement
Hi, I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' table: CREATE TABLE TeachingSaturdaysInSchoolYear ( idINT PRIMARY KEY UNIQUE, aDate DATE, TimetableForTheDay TEXT ); This table holds just two dates. These two dates are for two Saturdays. On these two Saturdays we have to teach students. When I do the following query on this table, I get these two records: 2018-04-14 2018-05-05 I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays' table. I am trying with this query: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: UNIQUE constraint failed: SchoolYearTeachingDays.aDate Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) It works. But I am not understanding it at all. I wish to know followings. How many times want to inserts the SELECT query the one of the date from the TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? That is: the how many times wants select statement to insert one record from first table into second table? -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Performance issue with left join in 3.24 compared with 3.22
Add "cross" before the first "join" to force the first table into the outermost loop -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Eric Grange Gesendet: Dienstag, 26. Juni 2018 10:13 An: General Discussion of SQLite Database Betreff: [EXTERNAL] Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22 Also ran a few index to "force" the query plan, but with limited success: - the "indexed by" clause does not result in the optimizer using the index first, it just uses the indexes in the later steps of the query plan. - using "not indexed" still results in the same table scan of high_volume_table first, just without the index. - using the unary "+" on the d table filters has no effect on the query plan (as these are not indexed in the first place I guess) Using unlikely() on the d table filters seems to be the only option that works. On Tue, Jun 26, 2018 at 10:02 AM, Eric Grange wrote: > Hi, > > I am experiencing a massive performance issue on a query with a left > join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > milliseconds. > The problematic query looks like > > select d.key_field, count(*) nb > from low_volume_table b > join mid_volume_table c on c.key_b = b.id > left join high_volume_table d on d.key_c = c.id > where b.id >= $1 > and b.filter1 = 0 > and c.filter2 > 0 > and d.filter3 > 0 > and d.filter4 = 1 > group by d.key_field > order by nb desc > > The filter fields on it are relatively non-discriminant (and > non_indexed), however the key_field is indexed. > > The most discriminating conditions in this query are those on the > low_volume and mid_volume tables, but the optimizer selects as first > action: > > SCAN TABLE high_volume_table USING INDEX key_field_idx > > which leads to a huge number of iterations. > > If on the other hand, just one of the d filter conditions is removed, > then the optimizer goes (like 3.22) first for > >SEARCH TABLE low_volume_table AS b USING COVERING INDEX > low_volume_table_id_idx (b.filter1=? AND rowid>?) > > This happens after running ANALYZE, the sqlite1_stat for the > high_volume table and key_field_idx is > > 5855234 6 > > while for the low_volume_table_filter1_idx it is > > 1976628 988314 > > While the low_volume_table_filter1_idx does not look very selective, > as it is coupled with rowid filtering, it is actually very effective > as it combines rowid & filter1, so there are just thousandths of rows > being considered in the "group by", while when starting from a > key_field_idx, there are millions of rows being considered, the > overwhelming majority not fulfilling the conditions. > > The above table names and fields have been anonymized, if someone from > the SQLite team want to have a look at the actual data, I can provide > a database download (it's about 1.7 GB) > > Thanks! > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22
Also ran a few index to "force" the query plan, but with limited success: - the "indexed by" clause does not result in the optimizer using the index first, it just uses the indexes in the later steps of the query plan. - using "not indexed" still results in the same table scan of high_volume_table first, just without the index. - using the unary "+" on the d table filters has no effect on the query plan (as these are not indexed in the first place I guess) Using unlikely() on the d table filters seems to be the only option that works. On Tue, Jun 26, 2018 at 10:02 AM, Eric Grange wrote: > Hi, > > I am experiencing a massive performance issue on a query with a left join > in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > milliseconds. > The problematic query looks like > > select d.key_field, count(*) nb > from low_volume_table b > join mid_volume_table c on c.key_b = b.id > left join high_volume_table d on d.key_c = c.id > where b.id >= $1 > and b.filter1 = 0 > and c.filter2 > 0 > and d.filter3 > 0 > and d.filter4 = 1 > group by d.key_field > order by nb desc > > The filter fields on it are relatively non-discriminant (and > non_indexed), however the key_field is indexed. > > The most discriminating conditions in this query are those on the > low_volume and mid_volume tables, but the optimizer > selects as first action: > > SCAN TABLE high_volume_table USING INDEX key_field_idx > > which leads to a huge number of iterations. > > If on the other hand, just one of the d filter conditions is removed, then > the optimizer goes (like 3.22) first for > >SEARCH TABLE low_volume_table AS b USING COVERING INDEX > low_volume_table_id_idx (b.filter1=? AND rowid>?) > > This happens after running ANALYZE, the sqlite1_stat for the high_volume > table and key_field_idx is > > 5855234 6 > > while for the low_volume_table_filter1_idx it is > > 1976628 988314 > > While the low_volume_table_filter1_idx does not look very selective, as > it is coupled with rowid filtering, it is actually very effective > as it combines rowid & filter1, so there are just thousandths of rows > being considered in the "group by", while when starting from > a key_field_idx, there are millions of rows being considered, the > overwhelming majority not fulfilling the conditions. > > The above table names and fields have been anonymized, if someone from the > SQLite team want to have a look at the actual data, > I can provide a database download (it's about 1.7 GB) > > Thanks! > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance issue with left join in 3.24 compared with 3.22
Hi, I am experiencing a massive performance issue on a query with a left join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few milliseconds. The problematic query looks like select d.key_field, count(*) nb from low_volume_table b join mid_volume_table c on c.key_b = b.id left join high_volume_table d on d.key_c = c.id where b.id >= $1 and b.filter1 = 0 and c.filter2 > 0 and d.filter3 > 0 and d.filter4 = 1 group by d.key_field order by nb desc The filter fields on it are relatively non-discriminant (and non_indexed), however the key_field is indexed. The most discriminating conditions in this query are those on the low_volume and mid_volume tables, but the optimizer selects as first action: SCAN TABLE high_volume_table USING INDEX key_field_idx which leads to a huge number of iterations. If on the other hand, just one of the d filter conditions is removed, then the optimizer goes (like 3.22) first for SEARCH TABLE low_volume_table AS b USING COVERING INDEX low_volume_table_id_idx (b.filter1=? AND rowid>?) This happens after running ANALYZE, the sqlite1_stat for the high_volume table and key_field_idx is 5855234 6 while for the low_volume_table_filter1_idx it is 1976628 988314 While the low_volume_table_filter1_idx does not look very selective, as it is coupled with rowid filtering, it is actually very effective as it combines rowid & filter1, so there are just thousandths of rows being considered in the "group by", while when starting from a key_field_idx, there are millions of rows being considered, the overwhelming majority not fulfilling the conditions. The above table names and fields have been anonymized, if someone from the SQLite team want to have a look at the actual data, I can provide a database download (it's about 1.7 GB) Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIMIT
> Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > If I would like to use these ranking techniques in SQLite, can I do this > with an extension? Has nobody created a Windowing.dll yet? > Just asking. My short term need has been solved, but now that I know this, > I'm sure I'd like to use this technique later. But I like to stick to > SQLite. Don't want to spoil any news, it is viewable by the public anyway, but there are clearly experimental, interesting (and significant) work ongoing by the SQLite developers around SQL windowing functions. A quick look to the exp-window-functions branch clearly shows that. https://www.sqlite.org/src/timeline?n=100&r=exp-window-functions So it _may_ be possible that you see these features in a later version of SQLite, or not. We'll see. I certainly wish the best for this experience which looks good and nicely ongoing. From experience as a user of SQLite, not all development of features through branches get merged to the trunk. Sometimes they stay available as a branch for you to choose to use, sometimes they end up in the main SQLite product (trunk branch). Sometimes they can stay significant time aside before one day being merged. I have no specific expectations, and you shouldn't have too, I'm just monitoring areas of development that are interesting to my eyes and programming needs. Mainly the 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this 'exp-window-functions' branch. :) -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
