Re: [sqlite] Update Query

[Pavel Ivanov]

On Tue, May 29, 2012 at 6:05 AM, IQuant  wrote:
>>CREATE VIEW TICKMAX
>>AS
>> SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
>>    FROM TICKDATA
>>   WHERE TIMESTAMP = MAX(TIMESTAMP)
>>GROUP BY SYMBOL;
>
> Trying to work through your suggestions:
> I'm getting "Misuse of aggregate max()"

With the latest version of SQLite I guess this can be rewritten as

CREATE VIEW TICKMAX
AS
 SELECT ASK, BID, TRADEPRICE, MAX(TIMESTAMP), SYMBOL
   FROM TICKDATA
 GROUP BY SYMBOL;

But note only in SQLite 3.7.11 and later fields ASK, BID and
TRADEPRICE will be selected from the row with maximum TIMESTAMP,
previous SQLite versions will return values from random row.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[IQuant]

Keith,

Trying to work through your suggestions:
I'm getting "Misuse of aggregate max()"


>CREATE VIEW TICKMAX
>AS
> SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
>FROM TICKDATA
>   WHERE TIMESTAMP = MAX(TIMESTAMP)
>GROUP BY SYMBOL;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[Simon Slavin]

On 29 May 2012, at 1:16am, IQuant  wrote:

> I may try a 2 pass approach:  Pass 1 update query  to record the rowid
> of the previous symbol record and pass 2 use a join for performing the
> IQ calcs.

Yep.  Just do it in your programming language.  One SELECT to find the previous 
symbol records, then UPDATES.  That's what programming language are for.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[IQuant]

Not sure how to combine the subqueries.

Our TickData table looks close to:  TimeStamp, Symbol, Ask, Bid, Last
...  IQ_A, IQ_B, IQ_T, IQ_X

We need to update the IQ fields with calculations always made between
the current record and previous symbol record ordered by timestamp.  I
don't know how to eliminate the 4 subqueries...  Anyone have any code
for implementing oracle style lag / lead analytic functions?

This update query has become a monster...  string functions, math
calcs, sign checks, case, case etc.  All very simple to do in Excel
but not so easy as a Sql update query.

The IQ fields hold arrays of values formatted into a string and are
used to feed different downstream processes: ie.  charting engine and
price analysis.

IQ_A =
{1,-2,3,-19,25,-28,175,-228,359,-611,7105.5,-14165,21247.5;41019.5416927083,41019.5416741898,41019.5416241319,41019.5415546875,41019.5037196181,41019.4764363426,41019.939236,41018.6088674769,41016.6075604745,41009.610889,41002.4481603009,41002.395833912,41002.395833912}

IQ_B =
{1,-2,3,-19,25,-28,175,-228,359,-611,7105.5,-14163,21244.5;41019.5416927083,41019.5416741898,41019.5416241319,41019.5415546875,41019.5037196181,41019.4764363426,41019.939236,41018.6088674769,41016.6075604745,41009.610889,41002.4481603009,41002.395833912,41002.395833912}

IQ_T
{-1,3,-19,25,-29,175,-227.5,359.5,-611,7105,-14164,21246;41019.5416741898,41019.5416232639,41019.5415546875,41019.5038145255,41019.4764427083,41019.939236,41018.6088674769,41016.6077332176,41009.6107991898,41002.4481603009,41002.395833912,41002.395833912}

IQ_X
{39653,-13516,6737,-3974,-2650,-1874,-1376,-1084,-886,-726,-592,-522,-454,-376,-316,-302,-266,-248,-232,215;39667,-13512,6737,-3974,-2650,-1874,-1378,-1086,-886,-726,-592,-522,-454,-376,-316,-302,-266,-248,-232,215;-19456,9205,5157,-3240,-2234,-1618,-1258,-998,-808,-682,-564,-490,-412,-352,-312,-290,-262,-248,-230,209}

I'm able to run the core calculation portions of the update query very
fast.  ie.  < 5 seconds to update 1 ...  The string / array
manipulations are another story.
I may try a 2 pass approach:  Pass 1 update query  to record the rowid
of the previous symbol record and pass 2 use a join for performing the
IQ calcs.  I think we might have hit the wall and need to code this
externally.

http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[Keith Medcalf]

> On 25 May 2012, at 3:04am, IQuant <sql...@iquant.co.cc> wrote:
> 
> > update TICKDATA set IQ_A = ROUND(ASK - (
> >   select t2.ASK from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_B = ROUND(BID - (
> >   select t2.BID from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_T = ROUND(TRADEPRICE - (
> >   select t2.TRADEPRICE from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_X = (select t2.timestamp from TICKDATA t2
> >   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);
> >
> > The consolidated tick database is 1.5GB / 32M records and the above
> > query takes 5 minutes to run.

> On 24 May, 2012 20:11, Simon Slavin said:

> You appear to be doing the same sub-select four times.  I don't know whether
> SQLite optimizes these into one or not, but if you're using a programming
> language I bet you could speed the query up a great deal by doing that
> subselect first, and substituting the results into the UPDATE command.

It doesn't optimize correlated subqueries even with the same subselect criteria 
-- however the btree indexes are already positioned after the first so the next 
three just traverse the same tree already in memory.


However, here are two ways of moving the correlated queries from the inner loop 
to an outer loop:

-- need unique index on TICKDATA (SYMBOL, TIMESTAMP)
-- the view and the trigger can be defined in the database schema once
CREATE VIEW TICKMAX
AS
  SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
   WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;

CREATE TRIGGER TICKMAXUPDATE INSTEAD OF UPDATE ON TICKMAX FOR EACH ROW
BEGIN
  UPDATE TICKMAX
 SET IQ_A = ROUND(ASK - OLD.ASK, 4),
 IQ_B = ROUND(BID - OLD.BID, 4),
 IQ_T = ROUND(TRADEPRICE - OLD.TRADEPRICE, 4),
 IQ_X = OLD.TIMESTAMP
   WHERE SYMBOL = OLD.SYMBOL
 AND TIMESTAMP < OLD.TIMESTAMP;
END;

-- everytime you want to do an update, run this:
UPDATE TICKMAX SET ASK = ASK + 1;



Of course, if you are using a programming language you could do something like 
this:

cr.execute('BEGIN IMMEDIATE;')
for row in cr.execute('''SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
   WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;'''):
cr.execute('''UPDATE TICKMAX
  SET IQ_A = ROUND(ASK - :ASK, 4),
  IQ_B = ROUND(BID - :BID, 4),
  IQ_T = ROUND(TRADEPRICE - :TRADEPRICE, 4),
  IQ_X = :TIMESTAMP
WHERE SYMBOL = :SYMBOL
  AND TIMESTAMP < :TIMESTAMP;''', row)
cr.execute('COMMIT;)

In other words, select the values at the maximum timestamp for each symbol, 
then for each row returned execute the update statement updating the rest of 
the rows for that symbol ... both the trigger and the above code move the 
selection of the updating values from the inner loop to an outer loop.


Of course, they may not make much of a difference at all.  The latter two will 
do exactly count(distinct symbol) less row updates, but that is still pretty 
much every row in the database.  You will have to try it and see if the 
performance increase (if any) is worth the additional complexity.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of 
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Update Query
> 
> 
> 
> 
> Simon.
> ___
> 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] Update Query

[Simon Slavin]

On 25 May 2012, at 3:04am, IQuant  wrote:

> update TICKDATA set IQ_A = ROUND(ASK - (
>   select t2.ASK from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> IQ_B = ROUND(BID - (
>   select t2.BID from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> IQ_T = ROUND(TRADEPRICE - (
>   select t2.TRADEPRICE from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> IQ_X = (select t2.timestamp from TICKDATA t2
>   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);
> 
> The consolidated tick database is 1.5GB / 32M records and the above
> query takes 5 minutes to run.

You appear to be doing the same sub-select four times.  I don't know whether 
SQLite optimizes these into one or not, but if you're using a programming 
language I bet you could speed the query up a great deal by doing that 
subselect first, and substituting the results into the UPDATE command.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[IQuant]

Thanks for your suggestion Igor Tandetnik:

Scope creep expanded the original query to the actual trading
instruments and the refactored code has evolved to::

update TICKDATA set IQ_A = ROUND(ASK - (
   select t2.ASK from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_B = ROUND(BID - (
   select t2.BID from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_T = ROUND(TRADEPRICE - (
   select t2.TRADEPRICE from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
IQ_X = (select t2.timestamp from TICKDATA t2
   where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);

The consolidated tick database is 1.5GB / 32M records and the above
query takes 5 minutes to run.
Found proper index is key for performance...  Symbol,Timestamp
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update query along with limit option

[thirunavukarasu selvam]

Thanks for ur equivalent query.
Its working as expected
using rowid will not be prob rightAs long as its value is unique.
Regards,
Thiru.

On Sun, Apr 19, 2009 at 8:33 PM, Igor Tandetnik  wrote:

> "thirunavukarasu selvam"
>  wrote in message
> news:5b5250670904190006h7faba51ejbd9c392c0584c...@mail.gmail.com
> > I need a help in using update query along with limit option.
> > I tried the following query
> > update table-name SET status='1' where status='0' limit 2
> > status - column name.
>
> You could use this equivalent query with "standard" SQLite build:
>
> update tableName set status = '1' where rowid in
> (select rowid from tableName where status='0' limit 2);
>
> Igor Tandetnik
>
>
>
> ___
> 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] Update query along with limit option

[thirunavukarasu selvam]

status is basically used for display
Always new row is added with status 0
Once its been displayed the status has to be changed to 1. Its just to track
of rows displayed and new added rows.
On Sun, Apr 19, 2009 at 12:51 PM, John Machin  wrote:

> On 19/04/2009 5:06 PM, thirunavukarasu selvam wrote:
> > I tried the following query
> > update table-name SET status='1' where status='0' limit 2
> > status - column name.
>
> What is "status - column name" meant to do?
>
> > I am using sqlite-amalgamation-3.6.13.tar.gz source from sqlite.org.
> >
> > I configured it with the following command.
> >
> > CFLAGS="-0s -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT" ./configure
> >
> > Then did a "make".
> >
> > With newly compiled one, when i try the above said query it gives the
> > following error.
> >
> > SQL error: near "limit": syntax error
>
> http://www.sqlite.org/compile.html#enable_update_delete_limit
> ___
> 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] Update query along with limit option

[Igor Tandetnik]

"thirunavukarasu selvam"
 wrote in message
news:5b5250670904190006h7faba51ejbd9c392c0584c...@mail.gmail.com
> I need a help in using update query along with limit option.
> I tried the following query
> update table-name SET status='1' where status='0' limit 2
> status - column name.

You could use this equivalent query with "standard" SQLite build:

update tableName set status = '1' where rowid in
(select rowid from tableName where status='0' limit 2);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update query along with limit option

[John Machin]

On 19/04/2009 5:06 PM, thirunavukarasu selvam wrote:
> I tried the following query
> update table-name SET status='1' where status='0' limit 2
> status - column name.

What is "status - column name" meant to do?

> I am using sqlite-amalgamation-3.6.13.tar.gz source from sqlite.org.
> 
> I configured it with the following command.
> 
> CFLAGS="-0s -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT" ./configure
> 
> Then did a "make".
> 
> With newly compiled one, when i try the above said query it gives the
> following error.
> 
> SQL error: near "limit": syntax error

http://www.sqlite.org/compile.html#enable_update_delete_limit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update query along with limit option

[thirunavukarasu selvam]

Hello all,
Am new to sqlite.
I need a help in using update query along with limit option.
I tried the following query
update table-name SET status='1' where status='0' limit 2
status - column name.

I need to update the value of status columns having value 0 to value 1. That
too for only 2 records/rows.

I am using sqlite-amalgamation-3.6.13.tar.gz source from sqlite.org.

I configured it with the following command.

CFLAGS="-0s -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT" ./configure

Then did a "make".

With newly compiled one, when i try the above said query it gives the
following error.

SQL error: near "limit": syntax error

Is there anything else i need to modify in the code?

Can anyone guide me in using the query. Any other alternate query which will
do the same purpose which am looking is also welcome..

Thanks in advance for ur help.

Regards,
Thiru.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE query: why is that code sooo slow?

[D. Richard Hipp]

On Sat, 2005-01-22 at 11:50 -0500, Luc Vandal wrote:

> Perhaps Iâm not doing  things correctly, but this code will take about 1
> minute to execute (2400 records on a total of around 44000 records):
> 

>   for(INT nCurrRow=1;nCurrRow<=nRows;nCurrRow++)
>   {
>   strQuery.Format( "UPDATE something SET
> thatThing = 0, otherthing = %f WHERE stuff = %d AND hello = %d AND bye =
> -1", â );
> 
>   }
> 

What indices do you have on the SOMETHING table?  You're
going to want one to prevent your code from running in
time O(n**2).

Re: [sqlite] UPDATE query: why is that code sooo slow?

[Clay Dowling]

Gwendolynn ferch Elydyr wrote:
I seem to recall that there was a significant performance difference 
between using sqlite3_exec vs prepared statements.  You might try
using sqlite3_prepare/bind/finalize.
I would add that you need to do all of your database retreiving before 
starting the updating. Misreading this advice, it would sound like 
Gwnedolynn was advising you to intermix them. Although that's certainly 
not the case, I made that mistake on my first reading.

Prepared statement should save you a lot of time here. This is precisely 
the situation for which they excel.

Clay

RE: [sqlite] UPDATE query: why is that code sooo slow?

[Gwendolynn ferch Elydyr]

On Sat, 22 Jan 2005, Luc Vandal wrote:
Wrap he updates in a transaction.
I'm already doing that (unless I'm doing it wrong?):
nRet = sqlite3_exec( m_pDB, _T("BEGIN;"), 0, 0,  );
...
nRet = sqlite3_exec( m_pDB, _T("COMMIT;"), 0, 0,  );
I seem to recall that there was a significant performance difference 
between using sqlite3_exec vs prepared statements.  You might try
using sqlite3_prepare/bind/finalize.

cheers!
==
"A cat spends her life conflicted between a deep, passionate and profound
desire for fish and an equally deep, passionate and profound desire to
avoid getting wet.  This is the defining metaphor of my life right now."

Re: [sqlite] UPDATE query: why is that code sooo slow?

[Scott Wilkinson]

Luc Vandal wrote:
Hi,
Perhaps I’m not doing  things correctly, but this code will take about 1
minute to execute (2400 records on a total of around 44000 records):
 

Wrap he updates in a transaction.
See http://www.sqlite.org/lang_transaction.html for the syntax.
--
Scott Wilkinson 
MICROS Systems, Inc.

[sqlite] UPDATE query: why is that code sooo slow?

[Luc Vandal]

Hi,

Perhaps I’m not doing  things correctly, but this code will take about 1
minute to execute (2400 records on a total of around 44000 records):

Basically, I’m getting records that need to be updated. Then, I go through
each record and update it. Should be fast IMHO but I don’t see why it’s that
slow!

Is there another way that would be faster?


if( nRet == SQLITE_OK )
{
sqlite3_free_table( paszResults );

strQuery.Format( _T( "SELECT something, thatThing, otherthing, stuff
FROM table WHERE hello = %d;" ), VARIANT_TRUE );

nRet = sqlite3_get_table( m_pDB, strQuery, , ,
, );

if( nRet == SQLITE_OK )
{
if( nRows > 0 )
{
nRet = sqlite3_exec( m_pDB, _T("BEGIN;"), 0, 0,
 );

//Go through all the records
for(INT nCurrRow=1;nCurrRow<=nRows;nCurrRow++)
{
strQuery.Format( "UPDATE something SET
thatThing = 0, otherthing = %f WHERE stuff = %d AND hello = %d AND bye =
-1", … );

nRet = sqlite3_exec( m_pDB, strQuery, 0, 0,
 );

nColBad+=4;
nColGood+=4;
}

nRet = sqlite3_exec( m_pDB, _T("COMMIT;"), 0, 0,
 );
}

sqlite3_free_table( paszResults );
}

Thanks!

Luc