Re: [sqlite] Split Function for SQLite?
On Tue, Aug 23, 2011 at 7:10 AM, Gregory Moore wrote:
> I need to split up a list of items in a single row so they each have
> their own row.
>
You can read about my trick query solving partly this task with a trick
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55935.html
This will require a user function (GetItemFromSet) and the results are
limited in a sense. You will get ti idea from the following query (the
version modified by Jim Morris)
SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4
) WHERE NOT (Item Is Null)
Also theoretically it is possible to use virtual tables for this. So
when your virtual query implementation accepts list in some way ('23,
14, 1, 7, 9') and returns the table when querying
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
If your programming language is C or C++, it's trivial to register a function with SQLite at runtime that can then be used in your queries. This does not require hacking SQLite itself. As noted, you can't return multiple rows per database row via a function. That's not how SQL works; it is "set" oriented. What you could do is write a function to extract the Nth item in a delimited string, passing the column name and the index of the item you want to extract, and then you could split out or coalesced string column into multiple columns like this for example: select c1, extract(c2,0) as c2_0, extract(c2,1) as c2_1 from mytable I can imagine a technique that you could possibly use to generate multiple rows that would have an extract function that keeps internal state and increments the index of the item to be extracted in a circular way (e.g., 0, 1, 2, 0, 1, 2) and then use a self join on the table to create a cross join where you get a new row for each combination of c1 and the extracted component of c2. But, this is imaginary only and quite a hack and it would definitely be better to handle this in your program directly. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Wednesday, August 24, 2011 11:09 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Split Function for SQLite? > Did you see the code in the link I provided? It's a function that can be added to SQL. Note, it's not SQL. SQL doesn't support "adding functions". SQL is all about SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE). That's it. So the link you provided is a function that can be added to T-SQL which is an internal programming language of MS SQL Server. SQLite doesn't have its own internal programming language, so it doesn't support writing stored procedures and functions like the example in the link. And although SQLite allows to add functions written in C those functions can't return such datatype as "table". So behavior you want is impossible to reproduce with SQLite and you have to implement it in your programming language. And just to prevent speculations about this: lacking of own programming language is not an issue for SQLite. Having such language won't have any benefit because SQLite works differently than any client-server DBMS. Doing what you want in your programming language will always work faster and is more convenient to implement. Pavel On Wed, Aug 24, 2011 at 10:47 PM, Gregory Moore wrote: > I don't know know whether it's a SQLite "issue" or not. Did you see the code in the link I provided? It's a function that can be added to SQL. I just need to find out whether it can be added to SQLite and if so, then how to add it. I'd like to know before I try it whether I would run the risk of messing up my SQLite "installation." Thanks! > > On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote: > >> On 8/24/2011 9:05 PM, Gregory Moore wrote: >>> I'm thinking it's parameters would be the name of a column and a character to designate where the string wold be separated. It would return multiple rows and each row would contain a piece of the string. >>> >>> In other word, take a table like this: >>> >>> c1 c2 >>> --- >>> 1 a; b; c >>> >>> run a statement like this: >>> >>> select c1, split(c2, ';')from t1; >>> >>> and get this: >>> >>> c1 c2 >>> --- >>> 1 a >>> 1 b >>> 1 c >>> >>> Maybe SQLite can do it or not, i don't know. I've searched using Google and found code for a few different implementations but they are for other dialects of SQL. Here's a link to one such function i found: >>> >>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to- >>> break-comma-separated-strings-into-table.aspx >>> >>> Would that work with SQLite? >>> >>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote: >>> On 8/24/2011 9:36 PM, Gregory Moore wrote: > Thanks for answering! Can this not be added as a function? What parameters would such a function take, and more interestingly, what would its return value be? -- Igor Tandetnik Gregory Moore thewatchful...@gmail.com >> lurker here, but felt compelled, and my apologies in advance, but >> >> Isn't this more a function of whatever language you are using and not >> a SQLite issue? >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Gregory Moore > thewatchful...@gmail.com > > > > ___ > 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 ___ sqlite-users
Re: [sqlite] saving pragma states in database
Thanks for the reply, > You could always create a table that stores the pragma values in which > you're interested, then have code that checks on start up to set those > pragmas to those values. I was thinking the same thing, but only as a last resort. It seems like the database should be able to remember that I set recursive_triggers to true or that I turned case_sensitive_like on. I'd like to be able to declare it once in my schema and let sqlite handle the details rather than reminding it every time that I open the database. It's obvious that some of the pragmas (freelist_count for example) should not be saved because they act like queries. But maybe others should have persistence. Am I just missing how to persist these values, or does the current implementation not have this capability? There could also be a completely great reason why these values don't persist, and I'm just not seeing it. --Erik Lechak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
> Did you see the code in the link I provided? It's a function that can be > added to SQL. Note, it's not SQL. SQL doesn't support "adding functions". SQL is all about SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE). That's it. So the link you provided is a function that can be added to T-SQL which is an internal programming language of MS SQL Server. SQLite doesn't have its own internal programming language, so it doesn't support writing stored procedures and functions like the example in the link. And although SQLite allows to add functions written in C those functions can't return such datatype as "table". So behavior you want is impossible to reproduce with SQLite and you have to implement it in your programming language. And just to prevent speculations about this: lacking of own programming language is not an issue for SQLite. Having such language won't have any benefit because SQLite works differently than any client-server DBMS. Doing what you want in your programming language will always work faster and is more convenient to implement. Pavel On Wed, Aug 24, 2011 at 10:47 PM, Gregory Moore wrote: > I don't know know whether it's a SQLite "issue" or not. Did you see the code > in the link I provided? It's a function that can be added to SQL. I just > need to find out whether it can be added to SQLite and if so, then how to add > it. I'd like to know before I try it whether I would run the risk of messing > up my SQLite "installation." Thanks! > > On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote: > >> On 8/24/2011 9:05 PM, Gregory Moore wrote: >>> I'm thinking it's parameters would be the name of a column and a character >>> to designate where the string wold be separated. It would return multiple >>> rows and each row would contain a piece of the string. >>> >>> In other word, take a table like this: >>> >>> c1 c2 >>> --- >>> 1 a; b; c >>> >>> run a statement like this: >>> >>> select c1, split(c2, ';')from t1; >>> >>> and get this: >>> >>> c1 c2 >>> --- >>> 1 a >>> 1 b >>> 1 c >>> >>> Maybe SQLite can do it or not, i don't know. I've searched using Google >>> and found code for a few different implementations but they are for other >>> dialects of SQL. Here's a link to one such function i found: >>> >>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx >>> >>> Would that work with SQLite? >>> >>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote: >>> On 8/24/2011 9:36 PM, Gregory Moore wrote: > Thanks for answering! Can this not be added as a function? What parameters would such a function take, and more interestingly, what would its return value be? -- Igor Tandetnik Gregory Moore thewatchful...@gmail.com >> lurker here, but felt compelled, and my apologies in advance, but >> >> Isn't this more a function of whatever language you are using and not a >> SQLite issue? >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Gregory Moore > thewatchful...@gmail.com > > > > ___ > 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
Re: [sqlite] Split Function for SQLite?
I don't know know whether it's a SQLite "issue" or not. Did you see the code in the link I provided? It's a function that can be added to SQL. I just need to find out whether it can be added to SQLite and if so, then how to add it. I'd like to know before I try it whether I would run the risk of messing up my SQLite "installation." Thanks! On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote: > On 8/24/2011 9:05 PM, Gregory Moore wrote: >> I'm thinking it's parameters would be the name of a column and a character >> to designate where the string wold be separated. It would return multiple >> rows and each row would contain a piece of the string. >> >> In other word, take a table like this: >> >> c1 c2 >> --- >> 1 a; b; c >> >> run a statement like this: >> >> select c1, split(c2, ';')from t1; >> >> and get this: >> >> c1 c2 >> --- >> 1 a >> 1 b >> 1 c >> >> Maybe SQLite can do it or not, i don't know. I've searched using Google and >> found code for a few different implementations but they are for other >> dialects of SQL. Here's a link to one such function i found: >> >> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx >> >> Would that work with SQLite? >> >> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote: >> >>> On 8/24/2011 9:36 PM, Gregory Moore wrote: Thanks for answering! Can this not be added as a function? >>> What parameters would such a function take, and more interestingly, what >>> would its return value be? >>> -- >>> Igor Tandetnik >>> >>> >>> Gregory Moore >>> thewatchful...@gmail.com >>> > lurker here, but felt compelled, and my apologies in advance, but > > Isn't this more a function of whatever language you are using and not a > SQLite issue? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Gregory Moore thewatchful...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
On 8/24/2011 9:05 PM, Gregory Moore wrote: > I'm thinking it's parameters would be the name of a column and a character to > designate where the string wold be separated. It would return multiple rows > and each row would contain a piece of the string. > > In other word, take a table like this: > > c1 c2 > --- > 1 a; b; c > > run a statement like this: > > select c1, split(c2, ';')from t1; > > and get this: > > c1 c2 > --- > 1 a > 1 b > 1 c > > Maybe SQLite can do it or not, i don't know. I've searched using Google and > found code for a few different implementations but they are for other > dialects of SQL. Here's a link to one such function i found: > > http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx > > Would that work with SQLite? > > On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote: > >> On 8/24/2011 9:36 PM, Gregory Moore wrote: >>> Thanks for answering! Can this not be added as a function? >> What parameters would such a function take, and more interestingly, what >> would its return value be? >> -- >> Igor Tandetnik >> >> >> Gregory Moore >> thewatchful...@gmail.com >> lurker here, but felt compelled, and my apologies in advance, but Isn't this more a function of whatever language you are using and not a SQLite issue? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
I'm thinking it's parameters would be the name of a column and a character to designate where the string wold be separated. It would return multiple rows and each row would contain a piece of the string. In other word, take a table like this: c1 c2 --- 1 a; b; c run a statement like this: select c1, split(c2, ';')from t1; and get this: c1 c2 --- 1 a 1 b 1 c Maybe SQLite can do it or not, i don't know. I've searched using Google and found code for a few different implementations but they are for other dialects of SQL. Here's a link to one such function i found: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx Would that work with SQLite? On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote: > On 8/24/2011 9:36 PM, Gregory Moore wrote: >> Thanks for answering! Can this not be added as a function? > > What parameters would such a function take, and more interestingly, what > would its return value be? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Gregory Moore thewatchful...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
On 8/24/2011 9:36 PM, Gregory Moore wrote: > Thanks for answering! Can this not be added as a function? What parameters would such a function take, and more interestingly, what would its return value be? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Split Function for SQLite?
Thanks for answering! Can this not be added as a function? On Aug 22, 2011, at 10:43 PM, Igor Tandetnik wrote: > Gregory Moore wrote: >> I need to split up a list of items in a single row so they each have >> their own row. >> >> Basically I need to take this: >> >> Key. Code >> -- >> 1. V1, v2, v3 >> >> And convert it to this: >> >> Key. Code >> -- >> 1. V1 >> 1. V2 >> 1. V3 > > I don't think you can do this with SQL alone. You'll have to implement the > logic in your favorite programming language. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Gregory Moore thewatchful...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
On 24 Aug 2011, at 11:18pm, GB wrote: > Well, that is exactly what the sqlite_stat2 table is meant for. It's > information is supposed to make the query planner able to decide upon > the usefulness of an index. Unfortunately, histogram information is not > collected for the implicit rowid index by the ANALYZE command, so the > planner has to rely on some rule of thumb which in turn seems to favour > the rowid index. Had you thought of creating an explicit index on the rowid column, then running ANALYZE again ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
Simon Slavin schrieb am 24.08.2011 23:33: > On 24 Aug 2011, at 9:59pm, GB wrote: > >> Simon Slavin schrieb am 24.08.2011 22:38: >> >>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >>> createdAt>= '2011-08-01' createdAt<= '2011-08-02' >> Thank you for your thoughts but I already tried this with no different >> results than before. And according to >> http://www.sqlite.org/optoverview.html#between_opt this is exactly what >> happens behind the curtains when SQLite processes BETWEEN statements. > Just checking. > >> What I'm actually looking for is a way to make SQLite create and >> consider histogram data for rowid lookup the same way as for regular >> indexes. > SQLite should be comparing the 'chunkiness' of the itemID and createdAt > columns. It won't know how chunky they are around the specific values > specified in that particular SELECT command. It also won't be able to > compare the distance between 1000 and 200, a million values, and > '2011-08-01' and '2011-08-02', which is just two values. The 'two values' > would suggest that using an index on that column might be the best way to > start, but I don't think the query optimizer can actually tell that. > > But I don't know details about how the optimizer uses its information so I > can't tell for sure whether it's making a good guess or it could be improved. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Well, that is exactly what the sqlite_stat2 table is meant for. It's information is supposed to make the query planner able to decide upon the usefulness of an index. Unfortunately, histogram information is not collected for the implicit rowid index by the ANALYZE command, so the planner has to rely on some rule of thumb which in turn seems to favour the rowid index. And if I make the itemID Column a regular (non-INTEGER PRIMARY KEY) one, the query plan gets generated as expected using the index on createdAt. regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
On 24 Aug 2011, at 9:59pm, GB wrote: > Simon Slavin schrieb am 24.08.2011 22:38: > >> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND >> createdAt>= '2011-08-01' createdAt<= '2011-08-02' > > Thank you for your thoughts but I already tried this with no different > results than before. And according to > http://www.sqlite.org/optoverview.html#between_opt this is exactly what > happens behind the curtains when SQLite processes BETWEEN statements. Just checking. > What I'm actually looking for is a way to make SQLite create and > consider histogram data for rowid lookup the same way as for regular > indexes. SQLite should be comparing the 'chunkiness' of the itemID and createdAt columns. It won't know how chunky they are around the specific values specified in that particular SELECT command. It also won't be able to compare the distance between 1000 and 200, a million values, and '2011-08-01' and '2011-08-02', which is just two values. The 'two values' would suggest that using an index on that column might be the best way to start, but I don't think the query optimizer can actually tell that. But I don't know details about how the optimizer uses its information so I can't tell for sure whether it's making a good guess or it could be improved. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
Simon Slavin schrieb am 24.08.2011 22:38: > On 24 Aug 2011, at 6:59pm, GB wrote: > >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' > Just out of curiosity, try changing both the BETWEEN formulations so it says > > SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND > createdAt>= '2011-08-01' createdAt<= '2011-08-02' > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Thank you for your thoughts but I already tried this with no different results than before. And according to http://www.sqlite.org/optoverview.html#between_opt this is exactly what happens behind the curtains when SQLite processes BETWEEN statements. What I'm actually looking for is a way to make SQLite create and consider histogram data for rowid lookup the same way as for regular indexes. regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
On 24 Aug 2011, at 6:59pm, GB wrote: > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' Just out of curiosity, try changing both the BETWEEN formulations so it says SELECT itemID FROM t WHERE itemID >= 100 AND itemID <= 200 AND createdAt >= '2011-08-01' createdAt <= '2011-08-02' Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
No, SQLite (as well as most other database systems) does a more elaborate evaluation. I breaks the statement apart into subterms and tries to determine which one makes the most beneficial use of an index so the order of the statement does not matter. See http://www.sqlite.org/optoverview.html for details. regards gerd Carlos Rocha schrieb am 24.08.2011 21:12: > Don't know how SQLite should behave in this case, but seems logical to > me that A and B would force that A is always evaluated, and B is > evaluated only if A is true. > I would change the order of the two betweens: > > SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND > '2011-08-02' AND itemID BETWEEN 100 AND 200 > >> Hi all, >> >> I have a table like this: >> >> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); >> CREATE INDEX createIdx on t(createdAt); >> >> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with >> current content. >> >> When perfoming a Statement like this: >> >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' >> >> the analyzer always chooses the rowid index which results in a scan over >> one million rows. It would have to scan only a few dozen rows if it >> chose createIdx instead (which is also a covering index). Looking at the >> sqlite_stat2 table shows that there is no data for the rowid index. >> Could this be the reason for the suboptimal query plan? The choice works >> as expected if itemID is a regular column with an index on it. >> >> regards >> gerd >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
Igor Tandetnik schrieb am 24.08.2011 20:20: > On 8/24/2011 1:59 PM, GB wrote: >> When perfoming a Statement like this: >> >> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND >> createdAt BETWEEN '2011-08-01' AND '2011-08-02' >> >> the analyzer always chooses the rowid index which results in a scan over >> one million rows. It would have to scan only a few dozen rows if it >> chose createIdx instead (which is also a covering index). Looking at the >> sqlite_stat2 table shows that there is no data for the rowid index. >> Could this be the reason for the suboptimal query plan? The choice works >> as expected if itemID is a regular column with an index on it. > I don't know the answer to your question, but if you are interested in a > workaround, you can write > > WHERE +itemID BETWEEN 100 AND 200 > > The unary plus suppresses the use of index on this column. I know of this way of forcing SQLite to not use a specific index. Forcing a specific index using INDEXED BY might as well be a solution. Unfortunately I can't rely on a specific data distribution, that's why I chose to set SQLITE_ENABLE_STAT2. I thought it would make the query analyzer choose a good plan based on the actual data distribution. regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
Don't know how SQLite should behave in this case, but seems logical to me that A and B would force that A is always evaluated, and B is evaluated only if A is true. I would change the order of the two betweens: SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND '2011-08-02' AND itemID BETWEEN 100 AND 200 > Hi all, > > I have a table like this: > > CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); > CREATE INDEX createIdx on t(createdAt); > > SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with > current content. > > When perfoming a Statement like this: > > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' > > the analyzer always chooses the rowid index which results in a scan over > one million rows. It would have to scan only a few dozen rows if it > chose createIdx instead (which is also a covering index). Looking at the > sqlite_stat2 table shows that there is no data for the rowid index. > Could this be the reason for the suboptimal query plan? The choice works > as expected if itemID is a regular column with an index on it. > > regards > gerd > ___ > 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
Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
On 8/24/2011 1:59 PM, GB wrote: > When perfoming a Statement like this: > > SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' > > the analyzer always chooses the rowid index which results in a scan over > one million rows. It would have to scan only a few dozen rows if it > chose createIdx instead (which is also a covering index). Looking at the > sqlite_stat2 table shows that there is no data for the rowid index. > Could this be the reason for the suboptimal query plan? The choice works > as expected if itemID is a regular column with an index on it. I don't know the answer to your question, but if you are interested in a workaround, you can write WHERE +itemID BETWEEN 100 AND 200 The unary plus suppresses the use of index on this column. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2
Hi all, I have a table like this: CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); CREATE INDEX createIdx on t(createdAt); SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with current content. When perfoming a Statement like this: SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND createdAt BETWEEN '2011-08-01' AND '2011-08-02' the analyzer always chooses the rowid index which results in a scan over one million rows. It would have to scan only a few dozen rows if it chose createIdx instead (which is also a covering index). Looking at the sqlite_stat2 table shows that there is no data for the rowid index. Could this be the reason for the suboptimal query plan? The choice works as expected if itemID is a regular column with an index on it. regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell tool locks database ? AFP access a problem ?
On 24 Aug 2011, at 3:07pm, Richard Hipp wrote: > You've run up against limitations of AFP. Or, more precisely, you've run up > against the performance/concurrency tradeoffs that are inherent in any > network filesystem. > > Apple has contributed working (though wildly complex) code that allows > SQLite to efficiently read and write database files on AFP filesystems. But > this code has the limitation that you can only open the SQLite database on > one machine at a time. You can open the same database multiple times on > that one machine. But you cannot have the database open simultaneously on > two or more machines. Many thanks, Richard. That explains what I'm seeing and reassures me I'm not doing something silly. > One work-around you should try is to ssh into the machine that is running > PHP and run the sqlite3 shell from there. I think that will work better for > you. Hadn't thought of that. I'll give it a try. > You'll get much better performance if you put your SQLite databases on a > local filesystem, fwiw. The databases are on the machine running the PHP app, which is the one that needs best and fastest access to them. The shell tool was just something I was using for a little debugging. So it sounds like my setup is good, and I'll just have to be careful about the use of my debugging tools. I appreciate the fast response. [later] and having seen other posts to this thread I'm happy to consider it open to any problems relating to what I posted about. I don't know the answers to any of this stuff since I've never had to consider things like AFP, SMB and NFS in too much detail before. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell tool locks database ? AFP access a problem ?
On Wed, Aug 24, 2011 at 6:14 PM, Pavel Ivanov wrote: > This is completely unrelated because Oracle works differently. > Regardless of the reason for the specific behaviours, the symptoms are similar, and they are both locking-related problems. Maybe off-topic, admittedly, but not "completely unrelated." And if > you saw this locking most probably you had an open transaction in the > sqlplus. sqlplus was there only to run SELECTs to make sure that PHP was writing what it should. It was running on the Oracle box and PHP was connecting over IP. i can't explain the behaviour, i can only tell you what the symptoms were. Note that OP's problem is specific to AFP and you won't see > such behavior e.g. on Linux with ext4 over NFS. I believe you won't > see such behavior on Windows over Samba either. > That might be, but the general category of problem is not specific to the combination of sqlite3/AFP. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell tool locks database ? AFP access a problem ?
> i saw the same behaviour with Oracle's CLI client
> ("sqlplus") - as long as i had sqlplus opened and connected, my PHP pages
> couldn't insert any data.
>
> i.e. this type of problem isn't limited to sqlite3.
This is completely unrelated because Oracle works differently. And if
you saw this locking most probably you had an open transaction in the
sqlplus. Note that OP's problem is specific to AFP and you won't see
such behavior e.g. on Linux with ext4 over NFS. I believe you won't
see such behavior on Windows over Samba either.
Pavel
On Wed, Aug 24, 2011 at 12:02 PM, Stephan Beal wrote:
> On Wed, Aug 24, 2011 at 3:51 PM, Simon Slavin wrote:
>
>> SQLite3::query(): Unable to execute statement: database is locked in
>> [...]readrec.php
>>
>
> For what it's worth: while porting a customer's PHP app from MySQL to Oracle
> early this year, i saw the same behaviour with Oracle's CLI client
> ("sqlplus") - as long as i had sqlplus opened and connected, my PHP pages
> couldn't insert any data.
>
> i.e. this type of problem isn't limited to sqlite3.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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
Re: [sqlite] Shell tool locks database ? AFP access a problem ?
On Wed, Aug 24, 2011 at 3:51 PM, Simon Slavin wrote:
> SQLite3::query(): Unable to execute statement: database is locked in
> [...]readrec.php
>
For what it's worth: while porting a customer's PHP app from MySQL to Oracle
early this year, i saw the same behaviour with Oracle's CLI client
("sqlplus") - as long as i had sqlplus opened and connected, my PHP pages
couldn't insert any data.
i.e. this type of problem isn't limited to sqlite3.
--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell tool locks database ? AFP access a problem ?
On Wed, Aug 24, 2011 at 9:51 AM, Simon Slavin wrote: > On a Mac running OS X 10.6.8 Server. Being accessed by another Mac running > 10.6.8 Client. > > The shell tool I'm using is the one found on a standard installation of OS > X 10.6.8. It accesses the database by opening the file across an AFP > connection to the server. The shell tool can read and write the database > file itself fine. > > The app which is meant to access this database is an Apache/PHP app. It > uses the SQLite3:: object-oriented API to access the database. For > necessary reasons the PHP app opens the database twice: once with > SQLITE_OPEN_READONLY, reads stuff, then closes it, then opens again with > SQLITE_OPEN_READWRITE, reads and/or writes stuff, then closes it. All this > happens very fast, with no pauses for user-input or long processing by > anything apart from the SQLite engine. > > When either the shell tool or the PHP app are used by themselves they work > fine, but I used the SQLite shell tool for monitoring something and while > that tool had the database open my PHP application got errors > > SQLite3::query(): Unable to execute statement: database is locked in > [...]readrec.php > > So my question is ... while the shell tool has a database open across an > AFP connection, do I expect it to prevent PHP from accessing the database ? > Or is perhaps the PHP language giving the wrong error at the wrong time, > opening the database as READONLY when I asked for READWRITE because it > couldn't get READWRITE ? > You've run up against limitations of AFP. Or, more precisely, you've run up against the performance/concurrency tradeoffs that are inherent in any network filesystem. Apple has contributed working (though wildly complex) code that allows SQLite to efficiently read and write database files on AFP filesystems. But this code has the limitation that you can only open the SQLite database on one machine at a time. You can open the same database multiple times on that one machine. But you cannot have the database open simultaneously on two or more machines. One work-around you should try is to ssh into the machine that is running PHP and run the sqlite3 shell from there. I think that will work better for you. You'll get much better performance if you put your SQLite databases on a local filesystem, fwiw. > > I looked at the source code for the shell tool. I'm not good at C, but as > far as I can tell it opens the database file in a sharable manner, not in a > way as to lock it against other apps. I tested this by using two copies of > the shell tool at the same time and the work just fine, both able to have > the database open at the same time and see each-other's changes. So that > doesn't seem to be the problem. > > Simon. > ___ > 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] Shell tool locks database ? AFP access a problem ?
On a Mac running OS X 10.6.8 Server. Being accessed by another Mac running 10.6.8 Client. The shell tool I'm using is the one found on a standard installation of OS X 10.6.8. It accesses the database by opening the file across an AFP connection to the server. The shell tool can read and write the database file itself fine. The app which is meant to access this database is an Apache/PHP app. It uses the SQLite3:: object-oriented API to access the database. For necessary reasons the PHP app opens the database twice: once with SQLITE_OPEN_READONLY, reads stuff, then closes it, then opens again with SQLITE_OPEN_READWRITE, reads and/or writes stuff, then closes it. All this happens very fast, with no pauses for user-input or long processing by anything apart from the SQLite engine. When either the shell tool or the PHP app are used by themselves they work fine, but I used the SQLite shell tool for monitoring something and while that tool had the database open my PHP application got errors SQLite3::query(): Unable to execute statement: database is locked in [...]readrec.php So my question is ... while the shell tool has a database open across an AFP connection, do I expect it to prevent PHP from accessing the database ? Or is perhaps the PHP language giving the wrong error at the wrong time, opening the database as READONLY when I asked for READWRITE because it couldn't get READWRITE ? I looked at the source code for the shell tool. I'm not good at C, but as far as I can tell it opens the database file in a sharable manner, not in a way as to lock it against other apps. I tested this by using two copies of the shell tool at the same time and the work just fine, both able to have the database open at the same time and see each-other's changes. So that doesn't seem to be the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] saving pragma states in database
You could always create a table that stores the pragma values in which you're interested, then have code that checks on start up to set those pragmas to those values. On Tue, Aug 23, 2011 at 1:24 PM, Erik Lechak wrote: > Hello all, > > Is there a way to save pragma states to the database? > > I have a delete trigger that I would like to fire off on a delete ( > actually a replace), but I need the database to maintain the "pragma > recursive_triggers=1" state. Otherwise the trigger does not get > fired. I would just like the database to remember that I set the > state to true. > > Here is some example code. If it works when "pragma > recursive_triggers=1", but not when "pragma recursive_triggers=0": > > > drop table abc; > drop table abc_history; > > create table if not exists abc (id integer primary key,a text, b text, c > text); > create table if not exists abc_history as select * from abc where rowid=-1; > > create trigger if not exists abc_delete_trigger > before delete on abc > begin > insert into abc_history select * from abc where rowid=old.rowid ; > end; > > create trigger if not exists abc_update_trigger > before update on abc > begin > insert into abc_history select * where rowid=old.rowid ; > end; > > insert into abc values(1,'xa','y','z'); > insert into abc values(2,'xb','y','z'); > insert into abc values(3,'xc','y','z'); > insert into abc values(4,'xd','y','z'); > > replace into abc values(1,'xg','y','z'); > > Thanks, > Erik Lechak > ___ > 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
Re: [sqlite] Re indexing (if such a thing exist) performance
On 24 Aug 2011, at 1:49pm, LiranR wrote: > I meant that the two PK columns are actually primary key of (id, > timestamp).. Okay. That's probably not what you want to do. If you think about your data you probably want just one of them as the primary key. The only way you would need both of them in the primary key is if it's possible to have two different IDs with the same TimeStamp AND two different TimeStamps with the same ID > What i don't understand, is why do i need primary keys as timeStamp No. > if not > to make an index tree of my table rows ? Does the PK doesn't build an index > tree of the rows so i can get a row faster The Primary Key, the way you're using is, is nothing more than a CREATE UNIQUE INDEX ... I don't think you need to use one at all. Let SQLite use the ID column as its primary key as normal and if you want to search quickly on the TimeStamps column just make an index on it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement failure
On 24 Aug 2011, at 1:13pm, a.azzol...@custom.it wrote: > On my laptop integrity_check fail > >> PRAGMA integrity_check >> returns >> >> *** in database main *** >> rowid 0 missing from index JournalDateIndex >> rowid 0 missing from index sqlite_autoindex_Journal_1 >> wrong # of entries in index JournalDateIndex >> wrong # of entries in index sqlite_autoindex_Journal_1 > > but I can continue to Insert new rows without any error. It does not matter. Once integrity_check fails, anything can go wrong: records can be lost, new data can magically appear. What you need to do is find out which operation is causing your integrity_check to fail. Start with a new database. Create your table and indexes. Put some data in it. After each command run integrity_check. Find out which command is the first one that makes integrity_check fail. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
Thanks... I meant that the two PK columns are actually primary key of (id, timestamp).. What i don't understand, is why do i need primary keys as timeStamp if not to make an index tree of my table rows ? Does the PK doesn't build an index tree of the rows so i can get a row faster? Simon Slavin-3 wrote: > > > On 24 Aug 2011, at 12:39pm, LiranR wrote: > >> ID (PK) | TimeStamp (PK) | data | data | and data ... >> - >>1 |101| float | float | float >>2 |102| float | float | float >>3 | 3 | float | float | float >>4 | 4 | float | float | float >>...|...| float | float | float > > The schema above is a little strange. You can't have two primary keys for > the same table. So either > > A) your ID column is the primary key, the TimeStamp column may or may not > be another key > B) your TimeStamp column is the primary key, the ID column may or may not > be another key > C) you have a primary key of (id,TimeStamp) which would be very unusual > >> As you can see, the table is already has been filled, and its the second >> time entering data. >> the next row to update is row 3, which will be have TimeStamp 103. >> Because the face that time stamp is always rising, i can't leave there >> only >> the number 3. > > If you are concerned about the (small amount of) extra work involved in > changing a primary key column, I think you can just have your ID column as > the primary key. > >> Also, I have to use TimeStamp as PK (Primary Key) because of >> the fact that when i want to read data from the table i search the table >> by >> timestamp. > > No, that's not what primary keys are for at all. You can search a SQL > table using any value you like. If you want to make the search fast, make > an INDEX on the column. > > CREATE INDEX myTableTimeStamp ON myTable (TimeStamp) > > 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32326182.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] Re indexing (if such a thing exist) performance
On 24 Aug 2011, at 12:39pm, LiranR wrote: > ID (PK) | TimeStamp (PK) | data | data | and data ... > - >1 |101| float | float | float >2 |102| float | float | float >3 | 3 | float | float | float >4 | 4 | float | float | float >...|...| float | float | float The schema above is a little strange. You can't have two primary keys for the same table. So either A) your ID column is the primary key, the TimeStamp column may or may not be another key B) your TimeStamp column is the primary key, the ID column may or may not be another key C) you have a primary key of (id,TimeStamp) which would be very unusual > As you can see, the table is already has been filled, and its the second > time entering data. > the next row to update is row 3, which will be have TimeStamp 103. > Because the face that time stamp is always rising, i can't leave there only > the number 3. If you are concerned about the (small amount of) extra work involved in changing a primary key column, I think you can just have your ID column as the primary key. > Also, I have to use TimeStamp as PK (Primary Key) because of > the fact that when i want to read data from the table i search the table by > timestamp. No, that's not what primary keys are for at all. You can search a SQL table using any value you like. If you want to make the search fast, make an INDEX on the column. CREATE INDEX myTableTimeStamp ON myTable (TimeStamp) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement failure
On my laptop integrity_check fail > PRAGMA integrity_check > returns > > *** in database main *** > rowid 0 missing from index JournalDateIndex > rowid 0 missing from index sqlite_autoindex_Journal_1 > wrong # of entries in index JournalDateIndex > wrong # of entries in index sqlite_autoindex_Journal_1 but I can continue to Insert new rows without any error. Any new row has rowid = 1. Autoincrement algorithm does not work. I'm looking for make my DB file more 'strong' and safe against this issue Alessandro From: Simon Slavin To: General Discussion of SQLite Database Date: 23/08/2011 18.44 Subject: Re: [sqlite] Autoincrement failure On 23 Aug 2011, at 4:28pm, a.azzol...@custom.it wrote: > I cannot run a shell sqlite3 on my embedded system. I will explain it > using your example. At some point your database file is becoming corrupt. > It happen that sometime autoincrement begin to fail > and new records overwrite the one at rowid =1 > > 1|1|2|3|one|two|three replaced by 1|4|2|3|one|two|three > 2|2|2|3|one|two|three > 3|3|2|3|one|two|three Can you stop at this point, copy the resulting database file back onto your desktop computer, and run PRAGMA integrity_check() on it using the shell tool ? If it passes the integrity_check there, can you then execute the next INSERT on it, then do the same test ? Simon. ___ 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
Re: [sqlite] Re indexing (if such a thing exist) performance
You won't know until you test it. It varies for all situations. You don't know if you have a problem until you test it. Is there something stopping you from testing? What you may not understand that it's not "redindexing" -- it's deleting one node from the index and inserting another. So the indexing will be slightly slower since the 1st pass is only doing an insert. However, you're not expanding the disk file so there's potential time savings there which may make up for it. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of LiranR [liran.rit...@gmail.com] Sent: Wednesday, August 24, 2011 6:55 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance mmm... I didnt state a problem, I stated a question: How much time will it take to reindex the new timestamp? As you can see, i do only update to the row. The things that changed are the data and the time stamp. Because the time stamp is PK i guess it will have to reindex the table, no? How bad in performance is it? Black, Michael (IS) wrote: > > What you're saying makes sense. But you haven't stated a problem... > > > > Are you far enough along you can show timing beween 1st and 2nd queue > fills? > > > > How long does it take you to insert your first million? > > How long does it take you to insert your second million? > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of LiranR [liran.rit...@gmail.com] > Sent: Wednesday, August 24, 2011 6:39 AM > To: sqlite-users@sqlite.org > Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance > > > I use: > > ID (PK) | TimeStamp (PK) | data | data | and data ... > - > 1 |101 | float | float | float > 2 |102 | float | float | float > 3 | 3| float | float | float > 4 | 4| float | float | float > ... |... | float | float | float > > As you can see, the table is already has been filled, and its the second > time entering data. > the next row to update is row 3, which will be have TimeStamp 103. > Because the face that time stamp is always rising, i can't leave there > only > the number 3. Also, I have to use TimeStamp as PK (Primary Key) because of > the fact that when i want to read data from the table i search the table > by > timestamp. > > > Kees Nuyt wrote: >> >> On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR >> wrote: >> >>> >>> Hi, Thanks for the answer, but i think you didn't understand what i >>> asked. >>> >>> I use primary key index in my table. >>> When i finish to fill the table, row after row, i want to start all over >>> again and update the table from row 1 to row 100. The difference is >>> that >>> in the second time, the index of the first row wont be 1, but 101, >>> and >>> then i will update the second row and it's index will be 102, and so >>> on... (when i reach the 100 row, i update it with the index 200, >>> and >>> than again, first row will be update with index 201). >>> My question is - Does it take heavy performance to reindex the row every >>> time (because the row get another index number - in this example, a >>> number >>> that is bigger by 100 than the last row's index number). >> >> It will take about the same effort as deleting and inserting a >> row, or about twice as much as inserting a row. >> Show us your schema and we may be able to advise on optimizations. >> (output of .schema in the sqlite3 command line tool will do). >> >> Note: index is a resserved word. Using it as a column name is >> confusing. >> -- >> ( Kees Nuyt >> ) >> c[_] >> ___ >> 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325716.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-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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p3
Re: [sqlite] Re indexing (if such a thing exist) performance
mmm... I didnt state a problem, I stated a question: How much time will it take to reindex the new timestamp? As you can see, i do only update to the row. The things that changed are the data and the time stamp. Because the time stamp is PK i guess it will have to reindex the table, no? How bad in performance is it? Black, Michael (IS) wrote: > > What you're saying makes sense. But you haven't stated a problem... > > > > Are you far enough along you can show timing beween 1st and 2nd queue > fills? > > > > How long does it take you to insert your first million? > > How long does it take you to insert your second million? > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of LiranR [liran.rit...@gmail.com] > Sent: Wednesday, August 24, 2011 6:39 AM > To: sqlite-users@sqlite.org > Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance > > > I use: > > ID (PK) | TimeStamp (PK) | data | data | and data ... > - > 1 |101 | float | float | float > 2 |102 | float | float | float > 3 | 3| float | float | float > 4 | 4| float | float | float > ... |... | float | float | float > > As you can see, the table is already has been filled, and its the second > time entering data. > the next row to update is row 3, which will be have TimeStamp 103. > Because the face that time stamp is always rising, i can't leave there > only > the number 3. Also, I have to use TimeStamp as PK (Primary Key) because of > the fact that when i want to read data from the table i search the table > by > timestamp. > > > Kees Nuyt wrote: >> >> On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR >> wrote: >> >>> >>> Hi, Thanks for the answer, but i think you didn't understand what i >>> asked. >>> >>> I use primary key index in my table. >>> When i finish to fill the table, row after row, i want to start all over >>> again and update the table from row 1 to row 100. The difference is >>> that >>> in the second time, the index of the first row wont be 1, but 101, >>> and >>> then i will update the second row and it's index will be 102, and so >>> on... (when i reach the 100 row, i update it with the index 200, >>> and >>> than again, first row will be update with index 201). >>> My question is - Does it take heavy performance to reindex the row every >>> time (because the row get another index number - in this example, a >>> number >>> that is bigger by 100 than the last row's index number). >> >> It will take about the same effort as deleting and inserting a >> row, or about twice as much as inserting a row. >> Show us your schema and we may be able to advise on optimizations. >> (output of .schema in the sqlite3 command line tool will do). >> >> Note: index is a resserved word. Using it as a column name is >> confusing. >> -- >> ( Kees Nuyt >> ) >> c[_] >> ___ >> 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325716.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-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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325797.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] Re indexing (if such a thing exist) performance
And...if you don't use ID at all you can get rid of it. CREATE TABLE mystuff (TimeStamp INTEGER PRIMARY KEY,Data1 FLOAT, ...) TimeStamp will be an alias for rowid. http://www.sqlite.org/autoinc.html That will get rid of one index and one field which will save you at least 16MB of memory/disk. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of LiranR [liran.rit...@gmail.com] Sent: Wednesday, August 24, 2011 6:39 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance I use: ID (PK) | TimeStamp (PK) | data | data | and data ... - 1 |101 | float | float | float 2 |102 | float | float | float 3 | 3| float | float | float 4 | 4| float | float | float ... |... | float | float | float As you can see, the table is already has been filled, and its the second time entering data. the next row to update is row 3, which will be have TimeStamp 103. Because the face that time stamp is always rising, i can't leave there only the number 3. Also, I have to use TimeStamp as PK (Primary Key) because of the fact that when i want to read data from the table i search the table by timestamp. Kees Nuyt wrote: > > On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR > wrote: > >> >> Hi, Thanks for the answer, but i think you didn't understand what i >> asked. >> >> I use primary key index in my table. >> When i finish to fill the table, row after row, i want to start all over >> again and update the table from row 1 to row 100. The difference is >> that >> in the second time, the index of the first row wont be 1, but 101, >> and >> then i will update the second row and it's index will be 102, and so >> on... (when i reach the 100 row, i update it with the index 200, >> and >> than again, first row will be update with index 201). >> My question is - Does it take heavy performance to reindex the row every >> time (because the row get another index number - in this example, a >> number >> that is bigger by 100 than the last row's index number). > > It will take about the same effort as deleting and inserting a > row, or about twice as much as inserting a row. > Show us your schema and we may be able to advise on optimizations. > (output of .schema in the sqlite3 command line tool will do). > > Note: index is a resserved word. Using it as a column name is > confusing. > -- > ( Kees Nuyt > ) > c[_] > ___ > 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325716.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
What you're saying makes sense. But you haven't stated a problem... Are you far enough along you can show timing beween 1st and 2nd queue fills? How long does it take you to insert your first million? How long does it take you to insert your second million? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of LiranR [liran.rit...@gmail.com] Sent: Wednesday, August 24, 2011 6:39 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance I use: ID (PK) | TimeStamp (PK) | data | data | and data ... - 1 |101 | float | float | float 2 |102 | float | float | float 3 | 3| float | float | float 4 | 4| float | float | float ... |... | float | float | float As you can see, the table is already has been filled, and its the second time entering data. the next row to update is row 3, which will be have TimeStamp 103. Because the face that time stamp is always rising, i can't leave there only the number 3. Also, I have to use TimeStamp as PK (Primary Key) because of the fact that when i want to read data from the table i search the table by timestamp. Kees Nuyt wrote: > > On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR > wrote: > >> >> Hi, Thanks for the answer, but i think you didn't understand what i >> asked. >> >> I use primary key index in my table. >> When i finish to fill the table, row after row, i want to start all over >> again and update the table from row 1 to row 100. The difference is >> that >> in the second time, the index of the first row wont be 1, but 101, >> and >> then i will update the second row and it's index will be 102, and so >> on... (when i reach the 100 row, i update it with the index 200, >> and >> than again, first row will be update with index 201). >> My question is - Does it take heavy performance to reindex the row every >> time (because the row get another index number - in this example, a >> number >> that is bigger by 100 than the last row's index number). > > It will take about the same effort as deleting and inserting a > row, or about twice as much as inserting a row. > Show us your schema and we may be able to advise on optimizations. > (output of .schema in the sqlite3 command line tool will do). > > Note: index is a resserved word. Using it as a column name is > confusing. > -- > ( Kees Nuyt > ) > c[_] > ___ > 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325716.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
I'd like to hear what you're doing and why you think SQLite is the way to do it. And what are your speed requirements? You are apparently worried about speed but never tested to see if SQLite was fast enough. You could've tested it yourself in a matter of an hour or so. I imagine an update is going to be faster than the original insert since you're not expanding the data file at all. Are you sure you need/want a database to instantiate your circular queue? Since you're talking about fixed-sized records why not a binary file? It would be blazingly fast and not need any indexing at all (you just maintain the index in memory and do a binary search on it). Are you actually doing some query which demands SQL? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of LiranR [liran.rit...@gmail.com] Sent: Wednesday, August 24, 2011 4:58 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Re indexing (if such a thing exist) performance Hi, Thanks for the answer, but i think you didn't understand what i asked. I use primary key index in my table. When i finish to fill the table, row after row, i want to start all over again and update the table from row 1 to row 100. The difference is that in the second time, the index of the first row wont be 1, but 101, and then i will update the second row and it's index will be 102, and so on... (when i reach the 100 row, i update it with the index 200, and than again, first row will be update with index 201). My question is - Does it take heavy performance to reindex the row every time (because the row get another index number - in this example, a number that is bigger by 100 than the last row's index number). Vikasumit wrote: > > Hello, > > IF you create index Primary key it automatically index the Information, > but > if not you can always create a separate index for your field(s). When > Index > are present at time of insertion or update index will also get update to > include information. That do not REindex complete table. So performance > won't be an issue if you use Index. But if you don't use index the > performace will be WAY TOO bad and every request can take lot of time. I > did > about 1 M row update in 9minute with indexes and other stuffs. But without > index same code took 30 minute to update only 40K records. > > Sumit > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LiranR > Sent: 24 August 2011 14:11 > To: sqlite-users@sqlite.org > Subject: [sqlite] Re indexing (if such a thing exist) performance > > > Hi all!! > In my project, i insert rows, one by one, to a fixed size table (100 > rows for example). When i reach the end of the table, i enter data to the > first row and then the seocond and so on. If i have indexes (primary > keys), > lets say from 1 to 100, and now i reached the end, and update the > first > row with index 1001, does it have to reindex? > How much time will it take to reindex? because i will update the second > row > later with index 1002 and i have to reindex it too, if its true, and i am > worry about the performance of such a thing. > > -- > View this message in context: > http://old.nabble.com/Reindexing-%28if-such-a-thing-exist%29-performance-tp3 > 2324832p32324832.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-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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325213.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
I use: ID (PK) | TimeStamp (PK) | data | data | and data ... - 1 |101 | float | float | float 2 |102 | float | float | float 3 | 3| float | float | float 4 | 4| float | float | float ... |... | float | float | float As you can see, the table is already has been filled, and its the second time entering data. the next row to update is row 3, which will be have TimeStamp 103. Because the face that time stamp is always rising, i can't leave there only the number 3. Also, I have to use TimeStamp as PK (Primary Key) because of the fact that when i want to read data from the table i search the table by timestamp. Kees Nuyt wrote: > > On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR > wrote: > >> >> Hi, Thanks for the answer, but i think you didn't understand what i >> asked. >> >> I use primary key index in my table. >> When i finish to fill the table, row after row, i want to start all over >> again and update the table from row 1 to row 100. The difference is >> that >> in the second time, the index of the first row wont be 1, but 101, >> and >> then i will update the second row and it's index will be 102, and so >> on... (when i reach the 100 row, i update it with the index 200, >> and >> than again, first row will be update with index 201). >> My question is - Does it take heavy performance to reindex the row every >> time (because the row get another index number - in this example, a >> number >> that is bigger by 100 than the last row's index number). > > It will take about the same effort as deleting and inserting a > row, or about twice as much as inserting a row. > Show us your schema and we may be able to advise on optimizations. > (output of .schema in the sqlite3 command line tool will do). > > Note: index is a resserved word. Using it as a column name is > confusing. > -- > ( Kees Nuyt > ) > c[_] > ___ > 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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325716.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] Re indexing (if such a thing exist) performance
On Wed, 24 Aug 2011 02:58:58 -0700 (PDT), LiranR wrote: > > Hi, Thanks for the answer, but i think you didn't understand what i asked. > > I use primary key index in my table. > When i finish to fill the table, row after row, i want to start all over > again and update the table from row 1 to row 100. The difference is that > in the second time, the index of the first row wont be 1, but 101, and > then i will update the second row and it's index will be 102, and so > on... (when i reach the 100 row, i update it with the index 200, and > than again, first row will be update with index 201). > My question is - Does it take heavy performance to reindex the row every > time (because the row get another index number - in this example, a number > that is bigger by 100 than the last row's index number). It will take about the same effort as deleting and inserting a row, or about twice as much as inserting a row. Show us your schema and we may be able to advise on optimizations. (output of .schema in the sqlite3 command line tool will do). Note: index is a resserved word. Using it as a column name is confusing. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
Hello, Do you truncate the table before iterating again ? I don't think it really effect performance, if you start from 1 or 1001. They are just number. You don't need to re-index your table. Indexes are created automatically at time of insertion/update/delete. So even if you delete old Data before starting new iteration it will do re-index at no additional cost. In my test I create a Index in database which have 1Million rows on numeric field took less than a minute. But if you create index before insertion, then there is nothing like re-index. And it works just same/similar speed. Sumit -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LiranR Sent: 24 August 2011 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re indexing (if such a thing exist) performance Hi, Thanks for the answer, but i think you didn't understand what i asked. I use primary key index in my table. When i finish to fill the table, row after row, i want to start all over again and update the table from row 1 to row 100. The difference is that in the second time, the index of the first row wont be 1, but 101, and then i will update the second row and it's index will be 102, and so on... (when i reach the 100 row, i update it with the index 200, and than again, first row will be update with index 201). My question is - Does it take heavy performance to reindex the row every time (because the row get another index number - in this example, a number that is bigger by 100 than the last row's index number). Vikasumit wrote: > > Hello, > > IF you create index Primary key it automatically index the Information, > but > if not you can always create a separate index for your field(s). When > Index > are present at time of insertion or update index will also get update to > include information. That do not REindex complete table. So performance > won't be an issue if you use Index. But if you don't use index the > performace will be WAY TOO bad and every request can take lot of time. I > did > about 1 M row update in 9minute with indexes and other stuffs. But without > index same code took 30 minute to update only 40K records. > > Sumit > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LiranR > Sent: 24 August 2011 14:11 > To: sqlite-users@sqlite.org > Subject: [sqlite] Re indexing (if such a thing exist) performance > > > Hi all!! > In my project, i insert rows, one by one, to a fixed size table (100 > rows for example). When i reach the end of the table, i enter data to the > first row and then the seocond and so on. If i have indexes (primary > keys), > lets say from 1 to 100, and now i reached the end, and update the > first > row with index 1001, does it have to reindex? > How much time will it take to reindex? because i will update the second > row > later with index 1002 and i have to reindex it too, if its true, and i am > worry about the performance of such a thing. > > -- > View this message in context: > http://old.nabble.com/Reindexing-%28if-such-a-thing-exist%29-performance-tp3 > 2324832p32324832.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-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/Reindexing-%28if-such-a-thing-exist%29-performance-tp3 2324832p32325213.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re indexing (if such a thing exist) performance
Hi, Thanks for the answer, but i think you didn't understand what i asked. I use primary key index in my table. When i finish to fill the table, row after row, i want to start all over again and update the table from row 1 to row 100. The difference is that in the second time, the index of the first row wont be 1, but 101, and then i will update the second row and it's index will be 102, and so on... (when i reach the 100 row, i update it with the index 200, and than again, first row will be update with index 201). My question is - Does it take heavy performance to reindex the row every time (because the row get another index number - in this example, a number that is bigger by 100 than the last row's index number). Vikasumit wrote: > > Hello, > > IF you create index Primary key it automatically index the Information, > but > if not you can always create a separate index for your field(s). When > Index > are present at time of insertion or update index will also get update to > include information. That do not REindex complete table. So performance > won't be an issue if you use Index. But if you don't use index the > performace will be WAY TOO bad and every request can take lot of time. I > did > about 1 M row update in 9minute with indexes and other stuffs. But without > index same code took 30 minute to update only 40K records. > > Sumit > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LiranR > Sent: 24 August 2011 14:11 > To: sqlite-users@sqlite.org > Subject: [sqlite] Re indexing (if such a thing exist) performance > > > Hi all!! > In my project, i insert rows, one by one, to a fixed size table (100 > rows for example). When i reach the end of the table, i enter data to the > first row and then the seocond and so on. If i have indexes (primary > keys), > lets say from 1 to 100, and now i reached the end, and update the > first > row with index 1001, does it have to reindex? > How much time will it take to reindex? because i will update the second > row > later with index 1002 and i have to reindex it too, if its true, and i am > worry about the performance of such a thing. > > -- > View this message in context: > http://old.nabble.com/Reindexing-%28if-such-a-thing-exist%29-performance-tp3 > 2324832p32324832.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-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/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32325213.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] Re indexing (if such a thing exist) performance
Hello, IF you create index Primary key it automatically index the Information, but if not you can always create a separate index for your field(s). When Index are present at time of insertion or update index will also get update to include information. That do not REindex complete table. So performance won't be an issue if you use Index. But if you don't use index the performace will be WAY TOO bad and every request can take lot of time. I did about 1 M row update in 9minute with indexes and other stuffs. But without index same code took 30 minute to update only 40K records. Sumit -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LiranR Sent: 24 August 2011 14:11 To: sqlite-users@sqlite.org Subject: [sqlite] Re indexing (if such a thing exist) performance Hi all!! In my project, i insert rows, one by one, to a fixed size table (100 rows for example). When i reach the end of the table, i enter data to the first row and then the seocond and so on. If i have indexes (primary keys), lets say from 1 to 100, and now i reached the end, and update the first row with index 1001, does it have to reindex? How much time will it take to reindex? because i will update the second row later with index 1002 and i have to reindex it too, if its true, and i am worry about the performance of such a thing. -- View this message in context: http://old.nabble.com/Reindexing-%28if-such-a-thing-exist%29-performance-tp3 2324832p32324832.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re indexing (if such a thing exist) performance
Hi all!! In my project, i insert rows, one by one, to a fixed size table (100 rows for example). When i reach the end of the table, i enter data to the first row and then the seocond and so on. If i have indexes (primary keys), lets say from 1 to 100, and now i reached the end, and update the first row with index 1001, does it have to reindex? How much time will it take to reindex? because i will update the second row later with index 1002 and i have to reindex it too, if its true, and i am worry about the performance of such a thing. -- View this message in context: http://old.nabble.com/Reindexing-%28if-such-a-thing-exist%29-performance-tp32324832p32324832.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
