Re: [sqlite] Newbie question
I'm relatively newbie too, but just curious, is it anything to do with (select id from tbl2 where name="Joe") not being guarenteed to return a scaler? Ie only a single value? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: 18 March 2009 15:47 To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Igor Tandetnik : > Dermot wrote: >> I want to do a select query a bit like this: >> >> SELECT * FROM tbl1 WHERE description="someval" AND >> foreign_key_id=(select id from tbl2 where name="Joe"); >> >> This gives me a syntax error and my other efforts are not yielding >> results. > > What's the text of the error message? SELECT * FROM tbl1 WHERE description="someval" AND foreign_key_id=(select id from tbl2 where name="Joe"); This attempt give the error: SQL error: near "=": syntax error SELECT * FROM tbl1 WHERE description="someval" AND foreign_key_id='select id from tbl2 where name="Joe"'; This return nothing, although it should. If I break the queries into 2 separate select statements they verify. This is SQLite 3.5.1. Thanx, Dp. ___ 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] Terrible performance for one of our tables
Out of interest why must it completely READ each entire row? Is it because '*' has been used? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: 19 November 2008 12:05 To: General Discussion of SQLite Database Subject: Re: [sqlite] Terrible performance for one of our tables On Nov 19, 2008, at 3:08 AM, Jens Miltner wrote: > Hi, > > we're seeing terrible performance problems when fetching data from one > of our tables: > The table contains roughly 1.2 Million rows and a plain "SELECT > COUNT(*) FROM t" query takes 8 minutes to finish. That's because "SELECT count(*) FROM t" has to read and count every row in the table - all 1.2 million rows. And this involves reading all 1.2 million rows from disk. If each row is 100 bytes in size, that involves reading 120MB. An SQLite database does *not* maintain the number of rows in a table as separate metadata. Many client/server database engines do maintain such metadata. When I was designing the SQLite file format, I deliberately choose to omit such metadata because including it would slow down inserts and deletes. If you frequently need to know how many rows are in a certain table, use insert and delete triggers to maintain the count yourself in a separate table. Then just read out the count from the separate table when you need it, rather than recomputing it by reading all 1.2 million rows of the original table. D. Richard Hipp [EMAIL PROTECTED] ___ 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] Profiling
I am registering a trace function and a profile function and both aoppear to be getting correctly called back and the text looks in order. How ever the elapsed time in the profile always shows zero. Does any one have a guess at what I may have left out? Regards, Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Logging
I've taken a brief look at this in the source. Do I have to pass function pointers (for functions that implement the appropriate logging) to these functions (the ones below) then my functions get called back omn the appropriate activity? I think I could use an example to help me along. Can some one point me to a simple example somewhere? Is this & rebuild the only way to achieve logging? Regards, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: 04 September 2008 10:33 To: General Discussion of SQLite Database Subject: Re: [sqlite] Logging -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hardy, Andrew wrote: > What's the most efficient way of getting a log file of the db activity > on your sql db over a time period. Can you get timings against these > acrtivities? You have to write code/callbacks that interface with the library. sqlite3_trace() will get you the text of queries while sqlite3_profile() will get you the text and timings. If you also want the bound parameters then you'll need to note them in your calls to sqlite3_prepare. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIv6s6mOOfHg372QQRAjRDAJ9tBVlHVEX5EfqN6+kMG8fyn7zdqQCfW9p5 awhgF/OzwCSSOPmQGHG2b9I= =RqBM -END PGP SIGNATURE- ___ 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] Logging
What's the most efficient way of getting a log file of the db activity on your sql db over a time period. Can you get timings against these acrtivities? Regards, Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Entity Relationship Diagram Tool for SQLite
Not sure if this is what David is getting at, iro specifically saying sqlite, but I'd be interested in something that generates a schema diagram from an existing sqlite db file. Or are there tools that can do this from the text of a dump, if the sql is standard enough? Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams Sent: 03 September 2008 19:06 To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] Entity Relationship Diagram Tool for SQLite What is so unique to SQLite that it would require its very own Entity Relationship engine? Relational databases are relational databases. Granted some are "more" relational than others, but that is where the designer's mind is required to function above a video game level. Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David Abrames Sent: Wednesday, September 03, 2008 12:55 PM To: sqlite-users@sqlite.org Subject: [sqlite] Entity Relationship Diagram Tool for SQLite Hello, I am looking for an Entity Relationship Diagram Tool for SQLite. Thank you in advance. David Abrames ___ 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] Manifest Typing performance impact?
Is there any way to log conversions to highlite any issues that would have been hilighted by failure with strict typing? It is my plan to match the column type & data stored type, but clearly if any conversions are occurring I will be unaware. Kind Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 27 August 2008 15:45 To: General Discussion of SQLite Database Subject: Re: [sqlite] Manifest Typing performance impact? Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any > issues that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ 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] Manifest Typing performance impact?
Is there a performance hit assosiated with manifest typing? Is it right that although info on the sqlite site suggests there is an avilable mode that supports strict typring, this is not infact the case? If there is a performance hit, what are the best ways to minimise this? And is there any way to at least log conversions to highlite any issues that would have been hilighted by failure with strict typing? Hope this makes sense. Newbie to sqlite, any hep or advice greatly appreciated. Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Manifest Typing performance impact?
Is there a performance hit assosiated with manifest typing? Is it right that although info on the sqlite site suggests there is an avilable mode that supports strict typring, this is not infact the case? If there is a performance hit, what are the best ways to minimise this? And is there any way to at least log conversions to highlite any issues that would have been hilighted by failure with strict typing? Hope this makes sense. Newbie to sqlite, any hep or advice greatly appreciated. Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
