SQL to Array

In profiling Ditto I found the bottleneck--- taking the SQL query’s content field and adding it to the resource array.

What is the fastest method for transfering the fields, specifically "content", from the SQL results to the resource array. The current method being used in the MODx core and Ditto seem to drastically slow down after 3 or 4 hundred records and ends up creating an array close to 4.5 mb.

Please advise.

Any ideas?

Quote from: Mark at Jul 31, 2006, 04:53 PM

Any ideas?

Yes, you are trying to load the content for 300 or 400 documents. That’s a lot of content, like you said, several megabytes. Think of loading a web page consisting of all of those documents and it’s easy to understand why it’s taking so much memory. You have to find ways to search content using SQL so you only have to get the content (if absolutely necessary) of the documents you need to output. Otherwise, there is no easy way to scale Ditto to this many records.

I’m not aware of a content search; all filtering is done by TVs etc, so I don’t see that you actually *need* content in the query, you just need it for the output, so surely it is faster to pull this up afterwards once you know which IDs you need the content for.

This is particularly important with pagination - I’ve seen many systems that pull up 97 data rows in full (just to find out it’s 97) and then just display 11-20. I’ve not checked how Ditto does it.

I have often wondered why there will be a query like SELECT * FROM... , then count the results, without actually using the full result set for anything. Wouldn’t SELECT count(*) FROM be better? Every book, article, and tutorial I’ve ever read says to never use your scripts to perform a function that the SQL engine natively provides. Like date manipulation, sorting and searching. Although date manipulation I can see if you are dealing with different locale settings.

Memory is still a "scarcer" resource than processor power, so giving the SQL engine a bit more to do instead of loading up memory, and then using up processor power to use a script to manipulate the data anyway, is not very efficient. Of course, that means we need to know HTML, CSS, Javascript, PHP and SQL. And maybe ActionScript, and how to do neat stuff in Photoshop/Gimp.

Hi Mark,

Sorry I was not able to reply sooner. I don’t know if you had found a solution but I would suggest that you try using the LEFT() function in MySQL:

SELECT LEFT(content,300) as ’content’ FROM...

Handy.... Good to note...