Re: [sqlite] Problem with LIMIT and transactions? Or is it just me
Howdy, "Jay Macaulay" <[EMAIL PROTECTED]> 23/10/2003 05:55 AM To: <[EMAIL PROTECTED]> cc: Subject:[sqlite] Problem with LIMIT and transactions? Or is it just me > I am running into a real odd problem that I hope someone can explain. I > have a simple query: > BEGIN; > SELECT * FROM users WHERE uid >= 1 LIMIT 10; > COMMIT; > Simple enough. No reason to use a transaction, but I use them for another > reason, actually. The problem I run into is: If I execute the above > statement 3 times, it works fine. It takes 70 ms to execute the select > statement. On the fourth time of execution the select statement takes over > 5 seconds to execute!! I can execute the statement again after the 5 > seconds, and it's back to a 70 ms execution. Then after other time I > execute this statement it takes 5 seconds. This does seem difficult to explain. Is it possible that another process or thread is accessing the database occasionally (thus locking this "writer" out for a period?). Apart from that, you probably have to work through a process of elimination. Does it still happen without the LIMIT clause? Does it still happen if you take the transaction away? Is there anything else going on on the machine at the time? How are you running your test? How are you obtaining your timings? What's the schema of the database? Is there an index? Hopefully the answers to some of these questions will bring you closer to a conclusion. Benjamin.
[sqlite] Re: Problem
Reply at the bottom. LucaBala wrote: I've a table (COLOURS) with 8 rows (like below), columns CODE (integer primary key) and NAME: 100 BLACK 103 BLUE 104 GREEN 109 GRAY 111 LIGHT GREEN 112 LIGHT BLUE 120 LIGHT RED 121 RED When I do this command... SELECT * FROM colours WHERE code >= 100 order by asc limit 3 ...the SQLite returns the first 3 rows, but if I do this command... SELECT count(*) FROM colours WHERE code >= 100 order by asc limit 3 ...the SQLite returns... count(*) = 8 that is the total number rows. Why ?? I think it should be return "count() = 3'", shouldn't it ? But, if I do this... SELECT count() FROM colours WHERE code >= 115 order by asc limit 3 the SQLite returns correctly the count() = 2. How do I to get the correct number of rows returned by COUNT() ? SQLite is behaving as designed. LIMIT restricts the number of rows of output, not the number of rows of input. To achieve the effect you desire, do this: SELECT count(*) FROM (SELECT * FROM colours WHERE code>=115 ORDER BY asc LIMIT 3) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Work around for count (distinct a) ?
Hi, I have noticed that it is hard to acheive the count (distinct a) command in sqlite. However, I have tried the suggested work around by: SELECT count(ticker) FROM (SELECT DISTINCT ticker FROM ListDetail WHERE TableName LIKE "sixty%" AND Ticker <> "" This is also giving an error, is there any way to work around this as well? (If the LIKE operation is omitted the query functions 100%.) regards, Jonas
