Re: [sqlite] Troubleshooting query performance

> Don't put the PRIMARY KEY as the first column of your index. Ever. This > applies to all SQL database engines, not just SQLite. > > For that matter, don't put the PRIMARY KEY anywhere in your index. The > PRIMARY KEY will be added automatically at the end, where it belongs. > > If you

Re: [sqlite] Troubleshooting query performance

On 6 Mar 2014, at 12:20am, Joseph L. Casale wrote: > SELECT a.name, p.value > FROM p_attribute p > JOIN attribute a > ON a.id=p.aid > WHERE p.pid=? > > This returns all relevant rows I need, where table profile has ~6000 rows, > p_attribute has ~ 170k and

Re: [sqlite] Troubleshooting query performance

On Wed, Mar 5, 2014 at 7:20 PM, Joseph L. Casale wrote: > Hey guys, > > I have a query that's giving me abysmal performance and it's not > immediately > obvious to me as to what's wrong with the table structure to cause this. > > CREATE TABLE profile ( > id

Re: [sqlite] Troubleshooting nested query performance issue

Quoth Petite Abeille , on 2014-02-03 23:49:14 +0100: > Not directly related to your question, but… why oh why do people > molest their queries by gratuitously and pointlessly aliasing > perfectly good table name to meaningless random one letter codes?!? > Masochism?

Re: [sqlite] Troubleshooting nested query performance issue

On Mon, 3 Feb 2014 23:49:14 +0100 Petite Abeille wrote: > > I have a query > > Not directly related to your question, but? why oh why do people > molest their queries by gratuitously and pointlessly aliasing > perfectly good table name to meaningless random one letter

Re: [sqlite] Troubleshooting nested query performance issue

> Not directly related to your question, but… why oh why do people molest their > queries by > gratuitously and pointlessly aliasing perfectly good table name to > meaningless random > one letter codes?!? Masochism? lol, you're not wrong. This code is used in Python, and we are strict

Re: [sqlite] Troubleshooting nested query performance issue

> No. It appears to be a correlated subquery. It depends on the current row > of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus > has to be reevalatued for every row of the "d" table. Richard, After a closer look, the subquery was useless and needed to be removed.

Re: [sqlite] Troubleshooting nested query performance issue

On Feb 3, 2014, at 11:30 PM, Joseph L. Casale wrote: > I have a query Not directly related to your question, but… why oh why do people molest their queries by gratuitously and pointlessly aliasing perfectly good table name to meaningless random one letter codes?!?

Re: [sqlite] Troubleshooting nested query performance issue

On Mon, Feb 3, 2014 at 5:30 PM, Joseph L. Casale wrote: > I have a query where if I hard code the results of the nested SELECT > DICTINCT to a few > static values, it completes very fast. Leaving the select causes this > query to slow down > badly. Running an explain

Re: [sqlite] Troubleshooting...

010 12:10 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Troubleshooting... > > On 12/17/2010 6:18 PM, john darnell wrote: > > Here's that statement from my code again with the mods included that make > > the > code work: > > > >char *surbuf[100]; &

Re: [sqlite] Troubleshooting...

On 12/17/2010 6:18 PM, john darnell wrote: > Here's that statement from my code again with the mods included that make the > code work: > >char *surbuf[100]; >memset(surbuf, 0, 100); >strcpy(surbuf, CurrentName -> second.GetSurName().c_str()); >idx = -1; >

Re: [sqlite] Troubleshooting...

On 12/17/2010 5:23 PM, john darnell wrote: > I could not figure out how to pipe my info to a file (I guess I am > still a very young (at 57 years) newbie when it comes to SQLite) so I > tried something else. In my code, I hardcoded the data instead of > using variables. Here is a copy of one of

Re: [sqlite] Troubleshooting...

te.org [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of jeff archer > Sent: Sunday, December 19, 2010 4:21 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Troubleshooting... > > >> My only guess is that basic_string::c_str() doesn't really provide a > >> pointer

Re: [sqlite] Troubleshooting...

nal Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Pavel Ivanov > Sent: Saturday, December 18, 2010 9:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Troubleshooting... > > > Okay.  I work

Re: [sqlite] Troubleshooting...

> You may probably already know this but maybe I'll remind you.  The pointer > returned by c_str() is only valid in the statement where it is used or > possibly > as long as the life of the basic_string it came from. Good catch, Jeff! I thought it's so obvious that I didn't even consider that as

Re: [sqlite] Troubleshooting...

>> My only guess is that basic_string::c_str() doesn't really provide a pointer >> to >>a null-terminated c-style string, >but a facsimile of one that SQLite doesn't >>like. > >c_str() provides pointer to the data that string has with additional >null byte added at the end. That's it. Whether

Re: [sqlite] Troubleshooting...

nd later found it in the SQLLite directory from which I > was running the shell. > > R, > John > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] >> On Behalf Of Pavel Ivanov >> Sent: Frida

Re: [sqlite] Troubleshooting...

o:sqlite-users-boun...@sqlite.org] > On Behalf Of Pavel Ivanov > Sent: Friday, December 17, 2010 4:44 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Troubleshooting... > > > So you can see that when I add the hard-coded data, everything looks fine >

Re: [sqlite] Troubleshooting...

SQLite Database > Subject: Re: [sqlite] Troubleshooting... > > > So you can see that when I add the hard-coded data, everything looks fine > > in the > results of the select statement, which leads me to believe that the problem > is not > confusion between UTF8 and UTF16

Re: [sqlite] Troubleshooting...

Lite3 have problems dealing with basic_strings? > > R, > John > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] >> On Behalf Of Pavel Ivanov >> Sent: Friday, December 17, 2010 9:22 AM >

Re: [sqlite] Troubleshooting...

problems dealing with basic_strings? R, John > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Pavel Ivanov > Sent: Friday, December 17, 2010 9:22 AM > To: General Discussion of SQLite Database > Sub

Re: [sqlite] Troubleshooting...

.org [mailto:sqlite-users- > boun...@sqlite.org] > >> On Behalf Of Pavel Ivanov > >> Sent: Thursday, December 16, 2010 2:53 PM > >> To: General Discussion of SQLite Database > >> Subject: Re: [sqlite] Troubleshooting... > >> > >> > Once

Re: [sqlite] Troubleshooting...

:53 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Troubleshooting... >> >> > Once I bind the data to the Insert statement, how can I look at the final >> > statement >> to see what I have done wrong when the statement does not work? >

Re: [sqlite] Troubleshooting...

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Pavel Ivanov > Sent: Thursday, December 16, 2010 2:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Troubleshooting...

Re: [sqlite] Troubleshooting...

> Once I bind the data to the Insert statement, how can I look at the final > statement to see what I have done wrong when the statement does not work? There's no way to do that. You should print what you bind yourself. For me it looks like you insert into database some UTF-8 string and then try