[PD-dev] Fwd: Fwd: Fwd: Connecting up an SQL Database to PD

[Jamie Bullock]

On Mon, 2007-11-12 at 16:18 -0600, Mike McGonagle wrote:

> 
> One of the assumptions that I was making is that only the DATA is
> being returned, I was not thinking about inserting the keys into the
> lists. I am trying to think in terms of what would be the most
> efficient in regards to the amount of time spent processing each
> result set.

Well it amounts to calling SETFLOAT() per iteration over the results or
not, so I think any difference in execution time would be negligible.
However, I like your idea of making the tuple indices optional. I will
add this to [psql]: |keys 0(, |keys 1( to turn the keys on and off.

> I just wonder about the extra overhead involved in dealing with tagged
> lists of data.
I just did a benchmark, and for a billion iterations, with a compiler
optimisation level of O2, both a loop with the SETFLOAT statement, and
an empty loop completed in the order of 1-2 microseconds!


> 
> That being said, I am curious to know how the performance is that you
> are getting? Having to deal with the keys doubles the amount of data
> handling right from the get-go.

See above, but what makes you think it doubles the data handling?


> 
> Well, I am thinking along the lines of not having to process, or
> process as little as possible, the result sets. I would prefer to be
> able to take the data straight out of the external, and use it without
> having to do any routing.
> 
> Would this approach provide for faster performance?

Maybe slightly, but personally I would think about getting the design
right, rather than optimisation at the moment. Unless you know that you
are going to be dealing with queries that return MASSIVE result sets!


> I guess one of my goals here is to be able to store a score (or some
> sort of squence) within the database, and then recall those things you
> need, when you need them. That being said, I know that databases are
> not guarenteed to work in realtime. That is one of the reasons that I
> would like to use sqlite directly, and not through a universal driver
> like libdbi.

I still think that a sqlite external is a nice idea in addition to other
solutions because of the self-contained nature of it. I definitely think
it's worth pursuing.

Jamie


-- 
www.postlude.co.uk