Optimization for data storage

I’d like your advice on the design of my application.

I use websockets to receive new data and the request module to retrieve older data.
Then I use pyqtgraph to display data and tables etc with pyqt5.

There are some data that I don’t keep in memory, I just display them on screen without the possibility to interact with them, and I have other data that I keep in memory, with which I do some processing.

I would like to know if I should use dictionaries to store and process data or create a database with SQL or use pandas.
There will be a lot of inserting, extracting, deleting and a lot of calculations.

Potentially, when there are big moves, I could have thousands of messages per second to process, which I would have to add to my database, process and then display them on screen or do whatever I wanted with them.

If you have any questions, don’t hesitate.

Example of connection:

import websockets
import asyncio
import json

async def capture_data():
    subscriptions = [sub for sub in ["quote", "trade", "instrument"]]
    uri = "wss://www.bitmex.com/realtime?subscribe=" + ",".join(subscriptions)

    async with websockets.connect(uri) as websocket:
        while True:
            data = await websocket.recv()
            print(json.loads(data))


asyncio.get_event_loop().run_until_complete(capture_data())

Go to Source
Author: antho

Clear Terms from Taxonomy for Specific Post IDs?

Trying figure out how I can use some SQL/$wpdb->query to delete all the terms from a taxonomy that are related to specific post IDs. The following works beautifully to delete all the terms from a specific taxonomy, but I need to modify it to only remove ones associated with specific post IDs as mentioned.

// Clear Terms from Taxonomy 'post_tag'
DELETE t, tr, tt
FROM wp_terms t  
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

I’m also using the following to delete posts (works great):

$wpdb->query( "DELETE FROM $wpdb->posts WHERE ID IN (".implode( ", ", $postIds ).")" );

I’ve tried to do the following but it doesn’t clear the associated terms they just remain as orphaned in the DB, it just clears the relationship.

// Delete Post Terms
$wpdb->query( "DELETE FROM wp_term_relationships WHERE object_id IN (".implode( ", ", $postIds ).")" );

I’m a novice when it comes to SQL, any help is appreciated, thanks in advance!

Go to Source
Author: contempoinc

Is a transaction time of <10ms for an SQL database viable? If so, under what conditions?

Appreciate this is a rather odd question, so I will try to clarify as much as possible. Please also be assured this is a question purely for my own education, I’m not about to rush off and do crazy things in our software on the back of it.

I have a customer requirement for a transaction time of <10ms on a system that is based around an SQL database – in our specific implementation it is Oracle DB. I’m aware that this is not a useful or meaningful requirement, so with my business hat on I’ll be dealing with that. I fully expect that the requirement will be revised to something more useful and achievable.

However, I am curious on a technical level. Could you squeeze transaction time on an SQL DB down below 10ms? Lets be generous and say this is pure SQL execution time, no comms, no abstraction layers etc. Right now, running select 1 from dual on one of our systems gives a reported execution time of 10-20ms and I’d assume that’s about the simplest query possible. What if anything might you do to reduce that time (a) within Oracle/SQL or the server environment (b) by making a different tech choice? I’d assume maybe a higher clock speed on the CPU might help, but I wouldn’t bet on it.

Go to Source
Author: SimonN