Question about the LIMIT keyword

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about the LIMIT keyword

Trygg Johan
Hello,

I'm using SQLite with TCL and I have a small question:

Is it possible to get information on how many lines a select query with
the LIMIT keyword would have produced if I hadn't used the LIMIT
keyword?

One way of doing this would be to do another query without the LIMIT
keyword, count the number of lines you got, and then throw away the
resulting data, but this seem to be a bit of waste of both resources and
time... so I hope someone has a better solution :)

Thanks in advance,
Johan Trygg

Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Paolo Vernazza
Trygg Johan wrote:

>Hello,
>
>I'm using SQLite with TCL and I have a small question:
>
>Is it possible to get information on how many lines a select query with
>the LIMIT keyword would have produced if I hadn't used the LIMIT
>keyword?
>
>One way of doing this would be to do another query without the LIMIT
>keyword, count the number of lines you got, and then throw away the
>resulting data, but this seem to be a bit of waste of both resources and
>time... so I hope someone has a better solution :)
>
>Thanks in advance,
>Johan Trygg
>  
>
I'm not sure what do you need... but you tried using
SELECT COUNT(*) FROM etc etc etc
?

Paolo
Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

BertV
Paolo Vernazza wrote:

> Trygg Johan wrote:
>
>> Hello,
>>
>> I'm using SQLite with TCL and I have a small question:
>>
>> Is it possible to get information on how many lines a select query with
>> the LIMIT keyword would have produced if I hadn't used the LIMIT
>> keyword?
>>
>> One way of doing this would be to do another query without the LIMIT
>> keyword, count the number of lines you got, and then throw away the
>> resulting data, but this seem to be a bit of waste of both resources and
>> time... so I hope someone has a better solution :)
>>
>> Thanks in advance,
>> Johan Trygg
>>  
>>
> I'm not sure what do you need... but you tried using
> SELECT COUNT(*) FROM etc etc etc


IMHO COUNT does a complete tablescan to count the records, it did in a
previous version of sqlite
Bert

> ?
>
> Paolo
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Trygg Johan
In reply to this post by Trygg Johan
If I would use SELECT COUNT(*) then I have to ask the query again,
right?
E.g:
First I have to do:
SELECT * FROM data WHERE Foo == "bar" LIMIT 1000;
to get the data and then
SELECT COUNT(*) FROM data WHERE Foo == "bar";
to get the total lnumber of lines.

The problem is that the database is very large and the query can be
complex, so I want to avoid to use two queries.
I had hoped that there would be a way to do just ask one query with the
LIMIT keyword and also get the total number of lines.

Johan

 

>Bert Verhees wrote:
>IMHO COUNT does a complete tablescan to count the records, it did in a
>previous version of sqlite
>Bert
>
>> Paolo Vernazza wrote:
>> I'm not sure what do you need... but you tried using
>> SELECT COUNT(*) FROM etc etc etc
>> Paolo
>>
>>> Trygg Johan wrote:
>>>
>>> Hello,
>>>
>>> I'm using SQLite with TCL and I have a small question:
>>>
>>> Is it possible to get information on how many lines a select query
with
>>> the LIMIT keyword would have produced if I hadn't used the LIMIT
>>> keyword?
>>>
>>> One way of doing this would be to do another query without the LIMIT
>>> keyword, count the number of lines you got, and then throw away the
>>> resulting data, but this seem to be a bit of waste of both resources
and
>>> time... so I hope someone has a better solution :)
>>>
>>> Thanks in advance,
>>> Johan Trygg
>>>
Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Paul Smith-6
In reply to this post by Trygg Johan
At 14:06 14/06/2005, you wrote:

>If I would use SELECT COUNT(*) then I have to ask the query again,
>right?
>E.g:
>First I have to do:
>SELECT * FROM data WHERE Foo == "bar" LIMIT 1000;
>to get the data and then
>SELECT COUNT(*) FROM data WHERE Foo == "bar";
>to get the total lnumber of lines.
>
>The problem is that the database is very large and the query can be
>complex, so I want to avoid to use two queries.
>I had hoped that there would be a way to do just ask one query with the
>LIMIT keyword and also get the total number of lines.

In that case, I'd probably just do the query once, without the LIMIT, but
throw away the results after you've reached 1000, just count the number of
rows. In C++ this seems to be pretty quick, but I'm not sure what it would
be like if the client code was written in TCL.


>Johan
>
>
> >Bert Verhees wrote:
> >IMHO COUNT does a complete tablescan to count the records, it did in a
> >previous version of sqlite
> >Bert
> >
> >> Paolo Vernazza wrote:
> >> I'm not sure what do you need... but you tried using
> >> SELECT COUNT(*) FROM etc etc etc
> >> Paolo
> >>
> >>> Trygg Johan wrote:
> >>>
> >>> Hello,
> >>>
> >>> I'm using SQLite with TCL and I have a small question:
> >>>
> >>> Is it possible to get information on how many lines a select query
>with
> >>> the LIMIT keyword would have produced if I hadn't used the LIMIT
> >>> keyword?
> >>>
> >>> One way of doing this would be to do another query without the LIMIT
> >>> keyword, count the number of lines you got, and then throw away the
> >>> resulting data, but this seem to be a bit of waste of both resources
>and
> >>> time... so I hope someone has a better solution :)
> >>>
> >>> Thanks in advance,
> >>> Johan Trygg
> >>>

Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/


Reply | Threaded
Open this post in threaded view
|

RE: Question about the LIMIT keyword

Christian Schwarz
In reply to this post by Trygg Johan
Johan,

why not simply executing your query without the LIMIT keyword? You could
step through the whole result set to count the total lines while
fetching the actual column values only for the first 1000 lines. This
would be faster than executing your query twice.

Greetings, Christian
Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Puneet Kishor
In reply to this post by Paul Smith-6

On Jun 14, 2005, at 8:21 AM, Paul Smith wrote:

> At 14:06 14/06/2005, you wrote:
>> If I would use SELECT COUNT(*) then I have to ask the query again,
>> right?
>> E.g:
>> First I have to do:
>> SELECT * FROM data WHERE Foo == "bar" LIMIT 1000;
>> to get the data and then
>> SELECT COUNT(*) FROM data WHERE Foo == "bar";
>> to get the total lnumber of lines.
>>
>> The problem is that the database is very large and the query can be
>> complex, so I want to avoid to use two queries.
>> I had hoped that there would be a way to do just ask one query with
>> the
>> LIMIT keyword and also get the total number of lines.
>
> In that case, I'd probably just do the query once, without the LIMIT,
> but throw away the results after you've reached 1000, just count the
> number of rows. In C++ this seems to be pretty quick, but I'm not sure
> what it would be like if the client code was written in TCL.
>

Don't know about Tcl, but make sure that you don't fetch all the
records (analogy: fetchall... methods in Perl DBI), but fetch just a
pointer to the cursor (fetchrow... methods), and then step through the
cursor. If you fetch all the records then the whole point is defeated.

An alternative method is to define a separate table that keeps the
COUNT of the rows, and define a trigger that keeps that COUNT updated
every time you DELETE/INSERT/UPDATE on the main table.

All depends on what you mean by "database is very large." For most
"large" SQLite still should be very fast, but if its largeness is
indeed contributing to a slowdown then the above alternatives should
work.




>
>> Johan
>>
>>
>> >Bert Verhees wrote:
>> >IMHO COUNT does a complete tablescan to count the records, it did in
>> a
>> >previous version of sqlite
>> >Bert
>> >
>> >> Paolo Vernazza wrote:
>> >> I'm not sure what do you need... but you tried using
>> >> SELECT COUNT(*) FROM etc etc etc
>> >> Paolo
>> >>
>> >>> Trygg Johan wrote:
>> >>>
>> >>> Hello,
>> >>>
>> >>> I'm using SQLite with TCL and I have a small question:
>> >>>
>> >>> Is it possible to get information on how many lines a select query
>> with
>> >>> the LIMIT keyword would have produced if I hadn't used the LIMIT
>> >>> keyword?
>> >>>
>> >>> One way of doing this would be to do another query without the
>> LIMIT
>> >>> keyword, count the number of lines you got, and then throw away
>> the
>> >>> resulting data, but this seem to be a bit of waste of both
>> resources
>> and
>> >>> time... so I hope someone has a better solution :)
>> >>>
>> >>> Thanks in advance,
>> >>> Johan Trygg
>> >>>
>
> Paul                            VPOP3 - Internet Email Server/Gateway
> [hidden email]                      http://www.pscs.co.uk/
>
>
>
--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Paolo Vernazza
In reply to this post by Trygg Johan
If you don't mind to use some memory, you can use sqlite3_get_table.
http://www.sqlite.org/capi3ref.html#sqlite3_get_table

Paolo

>If I would use SELECT COUNT(*) then I have to ask the query again,
>right?
>E.g:
>First I have to do:
>SELECT * FROM data WHERE Foo == "bar" LIMIT 1000;
>to get the data and then
>SELECT COUNT(*) FROM data WHERE Foo == "bar";
>to get the total lnumber of lines.
>
>The problem is that the database is very large and the query can be
>complex, so I want to avoid to use two queries.
>I had hoped that there would be a way to do just ask one query with the
>LIMIT keyword and also get the total number of lines.
>
>Johan
>
>  
>  
>
>>Bert Verhees wrote:
>>IMHO COUNT does a complete tablescan to count the records, it did in a
>>previous version of sqlite
>>Bert
>>
>>    
>>
>>>Paolo Vernazza wrote:
>>>I'm not sure what do you need... but you tried using
>>>SELECT COUNT(*) FROM etc etc etc
>>>Paolo
>>>
>>>      
>>>
>>>>Trygg Johan wrote:
>>>>
>>>>Hello,
>>>>
>>>>I'm using SQLite with TCL and I have a small question:
>>>>
>>>>Is it possible to get information on how many lines a select query
>>>>        
>>>>
>with
>  
>
>>>>the LIMIT keyword would have produced if I hadn't used the LIMIT
>>>>keyword?
>>>>
>>>>One way of doing this would be to do another query without the LIMIT
>>>>keyword, count the number of lines you got, and then throw away the
>>>>resulting data, but this seem to be a bit of waste of both resources
>>>>        
>>>>
>and
>  
>
>>>>time... so I hope someone has a better solution :)
>>>>
>>>>Thanks in advance,
>>>>Johan Trygg
>>>>
>>>>        
>>>>
>
>
>  
>

Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Paul Smith-6
In reply to this post by Puneet Kishor
At 14:46 14/06/2005, you wrote:

>An alternative method is to define a separate table that keeps the COUNT
>of the rows, and define a trigger that keeps that COUNT updated every time
>you DELETE/INSERT/UPDATE on the main table.

I actually thought of that as well, but he wants to know how many records
match a particular query - not how many are in a particular table. If there
are only a few different queries, it might still be doable, but if the
number of possible queries is large, it's not really practical.


Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/


Reply | Threaded
Open this post in threaded view
|

Re: Question about the LIMIT keyword

Trygg Johan
In reply to this post by Trygg Johan
Thaks for the suggestions!

I'm going to try to do the query without the LIMIT keyword, and then
only use the rows I need.
I'm not sure if you can get a "pointer" and then only fetch the rows you
need in TCL, but I'll see if that is possible.

The program I'm writing must be able to run on rather old machines, so
in this case speed is important. Hovewer I do agree that SQLite is a
very fast database :)

Johan


Puneet Kishor wrote:

>Don't know about Tcl, but make sure that you don't fetch all the
>records (analogy: fetchall... methods in Perl DBI), but fetch just a
>pointer to the cursor (fetchrow... methods), and then step through the
>cursor. If you fetch all the records then the whole point is defeated.
>
>An alternative method is to define a separate table that keeps the
>COUNT of the rows, and define a trigger that keeps that COUNT updated
>every time you DELETE/INSERT/UPDATE on the main table.
>
>All depends on what you mean by "database is very large." For most
>"large" SQLite still should be very fast, but if its largeness is
>indeed contributing to a slowdown then the above alternatives should
>work.