Re: [sqlite] sqlite3_get_table question

[Dan]

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

[Webb Sprague]

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

[Griggs, Donald]

 

-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

[schachtobi]

> 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.)

[Alexey Pechnikov]

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

[Chris Wedgwood]

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