[sqlite] Delete from FTS3 with ROWID really really slow.....
If I run the statement: delete from keyword where rowid in (SELECT idToDelete FROM keywordDeleteList); This statement takes an eternity even if there are only say 5 records in keywordDeleteList. Same thing if I do this: delete from keyword where rowid in (418458, 418541, 421168, 421326, 421367, 422676); However, this is very fast: delete from keyword where rowid = 418458 or rowid = 418541 The problem is I can't use a previously calculated table as a delete list. I'd have to select each one, generate separate delete statement for each record in the fts3 table. I can't imagine why this is so slow. Any elegant work arounds? -- View this message in context: http://old.nabble.com/Delete-from-FTS3-with-ROWID-really-really-slow.-tp28611977p28611977.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional "cannot commit - no transaction is active"
We're seeing exactly the same thing on .22. We were previously on .17 and never had this issue. Our usage is exactly as you describe as well. Multiple threads with the shared cache enabled but no single thread is using the same connection more than once. -- View this message in context: http://old.nabble.com/Occasional-%22cannot-commit---no-transaction-is-active%22-tp27871274p28416532.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imposinga minimum limit on a select. Anyway to do this?
OK, it just hit me that I can use a subselect to get the 50th recorder ordered by time and use a LIMIT 1 OFFSET 50 to get the time at that location. I should be be able to use that a MAX result.I hope.:) -- View this message in context: http://old.nabble.com/Imposinga-minimum-limit-on-a-select.-Anyway-to-do-this--tp28035954p28036162.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Imposinga minimum limit on a select. Anyway to do this?
I'm sitting here banging my head trying to decide the subject for this post because I don't know what I'd call what I want to do :) Here's what I want to do but don't know how. The schema is simplified for discussion. CREATE TABLE program (time_received INTEGER, name TEXT); Assume indices where appropriate for performance. The table has roughly 10K entries. I'd like a select that gets all programs received within the last 10 days and then order the results by name. However, if there are less than 50 results say because there aren't 50 programs that fit the time crieteria, I need to keep getting records, the next newest records until I hit 50 of them. This can't be that difficult but I'm just not seeing :( -- View this message in context: http://old.nabble.com/Imposinga-minimum-limit-on-a-select.-Anyway-to-do-this--tp28035954p28035954.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow JOIN on two indexed text fields. Why?????
Thank you! That was it. I've been pulling my hair out over this all day. I should have seen it. I've never used STRING in my own tables and I inherited this from someone else and didn't even think twice that the type difference would be the issue. Thanks you again. sorka wrote: > > This is driving me nuts. I have two tables I'm trying to join together on > two text fields. > > CREATE TABLE tmp_role ( programId INTEGER, > roleNameINTEGER, > positionINTEGER, > isNew BOOL, > personIdINTEGER, > nameSTRING); > > This table has up to a few dozen records at any one time. > > AND > > CREATE TABLE person ( > personId INTEGER PRIMARY KEY, > name text UNIQUE > ); > > This table has 10s of thousands of records. > > If I do this query: > SELECT person.ROWID FROM tmp_role JOIN person ON tmp_role.name = > person.name; > > to find the ROWID of each row in person who's name matches that of the > name in tmp_role, it takes about 1 second per matcha really long time. > > However, if I instead take each of names in tmp_role and do a seperate > select like this: > SELECT ROWID FROM person WHERE name = "Carell|Steve"; > > and do it for each name, the search takes only a few ms for few dozen > records in tmp_role. > > Now the real problem I'm trying to solve is an UPDATE like this: > > UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE > tmp_role.name = person.name); > > If I break this up into a bunch of different statements to iterate through > the records in tmp_role and then execute a single statement for each name, > I can accomplish this update statement fairly quickly, but as it is > authored above, it's taking about 24 seconds for 24 records or about 1000 > times longer than if I do it the long way :( > > > > > > -- View this message in context: http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why--tp26601433p26602612.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow JOIN on two indexed text fields. Why?????
This is driving me nuts. I have two tables I'm trying to join together on two text fields. CREATE TABLE tmp_role ( programId INTEGER, roleNameINTEGER, positionINTEGER, isNew BOOL, personIdINTEGER, nameSTRING); This table has up to a few dozen records at any one time. AND CREATE TABLE person ( personId INTEGER PRIMARY KEY, name text UNIQUE ); This table has 10s of thousands of records. If I do this query: SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON tmp_role.name = person.name; to find the ROWID of each row in person who's name matches that of the name in tmp_role, it takes about 1 second per matcha really long time. However, if I instead take each of names in tmp_role and do a seperate select like this: SELECT ROWID FROM person WHERE name = "Carell|Steve"; and do it for each name, the search takes only a few ms for few dozen records in tmp_role. Now the real problem I'm trying to solve is an UPDATE like this: UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE tmp_role.name = person.name); If I break this up into a bunch of different statements to iterate through the records in tmp_role and then execute a single statement for each name, I can accomplish this update statement fairly quickly, but as it is authored above, it's taking about 24 seconds for 24 records or about 1000 times longer than if I do it the long way :( -- View this message in context: http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why--tp26601433p26601433.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 IGNORE OR REPLACE????
There have been past discussions asking the same thing and various sqlite developers saying I'll check and reporting back that FTS3 doesn't implement replace or ignore. I was hoping someone had come up with a work around solution since then that doesn't have a big performance penalty. Simon Slavin-3 wrote: > > > On 4 Nov 2009, at 5:05pm, sorka wrote: > >> Hmm. Have you actually tried this yourself? >> >> Here's what I get with a simplified example: >> >> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY, >> title); >> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A"); >> INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A"); >> sqlite> select * from keyword; >> 3|A >> 3|A > > Erm ... I don't use FTS3 or VIRTUAL TABLEs but can someone else tell > me whether this shows a bug for a VIRTUAL TABLE ? > > sorka, instead of using INTEGER PRIMARY KEY, can you try one or both > of these: > > CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER UNIQUE, > title); > CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER > AUTOINCREMENT, title); > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-tp26191125p26201803.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 IGNORE OR REPLACE????
Hmm. Have you actually tried this yourself? Here's what I get with a simplified example: CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY, title); INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A"); INSERT OR IGNORE INTO keyword (programId, title) VALUES(3, "A"); sqlite> select * from keyword; 3|A 3|A So clearly this doesn't work as now duplicates are allowed to be inserted. If I execute this command now: INSERT OR IGNORE INTO keyword (ROWID, title) VALUES(3, "A"); SQL error: constraint failed So, clearly there is no duplicate checking at all when the explicit key name is used but then it's useless as I can't have duplicate keys. Simon Slavin-3 wrote: > > > On 4 Nov 2009, at 5:12am, sorka wrote: > >> Is there any way to have an intsert into an FTS3 table ignore a row >> if the >> ROWID being inserted already exists? > > First, make whatever column you're using for ROWID explicit, not > implicit. Declare one of your own named columns as INTEGER PRIMARY > KEY. Then when you use the INSERT command you can use the > > INSERT OR IGNORE ... > > form of the command and it'll ignore the command if it would duplicate > the value in that column. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-tp26191125p26200309.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 IGNORE OR REPLACE????
I'm doing both delete and insert within the same transaction already. The problem is there will alway be a few duplicates out of the hundreds of records so it will always fail. For whatever reason, the delete, even though it's just 2 or 3 records is taking 10 times longer than just the insert alone where I can artificially make a case where there are no duplicates. Scott Hess wrote: > > My experience suggests that the test before the insert is not quite > expensive, it's just shifting some expense from the insert to the test > (presumably a select). But the overall sequence of: > >BEGIN IMMEDIATE; > DELETE FROM ftstable WHERE docid = ?; > INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?); >COMMIT; > > Should be more-or-less the same speed as if you ran just the INSERT > without an explicit transaction. Really, you should test it! > > Anyhow, if you're still convinced there's a problem, you should just > do the INSERT, then check whether the INSERT failed due to an index > constraint, then do an UPDATE to set things to your desired values. > That's about as well as fts3 would do internally (fts3 UPDATE is > already implemented as internal-delete-operator followed by > internal-insert-operator). > > Note that the fts3 implementation provides some advantage to doing > updates in order by docid. Optimal would be something like: > > BEGIN IMMEDIATE; > -- for each document to insert, in numerically sorted order > DELETE FROM ftstable WHERE docid = ?; > INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?); > COMMIT; > > If you're doing the deletes as a separate first pass it will be > somewhat slower, because it is unordered WRT the inserts. If you do > each pass in order, though, the difference might be pretty small. > > [If I misunderstand and your goal is to not insert rows which are > already present, then ... I'm confused. Just insert all the rows you > have, and the ones which are already present will fail to insert, and > that's fine.] > > -scott > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-tp26191125p26198341.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 IGNORE OR REPLACE????
Is there any way to have an intsert into an FTS3 table ignore a row if the ROWID being inserted already exists? This is turning out to be quite troublesome because I'm inserting thousands of records where just a few like 3 or 4 will have the same rowid as existing records. However, to do the test prior to insertion to delete the duplicates first is quite expensive. It would be much better if the FTS3 insertion routine had the option of ignoring rather than failing on the constraint. -- View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-tp26191125p26191125.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Merging blobs on disk without taking up memory???
Hi. I have a table that stores pieces of an image as a bunch of blobs. I get the pieces out of order and store them in a table until I get all of the pieces I need. I then want to assemble them in order and store the resulting complete image in in another table entirely. Is there a smart way to go about this so that memory use is limited? A select and order by the part number will return each blob in order. Is there a way to use concat to build up the final blob in the other table without having to store all the pieces in memory first? Thanks. -- View this message in context: http://www.nabble.com/Merging-blobs-on-disk-without-taking-up-memorytp24799151p24799151.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
For this particular query, the only reason for the join on the channel table is simply to order by channel number and that's it. Currently, what I'm doing is leaving off the order by channel number. Since this is done through the C++ interface, I'm stepping through results until a showing with a new and greater time comes along. At that point, I take all the showings I got with the previous start time and sort them all manually with qsort by channel number. As soon as I have enough results for what I'm looking for, I stop stepping through rows and finish the transaction. It works well enough but it really seems that sql should have the ability to be smarter about ordering. Jeremy Hinegardner wrote: > > Ah, i see, so you are going to be calling this many times, and slowly > incrementing the value of startTime. Yes, in this query, if you have more > than > 8 shows at a particular start time, then when you increment the time to go > to > the next 'batch' of shows you will miss all the ones from then end of the > previous time. > > If that is the case, how about just making the inner query 'WHERE > showing.startTme = ' and leave off the limit, and call the > whole > thing repeatedly? I doubt that would do what you want, since you querying > for > startTime which is what your where clause, so you'll either get back rows > that > are all the value of your where clause or no rows. > > I guess more information about the whole problem is going to be in > necessary to > help further. For instance, it appears you have a table of 'showings' and > a > table of 'channels' and the goal is, find the next N items from the > showings > table for channels in the channel table. > > enjoy, > > -jeremy > > > > On Tue, Apr 21, 2009 at 10:11:41PM -0700, sorka wrote: >> >> Um, well, except that I'd have huge gaping holes. i.e. say there are 100 >> shows at or after the specified time all on different channels. The above >> query would limit to the first 8 showings on or after that time *before* >> sorting by channel number. >> >> >> >> Jeremy Hinegardner wrote: >> > >> > How about trying: >> > >> > SELECT s.startTime >> > FROM ( SELECT showing.startTime AS startTime >> > ,showing.stationId AS stationId >> > FROM showing >> > WHERE showing.startTime >= 123923 >> > ORDER BY showing.startTime >> >LIMIT 8 ) AS s >> > JOIN channel AS c >> > ON s.stationId = c.stationId >> > ORDER BY s.startTime, c.ChannelMajorNumber >> > >> > I believe this should limit your table of 100,000 rows down to just the >> 8 >> > you >> > want on the inner query, and then join that against the 100 rows in the >> > channel >> > table. >> > >> > Although, to tell you the truth, I don't see the purpose in your >> original >> > query: >> > >> > SELECT showing.startTime >> > FROM showing >> > JOIN channel >> > ON showing.startTime >= 123923 >> >AND showing.stationId = channel.stationId >> > ORDER BY showing.startTime, channel.ChannelMajorNumber >> > LIMIT 8; >> > >> > You are not doing anything with the channel table data other than >> joining >> > it >> > agains the showing tables. To me that means you are joining against >> the >> > channel >> > table in order to filter out rows in the showing table that have >> > stationId's >> > that do not exist in the channel table. >> > >> > Is that correct? If that is correct, then the query I gave will not do >> > what you >> > want. >> > >> > Can the goal you are attempting be stated as? >> > >> > Find next or earliest showings from the showings table for channels >> in >> > the >> > channel table? >> > >> > If this is the case, maybe this query will work: >> > >> > SELECT showing.startTime >> > FROM showing >> >WHERE showing.startTime >= 123923000 >> > AND showing.stationId IN ( SELECT DISTINCT channel.stationId >> FROM >> > channel ) >> >LIMIT 8; >> > >> > enjoy, >> > >> > -jeremy >> > >> > >> > On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: >> >> >> >>
Re: [sqlite] Multi column ORDER BY across table peformance problem....
Um, well, except that I'd have huge gaping holes. i.e. say there are 100 shows at or after the specified time all on different channels. The above query would limit to the first 8 showings on or after that time *before* sorting by channel number. Jeremy Hinegardner wrote: > > How about trying: > > SELECT s.startTime > FROM ( SELECT showing.startTime AS startTime > ,showing.stationId AS stationId > FROM showing > WHERE showing.startTime >= 123923 > ORDER BY showing.startTime >LIMIT 8 ) AS s > JOIN channel AS c > ON s.stationId = c.stationId > ORDER BY s.startTime, c.ChannelMajorNumber > > I believe this should limit your table of 100,000 rows down to just the 8 > you > want on the inner query, and then join that against the 100 rows in the > channel > table. > > Although, to tell you the truth, I don't see the purpose in your original > query: > > SELECT showing.startTime > FROM showing > JOIN channel > ON showing.startTime >= 123923 >AND showing.stationId = channel.stationId > ORDER BY showing.startTime, channel.ChannelMajorNumber > LIMIT 8; > > You are not doing anything with the channel table data other than joining > it > agains the showing tables. To me that means you are joining against the > channel > table in order to filter out rows in the showing table that have > stationId's > that do not exist in the channel table. > > Is that correct? If that is correct, then the query I gave will not do > what you > want. > > Can the goal you are attempting be stated as? > > Find next or earliest showings from the showings table for channels in > the > channel table? > > If this is the case, maybe this query will work: > > SELECT showing.startTime > FROM showing >WHERE showing.startTime >= 123923000 > AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM > channel ) >LIMIT 8; > > enjoy, > > -jeremy > > > On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: >> >> Writing the query as you said you would returns results in nearly >> instantly, >> within a few ms. >> >> The problem is when you add a secondary ordering field. The intended >> indices >> are being used. >> >> The problem, as I've explained several times already is that there is no >> way >> to create a multicolumn index across tables. Sqlite is using the >> stationId >> index on the channel table to join showings via the stationId. The query >> plan shows it clearly. When ordering by startTime or even startTime and >> stationId, the results are returned in a few ms. But when adding channel >> number(i.e startTime and then channel number), it jumps to two minutes. >> This >> is because sqlite is bringing in nearly 100K records from the showing >> table, >> sorted by time, because of the startTime index, before it starts sorting >> by >> channel number. It would sure be nice to be able to get sqlite to sort on >> the channel number for each time group. i.e. once all the 10:00 PM >> showings >> have been returned and the first 10:30 PM showing is about to be >> returned, >> sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing >> this but should. >> >> At this point, I'm going to assume that this is just a limitation in the >> sqlite engine and I'll have to proceed in other ways to solve this issue. >> >> >> >> Ian Walters wrote: >> > >> >> SELECT showing.startTime FROM showing JOIN channel ON >> >> showing.startTime >= >> >> 123923 AND showing.stationId = channel.stationId ORDER BY >> >> showing.startTime LIMIT 8; >> > >> > I don't know if it would be faster... but I would have written that. >> > >> > SELECT showing.startTime FROM showing JOIN channel USING (stationId) >> > WHERE startTime < 123923 ORDER BY startTime LIMIT 8. >> > >> > Also I know in the latest version of SQLite its possible to 'hint' >> > what indexes should be used, which might be helpful. There is also >> > something on the contrib page that lets you check a query to see what >> > indexes it does use. >> > >> > Sorry if the above lacks detail, its kinda a busy day. >> > >> > -- >> > Ian >> > ___ >&g
Re: [sqlite] Multi column ORDER BY across table peformance problem....
Writing the query as you said you would returns results in nearly instantly, within a few ms. The problem is when you add a secondary ordering field. The intended indices are being used. The problem, as I've explained several times already is that there is no way to create a multicolumn index across tables. Sqlite is using the stationId index on the channel table to join showings via the stationId. The query plan shows it clearly. When ordering by startTime or even startTime and stationId, the results are returned in a few ms. But when adding channel number(i.e startTime and then channel number), it jumps to two minutes. This is because sqlite is bringing in nearly 100K records from the showing table, sorted by time, because of the startTime index, before it starts sorting by channel number. It would sure be nice to be able to get sqlite to sort on the channel number for each time group. i.e. once all the 10:00 PM showings have been returned and the first 10:30 PM showing is about to be returned, sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing this but should. At this point, I'm going to assume that this is just a limitation in the sqlite engine and I'll have to proceed in other ways to solve this issue. Ian Walters wrote: > >> SELECT showing.startTime FROM showing JOIN channel ON >> showing.startTime >= >> 123923 AND showing.stationId = channel.stationId ORDER BY >> showing.startTime LIMIT 8; > > I don't know if it would be faster... but I would have written that. > > SELECT showing.startTime FROM showing JOIN channel USING (stationId) > WHERE startTime < 123923 ORDER BY startTime LIMIT 8. > > Also I know in the latest version of SQLite its possible to 'hint' > what indexes should be used, which might be helpful. There is also > something on the contrib page that lets you check a query to see what > indexes it does use. > > Sorry if the above lacks detail, its kinda a busy day. > > -- > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
That's completely untrue. The only reason for the difference in the name is because I changed it recently. MajoreChannelNumber is ChannelNumber, the name just changed. I've included the full schema and all the indices and indicated about how many records are in each table. The schema example is super simple and the question is one that anyone reasonably familiar with sqlite should be able to answer. sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > "showingIdINTEGER PRIMARY KEY, " > "stationId INTEGER, " > "startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelIdINTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationIdINTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168893.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
I can't think of any reason why this would result in a faster query. But I went ahead and tried it on the off chance that maybe specifying the channel.ChannelMajorNumber inside an inner select might somehow trick sqlite into doing the right thing. Unfortunately, the result was the same. A few ms without the channel number and over 2 minutes with it. Again, sqlite is bringing in all results, not just the limit before it sorts on the channel number. I tried your query but had to alias the inner columns so they could be selected in the outer query. SELECT startTime FROM (SELECT showing.startTime as startTime, channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId ) ORDER BY startTime, ChannelMajorNumber LIMIT 8; sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > "showingIdINTEGER PRIMARY KEY, " > "stationId INTEGER, " > "startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelIdINTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationIdINTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168567.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
It's in my original post above. sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > "showingIdINTEGER PRIMARY KEY, " > "stationId INTEGER, " > "startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelIdINTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationIdINTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168281.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
No. This can't be broken down into a query within a query. Perhaps maybe if you spell out an example of what you're thinking? Thanks. sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > "showingIdINTEGER PRIMARY KEY, " > "stationId INTEGER, " > "startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelIdINTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationIdINTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23168105.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
Wow. Anybody? I figured this would be a simple question for the gurus on this board. Seriously, nobody knows a better way to do this? sorka wrote: > > This should be simple but apparently it isn't. > > I have two tables: > "CREATE TABLE showing ( " > "showingIdINTEGER PRIMARY KEY, " > "stationId INTEGER, " > "startTime INTEGER, " > ") " > CREATE INDEX showing_startTime on showing(startTime); > > > AND > "CREATE TABLE channel ( " > " channelIdINTEGER PRIMARY KEY, " > " ChannelNumber INTEGER, " > " stationIdINTEGER, " > " ) " > CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); > CREATE INDEX channel_stationId on channel(stationId); > > When I do this select: > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime LIMIT 8; > > I get back the correct 8 results in about 3 milliseconds. > > If I throw a secondary order term in there say ChannelNumber: > > SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= > 123923 AND showing.stationId = channel.stationId ORDER BY > showing.startTime, channel.ChannelMajorNumber LIMIT 8; > > It now takes over 120 seconds!!! > > I've tried various multi-column indices including one on > channel(stationId, ChannelNumber). > > No difference. > > As far as I can tell, when ordering on columns that cross tables, sqlite > will bring in all the records that match the equality or inequality search > term on the first column before it does a secondary sort. > > I have over 100,000 records in the showing table and about 100 records in > the channel table. > > Sqlite should be smart enough to do the secondary sort on ChannelNumber as > soon as it sees that the records coming back have a later startTime than > the previous one. i.e. Say the first 5 records have the same startTime and > different channel numbers. Then the next 5 records have a later start time > than the first. Sqlite should be smart enough to see this as the results > come back and do a secondary sort on ChannelNumber on the first 5 results > and then rinse and repeat. > > What appears to be happening is that even though startTime is indexed, > sqlite is bringing all 100K records into memory sorted by time before it > starts to sort by channel number. > > Is there a way to get sqlite to do the right thing? If there was only a > way to have a multi-column index that included columns from different > tables. Oh wait, there is, it's called an intermediate table. However the > cost of doing this is pretty high for reasons I can't go into here. > > Any ideas? Maybe I'm just doing something wrong and this should be simple. > > Thanks. > > > > -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23166621.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multi column ORDER BY across table peformance problem....
This should be simple but apparently it isn't. I have two tables: "CREATE TABLE showing ( " "showingIdINTEGER PRIMARY KEY, " "stationId INTEGER, " "startTime INTEGER, " ") " CREATE INDEX showing_startTime on showing(startTime); AND "CREATE TABLE channel ( " " channelIdINTEGER PRIMARY KEY, " " ChannelNumber INTEGER, " " stationIdINTEGER, " " ) " CREATE INDEX channel_ChannelNumber on channel(ChannelNumber); CREATE INDEX channel_stationId on channel(stationId); When I do this select: SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= 123923 AND showing.stationId = channel.stationId ORDER BY showing.startTime LIMIT 8; I get back the correct 8 results in about 3 milliseconds. If I throw a secondary order term in there say ChannelNumber: SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= 123923 AND showing.stationId = channel.stationId ORDER BY showing.startTime, channel.ChannelMajorNumber LIMIT 8; It now takes over 120 seconds!!! I've tried various multi-column indices including one on channel(stationId, ChannelNumber). No difference. As far as I can tell, when ordering on columns that cross tables, sqlite will bring in all the records that match the equality or inequality search term on the first column before it does a secondary sort. I have over 100,000 records in the showing table and about 100 records in the channel table. Sqlite should be smart enough to do the secondary sort on ChannelNumber as soon as it sees that the records coming back have a later startTime than the previous one. i.e. Say the first 5 records have the same startTime and different channel numbers. Then the next 5 records have a later start time than the first. Sqlite should be smart enough to see this as the results come back and do a secondary sort on ChannelNumber on the first 5 results and then rinse and repeat. What appears to be happening is that even though startTime is indexed, sqlite is bringing all 100K records into memory sorted by time before it starts to sort by channel number. Is there a way to get sqlite to do the right thing? If there was only a way to have a multi-column index that included columns from different tables. Oh wait, there is, it's called an intermediate table. However the cost of doing this is pretty high for reasons I can't go into here. Any ideas? Maybe I'm just doing something wrong and this should be simple. Thanks. -- View this message in context: http://www.nabble.com/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23109024.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to get the previous row before rows matching a where clause...
I have a table of events that have a title, start time, and end time. The start time is guaranteed unique, so I've made it my primary integer key. I need all events that overlap the a window of time between say windowstart and windowend. Currently, the statement SELECT title FROM event WHERE startTime < windowEnd AND endTime > windowStart. I've indexed the end time and the query is pretty fast, but it could be a lot faster if I only had to use the integer primary key. If instead I do SELECT title from event WHERE startTime > windowStart AND startTime < windowEnd this will get me almost the same thing except that it will be missing the first event that overlaps the windowStart because it's startTime is at or before startTime. In this case, if I can get exactly the previous row added to what is returned in the results above, I'll have exactly what I need. So the question is, when a WHERE clause returns a set of rows, is there a way to also get the row at the ROWID that comes just before the row that is returned from above with the lowest ROWID? Another way of putting it, if I take the lowest ROWID that is returned in my second example and get the next lowest ROW, the one that is less than the lowest ROWID I got but closest to it .i.e, the one right before it, then it would be complete. -- View this message in context: http://www.nabble.com/How-to-get-the-previous-row-before-rows-matching-a-where-clause...-tp22650799p22650799.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nested SELECTS using UNION and INTERSECT syntax problems....
I can't for the life of me figure this out. I'm trying to do a nested select like this: SELECT x FROM (( UNION ) INTERSECT ( UNION )) WHERE X= Each of the select a through d statements all return the same column x. If I remove the inner parentheses, it executes just fine but of course the results are wrong because C UNION D was not executed prior to the INTERSECT. Also, in each sub select case, a, b, c, and d, are all selecting on different FTS3 tables using MATCH so I can't take advantage of FTS3's newer nested parentheses with AND OR NOT hence the nested selects since MATCH can only be used once per select. Any ideas? I really don't want to have to resort to using temporary tables. -- View this message in context: http://www.nabble.com/Nested-SELECTS-using-UNION-and-INTERSECT-syntax-problems-tp22441795p22441795.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
