Thread: [SQLObject] Connections And Threads
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Koen B. <ko...@ma...> - 2006-01-09 15:08:24
Attachments:
smime.p7s
|
I find it hard to understand how sqlobject manages its connections. I have one hub for all classes defined, and it works, but from what I understand sqlobject shares this one connection for every query. My interface is bound to results of lots of queries, and it would really speed things up if I had like 5 simultaneous connections fetching all the info I need for the view working in parralel. What would be the best way to approach this? On the model (sqlobject) or the controller layer? |
From: Randall S. <ra...@tn...> - 2006-01-09 22:27:20
|
Koen Bok wrote: > I find it hard to understand how sqlobject manages its connections. I > have one hub for all classes defined, and it works, but from what I > understand sqlobject shares this one connection for every query. My > interface is bound to results of lots of queries, and it would really > speed things up if I had like 5 simultaneous connections fetching all > the info I need for the view working in parralel. What would be the > best way to approach this? On the model (sqlobject) or the controller > layer? Koen, A connection in SQLObject functions as a pool and a hub allows you to bind a connection to a class after the class is created. To prepare a hub for a threaded environment, do like so: sqlobject.sqlhub.threadConnection = connectionForURI(your_conn_str) If you did not define a connection for your SQLObject subclasses, they will use sqlobject.sqlhub by default. Or you can create your own hub as an instance of sqlobject.dbconnection.ConnectionHub. The pooling is done automatically. Randall |
From: Koen B. <ko...@ma...> - 2006-01-10 00:21:37
Attachments:
smime.p7s
|
So is it smart to create a separate hub for every class or even every task? And can I fire multiple parallel fetch queries this way? Koen On 9-jan-2006, at 23:25, Randall Smith wrote: > Koen Bok wrote: >> I find it hard to understand how sqlobject manages its >> connections. I have one hub for all classes defined, and it >> works, but from what I understand sqlobject shares this one >> connection for every query. My interface is bound to results of >> lots of queries, and it would really speed things up if I had >> like 5 simultaneous connections fetching all the info I need for >> the view working in parralel. What would be the best way to >> approach this? On the model (sqlobject) or the controller layer? > Koen, > > A connection in SQLObject functions as a pool and a hub allows you > to bind a connection to a class after the class is created. To > prepare a hub for a threaded environment, do like so: > > sqlobject.sqlhub.threadConnection = connectionForURI(your_conn_str) > > If you did not define a connection for your SQLObject subclasses, > they will use sqlobject.sqlhub by default. Or you can create your > own hub as an instance of sqlobject.dbconnection.ConnectionHub. > > The pooling is done automatically. > > Randall > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through > log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD > SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Randall S. <ra...@tn...> - 2006-01-10 03:38:14
|
You only need one hub for all of your classes. That's why I like to use the built in hub, sqlobject.sqlhub. Someone please correct me if I'm wrong in what I'm about to say. After your thread is spawned, it should assign a connection to the hub like this: sqlobject.sqlhub.threadConnection = connectionForURI(...) The hub uses Python's built in threading.local to keep your connection out of the other threads so it is only used within the thread it is initiated in. "And can I fire multiple parallel fetch queries this way?" Yep. Randall Koen Bok wrote: > So is it smart to create a separate hub for every class or even every > task? And can I fire multiple parallel fetch queries this way? > > Koen |
From: Koen B. <ko...@ma...> - 2006-01-10 09:46:38
Attachments:
smime.p7s
|
So the idea is that you can have multiple parallel threads running sqlobject queries, but you have to create them yourself. Something like: def getAllPeople(): all_people = People.select() def getAllProducts(): all_Products = Product.select() t1 = Thread() t2 = Thread() t1.schuedleWork(getAllPeople()) t2.schuedleWork(getAllProducts()) # Here is nothing being done yet, but I fire up both threads to fetch data in parallel. t1.startWorking() t2.startWorking() > You only need one hub for all of your classes. That's why I like > to use the built in hub, sqlobject.sqlhub. Someone please correct > me if I'm wrong in what I'm about to say. After your thread is > spawned, it should assign a connection to the hub like this: > > sqlobject.sqlhub.threadConnection = connectionForURI(...) > > The hub uses Python's built in threading.local to keep your > connection out of the other threads so it is only used within the > thread it is initiated in. > > "And can I fire multiple parallel fetch queries this way?" > > Yep. > > Randall > > Koen Bok wrote: >> So is it smart to create a separate hub for every class or even >> every task? And can I fire multiple parallel fetch queries this way? >> Koen > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through > log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD > SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Randall S. <ra...@tn...> - 2006-01-10 14:55:11
|
Not what I had in mind. I was thinking along the lines of CherryPy, a threaded app server, or any other threaded server. How else would you run simultaneous queries without threads or multiple processes? Maybe I could help you better if I knew more about your application environment. Your example has the right idea. Just remember that the connection needs to be assigned to the hub within the thread. Probably better not to make your on threaded server, but use an existing one. What type of application are you creating (Web, WxWidgets, etc.)? Randall Koen Bok wrote: > So the idea is that you can have multiple parallel threads running > sqlobject queries, but you have to create them yourself. Something like: > > def getAllPeople(): > all_people = People.select() > > def getAllProducts(): > all_Products = Product.select() > > > t1 = Thread() > t2 = Thread() > > t1.schuedleWork(getAllPeople()) > t2.schuedleWork(getAllProducts()) > > # Here is nothing being done yet, but I fire up both threads to fetch > data in parallel. > > t1.startWorking() > t2.startWorking() |
From: Ian B. <ia...@co...> - 2006-01-10 17:26:15
|
Randall Smith wrote: > Not what I had in mind. I was thinking along the lines of CherryPy, a > threaded app server, or any other threaded server. How else would you > run simultaneous queries without threads or multiple processes? Maybe I > could help you better if I knew more about your application environment. > > Your example has the right idea. Just remember that the connection > needs to be assigned to the hub within the thread. Probably better not > to make your on threaded server, but use an existing one. What type of > application are you creating (Web, WxWidgets, etc.)? Also, if you have multiple threads but you know they will always share the same connection you can use: sqlhub.processConnection = ... As long as you don't need per-thread configurability this should be fine. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Koen B. <ko...@ma...> - 2006-01-10 20:11:32
Attachments:
smime.p7s
|
I am creating a point-of-sale client application partly based on sqlobject. The server is just a postgres database server. They talk in raw sql. Performance-wise it is fine. We tunnel the sql over a SSH compressed tunnel. The biggest queries take about 160ms, which is ok. So big queries aren't the problem. But sqlobject tends to generate lots of small queries, and if I could find a way to do those in parallel the my app would be a lot faster. On 10-jan-2006, at 18:25, Ian Bicking wrote: > Randall Smith wrote: >> Not what I had in mind. I was thinking along the lines of >> CherryPy, a threaded app server, or any other threaded server. >> How else would you run simultaneous queries without threads or >> multiple processes? Maybe I could help you better if I knew more >> about your application environment. >> Your example has the right idea. Just remember that the >> connection needs to be assigned to the hub within the thread. >> Probably better not to make your on threaded server, but use an >> existing one. What type of application are you creating (Web, >> WxWidgets, etc.)? > > Also, if you have multiple threads but you know they will always > share the same connection you can use: > > sqlhub.processConnection = ... > > As long as you don't need per-thread configurability this should be > fine. > > -- > Ian Bicking / ia...@co... / http://blog.ianbicking.org > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through > log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD > SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Randall S. <ra...@tn...> - 2006-01-11 18:31:10
|
Koen, This is a bit more difficult for me to tackle, but since no one else has, I'll give it a shot. For updates, you can set sqlmeta.lazyUpdates = True and call syncUpdate to issue your update statement(s) at one time. I do now know if this issues a single statement or multiple statements. Also, having sqlmeta.cacheValues=True (default) should help. Other than that I don't know what else you can do. If performance was too bad, I would consider putting SQLObject on the server side and using RPC of some sort. Randall Koen Bok wrote: > I am creating a point-of-sale client application partly based on > sqlobject. The server is just a postgres database server. They talk in > raw sql. > > Performance-wise it is fine. We tunnel the sql over a SSH compressed > tunnel. The biggest queries take about 160ms, which is ok. So big > queries aren't the problem. But sqlobject tends to generate lots of > small queries, and if I could find a way to do those in parallel the my > app would be a lot faster. > |
From: Koen B. <ko...@ma...> - 2006-01-12 13:51:20
Attachments:
smime.p7s
|
Thanks Randal. We reviewed that option, but adding an server-side rpc would mean add another point of failure. And the performance already pretty good. I work with cacheValues = True. I made my own methods to check if an object needs updating based on the sql now() function. In overall this already works really well. The only thing is when I get an order sqlobject needs: - The order - A person - An employee - The products as a list - Stock info for the selected product It now works linear in the above order. But it would be faster if it worked like this: |----------------------------------------------------------------------- ----| | THE ORDER | |----------------------------------------------------------------------- ----| | THREAD 1 | THREAD 2 | THREAD 3 | THREAD 4 | |----------------------------------------------------------------------- ----| | Employee | Person | Products | Stock | |----------------------------------------------------------------------- ----| | DONE | |----------------------------------------------------------------------- ----| So it gets all the data simultaneously. Is that achievable with sqlobject? On 11-jan-2006, at 19:29, Randall Smith wrote: > Koen, > > This is a bit more difficult for me to tackle, but since no one > else has, I'll give it a shot. > > For updates, you can set sqlmeta.lazyUpdates = True and call > syncUpdate to issue your update statement(s) at one time. I do now > know if this issues a single statement or multiple statements. > Also, having sqlmeta.cacheValues=True (default) should help. Other > than that I don't know what else you can do. If performance was > too bad, I would consider putting SQLObject on the server side and > using RPC of some sort. > > Randall > > > Koen Bok wrote: >> I am creating a point-of-sale client application partly based on >> sqlobject. The server is just a postgres database server. They >> talk in raw sql. >> Performance-wise it is fine. We tunnel the sql over a SSH >> compressed tunnel. The biggest queries take about 160ms, which is >> ok. So big queries aren't the problem. But sqlobject tends to >> generate lots of small queries, and if I could find a way to do >> those in parallel the my app would be a lot faster. > |
From: Randall S. <ra...@tn...> - 2006-01-12 16:18:40
|
That's a neat idea. If each of (Person, Employee, Products, StockInfo) is directly related to Order (not dependent on each other), that should work. I'm interested to see you implement this. Maybe like this: # This code is untested and probably riddled with errors. Just hashing out an idea. class TPerson(Thread): def __init__(self, person_id): self.person_id = person_id def run(self): person = Person.get(self.person_id) order_lock.acquire() order_data['person'] order_lock.release() class TProduct(Thread): def __init__(self, order): self.order = order def run(self): products = list(order.products) order_lock.acquire() order_data['products'] = products order_lock.release() # Where your final data will reside. order_data = {} order = Order.get(order_id) TPerson().start(order.person_id) TProduct().start(order) # Same for Employee and StockInfo # However, since StockInfo probably relies on Product, you might have to spawn a TStockInfo thread from the TProduct thread. While len(order_data) < 4: time.sleep(.05) # Got the data. Work with order_data Good luck! Randall Koen Bok wrote: > Thanks Randal. > > We reviewed that option, but adding an server-side rpc would mean add > another point of failure. And the performance already pretty good. I > work with cacheValues = True. I made my own methods to check if an > object needs updating based on the sql now() function. In overall this > already works really well. The only thing is when I get an order > sqlobject needs: > > - The order > - A person > - An employee > - The products as a list > - Stock info for the selected product > > It now works linear in the above order. > > But it would be faster if it worked like this: > > |---------------------------------------------------------------------------| > | THE ORDER > | > |---------------------------------------------------------------------------| > | THREAD 1 | THREAD 2 | THREAD 3 | THREAD 4 > | > |---------------------------------------------------------------------------| > | Employee | Person | Products | Stock > | > |---------------------------------------------------------------------------| > | DONE > | > |---------------------------------------------------------------------------| > > So it gets all the data simultaneously. Is that achievable with sqlobject? |
From: Koen B. <ko...@ma...> - 2006-01-12 17:02:59
Attachments:
smime.p7s
|
Thanks for the cool reply. I'm going to try this approach. I'll keep you updated. On 12-jan-2006, at 17:16, Randall Smith wrote: > That's a neat idea. If each of (Person, Employee, Products, > StockInfo) is directly related to Order (not dependent on each > other), that should work. I'm interested to see you implement this. > > Maybe like this: > > # This code is untested and probably riddled with errors. Just > hashing out an idea. > > class TPerson(Thread): > def __init__(self, person_id): > self.person_id = person_id > def run(self): > person = Person.get(self.person_id) > order_lock.acquire() > order_data['person'] > order_lock.release() > > class TProduct(Thread): > def __init__(self, order): > self.order = order > def run(self): > products = list(order.products) > order_lock.acquire() > order_data['products'] = products > order_lock.release() > > # Where your final data will reside. > order_data = {} > > order = Order.get(order_id) > TPerson().start(order.person_id) > TProduct().start(order) > # Same for Employee and StockInfo > # However, since StockInfo probably relies on Product, you might > have to spawn a TStockInfo thread from the TProduct thread. > > While len(order_data) < 4: > time.sleep(.05) > > # Got the data. Work with order_data > > Good luck! > > Randall > > Koen Bok wrote: >> Thanks Randal. >> We reviewed that option, but adding an server-side rpc would mean >> add another point of failure. And the performance already pretty >> good. I work with cacheValues = True. I made my own methods to >> check if an object needs updating based on the sql now() function. >> In overall this already works really well. The only thing is when >> I get an order sqlobject needs: >> - The order >> - A person >> - An employee >> - The products as a list >> - Stock info for the selected product >> It now works linear in the above order. >> But it would be faster if it worked like this: >> |-------------------------------------------------------------------- >> -------| >> | THE >> ORDER | >> |-------------------------------------------------------------------- >> -------| >> | THREAD 1 | THREAD 2 | THREAD 3 | >> THREAD 4 | >> |-------------------------------------------------------------------- >> -------| >> | Employee | Person | Products | >> Stock | >> |-------------------------------------------------------------------- >> -------| >> | >> DONE | >> |-------------------------------------------------------------------- >> -------| >> So it gets all the data simultaneously. Is that achievable with >> sqlobject? > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through > log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD > SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Koen B. <ko...@ma...> - 2006-03-09 11:41:44
Attachments:
smime.p7s
|
This does not actually work. When I call this: > order = Order.get(order_id) All the person and product info is already being acquired from the server, so I don't need to do anything more. Another idea I had was, overriding the databaseGet and databaseSet methods from the object to make them threaded. But maybe this has too many consequenses for the rest of the code as in when I insert an object threaded it has to wait for the thread to finish before it can get the new id... It would maybe be smarter to use a intermediate thread-launcher- object, but currently my interface is directly bound to the SQLObjects, and that is very nice! So maybe just make the UPDATE and SELECT functions from within the sqlobject threaded? On 12-jan-2006, at 17:16, Randall Smith wrote: > That's a neat idea. If each of (Person, Employee, Products, > StockInfo) is directly related to Order (not dependent on each > other), that should work. I'm interested to see you implement this. > > Maybe like this: > > # This code is untested and probably riddled with errors. Just > hashing out an idea. > > class TPerson(Thread): > def __init__(self, person_id): > self.person_id = person_id > def run(self): > person = Person.get(self.person_id) > order_lock.acquire() > order_data['person'] > order_lock.release() > > class TProduct(Thread): > def __init__(self, order): > self.order = order > def run(self): > products = list(order.products) > order_lock.acquire() > order_data['products'] = products > order_lock.release() > > # Where your final data will reside. > order_data = {} > > order = Order.get(order_id) > TPerson().start(order.person_id) > TProduct().start(order) > # Same for Employee and StockInfo > # However, since StockInfo probably relies on Product, you might > have to spawn a TStockInfo thread from the TProduct thread. > > While len(order_data) < 4: > time.sleep(.05) > > # Got the data. Work with order_data > > Good luck! > > Randall > > Koen Bok wrote: >> Thanks Randal. >> We reviewed that option, but adding an server-side rpc would mean >> add another point of failure. And the performance already pretty >> good. I work with cacheValues = True. I made my own methods to >> check if an object needs updating based on the sql now() function. >> In overall this already works really well. The only thing is when >> I get an order sqlobject needs: >> - The order >> - A person >> - An employee >> - The products as a list >> - Stock info for the selected product >> It now works linear in the above order. >> But it would be faster if it worked like this: >> |-------------------------------------------------------------------- >> -------| >> | THE >> ORDER | >> |-------------------------------------------------------------------- >> -------| >> | THREAD 1 | THREAD 2 | THREAD 3 | >> THREAD 4 | >> |-------------------------------------------------------------------- >> -------| >> | Employee | Person | Products | >> Stock | >> |-------------------------------------------------------------------- >> -------| >> | >> DONE | >> |-------------------------------------------------------------------- >> -------| >> So it gets all the data simultaneously. Is that achievable with >> sqlobject? > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through > log files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD > SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |