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
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
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
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
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
> 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
>
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
> > > 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
>
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
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
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
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
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?
>
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
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
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
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
> 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
18 matches
Mail list logo