[sqlite] Why did it need about 5 hours for converting?
I converted about 2000 binary files to SQLite3, it spent about 5 hours, I want to know if this is normal? Every file is about 2000 records. My process is very simple, read file in NSData and set to struct pointer, then use this pointer to get data and insert into database, is there any faster to do this converting? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why did it need about 5 hours for converting?
Thanks for your info, I try again tonight. --- Drake Wilson dr...@begriffli.ch wrote: Quoth Mike Zang mikez...@yahoo.co.jp, on 2010-09-05 17:49:31 +0900: I converted about 2000 binary files to SQLite3, it spent about 5 hours, I want to know if this is normal? Every file is about 2000 records. My process is very simple, read file in NSData and set to struct pointer, then use this pointer to get data and insert into database, is there any faster to do this converting? It sounds like you may be running into http://sqlite.org/faq.html#q19. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why did it need about 5 hours for converting?
Sorry that I asked the simplest question, I try to run PRAGMA synchronous = 0 for iPad app, can I just run it in sqlite3_exec(database, PRAGMA synchronous = 0, ...)? --- Drake Wilson dr...@begriffli.ch wrote: Quoth Mike Zang mikez...@yahoo.co.jp, on 2010-09-05 17:49:31 +0900: I converted about 2000 binary files to SQLite3, it spent about 5 hours, I want to know if this is normal? Every file is about 2000 records. My process is very simple, read file in NSData and set to struct pointer, then use this pointer to get data and insert into database, is there any faster to do this converting? It sounds like you may be running into http://sqlite.org/faq.html#q19. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why did it need about 5 hours for converting?
Thanks again, I got it. it is very fast. --- Drake Wilson dr...@begriffli.ch wrote: Quoth Mike Zang mikez...@yahoo.co.jp, on 2010-09-05 17:49:31 +0900: I converted about 2000 binary files to SQLite3, it spent about 5 hours, I want to know if this is normal? Every file is about 2000 records. My process is very simple, read file in NSData and set to struct pointer, then use this pointer to get data and insert into database, is there any faster to do this converting? It sounds like you may be running into http://sqlite.org/faq.html#q19. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which data type is better for date?
I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
#2 is non-standard. No time value. when I select, I will use days * 3600 #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option though. I will convert it to Date when select #4 gives room for mistakes; the year may be interpreted as the day. maybe you are right. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
Ben
In fact, I am converting a binary file to SQLite3, the file is in format as
below:
struct Stock {
int day;
int open;
int high;
int low;
int close;
double volume;
};
and I use code as below to get NSDate with 2010-09-03 00:00:00
#define kSecondsRest 18 * 60 * 60 - 59 * 60 - 28
NSData *data = [NSData dataWithContentsOfFile:file options:0 error:error];
struct Stock *stock = (struct Stock*)[data bytes];
int seconds = 86400 * (stock-day + 125913) - kSecondsRest;
int hours = seconds / 3600;
NSDate *date = [NSDate dateWithTimeIntervalSince1970:seconds];
--- Ben sqlite_l...@menial.co.uk wrote:
Mike,
If you are using iOS, then presumably you are using the NSDate class.
If you are, then the easiest thing to do is store the result of
- (NSTimeInterval)timeIntervalSinceReferenceDate . This stored value
can be turned back into an NSDate using [NSDate
dateWithTimeIntervalSinceReferenceDate:]
The type of NSTimeInterval is a double. This can be stored easily and
has good precision (see
http://developer.apple.com/mac/library/documentation/Cocoa/Reference/Foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html#//apple_ref/c/tdef/NSTimeInterval
)
Any further discussion along these lines would probably be better
taken to a mac development list such as cocoa-dev.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
I only need date, no time is ok, do you have more less memory method?
--- Zanardo zana...@gmail.com wrote:
While timestamps (seconds since 1970) need less storage space, I tend
to store dates and times with this format:
2010-09-04 09:15:37
This is more readable for ad-hoc queries, and you can easily use
range
operations with a simple BETWEEN or a = and =. SQLite has a
built-in function to generate this timestamp with the current date
and
time within the current time zone:
SELECT datetime('now', 'localtime') ;
Zanardo.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
It is ok even if use local time, because using UTC will let thing getting complex. --- Ted Rolle Jr. ster...@gmail.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And in addition, the TZ offset might be handy to convert to UTC. Local time is locally determined while UTC is constant, and other local offsets can be applied to display time in local terms. For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT is UTC-7; During WWII there was a ``War Time''. Some countries have a half-hour offset in addition to the hour offset, so 2010-09-03T09:10:12+4:30 is a valid time. It's _all_ politics; this makes it subject to the whim of each government. So, in addition to the half-hour offsets, time-zone offsets may change. Also, the determination of Daylight Savings time varies by country and can correspondingly change. UTC is best. That's the reason Unix uses seconds since 1970. I don't know what they do for dates before that; if the time can have a negative offset (proleptic) then all is well. Hmmm...Ask me the time; I'll give you my watch. :-) Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 5000 tables with 3000 records vs 1 table with 15000000 records
I have 5000 files and I want to converrt them to SQLite3 on iPad, now I have a performance question, I am not sure which way is better for select and insert data in SQLite3. I have two ideas for converting. 1. convert 1 file to 1 table, so that I will have about 5000 tables in SQLIte3 database, and any file will have about 3000 records. 2. convert all 5000 files to 1 table, there will be 1500 records. Please give a suggestion before I start my programming. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 5000 tables with 3000 records vs 1 table with 15000000 records
Thanks for your reply. In fact, the file is stock data and one file is for one stock, so they are all in the same format. Then, in most case, only one stock should be selected. --- Drake Wilson dr...@begriffli.ch wrote: Quoth Mike Zang mikez...@yahoo.co.jp, on 2010-08-22 17:51:05 +0900: I have two ideas for converting. 1. convert 1 file to 1 table, so that I will have about 5000 tables in SQLIte3 database, and any file will have about 3000 records. 2. convert all 5000 files to 1 table, there will be 1500 records. Please give a suggestion before I start my programming. This doesn't say very much. It depends a lot on what kind of files these are. In general, I would recommend picking whatever is most semantically appropriate first, and then optimizing later. SQLite scales well to millions of records for the most part, but your data may have unusual characteristics that you haven't mentioned. Since you mention the possibility of placing the data from all the files in a single table, does this imply that all the files contain the same type of records with the same meaning? If so, that may indicate that a single table solution is more appropriate so that any subset of the records can be selected at once. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
