bench_query.php

<?php

function bench_get_user_unpublished_posts( $n = 100 ) {
	$text = "| %-20.20s | %-20.20s | %-20.20s\n";

	printf( $text, 'Method', 'MemUsage', 'ExecTime' );
	printf( $text, 'WP_Query', get_user_unpublished_posts_mem_usage_avg( $n, 'get_user_unpublished_posts_using_wp_query' ), get_user_unpublished_posts_exec_time_avg( $n, 'get_user_unpublished_posts_using_wp_query' ) );
	printf( $text, 'WPDB', get_user_unpublished_posts_mem_usage_avg( $n, 'get_user_unpublished_posts_using_wpdb' ), get_user_unpublished_posts_exec_time_avg( $n, 'get_user_unpublished_posts_using_wpdb' ) );
}

function get_user_unpublished_posts_mem_usage_avg( $n = 100, $fn ) {
	$mem_usage = 0;
	for ( $i = 0; $i < $n; $i++ ) {
		$before = memory_get_usage();
		call_user_func( $fn );
		$after = memory_get_usage();

		if ( $after > $before ) {
			$mem_usage += $after - $before;
		}
	}
	return $mem_usage / $n;
}

function get_user_unpublished_posts_exec_time_avg( $n = 100, $fn ) {
	$time = 0;
	for ( $i = 0; $i < $n; $i++ ) {
		$start = microtime(true);
		call_user_func( $fn );
		$end = microtime(true);
		$time += $end - $start;
	}
	return $time / $n;
}

function get_user_unpublished_posts_using_wp_query() {
	$query = new WP_Query(
		array(
			'post_status' => array( 'draft', 'future', 'pending' ),
			'post_type' => 'post',
			'author' => 50,
			'order' => 'DESC',
			'orderby' => 'post_modified',
			'nopaging' => true,
		)
	);

	return reformat_posts_data( $query->posts );
}

function get_user_unpublished_posts_using_wpdb() {
	global $wpdb;

	$query = $wpdb->prepare( "SELECT ID, post_status, post_title FROM $wpdb->posts WHERE post_type = %s AND post_status IN ( 'draft', 'future', 'pending' ) AND post_author = %d ORDER BY post_modified DESC", 'post', 50 );
	$posts = $wpdb->get_results( $query );

	return reformat_posts_data( $posts );
}

function reformat_posts_data( $posts ) {
	$unpub_posts = array(
		'draft'   => array(),
		'pending' => array(),
		'future'  => array(),
	);
	foreach ( $posts as $post ) {
		$unpub_posts[ $post->post_status ][] = $post;
	}
	return $unpub_posts;
}

Simple benchmark between WP_Query and $wpdb

Please note that author ID and post type is hardcoded. To run it, simply put the file into mu-plugins directory and run wp shell. Inside the WP shell run the benchmark function:

wp> bench_get_user_unpublished_posts();
| Method               | MemUsage             | ExecTime
| WP_Query             | 33124.24             | 0.025115921497345
| WPDB                 | 10.4                 | 0.002782986164093