5
$\begingroup$

New in v12.1 is the ability to connect to Postgres. I have a Postgresql database with a few million images (stored as byte data). Each image retrieval is ~10 seconds, so I need to speed it up! But how to speed this up, I don't think MMA has any way of doing threading?

What I've tried:

These are the steps I am using to connect and do the query:

ref=DatabaseReference[URL["postgres://<connection params>"]]
schema=RelationalDatabase[ref]    
store = EntityStore[schema]    
EntityRegister[store]    
screenshots=EntityList["screenshots"];

enter image description here

screenshotProperties = EntityProperties[screenshots[[1]]]
AbsoluteTiming[#->screenshots[[1]][#]&/@screenshotProperties//TableForm]

enter image description here

Improve this question
$\endgroup$
4
  • $\begingroup$ I lack access to any sort of Postgres database (or any other database, really) to test, but SessionSubmit lets you start asynchronous tasks on a separate kernel and use an event handling function when they finish. Perhaps that would help. $\endgroup$ Commented Mar 19, 2020 at 23:16
  • $\begingroup$ Sure, something to try but I don’t know if there are any more ways to handle multithreaded queries that need multiple database connections to unblock each other... $\endgroup$ Commented Mar 20, 2020 at 0:03
  • 1
    $\begingroup$ Indeed, 10Mb ByteArray could / should've been transferred faster. We will take a note / keep this use case in mind. I can't tell off hand, without testing, whether you can do anything simple enough on the WL top level to speed this up, but as I said, we will pay attention to this case and try to address this soon. $\endgroup$ Commented Mar 20, 2020 at 15:38
  • $\begingroup$ Hi I am doing the similar thing for "MicrosoftSQL" which supports multiple schemas in which tables reside like it is possible in "PostgreSQL" too but I am having issues where RelationalDatabase is unable to detect tables when they are placed in custom schemas. Are you able to check if similar issue is happening with "PostgreSQL"? If you can also help look into my question here. Any help is appreciated! $\endgroup$ Commented Apr 13 at 3:56

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.