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 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]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
