RE: [sqlite] (select *) VS (select column1, column2 ...)
--- "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > I wanted to know how SQLITE works internally. Assume I am doing Try using the EXPLAIN command on your queries: EXPLAIN SELECT foo from bar; EXPLAIN SELECT * from bar; http://www.sqlite.org/lang_explain.html > But if SQLITE uses some other buffer then this might cause problem as > copying the whole data will certainly take more time than copying the > two columns. Selecting unnecessary columns add extra column opcodes to the select loop and can take up extra space in temp store for sub-queries. Here's a concrete example of how "select *" can be slower than just selecting the specific column(s) you need: $ ./sqlite3 slow.db .dump BEGIN TRANSACTION; CREATE TABLE n(a); INSERT INTO "n" VALUES(1); INSERT INTO "n" VALUES(2); INSERT INTO "n" VALUES(3); INSERT INTO "n" VALUES(4); INSERT INTO "n" VALUES(5); INSERT INTO "n" VALUES(6); INSERT INTO "n" VALUES(7); INSERT INTO "n" VALUES(8); INSERT INTO "n" VALUES(9); INSERT INTO "n" VALUES(10); CREATE VIEW v as select n1.a-n5.a k,* from n n1,n n2,n n3,n n4,n n5,n n6; COMMIT; $ time ./sqlite3 slow.db "select min(k) from (select * from v limit 99);" -9 6.99user 0.23system 0:07.24elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+4042minor)pagefaults 0swaps $ time ./sqlite3 slow.db "select min(k) from (select k from v limit 99);" -9 4.63user 0.13system 0:04.81elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+4036minor)pagefaults 0swaps You can see that using "select *" takes roughly 50% more time in this case. The difference in time depends entirely on your specific query and the data. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] (select *) VS (select column1, column2 ...)
Thanks Tom,
I wanted to know how SQLITE works internally. Assume I am doing
"select *" and "select column1, column2" for some row. In both the cases
it will have to go through the B-Tree to reach that row. I believe this
is done when sqlite3_step is called.
Now assume I have reached the node. Now I have to retrieve column1 and
column2 of this node. I believe this is done when I call
sqlite3_column_int and its variants. In case of "select *", I can input
the column number of column1, and column2 directly to get the results
without concerning about other columns. And if I retrieve these two
columns using "select column1, column2" then I have to input column no's
1 and 2 to get theses values. I believe the time to get any particular
column (column1 or column2 ... column40) will be same. If that's the
case then as far as sqlite3_step and Sqlite3_column_int functions are
concerned both "select *" (extracting only column1 and column2) and
"select column1, column2" will take the same time to retrieve the two
columns (column1 and column2). So I shouldn't see any difference (very
minor difference) between "select *" and "select column1, column2", when
I try to extract just the two columns.
But if SQLITE uses some other buffer then this might cause problem as
copying the whole data will certainly take more time than copying the
two columns.
Please let me know if I am wrong and let me understand what exactly
happens.
Regards,
Phani
-Original Message-
From: Tom Briggs [mailto:[EMAIL PROTECTED]
Sent: Friday, August 31, 2007 6:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] (select *) VS (select column1, column2 ...)
In general, it's best to only include the columns you need in the
SELECT clause. And not just with SQLite - that's the best approach when
dealing with any database. SQLite is a bit more forgiving because
there's no network between the client and the database to slow things
down, but that's still a good rule to follow.
In the particular example you cited, I think that the difference
would be so minimal as to be unnoticeable. But there will definitely be
a difference - the sqlite3_prepare call will make it possible to
retrieve any of the 40 columns if you do "select *", while it will only
make available the three you name if you use "select col1, col2, col3".
It can't know what you're going to do after the query is executed, so it
has to prepare for any possibility.
So, yes, there's a difference. Yes, selecting only the columns you
need is more efficient. No, I don't think you'll notice much of a
difference in terms of performance.
-T
> -Original Message-
> From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 7:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] (select *) VS (select column1, column2 ...)
>
> Assume I have a table with 40 columns. I would like to know the
> difference between
>
>
>
> Select * from table
>
> Select column1, column2, column3 from table
>
>
>
> While doing SQLITE3_PREPARE, will both take same amount of time?
>
> While doing SQLITE3_STEP, will both take same amount of time?
>
> --
> --
> ---
>
> sqlite3_prepare("Select * from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
> sqlite3_prepare("Select column1, column2, column3 from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
>
>
> If I want to extract just the 3 columns (column1, column2,
> column3), and
> use select* from table as sql query, how much impact it will have?
>
>
>
> Why I want to do this is because in some cases I need some particular
> combination in another any other combination of columns to be
> extracted?
> (It's possible for me to do this using "select * from
Re: [sqlite] (select *) VS (select column1, column2 ...)
Using SELECT * will be slower. How much slower depends on your table. Why don't you test it and see? --- "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > Assume I have a table with 40 columns. I would like to know the > difference between > > Select * from table > > Select column1, column2, column3 from table > > While doing SQLITE3_PREPARE, will both take same amount of time? > > While doing SQLITE3_STEP, will both take same amount of time? Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] (select *) VS (select column1, column2 ...)
In general, it's best to only include the columns you need in the
SELECT clause. And not just with SQLite - that's the best approach when
dealing with any database. SQLite is a bit more forgiving because
there's no network between the client and the database to slow things
down, but that's still a good rule to follow.
In the particular example you cited, I think that the difference
would be so minimal as to be unnoticeable. But there will definitely be
a difference - the sqlite3_prepare call will make it possible to
retrieve any of the 40 columns if you do "select *", while it will only
make available the three you name if you use "select col1, col2, col3".
It can't know what you're going to do after the query is executed, so it
has to prepare for any possibility.
So, yes, there's a difference. Yes, selecting only the columns you
need is more efficient. No, I don't think you'll notice much of a
difference in terms of performance.
-T
> -Original Message-
> From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 7:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] (select *) VS (select column1, column2 ...)
>
> Assume I have a table with 40 columns. I would like to know the
> difference between
>
>
>
> Select * from table
>
> Select column1, column2, column3 from table
>
>
>
> While doing SQLITE3_PREPARE, will both take same amount of time?
>
> While doing SQLITE3_STEP, will both take same amount of time?
>
> --
> --
> ---
>
> sqlite3_prepare("Select * from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
> sqlite3_prepare("Select column1, column2, column3 from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
>
>
> If I want to extract just the 3 columns (column1, column2,
> column3), and
> use select* from table as sql query, how much impact it will have?
>
>
>
> Why I want to do this is because in some cases I need some particular
> combination in another any other combination of columns to be
> extracted?
> (It's possible for me to do this using "select * from table" but it's
> not possible if I used "select column1, column2, column3 from
> table" as
> I will have to frame another query)
>
>
>
> NOTE: Please don't look at the syntax of sqlite3_prepare I just wrote
> the code to show what I want to do.
>
>
>
> Regards,
>
> Phani
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] (select *) VS (select column1, column2 ...)
Assume I have a table with 40 columns. I would like to know the
difference between
Select * from table
Select column1, column2, column3 from table
While doing SQLITE3_PREPARE, will both take same amount of time?
While doing SQLITE3_STEP, will both take same amount of time?
---
sqlite3_prepare("Select * from table");
while(1)
{
iRet = sqlite3_step(pStmt);
if(iRet != SQLITE_ROW)
{
iRet = sqlite3_finalize(pStmt);
break;
}
Sqlite3_column_int(pStmt, column1);
Sqlite3_column_int(pStmt, column2);
Sqlite3_column_int(pStmt, column3);
}
---
sqlite3_prepare("Select column1, column2, column3 from table");
while(1)
{
iRet = sqlite3_step(pStmt);
if(iRet != SQLITE_ROW)
{
iRet = sqlite3_finalize(pStmt);
break;
}
Sqlite3_column_int(pStmt, column1);
Sqlite3_column_int(pStmt, column2);
Sqlite3_column_int(pStmt, column3);
}
---
If I want to extract just the 3 columns (column1, column2, column3), and
use select* from table as sql query, how much impact it will have?
Why I want to do this is because in some cases I need some particular
combination in another any other combination of columns to be extracted?
(It's possible for me to do this using "select * from table" but it's
not possible if I used "select column1, column2, column3 from table" as
I will have to frame another query)
NOTE: Please don't look at the syntax of sqlite3_prepare I just wrote
the code to show what I want to do.
Regards,
Phani
