Re: [sqlite] query optimization

[d b]

Thanks alot RSmith.




On Mon, Nov 18, 2013 at 6:04 PM, d b  wrote:

> Hi Igor/Keith,
>
> I tried with both queries. I expect to delete all rows belongs to key 1.
> But not deleted. Am I missing something while writing queries?
>
> delete from emp where key = 1 and (name='' or name='f');
> DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');
>
>
>
> -
>
> create table if not exists emp(key integer not null, name text not null ,
> personaldata text not null, unique(key, name));
> insert into emp (key, name, personaldata) values(1, 'a', 'z');
> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
> insert into emp (key, name, personaldata) values(2, 'c', 'y');
> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>
> --
>
>
> On Mon, Nov 18, 2013 at 5:20 PM, d b  wrote:
>
>> Thanks RSmith.
>>
>> It works.
>>
>> But, I am looking for single query for prepared statements. That's the
>> actual struggle for me.
>>
>>
>> On Mon, Nov 18, 2013 at 4:24 PM, d b  wrote:
>>
>>> Hi RSmith,
>>>
>>>   Thanks. Still, I could not delete with single query.
>>>
>>>
>>> create table if not exists emp(key integer not null, name text not null
>>> , personaldata text not null, unique(key, name));
>>> insert into emp (key, name, personaldata) values(1, 'a', 'z');
>>> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
>>> insert into emp (key, name, personaldata) values(2, 'c', 'y');
>>> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
>>> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>>>
>>> bool delete_emp(int key, string name = "")
>>> {
>>> string query = ???;
>>>
>>> if(name.length() > 0)
>>> {
>>> //needs to delete specific row. by unique key.
>>> }
>>> else
>>> {
>>> //needs to delete rows belongs to key
>>> }
>>> }
>>>
>>>
>>> On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:
>>>
 Hi Luis,

 Those are parameters.

 This is the query after replacing with ?1 and ?2.

 delete from emp where key = '123' and (case when name = 'abc' is null
 THEN 1 else name = 'abc' end);

 It covered  "delete from emp where key = '123' and name = 'abc';" but
 not other query.

 I tried with "select  (case when name = 'abc' is null THEN 1 else name
 = 'abc' end) from emp;"  query. It's always going to else portion when
 'abc' doesn't exist in table. Any suggestions?

>>>
>>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[RSmith]

Thanks RSmith.

It works.

But, I am looking for single query for prepared statements. That's the
actual struggle for me.


Ok, but you give code examples that has nothing to do with prepared statements.

Giving this one last push, I iwll try to ignore all you have said and simply show the best way to do it with prepared statements 
(much like Igor noted) in some pseudo-code trying to avoid any ambiguity - Hope this is easily understood.



function - bool deleteEmps( key_param, name_param ) {

string sQuery = "DELETE FROM `Emp` WHERE (key = ?1) AND (( ?2 = '' ) OR ( ?2 = 
name )); "

pointer stmt;
int sqlResult = sqlite3_prepareV2(dbHandle, sQuery, sQuery.length, stmt, 
null );

while ( sqlResult == SQLITE_OK || sqlResult == SQLITE_ROW )
{
if ( sqlite3_bind_int(stmt, 1, key_param) == SQLITE_OK  )
if ( sqlite3_bind_text(stmt, 2, name_param == SQLITE_OK )
{
sqlResult = sqlite3_step(stmt);
}
}

return (sqlResult == SQLITE_DONE);
}


Notes:
No need to check if the second parameter (?2) is NULL in this case, because I forcibly bind it every iteration, it can only be == 
name_param - which in turn can be empty, but cannot be regarded as NULL.


Most of the bracketing and spaces are superfluous but harmless, and only added 
for clarity.

In the bindings, I simply KNOW that I am dealing with parameters 1 and 2 because I specified them just so in the Query, but this 
should really be automated or using named parameters (:key, :name etc) with binding on names or index-of-name kind of resolves. If 
this procedure only ever does this one thing, it matters none - but that would be rather inefficient.


I assumed the code is obvious, but in case you are not familiar:
&& means logical AND
|| means logical OR.
== means testing equality
= means assignment of a value.

There are no fault reporting, typically if the return statement is reached and it is NOT SQLITE_DONE, it means something went wrong. 
The prepare and binds should all return SQLILTE_OK and the step may return SQLITE_ROW or SQLITE_DONE depending on whether it is 
finished or there are more work to be done. Of course, ANY of them can return a different value which would mean an immediate error 
occured.


This must definitely do what you require - if it doesn't, please post your 
actual code.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[Kees Nuyt]

On Mon, 18 Nov 2013 14:08:28 +0200, RSmith  wrote:

>Well this is the reason for my initial misunderstanding - which I then thought 
>I had wrong, but either you have it wrong too... or I 
>had it right in the first place. Ok, less cryptically now:
>
>It all depends on whether he has a Column called "name" that might be Null, or 
>whether he has a parameter which checks column "name" 
>and which might be null... in one case (your's) we check for null values in 
>the column and in another case (my later case) we check 
>if the parameter is null, not the column, and then from that decide whether to 
>use it as a check or not - not sure which but between 
>your and my solutions both are covered though, so I hope the OP gets sorted 
>out - if not, let us know...

Yeah, I didn't go back far enough in the discussion,
so I missed part of the spec.

I'm sure the OP will sort it out after so many hints :)


>On 2013/11/18 13:55, Kees Nuyt wrote:
>> On Mon, 18 Nov 2013 13:04:31 +0200, RSmith  wrote:
>>
>>> Oops, misprint...
>>>
>>> name won't be null of course, the parameter needs to be null, kindly 
>>> replace the queries offered like this:
>>>
>>>   delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 
>>> ));
>>>
>>> or in the second form:
>>>
>>>   delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));
>>>
>>> I think this is closer to the intended - thanks,
>>> Ryan
>> Uhm, I think you mean:
>>
>> delete from emp
>> where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 ));
>>
>> delete from emp
>> where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 ));
>>
>> To cover both NULL and empty:
>>
>> delete from emp
>> where key = ?1 AND (name IS NULL OR name = '' OR name = ?2);
>>
>>
>>> On 2013/11/18 12:56, RSmith wrote:
 I might be missing something extraordinarily obvious... but I cannot 
 understand the use case for this logic you have.

 My first response was to just use "delete from emp where key=123" and be 
 done with it, who cares what the name is, right?

 But then it dawned on me that you may for some reason have that key NOT as 
 a unique key, which means you can have many keys that
 are 123 in which case delete where key = 123 will remove all of them, but 
 adding a name as an optional second parameter/check now
 makes sense.

 Some old-school boolean logic to the rescue then:
 If this last case is true:

   delete from emp where (key = '123') AND ((name IS NULL) OR (name = 
 'abc'));


 will simply delete all keys with 123 values, but only if the name is 
 either not specified, or the name is both specified and
 specific.



 Be careful that you might not be binding null values, but maybe empty 
 strings in stead of values, so another solution might be:

   delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


 You get the idea.

 Cheers,
 Ryan


 On 2013/11/18 09:45, d b wrote:
> Hi,
>
>
> I am trying to make single query instead of below two queries. Can
> somebody help?
>
>1. delete from emp where key = '123';
>2. delete from emp where key = '123' and name = 'abc';
>
> if Key available, execute 1st query. if key and name available, execute 
> 2nd
> query.
>
>Is it possible to write in single query?
>

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[d b]

Hi Igor/Keith,

I tried with both queries. I expect to delete all rows belongs to key 1.
But not deleted. Am I missing something while writing queries?

delete from emp where key = 1 and (name='' or name='f');
DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');


-
create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
--


On Mon, Nov 18, 2013 at 5:20 PM, d b  wrote:

> Thanks RSmith.
>
> It works.
>
> But, I am looking for single query for prepared statements. That's the
> actual struggle for me.
>
>
> On Mon, Nov 18, 2013 at 4:24 PM, d b  wrote:
>
>> Hi RSmith,
>>
>>   Thanks. Still, I could not delete with single query.
>>
>>
>> create table if not exists emp(key integer not null, name text not null ,
>> personaldata text not null, unique(key, name));
>> insert into emp (key, name, personaldata) values(1, 'a', 'z');
>> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
>> insert into emp (key, name, personaldata) values(2, 'c', 'y');
>> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
>> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>>
>> bool delete_emp(int key, string name = "")
>> {
>> string query = ???;
>>
>> if(name.length() > 0)
>> {
>> //needs to delete specific row. by unique key.
>> }
>> else
>> {
>> //needs to delete rows belongs to key
>> }
>> }
>>
>>
>> On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:
>>
>>> Hi Luis,
>>>
>>> Those are parameters.
>>>
>>> This is the query after replacing with ?1 and ?2.
>>>
>>> delete from emp where key = '123' and (case when name = 'abc' is null
>>> THEN 1 else name = 'abc' end);
>>>
>>> It covered  "delete from emp where key = '123' and name = 'abc';" but
>>> not other query.
>>>
>>> I tried with "select  (case when name = 'abc' is null THEN 1 else name =
>>> 'abc' end) from emp;"  query. It's always going to else portion when 'abc'
>>> doesn't exist in table. Any suggestions?
>>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[Keith Medcalf]

DELETE FROM emp
  WHERE key = '123'
AND (name IS NULL OR name = 'abc');

assuming "available" means "is not null ...

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of d b
>Sent: Monday, 18 November, 2013 00:46
>To: sqlite-users@sqlite.org
>Subject: [sqlite] query optimization
>
>Hi,
>
>
>  I am trying to make single query instead of below two queries. Can
>somebody help?
>
> 1. delete from emp where key = '123';
> 2. delete from emp where key = '123' and name = 'abc';
>
>if Key available, execute 1st query. if key and name available, execute
>2nd
>query.
>
> Is it possible to write in single query?
>
>Regards,
>va
>___
>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] query optimization

[Igor Tandetnik]

On 11/18/2013 7:24 AM, d b wrote:

bool delete_emp(int key, string name = "")
{
 string query = ???;

 if(name.length() > 0)
 {
 //needs to delete specific row. by unique key.
 }
 else
 {
 //needs to delete rows belongs to key
 }
}


delete from emp where key = :key and (:name='' or name=:name);

Just bind both parameters - you don't need an if statement in your host 
application, the condition is baked into the query.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[d b]

Thanks RSmith.

It works.

But, I am looking for single query for prepared statements. That's the
actual struggle for me.


On Mon, Nov 18, 2013 at 4:24 PM, d b  wrote:

> Hi RSmith,
>
>   Thanks. Still, I could not delete with single query.
>
>
> create table if not exists emp(key integer not null, name text not null ,
> personaldata text not null, unique(key, name));
> insert into emp (key, name, personaldata) values(1, 'a', 'z');
> insert into emp (key, name, personaldata) values(1, 'b', 'zz');
> insert into emp (key, name, personaldata) values(2, 'c', 'y');
> insert into emp (key, name, personaldata) values(3, 'd', 'yy');
> insert into emp (key, name, personaldata) values(1, 'e', 'yyy');
>
> bool delete_emp(int key, string name = "")
> {
> string query = ???;
>
> if(name.length() > 0)
> {
> //needs to delete specific row. by unique key.
> }
> else
> {
> //needs to delete rows belongs to key
> }
> }
>
>
> On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:
>
>> Hi Luis,
>>
>> Those are parameters.
>>
>> This is the query after replacing with ?1 and ?2.
>>
>> delete from emp where key = '123' and (case when name = 'abc' is null
>> THEN 1 else name = 'abc' end);
>>
>> It covered  "delete from emp where key = '123' and name = 'abc';" but not
>> other query.
>>
>> I tried with "select  (case when name = 'abc' is null THEN 1 else name =
>> 'abc' end) from emp;"  query. It's always going to else portion when 'abc'
>> doesn't exist in table. Any suggestions?
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[RSmith]

Thanks, this explanation makes it easier to understand what you are tryingto 
achieve.

I do not see any binding in your code, so let us assume you are not binding anything and just executing the query, this rework of 
your code should be the easiest:


bool delete_emp(int key, string name = "")
{
string query = "DELETE FROM `emp` WHERE (`key`="+intToStr(key)+")";

if(name.length() > 0)
{
//needs to delete specific row. by unique key.
query = query + " AND (`name`="+quotedStr(name)+")";
}

query = query + ";";  // Just add ending SQL delimeter - (pedanticness)

return sqlite3_execute(fDBHandle, query);
}


The above assumes your version of whatever language this is (looks like a C or JAVA / FLEX version of sorts) has a function that 
makes integers into strings (replace intToStr() with the correct one) and a function which can encode and double quotations for SQL 
(replace quotedStr() with whatever does that for you) and use whatever you normally use to execute the queries if it isn't 
sqlite3_execute.


Hoping this all makes sense.

Pseudo-code for quotedStr() // just incase you dont have such a thing yet - It needs to double up on quotes found and enclose it all 
in a set of quotes.


string quotedStr(sOriginStr) {
for ( n = sOriginStr.length()-1;  n > 0;  n-- ) {
if ( sOriginStr[n] = "'" )  insert ( sOriginStr(n, "'");
}
return "'" + sOriginStr + "'";
}

where "'" is a single quote enclosed in double-quotes (incase that was not 
obvious)




On 2013/11/18 14:24, d b wrote:

Hi RSmith,

   Thanks. Still, I could not delete with single query.


create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');

bool delete_emp(int key, string name = "")
{
 string query = ???;

 if(name.length() > 0)
 {
 //needs to delete specific row. by unique key.
 }
 else
 {
 //needs to delete rows belongs to key
 }
}


On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:


Hi Luis,

Those are parameters.

This is the query after replacing with ?1 and ?2.

delete from emp where key = '123' and (case when name = 'abc' is null THEN
1 else name = 'abc' end);

It covered  "delete from emp where key = '123' and name = 'abc';" but not
other query.

I tried with "select  (case when name = 'abc' is null THEN 1 else name =
'abc' end) from emp;"  query. It's always going to else portion when 'abc'
doesn't exist in table. Any suggestions?


___
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] query optimization

[d b]

Hi RSmith,

  Thanks. Still, I could not delete with single query.


create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');

bool delete_emp(int key, string name = "")
{
string query = ???;

if(name.length() > 0)
{
//needs to delete specific row. by unique key.
}
else
{
//needs to delete rows belongs to key
}
}


On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:

> Hi Luis,
>
> Those are parameters.
>
> This is the query after replacing with ?1 and ?2.
>
> delete from emp where key = '123' and (case when name = 'abc' is null THEN
> 1 else name = 'abc' end);
>
> It covered  "delete from emp where key = '123' and name = 'abc';" but not
> other query.
>
> I tried with "select  (case when name = 'abc' is null THEN 1 else name =
> 'abc' end) from emp;"  query. It's always going to else portion when 'abc'
> doesn't exist in table. Any suggestions?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[RSmith]

Well this is the reason for my initial misunderstanding - which I then thought I had wrong, but either you have it wrong too... or I 
had it right in the first place. Ok, less cryptically now:


It all depends on whether he has a Column called "name" that might be Null, or whether he has a parameter which checks column "name" 
and which might be null... in one case (your's) we check for null values in the column and in another case (my later case) we check 
if the parameter is null, not the column, and then from that decide whether to use it as a check or not - not sure which but between 
your and my solutions both are covered though, so I hope the OP gets sorted out - if not, let us know...



On 2013/11/18 13:55, Kees Nuyt wrote:

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith  wrote:


Oops, misprint...

name won't be null of course, the parameter needs to be null, kindly replace 
the queries offered like this:

  delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 ));

or in the second form:

  delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));

I think this is closer to the intended - thanks,
Ryan

Uhm, I think you mean:

delete from emp
where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 ));

delete from emp
where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 ));

To cover both NULL and empty:

delete from emp
where key = ?1 AND (name IS NULL OR name = '' OR name = ?2);



On 2013/11/18 12:56, RSmith wrote:

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a 
unique key, which means you can have many keys that
are 123 in which case delete where key = 123 will remove all of them, but 
adding a name as an optional second parameter/check now
makes sense.

Some old-school boolean logic to the rescue then:
If this last case is true:

  delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not 
specified, or the name is both specified and
specific.



Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

  delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


I am trying to make single query instead of below two queries. Can
somebody help?

   1. delete from emp where key = '123';
   2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

   Is it possible to write in single query?

Regards,
va
___
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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[Kees Nuyt]

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith  wrote:

>Oops, misprint...
>
>name won't be null of course, the parameter needs to be null, kindly replace 
>the queries offered like this:
>
>  delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 ));
>
>or in the second form:
>
>  delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));
>
>I think this is closer to the intended - thanks,
>Ryan

Uhm, I think you mean:

delete from emp 
where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 ));

delete from emp 
where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 ));

To cover both NULL and empty:

delete from emp 
where key = ?1 AND (name IS NULL OR name = '' OR name = ?2);


>On 2013/11/18 12:56, RSmith wrote:
>> I might be missing something extraordinarily obvious... but I cannot 
>> understand the use case for this logic you have.
>>
>> My first response was to just use "delete from emp where key=123" and be 
>> done with it, who cares what the name is, right?
>>
>> But then it dawned on me that you may for some reason have that key NOT as a 
>> unique key, which means you can have many keys that 
>> are 123 in which case delete where key = 123 will remove all of them, but 
>> adding a name as an optional second parameter/check now 
>> makes sense.
>>
>> Some old-school boolean logic to the rescue then:
>> If this last case is true:
>>
>>  delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));
>>
>>
>> will simply delete all keys with 123 values, but only if the name is either 
>> not specified, or the name is both specified and 
>> specific.
>>
>>
>>
>> Be careful that you might not be binding null values, but maybe empty 
>> strings in stead of values, so another solution might be:
>>
>>  delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));
>>
>>
>> You get the idea.
>>
>> Cheers,
>> Ryan
>>
>>
>> On 2013/11/18 09:45, d b wrote:
>>> Hi,
>>>
>>>
>>>I am trying to make single query instead of below two queries. Can
>>> somebody help?
>>>
>>>   1. delete from emp where key = '123';
>>>   2. delete from emp where key = '123' and name = 'abc';
>>>
>>> if Key available, execute 1st query. if key and name available, execute 2nd
>>> query.
>>>
>>>   Is it possible to write in single query?
>>>
>>> Regards,
>>> va
>>> ___
>>> 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

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[RSmith]

Oops, misprint...

name won't be null of course, the parameter needs to be null, kindly replace 
the queries offered like this:

 delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 ));

or in the second form:

 delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));

I think this is closer to the intended - thanks,
Ryan


On 2013/11/18 12:56, RSmith wrote:

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a unique key, which means you can have many keys that 
are 123 in which case delete where key = 123 will remove all of them, but adding a name as an optional second parameter/check now 
makes sense.


Some old-school boolean logic to the rescue then:
If this last case is true:

 delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not specified, or the name is both specified and 
specific.




Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

 delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


   I am trying to make single query instead of below two queries. Can
somebody help?

  1. delete from emp where key = '123';
  2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

  Is it possible to write in single query?

Regards,
va
___
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] query optimization

[RSmith]

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a unique key, which means you can have many keys that are 
123 in which case delete where key = 123 will remove all of them, but adding a name as an optional second parameter/check now makes 
sense.


Some old-school boolean logic to the rescue then:
If this last case is true:

 delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not 
specified, or the name is both specified and specific.



Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

 delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


   I am trying to make single query instead of below two queries. Can
somebody help?

  1. delete from emp where key = '123';
  2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

  Is it possible to write in single query?

Regards,
va
___
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] query optimization

[d b]

Hi Luis,

Those are parameters.

This is the query after replacing with ?1 and ?2.

delete from emp where key = '123' and (case when name = 'abc' is null THEN
1 else name = 'abc' end);

It covered  "delete from emp where key = '123' and name = 'abc';" but not
other query.

I tried with "select  (case when name = 'abc' is null THEN 1 else name =
'abc' end) from emp;"  query. It's always going to else portion when 'abc'
doesn't exist in table. Any suggestions?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[Luís Simão]

Assuming you are using parameters you may use something like:

DELETE FROM emp WHERE key=?1 AND CASE ?2 IS NULL THEN 1 ELSE name=?2;

BR

2013/11/18 d b 

> Hi,
>
>
>   I am trying to make single query instead of below two queries. Can
> somebody help?
>
>  1. delete from emp where key = '123';
>  2. delete from emp where key = '123' and name = 'abc';
>
> if Key available, execute 1st query. if key and name available, execute 2nd
> query.
>
>  Is it possible to write in single query?
>
> Regards,
> va
> ___
> 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] query optimization

[Clemens Ladisch]

d b wrote:
>  1. delete from emp where key = '123';
>  2. delete from emp where key = '123' and name = 'abc';
>
> if Key available, execute 1st query. if key and name available, execute 2nd
> query.

What do you mean with "available"?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query optimization

[d b]

Hi,


  I am trying to make single query instead of below two queries. Can
somebody help?

 1. delete from emp where key = '123';
 2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

 Is it possible to write in single query?

Regards,
va
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[James K. Lowden]

On Mon, 29 Jul 2013 13:23:07 +0100
Simon Slavin  wrote:

> INSERT OR IGNORE a new row with the correct 'word' and a confidence
> of 0 
> UPDATE the row with that word to increment the confidence.
...
> If that solution doesn't work for you you might like to try first
> doing
> 
> UPDATE myTable SET ...
> 
> and then looking at the result of
> 
> sqlite3_changes()
> 
> to see whether it is 1 or not.  If it's zero, then you insert a new
> row, with a confidence of 1.

In the general case, if you UPDATE and find zero rows changed, and
then another process inserts the row (with count 1) before your INSERT,
then your INSERT will fail and confidence will remain at 1 when it
should be incremented to 2.  

INSERT OR IGNORE a new row with confidence of 1 
if( 0 == sqlite3_changes() )
UPDATE set confidence = confidence +1

works in the presence of other updaters.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 2 Aug 2013, at 2:09pm, Igor Tandetnik  wrote:

> On 8/2/2013 8:14 AM, Simon Slavin wrote:
>> 
>> On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:
>> 
>>> Hi Simon,
>>> 
>>> the solution might look elegant, but it is probably a lot slower. I did not
>>> check this particular case, but in the past I found triggers to perform
>>> rather badly.
>> 
>> I am puzzled.  My solution does not involve any triggers.
> 
> ... whereas that from Simon Davies does.

I am unpuzzled.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Igor Tandetnik]

On 8/2/2013 8:14 AM, Simon Slavin wrote:


On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:


Hi Simon,

the solution might look elegant, but it is probably a lot slower. I did not
check this particular case, but in the past I found triggers to perform
rather badly.


I am puzzled.  My solution does not involve any triggers.


... whereas that from Simon Davies does.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 2 Aug 2013, at 10:13am, Jan Slodicka  wrote:

> Hi Simon,
> 
> the solution might look elegant, but it is probably a lot slower. I did not
> check this particular case, but in the past I found triggers to perform
> rather badly.

I am puzzled.  My solution does not involve any triggers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Jan Slodicka]

Hi Simon,

the solution might look elegant, but it is probably a lot slower. I did not
check this particular case, but in the past I found triggers to perform
rather badly.

Regards,
Jan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Query-optimization-Checking-for-existence-before-performing-action-tp70297p70356.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] Query optimization: Checking for existence before performing action

[Keith Medcalf]

INSERT OR IGNORE INTO table (word, confidence) VALUES (:word, 
:initialconfidence - :confidenceincrement);
UPDATE table SET confidence=confidence+:confidenceincrement WHERE word=:word;

Still two statements but does not require application "help" and the rowid is 
stable ...

Assuming that :initialconfidence and :confidenceincrement are constant, you 
could put the two statements in an instead of trigger on the table.  Then your 
code would only need to execute a single statement ...


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Navaneeth.K.N
> Sent: Monday, 29 July, 2013 05:58
> To: General Discussion of SQLite Database
> Subject: [sqlite] Query optimization: Checking for existence before
> performing action
> 
> Hello,
> 
> I am trying to optimize the SQL calls that my application makes. I
> have a scenario where words are inserted into a table. Now each word
> will have a column called "confidence". There is a unique primary key
> on "word".
> 
> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
> 
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.
> 
> Any help would be great!
> 
> --
> Thanks
> Navaneeth
> ___
> 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] Query optimization: Checking for existence before performing action

[Simon Davies]

On 29 July 2013 12:57, Navaneeth.K.N  wrote:
> Hello,
>
> I am trying to optimize the SQL calls that my application makes. I
> have a scenario where words are inserted into a table. Now each word
> will have a column called "confidence". There is a unique primary key
> on "word".
>
> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
>
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.
>
> Any help would be great!

Something like:

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table words( id text unique, confidence integer default 0 );
sqlite> create trigger words_before_insert before insert on words
   ...> begin update words set confidence=confidence+1 where id=new.id;
   ...> end;
sqlite>
sqlite> select * from words;
sqlite>
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'bill' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite>
sqlite> select * from words;
fred|4
bill|0
joe|1
sqlite>

>
> --
> Thanks
> Navaneeth

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Slavin]

On 29 Jul 2013, at 12:57pm, Navaneeth.K.N  wrote:

> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
> 
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.

If you have defined your tables correctly, the rowid would not change, the 
INSERT would just fail.  You could define the 'word' column as UNIQUE, or once 
our table is created create an index which forces the 'word' column to be 
unique.  Then inserting another row with the same 'word' would fail.  Then your 
program to increment a word could would do something like

INSERT OR IGNORE a new row with the correct 'word' and a confidence of 0
UPDATE the row with that word to increment the confidence.

Since the table is set up not to allow duplication, if the row already exists 
the 'INSERT' will fail, but since you used 'INSERT OR IGNORE' your program 
would IGNORE the failure and carry on regardless, incrementing the existing 
confidence.


If that solution doesn't work for you you might like to try first doing

UPDATE myTable SET ...

and then looking at the result of

sqlite3_changes()

to see whether it is 1 or not.  If it's zero, then you insert a new row, with a 
confidence of 1.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query optimization: Checking for existence before performing action

[Navaneeth.K.N]

Hello,

I am trying to optimize the SQL calls that my application makes. I
have a scenario where words are inserted into a table. Now each word
will have a column called "confidence". There is a unique primary key
on "word".

When inserting a word, first I check if the words exists by performing
a "select" query. If it exists, I fire an update query to increment
the confidence for that word.  If word is not available, I fire an
insert query to insert the word.

In both the cases, I can't skip doing two queries. One for checking
existence and second for updating or creating. I am wondering is there
an easy way to solve this by just doing one query? I have tried
"insert or replace", but I can't use that as it changes the rowid's.

Any help would be great!

-- 
Thanks
Navaneeth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Gabriel Corneanu]

I admit I didn't check what the standards say about "select", I just 
wanted to make sure the potential users (which are by no means 
"developers") get the data properly.
But you misread my example, I had "order by id" everywhere (no mixed 
sorting).
I expected that the optimizer would "see" it's the same order and avoid 
doing it twice.
Otherwise I can also admit that multiple/mixed "order by" would be 
problematic.


Regards,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Konrad Hambrick]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of James K. Lowden
> Sent: Tuesday, February 19, 2013 12:07 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query optimization with "order by" in a view
> 
> On Tue, 19 Feb 2013 10:19:26 +0100
> "Gabriel Corneanu" <gabrielcorne...@gmail.com> wrote:
> 
> > I included the "order by" in view because it's meant for some
> > end-users and I wanted to avoid mistakes.
> ...
> > Am I doing a mistake??
> 
> Well, yes, by including ORDER BY in the view definition.  Most DBMSs
> don't allow that, and the SQL standard doesn't allow it.  So don't do
> it!  :-)
> 
> ORDER BY is best understood as *not* part of the SELECT statement.
> Rather, it's a post-processor.  Consider that there can be many SELECTs
> in a query, but only one ORDER BY.
> 
> According to the SQL standard, SELECT produces a "table expression" that
> can be used wherever a table can be used.  ORDER BY *reads* a table
> expression; what it returns is technically a "cursor".
> 
> It's tempting to think, OK, but the view's ORDER BY would be processed
> first and the final ORDER BY would be processed last, so the order is
> predictable.  In fact, though, there is no first and last.  The SQL
> statement is a declaration, not an imperative.  It describes which rows
> and columns to retrieve.  It specifies only an outcome, not an
> algorithm or an order of operation.
> 
> In effect, your query specified
> 
>   ORDER BY id
>   AND
>   ORDER BY data
> 
> which you would never do, and SQLite can't, either.  ;-)
> 
> HTH.
> 
> --jkl


Very nice explanation, James !

Thank you.

-- kjh( I had to look twice to make sure I was in my SQLite Mailbox and not in 
FreeTDS :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[James K. Lowden]

On Tue, 19 Feb 2013 10:19:26 +0100
"Gabriel Corneanu"  wrote:

> I included the "order by" in view because it's meant for some
> end-users and I wanted to avoid mistakes.
...
> Am I doing a mistake??

Well, yes, by including ORDER BY in the view definition.  Most DBMSs
don't allow that, and the SQL standard doesn't allow it.  So don't do
it!  :-)  

ORDER BY is best understood as *not* part of the SELECT statement.
Rather, it's a post-processor.  Consider that there can be many SELECTs
in a query, but only one ORDER BY.  

According to the SQL standard, SELECT produces a "table expression" that
can be used wherever a table can be used.  ORDER BY *reads* a table
expression; what it returns is technically a "cursor".  

It's tempting to think, OK, but the view's ORDER BY would be processed
first and the final ORDER BY would be processed last, so the order is
predictable.  In fact, though, there is no first and last.  The SQL
statement is a declaration, not an imperative.  It describes which rows
and columns to retrieve.  It specifies only an outcome, not an
algorithm or an order of operation.  

In effect, your query specified

ORDER BY id
AND
ORDER BY data

which you would never do, and SQLite can't, either.  ;-)

HTH.

--jkl


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Richard Hipp]

On Tue, Feb 19, 2013 at 8:30 AM, Gabriel Corneanu  wrote:

> I hoped it was either a slip or would be relatively simple to implement.
>

Good rule of thumb:  Nothing is ever simple in a query optimizer


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Gabriel Corneanu]

It's hard to accept this conclusion... it seems like a simple justification.
If you say so, why is "select from v order by id" not doing a sort (with 
the data from view)?

Obviously it "sees" the id is the primary key and uses it for sorting.

I read here lots of messages about complex query optimizations, and 
there is a whole chapter about this here:

http://www.sqlite.org/optoverview.html#flattening

I will obviously handle this somehow, but I hoped it was either a slip 
or would be relatively simple to implement.
I have the feeling (at least for this case) that a simple rule (for the 
optimizer) is, only the last sort should be honored.
That means, ignore the sort from the view; and because this case seems 
to be correctly handled


Regards,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization with "order by" in a view

[Simon Slavin]

On 19 Feb 2013, at 9:19am, "Gabriel Corneanu"  wrote:

> As a summary, it seems that having multiple "order by" disturbs the query 
> builder; of course, I expected the "optimizer" to recognize that i was the 
> same order and avoid extra sorting.
> Am I doing a mistake??

I think you have figured it out correctly.  SQLite prepares the data you asked 
for in two stages.  First it executes the SELECT which you defined to make the 
VIEW.  Then it executes a SELECT on the results.  It does not merge the two 
SELECTs into one command, so it never notices that the result of the first 
SELECT already has the rows in the right order.

I think you laid out the three possible approaches to ORDER BY nicely and can 
pick whichever one suits you best: either depend on the VIEW always returning 
rows in the right order or don't.

However, in SELECT from a TABLE (rather than from a VIEW) you can never depend 
on the order of the returned rows.  So perhaps it's better not to have your 
VIEW do sorting.  That way your VIEW has the same behaviour as SQL users would 
expect from a TABLE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query optimization with "order by" in a view

[Gabriel Corneanu]

I need some clarifications on this issue. Here is a simplified example.
There is a table:
CREATE TABLE t(id integer primary key, data integer);
and a (simplified) view:
CREATE VIEW v as SELECT * FROM "t" order by id;

I included the "order by" in view because it's meant for some end-users  
and I wanted to avoid mistakes.
BUT I queried the view myself; I wanted to also be "safe" and included  
another "order by":


explain query plan SELECT * FROM "v" order by id;

As one can see, the plan uses 2 scans and an extra sort (btree)!! Which  
can be very expensive, of course...

I could find the following:
- if I query the view w/o "order by", it works as expected (uses primary  
key)
- if I define the view w/o "order by", and use "order by" in query, it  
also works as expected
- if I use the query with "order by rowid" (instead of id), the query plan  
is a little different; it has 2 scans, but it doesn't use a temporary  
btree anymore


As a summary, it seems that having multiple "order by" disturbs the query  
builder; of course, I expected the "optimizer" to recognize that i was the  
same order and avoid extra sorting.

Am I doing a mistake??

Thanks,
Gabriel


--
Using Opera's revolutionary email client: http://www.opera.com/mail/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

[Igor Tandetnik]

sattu  wrote:
> select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
> 1sec
> select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
> 15secs

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

[Simon Slavin]

On 4 Sep 2012, at 3:56pm, sattu  wrote:

> What I observed is, if the offset is very high like say 9, then it takes
> more time for the query to execute. Following is the time difference between
> 2 queries with different offsets:
> 
> 
> select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
> 1sec
> select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
> 15secs

These queries are not useful, since you have not specified an ORDER BY clause 
in your SELECT.  If you don't specify an ORDER BY clause, then SQL is allowed 
to sort the records two different ways for two different SELECT commands, which 
would mean that some rows might appear in both queries, or and some not in 
either.  If you intend to use OFFSET then you should be using ORDER BY too.

> Can anyone suggest me how to optimize this query? I mean, the Query
> Execution Time should be same and fast for any number of records I wish to
> retrieve from any OFFSET.

The problem is that use of OFFSET 95000 requires SQLite to do a search which 
/does/ include the first 95000 rows, then quickly run through 95000 rows and 
discard them before returning the first result to your program.  Discarding the 
95000 results you don't want is what's taking the time.  I don't know how your 
program is written but it might be possible to use a key column to allow SQLite 
to find the right rows immediately.

For example, suppose you were using a key column called 'rowid' for your SELECT 
command:

SELECT * FROM myTable ORDER BY rowid LIMIT 100

To have this SELECT run quickly, you would make sure you had an index on your 
'rowid' column.  If your 'rowid' column is the real INTEGER PRIMARY KEY for the 
table, then SQLite will automatically create such an index for you without you 
having to ask for it.

Then instead of using OFFSET to make SQLite discard 95000 results, you use 
WHERE to specify the records you're interested in.  In your software you keep 
track of the last value for rowid returned and use that when writing the next 
command.  So your first SELECT might be

SELECT * FROM myTable WHERE rowid > -1 ORDER BY rowid LIMIT 100

and perhaps the last row returned by this has a rowid of 104.  Then your next 
SELECT should be

SELECT * FROM myTable WHERE rowid > 104 ORDER BY rowid LIMIT 100

and perhaps the last row returned by this has a rowid of 205.  Then your next 
SELECT should be

SELECT * FROM myTable WHERE rowid > 205 ORDER BY rowid LIMIT 100

If you use SELECT commands like this, SQLite can use the index on the rowid 
column to zoom straight to the first row it should return.  It could do this in 
far less than a second, no matter how big your value for rowid is.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Query Optimization (using Limit and Offset)

[sattu]

Following is the query that I use for getting a fixed number of records from
a database with millions of records:-

select * from myTable LIMIT 100 OFFSET 0


What I observed is, if the offset is very high like say 9, then it takes
more time for the query to execute. Following is the time difference between
2 queries with different offsets:


select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
1sec
select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
15secs


Can anyone suggest me how to optimize this query? I mean, the Query
Execution Time should be same and fast for any number of records I wish to
retrieve from any OFFSET.


-Thanks in advance



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-Query-Optimization-using-Limit-and-Offset-tp64000.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] Query Optimization Help

[Mike Eggleston]

On Wed, 11 Feb 2009, inZania might have said:

> 
> Hello,
> 
> I have a query that is slowing down my application significantly; in some
> cases, it takes 20+ seconds (this is in a SQLite database in an iPhone app,
> which is why it is so slow - the iPhone doesn't have as much system
> resources).  If anybody could help me optimize this query, I'd appreciate it
> very much.
> 
> The situation is this: there is a table, "cards", which I am searching. 
> Each card has a card_id, name, text, etc.  There is also a table "card_tags"
> which has only the rows "card_id" and "tag", because a single card may have
> several tags.  The query I'm trying to execute is attempting to search the
> card's name, text, OR any of its tags for a specific search string.
> 
> Here's the query I've constructed that is operating slowly:
> SELECT DISTINCT cards.* FROM cards LEFT JOIN card_tags ON
> cards.card_id=card_tags.card_id WHERE (cards.name LIKE '%query%' OR
> cards.text LIKE '%query%' OR card_tags.tag LIKE '%query%')
> 
> Any help would be appreciated!

Would you post the relevant portions of your DML? That may help the
question be more clear.

Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Optimization Help

[Igor Tandetnik]

inZania  wrote:
> The situation is this: there is a table, "cards", which I am
> searching. Each card has a card_id, name, text, etc.  There is also a
> table "card_tags" which has only the rows "card_id" and "tag",
> because a single card may have several tags.  The query I'm trying to
> execute is attempting to search the card's name, text, OR any of its
> tags for a specific search string.
>
> Here's the query I've constructed that is operating slowly:
> SELECT DISTINCT cards.* FROM cards LEFT JOIN card_tags ON
> cards.card_id=card_tags.card_id WHERE (cards.name LIKE '%query%' OR
> cards.text LIKE '%query%' OR card_tags.tag LIKE '%query%')

Try this:

select * from cards where card_id in (
select card_id from cards
where name LIKE '%query%' OR text LIKE '%query%'
union all
select card_id from card_tags where tag LIKE '%query%'
);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Optimization Help

[inZania]

Hello,

I have a query that is slowing down my application significantly; in some
cases, it takes 20+ seconds (this is in a SQLite database in an iPhone app,
which is why it is so slow - the iPhone doesn't have as much system
resources).  If anybody could help me optimize this query, I'd appreciate it
very much.

The situation is this: there is a table, "cards", which I am searching. 
Each card has a card_id, name, text, etc.  There is also a table "card_tags"
which has only the rows "card_id" and "tag", because a single card may have
several tags.  The query I'm trying to execute is attempting to search the
card's name, text, OR any of its tags for a specific search string.

Here's the query I've constructed that is operating slowly:
SELECT DISTINCT cards.* FROM cards LEFT JOIN card_tags ON
cards.card_id=card_tags.card_id WHERE (cards.name LIKE '%query%' OR
cards.text LIKE '%query%' OR card_tags.tag LIKE '%query%')

Any help would be appreciated!
-- 
View this message in context: 
http://www.nabble.com/Query-Optimization-Help-tp21958799p21958799.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] query optimization for inner table join

[Igor Tandetnik]

"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> There's two tables with the same problem. One has an undetermined
> number of values: 'm' points to user-definable tag.
> In the other table I have about 110 values. This could be spread over
> two integer columns. I'm a bit hesitant to use integer values as
> bitmasks. How is the signedness handled in the binding? Should I
> simply use a uint64_t and not worry?

I believe it would just work. But, if you think that would be a problem, 
you can use only 63 bits. Two columns will still cover 126 possible 
values.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Jos van den Oever]

2008/12/2 Igor Tandetnik <[EMAIL PROTECTED]>:
> You could also try something more straightforward:
>
> select distinct n from map m1 where
>exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and
>exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and
>not exists (select 1 from map m2 where m1.n=m2.n and m2.m=7);
>
> -- or
>
> select distinct n from map where
>n in (select n from map where m=3) and
>n in (select n from map where m=5) and
>n not in (select n from map where m=7);

This would have a worse worst case scenario, but by cleverly ordering
the inclusive statements from infrequent to frequent and the exclusive
ones from frequent to infrequent this could be improved. I'd have to
do a
  select m, count(m) from map group by m;
to get the info I need for that.

> If you need to run this kind of query often, and values of m are small
> (preferably less than 64), you might want to store a map from n to a
> bitmask where each bit corresponds to one value of m. Then the query
> becomes simply
>
> select n from map
> where (n & 168) = 40;
>
> This is going to be linear, but in the number of distinct values of n,
> not in the number of all pairs.

There's two tables with the same problem. One has an undetermined
number of values: 'm' points to user-definable tag.
In the other table I have about 110 values. This could be spread over
two integer columns. I'm a bit hesitant to use integer values as
bitmasks. How is the signedness handled in the binding? Should I
simply use a uint64_t and not worry?

Cheers,
Jos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Igor Tandetnik]

"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> 2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>:
>> Try this:
>>
>> select n from map
>> group by n
>> having
>>count(case when m=3 then 1 else null end) != 0 and
>>count(case when m=5 then 1 else null end) != 0 and
>>count(case when m=7 then 1 else null end) = 0;
>>
>> Having an index on map(n) should speed it up.
>
> Thank you very much, Igor. I would have not thought of that.
>
> This is a nicely predictable single linear scan. Still not awfully
> fast, but it will have to do.

You could also try something more straightforward:

select distinct n from map m1 where
exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and
exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and
not exists (select 1 from map m2 where m1.n=m2.n and m2.m=7);

-- or

select distinct n from map where
n in (select n from map where m=3) and
n in (select n from map where m=5) and
n not in (select n from map where m=7);


If you need to run this kind of query often, and values of m are small 
(preferably less than 64), you might want to store a map from n to a 
bitmask where each bit corresponds to one value of m. Then the query 
becomes simply

select n from map
where (n & 168) = 40;

This is going to be linear, but in the number of distinct values of n, 
not in the number of all pairs.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Jos van den Oever]

2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>:
> Try this:
>
> select n from map
> group by n
> having
>count(case when m=3 then 1 else null end) != 0 and
>count(case when m=5 then 1 else null end) != 0 and
>count(case when m=7 then 1 else null end) = 0;
>
> Having an index on map(n) should speed it up.

Thank you very much, Igor. I would have not thought of that.

This is a nicely predictable single linear scan. Still not awfully
fast, but it will have to do.
Using an index on map(n,m) seems faster. This may be because the m
values are in the index and there is no need to access the table.

Cheers,
Jos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Igor Tandetnik]

"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> I've trouble optimizing for an N:M mapping table. The schema of the
> table is this:
>
> CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);
>
> I want to retrieve a list of n filtered on the presence of certain
> values of m, e.g. give me all n for which there is an m = 3 and m = 5,
> but no m = 7.

Try this:

select n from map
group by n
having
count(case when m=3 then 1 else null end) != 0 and
count(case when m=5 then 1 else null end) != 0 and
count(case when m=7 then 1 else null end) = 0;

Having an index on map(n) should speed it up.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query optimization for inner table join

[Jos van den Oever]

Hi all,

I've trouble optimizing for an N:M mapping table. The schema of the
table is this:

CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);

I want to retrieve a list of n filtered on the presence of certain
values of m, e.g. give me all n for which there is an m = 3 and m = 5,
but no m = 7.
A naive query would look like this:

SELECT a.n FROM map a, map b, map c
  WHERE a.n = b.n AND a.n = c.n AND a.m = 3 AND b.m = 5
 AND c.id not in (select id from map where c.m = 7);

This can be slow, even for the more simple case with only positive selection:

SELECT a.n FROM map a, map b
  WHERE a.n = b.n AND a.m = 3 AND b.m = 5;

And this variation does not make it a lot faster:

SELECT n FROM map WHERE m = 3 INTERSECT SELECT n FROM map where m = 5;

There are about a million entries in the table map and want to
increase to about 10 million.

The current indexes are

CREATE INDEX map_n ON map(n);
CREATE INDEX map_m ON map(n,m);

Is there a cleverer way of doing these queries?

The fraction of n's that has a particular m can be anywhere between 0 and 1.

Cheers,
Jos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Optimization

[Dennis Cote]

Mitchell Vincent wrote:
> SELECT customer_id FROM customers WHERE cust_balance != (select
> coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND
> invoice.customer_id = customers.customer_id)
> 
> The above query is used to determine if any stored balances are out of
> date. It works very well but is *really* slow when the customer and
> invoice tables get into the thousands of rows. Is there a better way
> to accomplish the same thing, or some combination of indexes I can
> create to help speed that query up? Currently indexes are on the
> customer_id columns of both tables as well as the cust_balance field
> in customers.
> 

The index on cust_balance does no good for this query, sqlite must do a 
complete table scan of the customer table anyway.

You could speed up the sub-select somewhat by replacing the index on 
invoice.customer_id with a compound index on invoice.customer_id and 
invoice.status.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Optimization

[Mitchell Vincent]

SELECT customer_id FROM customers WHERE cust_balance != (select
coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND
invoice.customer_id = customers.customer_id)

The above query is used to determine if any stored balances are out of
date. It works very well but is *really* slow when the customer and
invoice tables get into the thousands of rows. Is there a better way
to accomplish the same thing, or some combination of indexes I can
create to help speed that query up? Currently indexes are on the
customer_id columns of both tables as well as the cust_balance field
in customers.

Thanks!

-- 
- Mitchell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] up to date info on SQLite query optimization?

[M. Fioretti]

On Tue, Jul 22, 2008 14:24:38 PM -0400, Igor Tandetnik wrote:
 
> Are you familiar with SQLite full-text search (FTS) extension?

No, thanks for the link, will study it.

> > - calculation of moving average of a floating field, eg if a table is
...
> Any solution in pure SQL is going to be awkward. Personally, I'd run
> a simple query like
> 
> select MONTH, SALES_TOTAL from Sales order by MONTH
> 
> and, as I iterate through the resultset, keep a queue and a running
> sum of the last 12 totals.

By "keep a queue and a running sum" you mean "do the running sum in
whatever language (C, Php, Perl...) you are calling SQLite from",
right? If yes, that's what I'm doing already, and I know an SQL-only
solution is awkward.

But putting all the queries in SQLite reduces the work to be done if I
want to use that same database and query from many independent
programs, say a Php web page, a Perl Script and OpenOffice via ODBC:
development speed vs query speed.

So, yes, I'll insist :-), if nothing else to find out, with the list
help, the fastest possible SQL-only solution. I'm not at the PC with
the sqlite database right now, but will test your query asap. In the
meantime, again, any other optimization trick, on this or other
specific cases, is welcome!

Thanks,
Marco
-- 
Your own civil rights and the quality of your own life heavily depend on
how software is used *around* you:   http://digifreedom.net/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] up to date info on SQLite query optimization?

[Igor Tandetnik]

M. Fioretti <[EMAIL PROTECTED]> wrote:
> A few examples of the kind of queries I'd
> like to (learn how to) optimize first:
>
> - search of strings in text fields (both sub-words and whole words)

Are you familiar with SQLite full-text search (FTS) extension?

http://www.sqlite.org/cvstrac/wiki?p=FtsUsage

> - calculation of moving average of a floating field, eg if a table is
>  like this:
>
>   MONTH SALES_TOTAL
>   2007-01 500
>   2007-02 1059.3
>   ...
>   2008-06 439
>   2008-07 605.52
>
>   what is the fastest SQLITE3 query to read that table and return
>   another table with the average sales over the last 12 months:
>
>   2008-05  (total sales from 2007-06 to 2008-05)/12
>   2008-06  (total sales from 2007-07 to 2008-06)/12
>   2008-07  (total sales from 2007-08 to 2008-07)/12

Any solution in pure SQL is going to be awkward. Personally, I'd run a 
simple query like

select MONTH, SALES_TOTAL from Sales order by MONTH

and, as I iterate through the resultset, keep a queue and a running sum 
of the last 12 totals.

If you insist, something like this should work (but not very fast):

select s1.MONTH, avg(s2.SALES_TOTAL)
from Sales s1 join Sales s2 on
(s2.MONTH between strftime('%Y-%m', s1.MONTH || '-01', '-11 months') 
and s1.MONTH)
group by s1.MONTH
-- optional, if you don't want partial averages at the beginning
-- HAVING count(*)=12
order by s1.MONTH;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimization

[Matthew Gertner]

Igor Tandetnik wrote:
> 
> Reordering LEFT JOIN changes the meaning of the statement. You don't 
> want your DBMS to do that to you behind your back. Make sure you know 
> what you are doing, and that the reordered statement still does what 
> it's supposed to do. Again, (A LEFT JOIN B) produces different results, 
> in general, than (B LEFT JOIN A).
> 
> Igor Tandetnik 
> 

Ah, good point. I overlooked this because in this instance the outer joins
actually have the semantics of inner joins (i.e. I know the tables have the
same entries with the same primary keys). I'm using outer joins because the
query is generated using a generic framework which might need outer joins in
some cases. But considering the implications of our approach for the query
optimizer, I'll rewrite it so it uses inner joins and handles the other
cases in some other way.

Cheers,
Matt
-- 
View this message in context: 
http://www.nabble.com/Query-optimization-tf4724113.html#a13508442
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Query optimization

[Matthew Gertner]

I'm running the following query:

SELECT DISTINCT _Resource.ResourceType, _Resource.Id, _Resource.OwnerId,
_Resource.Created, _Resource.Modified, _Resource.Name, _Resource.ParentId,
_Resource.Version, _Resource.Description, _Resource.Creator, _File.Size,
_File.MimeType, _File.OriginURI, _File.OriginMimeType, _File.Hash,
_File.ContentRating, _File.OriginalExtension, _File.Thumbnail, _File.Data,
_File.TorrentInfoHash, _Resource_Metadata.New, _File_Metadata.IsPublished,
_File_Metadata.ReceiveStatus, _File_Metadata.ShareMessageId, _Audio.Length,
_Audio.BitRate, _Image.Height, _Image.Width, _Image.ColorsUsed,
_Text.Summary, _Video.Height, _Video.Width, _Video.Length, _Video.FrameRate,
_Video.DataRate, _Video.SampleSize FROM _File LEFT OUTER JOIN _Resource ON
_File.Id=_Resource.Id LEFT OUTER JOIN _Resource_Metadata ON
_Resource_Metadata.ParentId=_Resource.Id LEFT OUTER JOIN _File_Metadata ON
_File_Metadata.Id=_Resource_Metadata.Id LEFT OUTER JOIN _Audio ON
_File.Id=_Audio.Id LEFT OUTER JOIN _Image ON _File.Id=_Image.Id LEFT OUTER
JOIN _Text ON _File.Id=_Text.Id LEFT OUTER JOIN _Video ON _File.Id=_Video.Id
LEFT OUTER JOIN _Source ON _Source.ParentId=_Resource_Metadata.Id LEFT OUTER
JOIN _Source_PeerCommonName ON _Source_PeerCommonName.ResourceId=_Source.Id
WHERE _Source_PeerCommonName.Value=? AND _File_Metadata.Id IN (SELECT Id
FROM _File_Metadata WHERE ReceiveStatus=?)

EXPLAIN QUERY PLAN tells me that a full scan is being performed on the _File
table, which is the first in the FROM clause. When I reorder the joins so
that _File_Metadata is first in the FROM clause, then its primary key index
(on Id) is used, as expected (since Id is used in the WHERE clause). I would
have expected SQLite's query optimizer to reorder the joins automatically to
use an index rather than a full table scan. Am I doing something wrong or do
I have to reorder the joins myself to optimize index usage?
-- 
View this message in context: 
http://www.nabble.com/Query-optimization-tf4724113.html#a13506536
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Query optimization help

[Rickard Andersson]

The advice from Dr. Richard Hipp did the trick. I added a multi column index
and now the query takes less than a tenth of a seconds. Thanks a lot for the
help you guys!

I was going to send this acknowledgement by replying to the message by Dr.
Hipp, but for some reason I didn't receive it, so I'll just send it like
this instead.

-- 
Rickard Andersson
arpen_at_home_dot_se


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Query optimization help

[D. Richard Hipp]

Rickard Andersson wrote:
I'm having some performance problems with queries looking like the
following:
SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster,
t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views,
t.num_replies, t.closed, t.sticky, t.moved_to
FROM topics AS t
LEFT JOIN posts AS p
ON t.id=p.topic_id AND p.poster_id=2
WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,
2761, 2745)
The above query takes a full second to complete on my P3-450. The database
contains approx. 1200 topics and 8000 posts. All relevant columns have
indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to
speed it up?
If you have separate indices on p.topic_id and p.poster_id, SQLite will
only use one or the other, and it has a 50% chance of choosing the wrong
one.  I suggest you try it with a single index that includes both
p.topic_id and p.poster_id.  Like this:
   CREATE TABLE p_idx ON p(topic_id, poster_id);



--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Query optimization help

[Greg Obleshchuk]

Hi Richard,
try this 

SELECT 
DISTINCT 
p.poster_id AS has_posted, 
t.id, 
t.subject, 
t.poster,
t.posted, 
t.last_post, 
t.last_post_id, 
t.last_poster, 
t.num_views,
t.num_replies, 
t.closed, 
t.sticky, 
t.moved_to
FROM 
topics AS t , posts AS p
WHERE 
t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,2761, 2745)
and 
t.id=p.topic_id 
AND 
p.poster_id=2

There a post from Dr R about how SQLite works out joins.  The above should be the 
final result it I read it right.  You might want to also try using group by instead of 
DISTINCT
In other DB it is faster.
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning


regards
Greg 




  - Original Message - 
  From: Rickard Andersson 
  To: [EMAIL PROTECTED] 
  Sent: Monday, February 02, 2004 2:00 PM
  Subject: [sqlite] Query optimization help


  I'm having some performance problems with queries looking like the
  following:

  SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster,
  t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views,
  t.num_replies, t.closed, t.sticky, t.moved_to
  FROM topics AS t
  LEFT JOIN posts AS p
  ON t.id=p.topic_id AND p.poster_id=2
  WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,
  2761, 2745)

  The above query takes a full second to complete on my P3-450. The database
  contains approx. 1200 topics and 8000 posts. All relevant columns have
  indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to
  speed it up?

  -- 
  Rickard Andersson
  arpen_at_home_dot_se


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Query optimization help

[Rickard Andersson]

I'm having some performance problems with queries looking like the
following:

SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster,
t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views,
t.num_replies, t.closed, t.sticky, t.moved_to
FROM topics AS t
LEFT JOIN posts AS p
ON t.id=p.topic_id AND p.poster_id=2
WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,
2761, 2745)

The above query takes a full second to complete on my P3-450. The database
contains approx. 1200 topics and 8000 posts. All relevant columns have
indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to
speed it up?

-- 
Rickard Andersson
arpen_at_home_dot_se


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]