Re: [sqlite] sqlite3_get_table question
On Dec 30, 2008, at 1:29 AM, schachtobi wrote: > >> Date: Mon, 29 Dec 2008 11:20:58 +0700 >> From: Dan >> Subject: Re: [sqlite] sqlite3_get_table question >> To: General Discussion of SQLite Database >> Message-ID: <65fa0cc6-4248-4675-8100-f069423e0...@gmail.com > > >> Why (ncol+2)? > > > I make the following select statement: > > SELECT fname, dsize, data, ABS(red-%d)+ABS(green-%d)+ABS(blue-%d) err > FROM ch ORDER BY err LIMIT 1; > > If I want to have the data then this would be 2 + the number of > heading > columns (ncol) Right. Obviously. Note that sqlite3_get_table() won't work for data with embedded 0x00 bytes. It will truncate each value at the first one encountered. That could be the problem. > > > The database structure is like the following: > > CREATE TABLE IF NOT EXISTS ch( > fname TEXT UNIQUE, > red INTEGER, > green INTEGER, > blue INTEGER, > dsize INTEGER, > data BLOB); > > Does anybody sees a possible solution for accessing the data (blob) > for > indexes (rgb) higher than 4k Pixels? > > Regards, > Tobias > > ___ > 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] SQLITE_MAX_VARIABLE_NUMBER and .import for very wide file
>> I am sure there is a better way to deal with 12K rows by 2500 columns, >> but I can't figure it out > > 2500 columns sounds like a nightmare to deal with > > could you perhaps explain that data layout a little? It is a download of a huge longitudinal survey (www.bls.gov/nls/nlsy79.htm) that has been converted out of the proprietary format into SAS, and now I want to convert it into a single SQLITE database per wave. I will wind up connecting people by ID across the waves to show patterns of moving etc... For each wave/ table, each row describes contains integers that code for information about a single respondent, such as age, whether employed in June (either zero or one), whether employed in July, etc... Since the NLSY doesn't do multiple tables, this is very much NOT normalized. What the codes mean is described in a separate codebook (-5 = missing data, 1=living at home, etc). There is a separate table for each wave (1979, 1980, ... 2006). I have managed (just now) to get it working with a hacked version of SQLITE. Here is a meaningless query, just to confirm: sqlite> select W0072400, count(*) as c from data_stuff group by W0072400 order by c desc limit 5; 0,9204 -5,2513 100,293 1,80 3,43 CPU Time: user 0.917062 sys 0.364962 Like I say, I may be going about it all wrong, but I can't run the proprietary software on my Mac, and SQL makes me comfortable. I hope to pull out the data I want via SQL (a processed 1% of the total), then run statistical analyses and graphics with R. I am describing all this in hopes there is another quantitative sociologist out there using SQLITE! TIA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rachmat Febfauza Sent: Sunday, December 28, 2008 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] confusing with how to to this in sqlite thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,"Begin"); create index akhir1i1 on akhir1(Code,Category,Product,Location,"End"); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again = = Regarding syntax: If you don't get an error, the syntax is acceptible.;-) Sqlite *does* support compound indicies. However: -- You may want to use "EXPLAIN QUERY PLAN" as a prefix to your SELECT (just running as a test) to ensure than your index is used. -- You can quickly experiment with using a simple index on "BEGIN" or "PRODUCT" instead and measure times. -- As you measure times, be aware of possible "caching effects" -- i.e. the first run may be slower than subsequent runs of the a query on the same tables. -- Make sure you see the link on performance, below. -- Make sure you include many INCLUDES within a single TRANSACTION (if appropriate to your application). This can make a dramatic difference. -- You want to be familiar with the PRAGMA's that can affect performance. http://www.sqlite.org/pragma.html (but note that some of these can be used to trade data safety for performance -- make sure you're making an informed choice) Regarding: " is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file." Have you read http://www.sqlite.org/whentouse.html And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ? If not, you'll want to. Many folks successfully run sqlite on multi-gigabyte databases, BUT -- in those cases, the simplicity and small footprint of sqlite may be less compelling, -- Are there any features in http://www.sqlite.org/omitted.html that you will grieve in their absence? You might go over the detailed feature lists for postgres, Mysql, etc. with the same question in mind. -- How much concurant access do you anticipate? -- Will you control the queries (so as to optimize them and the indicies) or will the database be subjected frequently to ad hoc queries (which *might* benefit from a sophisticated query optimizer)? Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_get_table question
> Date: Mon, 29 Dec 2008 11:20:58 +0700 > From: Dan > Subject: Re: [sqlite] sqlite3_get_table question > To: General Discussion of SQLite Database > Message-ID: <65fa0cc6-4248-4675-8100-f069423e0...@gmail.com > Why (ncol+2)? I make the following select statement: SELECT fname, dsize, data, ABS(red-%d)+ABS(green-%d)+ABS(blue-%d) err FROM ch ORDER BY err LIMIT 1; If I want to have the data then this would be 2 + the number of heading columns (ncol) The database structure is like the following: CREATE TABLE IF NOT EXISTS ch( fname TEXT UNIQUE, red INTEGER, green INTEGER, blue INTEGER, dsize INTEGER, data BLOB); Does anybody sees a possible solution for accessing the data (blob) for indexes (rgb) higher than 4k Pixels? Regards, Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About clustering (GUIDs, PK offsets, etc.)
Hello! I did find interesting article about this popular question http://blog.maxindelicato.com/2008/12/how-to-organize-a-database-tables-keys-for-scalability.html I would also like to add that SQLite supports "attach" command due to which we are able to use a lot of database shards on a single machine without using a lot of memory. I did create ticket "GUID primary keys" and think it's may be very good feature: http://www.sqlite.org/cvstrac/tktview?tn=3557 Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER and .import for very wide file
On Sun, Dec 28, 2008 at 11:49:34PM -0800, Webb Sprague wrote: > I am sure there is a better way to deal with 12K rows by 2500 columns, > but I can't figure it out 2500 columns sounds like a nightmare to deal with could you perhaps explain that data layout a little? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users