batch or one by one?

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

batch or one by one?

Jim Wang
hi,all
   
     There is an interesting phenomenon.As you know, SQLite can retrieve records by batch or one by one.
     1.Retrieve by batch such as sqlite3_get_table.
     2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together.
     Using the above two methods to retrieve hundreds of records by random, which method is fast?
   
     best regard!
     WQG
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Scott Robison-2
On Wed, Dec 16, 2015 at 9:24 PM, 王庆刚 <[hidden email]> wrote:

> hi,all
>
>      There is an interesting phenomenon.As you know, SQLite can retrieve
> records by batch or one by one.
>      1.Retrieve by batch such as sqlite3_get_table.
>      2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step
> together.
>      Using the above two methods to retrieve hundreds of records by
> random, which method is fast?
>

1. Given that the get table method converts everything to a string, is a
legacy interface, and is not recommended for new use, probably prepare and
step assuming you don't use functions that will force datatype conversions.

2. It's incredibly simple to compile a little test apps with each option
and time them to know for certain in your environment, since if there are
differences it might depend on your schema, your hardware, your operating
system ... who knows what.

--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Igor Tandetnik-2
In reply to this post by Jim Wang
On 12/16/2015 11:24 PM, 王庆刚 wrote:
>       There is an interesting phenomenon.As you know, SQLite can retrieve records by batch or one by one.
>       1.Retrieve by batch such as sqlite3_get_table.
>       2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together.

If you look at the implementation of sqlite3_get_table, it works by
calling _prepare and _step and so on. It's not some kind of alternative
interface to SQLite, merely a wrapper.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Jim Wang
Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and sqlite3_step together.  In fact.

 

At 2015-12-17 14:48:18, "Igor Tandetnik" <[hidden email]> wrote:

>On 12/16/2015 11:24 PM, 王庆刚 wrote:
>>       There is an interesting phenomenon.As you know, SQLite can retrieve records by batch or one by one.
>>       1.Retrieve by batch such as sqlite3_get_table.
>>       2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together.
>
>If you look at the implementation of sqlite3_get_table, it works by
>calling _prepare and _step and so on. It's not some kind of alternative
>interface to SQLite, merely a wrapper.
>--
>Igor Tandetnik
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Igor Tandetnik-2
On 12/17/2015 2:04 AM, 王庆刚 wrote:
> Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and sqlite3_step together.  In fact.

You must be doing something wrong in your test harness. You are likely
measuring something other than actual SQLite performance. Show your code.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Stephan Beal-3
In reply to this post by Jim Wang
On Thu, Dec 17, 2015 at 8:04 AM, 王庆刚 <[hidden email]> wrote:

> Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and
> sqlite3_step together.  In fact.
>
>
And uses, on average, much more memory, as it stores all rows for the query
results in the result table. If your results have 10 rows, that method will
use, abstractly speaking, 10x as much memory. prepare/step allows code to
have a more or less constant memory usage, independent of the number of
rows in the result set.

The very first line of the get_table documentation says:

This is a legacy interface that is preserved for backwards compatibility.
Use of this interface is not recommended.


--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: batch or one by one?

Simon Slavin-3
In reply to this post by Jim Wang

On 17 Dec 2015, at 4:24am, 王庆刚 <[hidden email]> wrote:

>     1.Retrieve by batch such as sqlite3_get_table.

As the documentation says, please do not use sqlite3_get_table().  It was written a long time ago and better calls are now available.

"This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended."

You may use sqlite3_exec() instead if you want.

>     2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together.
>     Using the above two methods to retrieve hundreds of records by random, which method is fast?

As the documentation says

sqlite3_get_table() is a wrapper around sqlite3_exec().  In other words it calls sqlite3_exec() and does some other things.  So it cannot be faster than sqlite3_exec().

sqlite3_exec() is a wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize().  In other words it calls those routines and does some other things.  So it cannot be faster than they are.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users