Forums/SQL Yoga
SQLYoga vs RevDB commands - performance
gbojsza
asked this on May 22 09:43
I saw this on the Rev use list and wondered if thee is such a large difference in performance between SQLYoga and RevDB commands?
Posted by Bob Sneidar Fri, May 21, 2010 at 6:17 PM
Well I discovered what my problem was. I commented out the method I employed which used sqlYoga for all the queries, and wrote a new method using only RevDB API calls. The sqlYoga method took about 1 to 1.5 seconds per iteration, so for 100 single record queries, let's say roughly 2 minutes. The RevDB method took less than 10 seconds for 100 queries.
I guess what I am saying is that if you need to do lots of small queries like I am doing, and you don't mind learning a little SQL, the RevDB commands and functions are going to be a TON more efficient. But if you are doing a few queries here and there, the performance hit would be minimal, and sqlYoga can save you enormous amounts of trouble learning SQL and get you going much quicker.
Comments
I haven't read Bob's post on the list yet (just got back from vacation) but looking at some code he sent to me he was calling dbobject_save twice in the repeat loop. This saves the stack to disk each time which would dramatically slow things down. dbobject_save wasn't meant to be called within a repeat loop and there is no reason to so I'm checking with Bob to see if removing those calls speeds things up.
There is definitely not as big a difference between SQL Yoga and RevDB as Bob is seeing. He is only fetching data as text. Of course if you are using SQL Yoga calls that convert the result set to arrays it will take longer.
Thanks Trevor, do you have a estimate / ballpark on how much longer converting the result to a set of arrays would be for 1,000 and 5,000 data points?
Also, does SQL Yoga have any method for a user to query for what databases are available?
I don't have any time tests (though I should do that at some point) that I've done though I have tried to make it very efficient. The time will also depend on what data (is there binary data) is stored and how many columns. sqlquery_retrieveAsArray just loops through your data and puts each column into the array. Converting to Record Objects using sqlquery_retrieveAsRecords is more time intensive as SQL Yoga will convert an MD array that mimics your table relationships.
I would do tests with your data to see how it goes.
SQL Yoga does not have a built-in means of querying which databases are available. You would need to connect to the database and then use dbconn_retrieveQueryAsData to execute a query that returned a list of databases.
I located the issue that Bob was seeing. When executing a query using a database object other than the "default" object SQL Yoga was trying to import the database schema every time. I just posted 1.0.1 build 5 which fixes this problem.