Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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');

Re: [sqlite] query optimization

ay, 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 = '1

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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 = "")

Re: [sqlite] query optimization

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)

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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 (

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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

Re: [sqlite] query optimization

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] 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

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

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

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

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

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

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.

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

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

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

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:

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

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 SQ

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

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

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

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

[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

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

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

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

> -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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Query Optimization Help

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 >

Re: [sqlite] Query Optimization Help

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

[sqlite] Query Optimization Help

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

Re: [sqlite] query optimization for inner table join

"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.

Re: [sqlite] query optimization for inner table join

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

Re: [sqlite] query optimization for inner table join

"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

Re: [sqlite] query optimization for inner table join

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)

Re: [sqlite] query optimization for inner table join

"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

[sqlite] query optimization for inner table join

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

Re: [sqlite] Query Optimization

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

[sqlite] Query Optimization

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

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

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

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

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?

Re: [sqlite] Query optimization

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)

[sqlite] Query optimization

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,

Re: [sqlite] Query optimization help

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

Re: [sqlite] Query optimization help

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

Re: [sqlite] Query optimization help

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

[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