Increasing the performance and usability of Facebook's FQL

Question

I try to get some insights from the pages I am administrator on Facebook.
What my code does, it gets the IDs of the pages I want to work with through mySQL. I did not include that part though.

After this, I get the page_id, name and fan_count of each of those facebook IDs and are saved in fancounts[].

I have two problems with it.

1. It has a very slow performance
2. I can't find a way to echo the data like this:

My questions are, how can the code be modified to increase performance and show the data as above? I read about fql.multiquery. Can it be used here?

Please provide me with code examples. Thank you

Answer 1

At the moment, you're making two separate calls to Facebook's database which is slowing everything down. Facebook offer their multiquery so that you can do everything in as few DB calls as possible. So the calls you should think about using are:

"query1":"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)"

And because they allow you to reference a prior query, you can just include it after a #:

"query2":"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2"

The PHP you need to use is something like this:

$query = array(
    "pages"=>"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)",
    "messages"=>"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2"
);

$fql_url = $facebook->api(array(
    'method' => 'fql.multiquery',
    'queries' => $query
));

print_r($fql_url);

If the second query isn't going through, try testing the FB DB with just this query and see if it works. If the query doesn't return anything by itself, the problem might be with permissions (i.e. accessing a sensitive table -- but I don't think this is the case). Another problem which I've frequently encountered is how FQL trips itself up with whitespace, so try omitting all possible whitespace from your array:

$query = array("pages"=>"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)","messages"=>"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2");

Wow, that's readable... This was taken from Facebook's documentation on FQL, though, so you might need to adapt it for your web application if using a third party library. All of your data is saved in $fql_url. All you need to do is loop through it and echo out the information you want. If you want to see a summary of everything it contains, think about using print_r() or var_dump() just to get your bearings.

EDIT

The reason that you're receiving an empty array for the second query is because you don't seem to have permissions for the stream table. If you check Facebook's documentation, they mention the criteria needed to access this table:

To read the stream table you need

• read_stream permissions for all posts that the current session user is able to view
• read_insights permissions to see post impressions for any posts made by a Page owned by the current session user

To check what permissions you have, you can run this query:

$check_query = $facebook->api(array(
    "method"    => "fql.query",
    "query"     => "SELECT * FROM permissions WHERE uid=me()"
));

foreach($check_query[0] as $k => $v) {
    if($v === "1") {
        echo "<strong>$k</strong> permission is granted.<br>";
    } else {
        echo "<strong>$k</strong> permission is not granted.<br>";
    }
}

Answer 2

If you have n pages, your script makes n+1 queries. This is the main drawback of your script. This is the reason for low performance.

You can use batch request to combine the queries. You can use the below script to achieve what you want. I have combined those n+1 queries to just one batch query. So it will be mush faster than your script.

I've also corrected the echo part. Now the script will display the output as you stated in your question.

// Get the IDs
$pages = array(); 
$pagesIds = implode(',', $pages);

// fancounts[] holds the page_id, name and fan_count of the Ids I work with
$fancounts = array();
$q = "SELECT page_id, name, fan_count FROM page WHERE page_id IN ({$pagesIds})";
$queries[] = array('method'=>'GET', 'relative_url' => 'method/fql.query?query=' . urlencode($q) );

$messages = array();
foreach( $pages as $id) 
{
   $q = "SELECT message FROM stream WHERE source_id = '$id' LIMIT 2";
   $queries[] = array('method'=>'GET', 'relative_url' => 'method/fql.query?query=' . urlencode($q) );
}

// The batch query
$batchResponse = $facebook->api('?batch='.json_encode($queries), 'POST');
$pagesFanCounts = json_decode($batchResponse[0]['body'], TRUE);

foreach ($pagesFanCounts as $page)
{       
   $fancounts[] = number_format($page['page_id'],0,'','')."-".$page['name']."-".$page['fan_count'];
}

for($i=0; $i < count($fancounts); $i++) 
{
   echo '</br>',$fancounts[$i],'<br>';
   $temp = json_decode($batchResponse[$i+1]['body'], TRUE);
   foreach ($temp as $msg)
   {
      echo ($msg['message']);
      echo "</br>";
   }
}

Answer 3

You can try anything but your code will not run fast because you are breaking golden rule of high speed web app. What you actually doing write now is:

request1->(wait for response-> download data) -> request2 (wait for response -> 
download data)  -> and so on ......

And what you should really do :-

request1->(wait for response-> download data)
request2->(wait for response-> download data)
request3->(wait for response-> download data)
......
......

Yes, you should made multiple request at once in order to decrease total response time. Browser speed up page loading by this way only.

I have faced similar issue while working on RSS feed fetcher( It has huge database of RSS links).

To solve this problem I can suggest you two things

1. Use multi-curl command to fetch multiple command at once. It will really speed up your script as multiple request at once will decrease overall time.

2. But above solution will work to some extend. If you are querying a lot of data then you have to look somewhere else as php doesn't support multi-threading. You can use java or node.js as an alternative and using redis server ( don't underestimate it ) as a pipeline between php script and java or node.js by using it's pub/sub facility. According to me it's the best alternative and I have used it to fetch over hundred of thousands of record and it never fails.

And other thing in which I can't really help you is your Internet connection speed ;)

Hope this solves your problem :)

Deepak