Re: [sqlite] Index performance using 2 integers vs. 1 float
Kosenko Max wrote: > > > I think it's better to try go with single integer. It perfectly fits range > 12:00:00 midnight, January 1, 0001 Anno Domini (Common Era) to 11:59:59 > P.M., December 31, A.D. (C.E.) in 100 nanosecond units. And it's good > idea to store all dates in UTC. > > Why do you need second integer? > > You can have even a bit better performance (due to smaller size of index) > in case you will use lower precision 1 second instead of 100 nanoseconds > or shorter date range (i.e. number of seconds since 1/1/1900 00:00) > > Float value will always take 8 bytes to store, integer will depend from > the size of value. Compound index will be slower than single. > That is helpful information, Kosenko. Thanks! -- View this message in context: http://www.nabble.com/Index-performance-using-2-integers-vs.-1-float-tp25165036p25228125.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index performance using 2 integers vs. 1 float
Simon Slavin-2 wrote: > > > We can't give you much idea because . . . > Another aspect is which fields you need to retrieve when you do your > SELECT. If your select needs to retrieve the time field, and the time > field doesn't appear in the index it's using, it will need to read the > time from the record too. So that would increase the amount of time > taken for retrieval. > > And lastly, of course, sorting on INTEGERs is a little quicker than > sorting on REALs. And there are a few other considerations about > handing and passing integers as opposed to floating-point numbers. > > Simon. > __ > Thanks for the information, Simon. I didn't realize a SELECT statement would read the data from the index itself, when possible, and I can see how that could speed up a query. No one jumped in and said "Definitely use integers for indexes instead of reals" so I'm assuming there isn't a very large penalty in performance using floats. That was what I was after in posting the question and I appreciate you taking the time to reply to me. Thanks again! Dan -- View this message in context: http://www.nabble.com/Index-performance-using-2-integers-vs.-1-float-tp25165036p25228071.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index performance using 2 integers vs. 1 float
Hello, We use a julian.decimal format to represent date/time (i.e. noon of August 26, 2009 would be 40049.5000) and we use this julian date for an index key. Our databases are frequently up to 3GB in size containing 10 million records with 15 assorted field types per record and contain 6 months of data. I'm proposing to break up the julian.date into two separate integers for the data and time and index off the date integer. Of course this change will give queries some sort of a speed boost, but by how much would you think? Many thanks, Dan -- View this message in context: http://www.nabble.com/Index-performance-using-2-integers-vs.-1-float-tp25165036p25165036.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 100 million records will be fine?
Yes, that does help me. Thank you for sharing! -Dan Rajesh Nair-5 wrote: > > I have a real time program which logs more than 30,000 records, each > record of about 200 bytes, per day and the company in which it has > been installed is working 24/365. I installed the project on 2005 > August and it is working fine till date. It perform some report > generations (4 or 5) every day. The data is dumped to Sqlite database > and I don't know the current size of that database. But the PC is with > just 256 MB RAM and 160 GB hard disk and CPU is 800MHz. It is working > for last 3-4 years without shutting down and without any data crash or > program crash. It is so designed to VACUUM the database on 20th of > each month or the nearest sunday. > The database when I last checked was of size 4 GB and it may be > increased to 6 or 7 GB now. > I have modified the program to split the database on yearly basis long > before, but the company is not ready to accept the modification. > > But still it works fine. Is that enough for you.. > > -- > Regards > Rajesh Nair > > -- View this message in context: http://www.nabble.com/100-million-records-will-be-fine--tp22038526p22120394.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 100 million records will be fine?
Hi. I've started a SQLite C++ project that could peak at 100 million records (250 bytes per record spread over 20 fields) and would like to ask if anyone has seen SQLite projects of this magnitude. The Windows data logging project will add up to 1 million records per day and run queries containing approximately 20,000 records a few times a day. I fully understand that performance will depend on the coding, database structure and indexing (& hardware) but, assuming these are taken care of, should a 100 million record table perform loosely in the same performance class as other popular databases? My concern is, for example, is hitting a brick wall in performance after 2 million, or whatever, records and so I wanted to post the question here before I go too far down development road. I appreciate your input on telling me if you've seen SQLite databases this large. Best regards, Dan Jenkins -- View this message in context: http://www.nabble.com/100-million-records-will-be-fine--tp22038526p22038526.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem using Attach to insert data from another table
Hi. I battling my way through the Attach command and I can't get my simple
test to work.
Given a database named "ultra2008.sql" that contains a table named "ultra"
and a database named "archive2007.sql" that also contains a table named
"ultra" where the databases and tables have identical structures, shouldn't
these statements insert ultra's data from archive2007.sql into
ultra2008.sql?
(For brevity I've removed the error checking lines for this posting.)
sqlite3 *pDB = NULL;
sqlite3_open("ultra2008.sql", );
sqlite3_exec(pDB, "attach 'archive2007.sql' as arc", NULL, NULL, NULL);
sqlite3_exec(pDB, "insert into main.ultra select * from arc.ultra", NULL,
NULL, NULL);
sqlite3_close(pDB);
After this function runs, the databases are left with the same data inside
of them so something isn't right.
I appreciate your input.
-Dan
--
View this message in context:
http://www.nabble.com/Problem-using-Attach-to-insert-data-from-another-table-tp17958315p17958315.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible to use field names instead of index offsets in queries?
Thank you for your help- I understand it better and can work it out now. -- View this message in context: http://www.nabble.com/Possible-to-use-field-names-instead-of-index-offsets-in-queries--tp17461475p17511500.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible to use field names instead of index offsets in queries?
Hi,
Without using a wrapper, is there a simple way of accessing a query's
results using field/column names instead of index offsets?
For example, instead of:
strcpy(DeptCodeStructure[i-1].DeptName, result[i*ncols+1]);
Something more like:
strcpy(DeptCodeStructure[i-1].DeptName,
fieldname("DeptName",result,i,ncols);
How does everyone else handle this kind of thing?
Many thanks!
Dan
For context, below I've included my typical routine.
sqlite3 *pDB = NULL; // database pointer
FILE* fp = fopen("dept.sql","a+");
sqlite3_open("dept.sql",);
char* sql = "SELECT * FROM Dept;";
char **result = 0;
int nrows, ncols;
if(pDB != NULL){
sqlite3_get_table(pDB,sqlNULL);
for(i = 1; i <= nrowss;i++){
strcpy(DeptCodeStructure[i-1].DeptName, result[i*ncols+1]);
DeptCodeStructure[i-1].RateCodeToUse = atoi(result[i*ncols+2]);
}
sqlite3_free_table(result);
}
sqlite3_close(pDB);
fclose(fp);
--
View this message in context:
http://www.nabble.com/Possible-to-use-field-names-instead-of-index-offsets-in-queries--tp17461475p17461475.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there direct (non SQL) table access?
Now that I know it's not easily doable, I'll just use an update statement. Thanks for saving me a lot of time guys! -Dan -- View this message in context: http://www.nabble.com/Is-there-direct-%28non-SQL%29-table-access--tp16075825p1608.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there direct (non SQL) table access?
Is it possible to access a table without a SQL query? I've been searching
and haven't found a way.
I'd like to do something like this:
sqlite3 *pDB = NULL; // database pointer
FILE* fp = fopen("people.db","a+");
sqlite3_open("people.db",);
// Pseudo code that I need help with follows...
while(!pDB->Eof){
if(pDB->FieldValues["Age"] > 65;
pDB->Edit();
pDB->FieldValues["Classification"] = "Retired";
pDB->Post();
}
pDB->Next();
}
fclose(fp);
sqlite3_close(pDB);
I'd really appreciate a couple of tips so I can get back on track with my
project.
Thanks a million.
Dan
--
View this message in context:
http://www.nabble.com/Is-there-direct-%28non-SQL%29-table-access--tp16075825p16075825.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
