I understand this - but, there always a but, I still would like to do something. Applying the limit anyway and then telling them the query has been limited might be a solution.
Time is usually not an issue but as the results are loaded into a grid for display memory can be the issue. I'll give it some more thought, but thank you for your answers it has helped. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 6 March 2016 at 08:09, R Smith <rsmith at rsweb.co.za> wrote: > > > On 2016/03/05 6:21 PM, Paul Sanderson wrote: >> >> Thanks Richard >> >> so suppose I have two tables table1 and table2 each with 1000 rows and >> say 100 columns some containing large blobs. >> >> My user choose a query "select * from table1, table2" >> >> I can modify the query and do a "select count(*) from table1, table2" >> to determine that 1M rows will be returned and ask him if this is what >> he really wants to do. >> >> But what if he has "select * from table1, table2 limit 1000" >> >> In this instance there is no need for me to warn him as he has already >> applied a limit to the amount of data returned. >> >> Any suggestions as to how I can detect this , other than parsing the >> query for "limit x" > > > I see your point, but I think you are missing something - The amount of rows > returned by a query is a very bad indicator of how long such a query will > run - AND, if it is a long query, you will waste twice the time for the end > user. Further to this, any amount of queries can be made that takes hours > to complete, but produces perhaps less than 5 rows, or even just 1. > > Similarly, but on a very different tack, you can have a query like "SELECT > xxx FROM A,B,C,D WHERE yyyy" without the limit clause, which might itself > really only produce 10 or less rows (and perhaps take really long) due to > the WHERE clause. So even if you did parse the LIMIT clause, you are no > closer to a true tale. > > My strategy has always been to run queries as the user enters them and limit > them to 1000 or such when a LIMIT is not already present. If I do actually > produce 1000 rows (which means there may be more) then I prompt the user > with "This query has more (perhaps significantly more) rows, would you like > to: Load all (May take a long time), Load Next 1000, Stop here." or some > other mechanism that achieves the same, though the "load next 1000" option > is harder to implement when you connect via 3rd party tools. > > A last thought, LIMIT is not a Query word or in any way affects the Query > idea that the Query planner sees, it has no algebraic influence on the > query. It is an output limitation only. If there is room with 100 people in > it, and I were to ask the curator: Please give me the names of the people > limited to 10 values, like so: > SELECT Name FROM People LIMIT 10 > he would take the list of 100 people and read off the 1st 10 of them. This > limitation doesn't shorten his list or affects the number of people in the > room, he just stops reading at some point to please me. So if I ask, how > many people are in the room, but stop reading at the 10th result. he is just > going to say: 100 people and stop there. Your limiting clause did not change > that fact. > > Were I to ask: How many people would be in the list if I asked you for a > list of people limited to 10 output values? He would answer 10 - of course, > that is why this query works: > SELECT COUNT(*) FROM (SELECT * FROM People LIMIT 10); > > Cheers, > Ryan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
