aboutsummaryrefslogtreecommitdiffstats
path: root/Source/CustomQuery.m
diff options
context:
space:
mode:
authorrowanbeentje <rowan@beent.je>2009-08-26 00:46:24 +0000
committerrowanbeentje <rowan@beent.je>2009-08-26 00:46:24 +0000
commit7de1a9a8517c2004e6a97543fbb1462d9c32f4d1 (patch)
treee5fe038f9dc6d55652e4e109adca542e468b2073 /Source/CustomQuery.m
parentd4294235a11548d4712cf3174b0887eea50a5e4c (diff)
downloadsequelpro-7de1a9a8517c2004e6a97543fbb1462d9c32f4d1.tar.gz
sequelpro-7de1a9a8517c2004e6a97543fbb1462d9c32f4d1.tar.bz2
sequelpro-7de1a9a8517c2004e6a97543fbb1462d9c32f4d1.zip
Alter CustomQuery to use StreamingResult to download and process result sets:
- Significantly improve memory usage - Minor speedup (1.1x faster?) to overall query/display times - Improvements to MCPStreamingResult and MCPConnection to accurately report affected row count
Diffstat (limited to 'Source/CustomQuery.m')
-rw-r--r--Source/CustomQuery.m111
1 files changed, 58 insertions, 53 deletions
diff --git a/Source/CustomQuery.m b/Source/CustomQuery.m
index 81532735..daa09cf6 100644
--- a/Source/CustomQuery.m
+++ b/Source/CustomQuery.m
@@ -79,6 +79,7 @@
tableReloadAfterEditing = NO;
[self performQueries:queries];
+
// If no error was selected reconstruct a given selection
if([textView selectedRange].length == 0)
[textView setSelectedRange:curRange];
@@ -403,11 +404,11 @@
- (void)performQueries:(NSArray *)queries;
{
- NSArray *theColumns;
- NSTableColumn *theCol;
- MCPResult *theResult = nil;
- NSMutableArray *menuItems = [NSMutableArray array];
- NSMutableString *errors = [NSMutableString string];
+ NSArray *theColumns;
+ NSTableColumn *theCol;
+ MCPStreamingResult *streamingResult = nil;
+ NSMutableArray *menuItems = [NSMutableArray array];
+ NSMutableString *errors = [NSMutableString string];
int i, totalQueriesRun = 0, totalAffectedRows = 0;
double executionTime = 0;
@@ -454,13 +455,22 @@
[tempQueries addObject:query];
// Run the query, timing execution (note this also includes network and overhead)
- theResult = [mySQLConnection queryString:query];
+ streamingResult = [mySQLConnection streamingQueryString:query];
executionTime += [mySQLConnection lastQueryExecutionTime];
totalQueriesRun++;
+ // If this is the last query, retrieve and store the result; otherwise,
+ // discard the result without fully loading.
+ if (totalQueriesRun == queryCount)
+ [self processResultIntoDataStorage:streamingResult];
+ else
+ [streamingResult cancelResultLoad];
+
// Record any affected rows
if ( [mySQLConnection affectedRows] != -1 )
totalAffectedRows += [mySQLConnection affectedRows];
+ else if ( [streamingResult numOfRows] )
+ totalAffectedRows += [streamingResult numOfRows];
// Store any error messages
if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
@@ -536,9 +546,10 @@
lastExecutedQuery = [[tempQueries lastObject] retain];
- //perform empty query if no query is given
+ // Perform empty query if no query is given
if ( !queryCount ) {
- theResult = [mySQLConnection queryString:@""];
+ streamingResult = [mySQLConnection streamingQueryString:@""];
+ [streamingResult cancelResultLoad];
[errors setString:[mySQLConnection getLastErrorMessage]];
}
@@ -640,9 +651,9 @@
// Restore automatic query retries
[mySQLConnection setAllowQueryRetries:YES];
-
+
// If no results were returned, redraw the empty table and post notifications before returning.
- if ( !theResult || ![theResult numOfRows] ) {
+ if ( ![fullResult count] ) {
[customQueryView reloadData];
// Notify any listeners that the query has completed
@@ -653,15 +664,12 @@
description:[NSString stringWithFormat:NSLocalizedString(@"%@",@"description for query finished growl notification"), [errorText stringValue]]
notificationName:@"Query Finished"];
+ [streamingResult release];
return;
}
// get column definitions for the result array
- cqColumnDefinition = [[theResult fetchResultFieldsStructure] retain];
-
- // set datasource
- [fullResult removeAllObjects];
- [fullResult setArray:[self fetchResultAsArray:theResult]];
+ cqColumnDefinition = [[streamingResult fetchResultFieldsStructure] retain];
// Find result table name for copying as SQL INSERT.
// If more than one table name is found set resultTableName to nil.
@@ -678,10 +686,10 @@
}
// Add columns corresponding to the query result
- theColumns = [theResult fetchFieldNames];
+ theColumns = [streamingResult fetchFieldNames];
if(!tableReloadAfterEditing) {
- for ( i = 0 ; i < [theResult numOfFields] ; i++) {
+ for ( i = 0 ; i < [streamingResult numOfFields] ; i++) {
NSDictionary *columnDefinition = NSArrayObjectAtIndex(cqColumnDefinition,i);
theCol = [[NSTableColumn alloc] initWithIdentifier:[columnDefinition objectForKey:@"datacolumnindex"]];
[theCol setResizingMask:NSTableColumnUserResizingMask];
@@ -739,56 +747,53 @@
}
/*
- * Fetches the result as an array, with an array for each row in it
+ * Processes a supplied streaming result set, loading it into the data array.
*/
-- (NSArray *)fetchResultAsArray:(MCPResult *)theResult
+- (void)processResultIntoDataStorage:(MCPStreamingResult *)theResult
{
- // NSArray *columns;
- unsigned long numOfRows = [theResult numOfRows];
- NSMutableArray *tempResult = [NSMutableArray arrayWithCapacity:numOfRows];
-
NSArray *tempRow;
- NSMutableArray *modifiedRow = [NSMutableArray array];
- int i, j, numOfFields;
+ NSMutableArray *newRow;
+ int i;
+ long rowsProcessed = 0;
+ long columnsCount = 0;
+ NSAutoreleasePool *dataLoadingPool;
+ id prefsNullValue = [[prefs objectForKey:@"NullValue"] retain];
Class nullClass = [NSNull class];
- id prefsNullValue = [prefs objectForKey:@"NullValue"];
- // BOOL prefsLoadBlobsAsNeeded = [prefs boolForKey:@"LoadBlobsAsNeeded"];
- // columns = [customQueryView columns];
- // long columnsCount = [cqColumnDefinition count];
+ // Remove all items from the table
+ [fullResult removeAllObjects];
- if (numOfRows) [theResult dataSeek:0];
- for ( i = 0 ; i < numOfRows ; i++ ) {
- tempRow = [theResult fetchRowAsArray];
+ // Set up an autorelease pool for row processing
+ dataLoadingPool = [[NSAutoreleasePool alloc] init];
- if ( i == 0 ) numOfFields = [tempRow count];
+ // Loop through the result rows as they become available
+ while (tempRow = [theResult fetchNextRowAsArray]) {
+ if (columnsCount == 0) columnsCount = [tempRow count];
- for ( j = 0; j < numOfFields; j++) {
- if ( [NSArrayObjectAtIndex(tempRow, j) isMemberOfClass:nullClass] ) {
- [modifiedRow addObject:prefsNullValue];
- } else {
- [modifiedRow addObject:NSArrayObjectAtIndex(tempRow, j)];
- }
+ NSMutableArrayAddObject(fullResult, [NSMutableArray arrayWithArray:tempRow]);
+ newRow = NSArrayObjectAtIndex(fullResult, rowsProcessed);
+
+ // Process the retrieved row
+ for ( i = 0; i < columnsCount; i++ ) {
+ if ( [NSArrayObjectAtIndex(tempRow, i) isMemberOfClass:nullClass] )
+ [newRow replaceObjectAtIndex:i withObject:prefsNullValue];
}
- // Add values for hidden blob and text fields if appropriate
- // if ( prefsLoadBlobsAsNeeded ) {
- // for ( j = 0 ; j < columnsCount ; j++ ) {
- // if ( [[NSArrayObjectAtIndex(cqColumnDefinition, j) objectForKey:@"typegrouping"] isEqualToString:@"blobdata"] ||
- // [[NSArrayObjectAtIndex(cqColumnDefinition, j) objectForKey:@"typegrouping"] isEqualToString:@"textdata"]) {
- // [modifiedRow setObject:NSLocalizedString(@"(not loaded)", @"value shown for hidden blob and text fields") forKey:[NSArrayObjectAtIndex(cqColumnDefinition, j) objectForKey:@"name"]];
- // }
- // }
- // }
+ // Update the count of rows processed
+ rowsProcessed++;
- [tempResult addObject:[NSArray arrayWithArray:modifiedRow]];
- [modifiedRow removeAllObjects];
+ // Drain and reset the autorelease pool every ~1024 rows
+ if (!(rowsProcessed % 1024)) {
+ [dataLoadingPool drain];
+ dataLoadingPool = [[NSAutoreleasePool alloc] init];
+ }
}
-
- return tempResult;
+
+ // Clean up the autorelease pool
+ [dataLoadingPool drain];
+ [prefsNullValue release];
}
-
/*
* Retrieve the range of the query at a position specified
* within the custom query text view.