Hi yoky,
2008/10/29 yoky <[EMAIL PROTECTED]>:
> Hi all,
> I create a table with 250 columns and this table has 30000 records, I
> want to select the records from the table which satisfy certain conditions.
> the SQL statement like this:
> select * from tablename where (conditions);
> Then use sqlite3_get_table() function to get the contents. If the record
> set I get is a little more, my system has not enough memory to hold them.
> So changed the SQL statement:
> select ID from tablename where (conditions);
> ID is a primary key in the table, first I save all of ID into a buffer,
> then select them through ID one by one.
> select * from tablename where ID=id;
> Here is my problems:
> 1. The time to get all of the ID in the table (has 30000 records) is
> about 40 seconds. It's too long to meet the performance required.
> Are there some efficient way to select a lot of records satisfied
> some conditions from the table in the embedded system with not
> so fast CPU and not enough memory? Or select certain numbers records
> satisfied some conditions one time, and then select several
> times.
Don't use sqlite3_get_table(). Use
sqlite3_prepare_v2()
//
// bind any relevant parameters here, sqlite3_bind_int() etc...
//
while( SQLITE_ROW == sqlite3_step() )
{
//
// process row of data - get columns using sqlite3_column_int() etc
//
}
sqlite3_finalize()
>
> 2. I found the memory malloced by sqlite3_get_table() is more large than
> the database file when use "select * from tablename",why?
sqlite3_get_table() returns all data as strings. Any column data that
was of integer or real type would probably require more bytes in the
return from sqlite3_get_table() than the original storage format.
It is difficult to be certain without your source data and more
infomation on how big the difference is.
Rgds,
Simon
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
