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]
-----------------------------------------------------------------------------

Reply via email to