PLEASE NOTE: These forums are no longer utilized and are provided as an archive for informational purposes only. All support issues will be handled via email using our support ticket system. For more detailed information on this change, please see this blog post.

Slow form submission - 26k+ draft posts - function improvement request

  1. Hello,

    We are running quite a few Gravity Forms forms and form submissions have slowed down significantly over time.

    I wrote a timer utility to check and see which part of the process was taking so much extra time and was a bit surprised to see that the actual entry creation was taking 26+ seconds per form submission.

    After a bit of digging, I narrowed the culprit down ( gravity forms 1.7.6 ) to the forms_model.php file, get_default_post_title function.

    Currently it looks like the following:

    private static function get_default_post_title(){
            global $wpdb;
            $title = "Untitled";
            $count = 1;
    
            $titles = $wpdb->get_col("SELECT post_title FROM $wpdb->posts WHERE post_title like '%Untitled%'");
            $titles = array_values($titles);
            while(in_array($title, $titles)){
                $title = "Untitled_$count";
                $count++;
            }
            return $title;
        }

    With 25000 records that match, a procedural language approach ( like C, C++, php, etc. ) will take a good long while to run through the loop and find an untaken value.

    It would be much better to use a set language approach ( sql ) which might look like the following:

    1. Grab the ID ( or title ) of the most recently added post
    2. Add one to the ID ( or parse the title, grab the integer, and increment it )
    3. Set the title to Untitled_N where N is the result of the integer found + 1
    4. If needed, use a small loop to check for existence of a post having that title, and increment the integer until an unused title is found
    5. return the resulting title

    Doing things this way would save a ton of processing power and time, and the fix would be rather simple.

    Obviously, it would be even better to use a custom post type for these things as that would avoid cluttering the post list with tons of drafts, allow full control over title generation ( e.g. with your own custom post type you could be certain the next unused id was available for use as a title ), etc. But that would also take a lot more work, especially to maintain backward compatibility.

    Anywise, if you could take a look at that function and revise it so that it doesn't bog larger systems down, it would be much appreciated.

    Posted 10 years ago on Tuesday July 9, 2013 | Permalink
  2. Just a quick followup to note that I did what I recommended above to our copy of gravity forms and the result speaks for itself:

    26K draft posts ( from gravity forms ).

    Original processing time for form submissions: 26-28 seconds

    New processing time for form submissions: 2-3 seconds

    Here is the re-written code ( it could be done differently ) with my debug code and error handling taken out:

    private static function get_default_post_title(){
    
    	global $wpdb;
    
    	# A default to use for the final title to return
    	$final_title = 'Untitled_1';
    
    	# Grab the most recent post that has Untitled_*
    	$sql = "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_title like 'Untitled\_%' ORDER BY post_date DESC LIMIT 0, 1";
    
    	$results = $wpdb->get_results( $sql, ARRAY_A );
    	if ( !is_null($results) ) {
    		# found a match
    		if ( is_array($results) ) {
    			if ( count($results) > 0 ) {
    				$match = array_pop($results);
    				$found_title = $match['post_title'];
    				$wanted_part = str_replace('Untitled_', '', $found_title);
    				$wanted_part = (int)$wanted_part;
    				if ( $wanted_part > 0 ) {
    					# we successfully found the wanted Untitled entry
    					$wanted_part += 1;
    				} else {
    					# non numeric Untitled_* - start with the post ID + 1 when doing checks
    					$wanted_part = (int)$match['ID'] + 1;
    				}
    				# Update the default title
    				$final_title = "Untitled_{$wanted_part}";
    
    				# Set up our early break flag
    				$done_with_loop = false;
    
    				# Check for existence of the wanted part ( up to 100 times )
    				# - still much faster than looping 26k times
    				for ( $i = 0; $i < 100; $i++ ) {
    					$sql = "SELECT 1 as <code>it_exists</code> FROM {$wpdb->posts} WHERE post_title = 'Untitled_{$wanted_part}'";
    
    					$exists = $wpdb->get_results( $sql, ARRAY_A );
    					if ( is_null($exists) ) {
    						# no results returned
    						if ( !empty( $wpdb->last_error ) ) {
    							# Error with query
    						} else {
    							# simply not found
    							$final_title = "Untitled_{$wanted_part}";
    							$done_with_loop = true;
    						}
    					} else {
    						if ( is_array($exists) && count($exists) > 0 ) {
    							$test = array_pop($exists);
    							if ( '1' == "{$test['it_exists']}" ) {
    								# this exists - try the next item
    								$wanted_part += 1;
    							} else {
    								# does not exist - good to go
    								$final_title = "Untitled_{$wanted_part}";
    								$done_with_loop = true;
    							}
    						} else {
    							# no matches found
    							$final_title = "Untitled_{$wanted_part}";
    							$done_with_loop = true;
    						}
    					}
    					if ( $done_with_loop ) {
    						break;
    					}
    				}
    			} else {
    				# no match found for Untitled_* in the system
    				# - should not happen - treat as an error
    			}
    		} else {
    			# not an array
    			# - should not happen - treat as error
    		}
    	} else {
    		# no results at all likely an error
    		if ( !empty($wpdb->last_error) ) {
    			# database call error
    		} else {
    			# null result but no last error
    		}
    	}
    
    	return $final_title;
    }

    Rewriting this function in the core code base would be greatly appreciated by those of us with much larger recordsets.

    Posted 10 years ago on Tuesday July 9, 2013 | Permalink
  3. I'll bring this up to the dev team.

    Posted 10 years ago on Tuesday July 9, 2013 | Permalink
  4. Thanks much!

    Gravity forms works very nicely, but the cycling through all current posts with 26k records was getting to be a showstopper as it was causing submissions to take almost 30 seconds.

    Posted 10 years ago on Tuesday July 9, 2013 | Permalink
  5. Dev team has added this to Pivotal Tracker for discussion.

    Posted 10 years ago on Wednesday July 10, 2013 | Permalink

This topic has been resolved and has been closed to new replies.