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
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
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 calle
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');
---
f 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.
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
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
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 = "")
{
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) val
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 paramete
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:
>
> d
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 ( nam
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
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 (ca
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';
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
_
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
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
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
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 doe
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.
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-exi
te-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
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
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.
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
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 t
> -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 "o
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
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-user
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 opti
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
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
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
___
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 //Execut
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
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).
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 i
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 appreciat
"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'
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 fr
"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
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) sho
"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 certai
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
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 work
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 whe
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.
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=F
Greetings,
I am looking for tips, tricks and general information, both for
personal usage (I'd like to move as many as possible of my
SOHO-related databases to SQLITE) and for an online article, about
SQLite3 query optimization tricks. I have already found and will study
these pages:
http://www.s
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
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.Orig
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
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 A
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
"Rickard Andersson" <[EMAIL PROTECTED]> writes:
> 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
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
57 matches
Mail list logo