Hubertus wrote:
Well now, if this isn't something!
fist I wann thank you for your quick reply. Finally I have to wait for my result
satisfying 0.6 sec. This is great. Now I can add lots more data.
What I did:
- I added a second index for the column "campId" and did the analyze trick
(I had this column in the index before, but that time it was quicker without
that column).
This already improved the waiting time from the former best 3 sec to 1 sec
- I increased the page_size to 4096, this decresed the size of the
database from 650 Mb to 450.
- And at last I replaced the -9999.99 values with NULL (don't think about it. I
was asked to do this as missing value, now I found the .nullvalue...)
This again decreased the size to stunning 165 Mb!! and improved the
query time to even better 0.6 sec.
To Dennis:
I'm afraid I haven't quite understood the quote thing.
First how can I do a query like
select "42" from data where campId='stream94' and "14">-9999;
from my shell?
Secondondly usually I use python or R to access the data where I do somthing
like
INSERT = 'SELECT "%i" FROM data where campId="%s"' % col, campId
query <- paste('SELECT "34" AS "N2O_TDL", "29" AS "O3"',
'FROM data where campId="polstar97"')
rs <- dbSendQuery(con, statement = query)
How is this done correctly?
Thanks a lot
Hubertus
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Hubertus,
There are a couple of issues involved here. First, standard SQL syntax
uses single quote to delimit literal values and double quotes to delimit
quoted identifiers (usually used for identifiers that are keywords or
contain special characters like space). Second you are using both types
of quoting in your SQL query. Third, the bash shell uses both single and
double quotes to delimit strings with or without variable substitution
applied. And finally, Python uses both types of quotes (and several
other) for string literals.
In python you can use a triple quote to delimit a string that contains
other quotes to treat them as literal quotes. Your query can be done
like this.
>>> print '''select "%i" from data where campId='%s';''' % (14, 'polestar')
select "14" from data where campId='polestar';
or you can use a backslash to escape the quotes used to delimit the
string like this.
>>> print 'select "%i" from data where campId=\'%s\';' % (14, 'polestar')
select "14" from data where campId='polestar';
In the bash shell single quotes are used where command and variable
substitution are not desired, and single quotes can not appear in a
single quoted string (even with a backslash escape). Double quotes allow
command and variable substitution and escaping of literal characters. To
prepare a command that includes both types of quotes you need to use
double quotes as the outer delimiters and then escape any double quotes
in the string using a backslash.
sqlite3 mydb "select \"14\" from data where campId='polestar';"
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
