Re: [sqlite] Multi column ORDER BY across table peformance problem....
> 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
Re: [sqlite] Violating Primary key Constraint
I copied and pasted the lines from your email into SQLite and got a
different response from SQLite than you claim you got.
sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
...> text);
sqlite> insert into secondtable (label) VALUES ('foo');
SQL error: secondtable.recid may not be NULL
sqlite>
Check you used INT, not INTEGER. Check you used NOT NULL. Check your
version of SQLite.
If your output below really did come from SQLite and you are using a
recent version, then I doubt I can help.
--
Ian
Satish wrote:
> Hi!
> You Said me to create the field as "INT PRIMARY KEY NOT NULL" instead of
> "INTEGER PRIMARY KEY NOT NULL".Even we spell differently they are working
> same.it is also auto incrementing.
>
> sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
> label text);
> sqlite> insert into dummytable (label) VALUES ('foo');
> sqlite> select * from dummytable;
> 1|foo
> sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
> text);
> sqlite> insert into secondtable (label) VALUES ('foo');
> 1|foo
> I am not getting any constraint failed error
>
> Regards,
> Satish.G
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Ian Walters
> Sent: Tuesday, November 25, 2008 11:17 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Violating Primary key Constraint
>
> Hi Satish,
>
> I just re-read your original email and Igor's response seemed
> appropriate. The original email read as if you were reporting a problem
> rather than asking a question.
>
> As for you question:
>
> sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
> label text);
> sqlite> insert into dummytable (label) VALUES ('foo');
> sqlite> select * from dummytable;
> 1|foo
> sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
> text);
> sqlite> insert into secondtable (label) VALUES ('foo');
> SQL error: secondtable.recid may not be NULL
>
> --
> Ian
>
>
>
> Satish wrote:
>> Hi Igor!
>>
>> Thanks for the reply.The thing I need is I don't want the field to be
>> auto incremented it should show me the error that constraint violated even
>> if I give NULL or empty value.how can I do this.Don't send me links which
>> doesn't have matter at all.First try to understand the problem and then
> give
>> me reply.
>>
>> Regards,
>> Satish.G
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
>> Sent: Tuesday, November 25, 2008 10:59 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Violating Primary key Constraint
>>
>> "Satish" <[EMAIL PROTECTED]> wrote in
>> message news:[EMAIL PROTECTED]
>>> I have created a table in sqlite.Upto my knowledge a column which is
>>> declared as primary key will not accept null and even if I don't give
>>> any value to that field an error occurs that violating the constraint
>>>
>>> For Example
>>>
>>> Create table emp(empno integer PRIMARY KEY,...,...)
>>>
>>> . Even if I give u a NULL as a value to the field that is
>>> declared as primary key .it is accepting
>> http://sqlite.org/autoinc.html
>
> ___
> 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] Violating Primary key Constraint
Hi Satish,
I just re-read your original email and Igor's response seemed
appropriate. The original email read as if you were reporting a problem
rather than asking a question.
As for you question:
sqlite> create table dummytable (recid integer PRIMARY KEY NOT NULL,
label text);
sqlite> insert into dummytable (label) VALUES ('foo');
sqlite> select * from dummytable;
1|foo
sqlite> create table secondtable (recid INT PRIMARY KEY NOT NULL, label
text);
sqlite> insert into secondtable (label) VALUES ('foo');
SQL error: secondtable.recid may not be NULL
--
Ian
Satish wrote:
> Hi Igor!
>
> Thanks for the reply.The thing I need is I don't want the field to be
> auto incremented it should show me the error that constraint violated even
> if I give NULL or empty value.how can I do this.Don't send me links which
> doesn't have matter at all.First try to understand the problem and then give
> me reply.
>
> Regards,
> Satish.G
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
> Sent: Tuesday, November 25, 2008 10:59 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Violating Primary key Constraint
>
> "Satish" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> I have created a table in sqlite.Upto my knowledge a column which is
>> declared as primary key will not accept null and even if I don't give
>> any value to that field an error occurs that violating the constraint
>>
>> For Example
>>
>> Create table emp(empno integer PRIMARY KEY,...,...)
>>
>> . Even if I give u a NULL as a value to the field that is
>> declared as primary key .it is accepting
>
> http://sqlite.org/autoinc.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About "select * from table ....." with Sqlite
yoky wrote: > Hi all, > I create a table with 200 columns, then use sqlite3_prepare() and > sqlite3_step()to get a record by this: "select * from tbl where ID =.. ". > Test in my > embedded system,The total time is about 65ms,it is too long for the > performance > requirement, I found sqlite3_prepare() time is about 50ms. > Then test the code with VTune,I found lookupName() function in Sqlite > take about 50% of the total time, and sqlite3StrICmp() occupied most > execution time > (867/922, see the follow pics ) in lookupName(). > The follow is the graph info link generated by VTune: >http://img15.tianya.cn/photo/2008/11/18/10872206_2807293.jpg >http://img15.tianya.cn/photo/2008/11/18/10872205_2807293.jpg >If use "select * from tbl where" ,tbl is a table with 200 > columns, > sqlite3StrICmp() will be executed more than 2 times inlookupName(), > this is the main bottleneck in sqlite3_prepare(). >I think maybe Sqlite should do a special optimization for "select * > from tbl ...", > and there is no necessary to compare the column names expanded by "*" with > the table > column names. > Are there some efficient ways to get a whole record with many many > columns ? the functions you are looking for are sqlite3_reset and sqlite3_bind* Prepare the query just once. Use bound variables to change the id you are looking up. Use _reset to release any locks when not running the query. -- Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Satish wrote: > Hi! > > Really I am Serious about this I am very new to database and I want > to use sqllite.so I want to know.THE Reason why asked this question is I > have seen a video of google tech bytes in that the presenter said that > sqllite doesn't support foreign key and alter table.is this True.i want to > know based on this I can proceed further. http://www.sqlite.org/lang_altertable.html http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause As for wrappers, I like Qt, but then I am biased in this regard. More seriously though, don't use a wrapper unless you want to change SQL database back ends later. You only really get portability and integration with other code related to said wrapper. -- Ian p.s. sorry for the blatant plug. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] look up by row on ordered table... must be very fast
Igor Tandetnik wrote: > "Ian Walters" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> What I'm trying to do is create an ordered table in SQLite that I can >> then look up by row number. If performance isn't considered this is >> actually quite easy. For example >> >> CREATE TEMPORARY TABLE mytable AS ... complex select statement ordered >> by label, recordId ; >> CREATE INDEX ON mytable (label, recordId); >> >> SELECT recordId, label FROM mytable ORDER BY label, recordId LIMIT 1 >> OFFSET row; > > See if this helps: > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > Thanks for the suggestion. That indeed works when you know what the last title was, and I have code that handles that logic for the more complex select (if you want to see it, it is GPL, release 4.3 of Qtopia, src/libraries/qtopiapim/qsqlpimtablemodel.cpp). One of my goals with the design I'm investigating is to reduce the complexity of the C++ code while increasing flexibility of the schema. Perhaps I won't be able to offload quite as much onto SQLite as I hoped. btw, thanks also to the SQLite coders for creating such a stable, fast, small, powerful and incredibly good value product. Very little I've looked at comes close to SQLite on portable devices. -- Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] look up by row on ordered table... must be very fast
Hi, What I'm trying to do is create an ordered table in SQLite that I can then look up by row number. If performance isn't considered this is actually quite easy. For example CREATE TEMPORARY TABLE mytable AS ... complex select statement ordered by label, recordId ; CREATE INDEX ON mytable (label, recordId); SELECT recordId, label FROM mytable ORDER BY label, recordId LIMIT 1 OFFSET row; However I'm very concerned about performance. For instance, I don't want to re prepare the query, so 'row' would need to be a bound value in the above. Given that the C++ API for SQLite only has _step, another worry is that is what OFFSET might do internally. O(N) lookup per row isn't good enough. My other thought was to use ROWID, e.g. "WHERE ROWID = :row", but the warnings in that documentation seem to indicate it isn't good enough to use as a row number. Alternatively suggestions on how to add a row as an explicit column in the select statement for the temporary table would also be useful. Again, performance matters. It doesn't have to be as fast as the row lookup, but anything involving 'count(*) ' isn't going to cut it. Something like 'SELECT ..., rowNumber++ FROM ', although I realize there is no ++ operator in SQLite. As a side question, if I order the select statement when creating the table, will that order the inserts, or will that be ignored. If it isn't ignored I may be able to do this via a sqlite3_create_function Thanks in advance for any help, -- Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
