aeb00b82ee56facd622b8f5347ebbf85309c9403 braney Wed Sep 2 14:02:01 2026 -0700 Ask the database once per track for RefSeq status, not once per gene, refs #38233 refGeneColor is the per-item color callback for the RefSeq gene tracks. Each call opened a database connection, checked that the status table existed, asked for one gene's status, and closed the connection again. A view with two RefSeq tracks in pack mode draws a few thousand genes, so the browser made a few thousand round trips to the database while it was drawing the image. refSeqStatusHashLoad now builds the whole name-to-status map with one batched query when the track loads, and leaves it on tg->customPt. refGeneColorByStatus reads that map, so drawing asks the database nothing. Both are static; nothing outside this file used either. The map is built after limitVisibility() and only for a track that will draw. A track limitVisibility hides needs no colors, and the tracks it hides are the ones with the most items, so they are exactly the ones whose query would be largest. The existence test goes through a connection rather than hTableExists(), because refSeqStatusTable carries its database (normally hgFixed.refSeqStatus) and hTableExists() looks a name up in one database's own list of tables. It answers FALSE for any name with a database prefix, which silently drops the shading for plain refGene and xenoRefGene. This also removes a latent errAbort: the old gate tested refSeqStatus OR ncbiRefSeqLink and then queried whichever table the track type wanted, so a database with only ncbiRefSeqLink sent a refGene track to query a table that was not there. Rendering is unchanged. Eight scenarios pixel-identical to genome-test, including whole chr1 and the plain refGene and xenoRefGene tracks. Across the seven clinical Recommended Track Sets on hg38, 14% faster on a 2.5 Mb view and 20% on a 25 Mb view, with peak memory unchanged. Co-Authored-By: Claude Opus 5 (1M context) diff --git src/hg/hgTracks/simpleTracks.c src/hg/hgTracks/simpleTracks.c index 0d37b41385f..1af2136f795 100644 --- src/hg/hgTracks/simpleTracks.c +++ src/hg/hgTracks/simpleTracks.c @@ -8401,50 +8401,57 @@ strcat(lf->extra, "k"); } } } sqlFreeResult(&sr); } hFreeConn(&conn); } } } else for (lf = tg->items; lf != NULL; lf = lf->next) lf->extra = cloneString(lf->name); } +static struct hash *refSeqStatusHashLoad(struct track *tg); +/* Build a name->refSeqStatus hash for tg's items (defined below). */ + void loadNcbiRefSeq(struct track *tg) /* Load up RefSeq known genes. */ { enum trackVisibility vis = tg->visibility; loadGenePredWithName2(tg); if (vis != tvDense) lookupRefNames(tg); vis = limitVisibility(tg); +if (vis != tvHide) + tg->customPt = refSeqStatusHashLoad(tg); // so refGeneColor does no draw-time SQL } void loadRefGene(struct track *tg) /* Load up RefSeq known genes. */ { enum trackVisibility vis = tg->visibility; tg->items = lfFromGenePredInRange(tg, tg->table, chromName, winStart, winEnd); if (vis != tvDense) { lookupRefNames(tg); } vis = limitVisibility(tg); +if (vis != tvHide) + tg->customPt = refSeqStatusHashLoad(tg); // so refGeneColor does no draw-time SQL } /* A spectrum from blue to red signifying the percentage of methylation */ Color bedMethylColorArray[] = { 0xffff0000, 0xffff4444, 0xffaa4488, 0xff884488, 0xff4444aa, 0xff0000ff, }; void bedMethylMapItem(struct track *tg, struct hvGfx *hvg, void *item, char *itemName, char *mapItemName, int start, int end, int x, int y, int width, int height) @@ -8549,105 +8556,138 @@ *colon = 0; col = getSeqColor(pos, hvg); } } } break; case 2: /* black */ col = MG_BLACK; break; } tg->ixAltColor = col; return(col); } -Color refGeneColorByStatus(struct track *tg, char *name, struct hvGfx *hvg) +static struct hash *refSeqStatusHashLoad(struct track *tg) +/* Build (at load time) a name->refSeqStatus hash for this track's items, so that + * refGeneColorByStatus can shade items at draw time without asking the database + * once per item. One batched query instead of one query per drawn gene. + * Returns NULL when no status table applies, in which case + * refGeneColorByStatus colors everything normally, exactly as an empty query + * result did before. Call it after limitVisibility() and only for a track that + * will actually draw: a track limitVisibility hides needs no colors, and it is + * the tracks with the most items -- the ones it hides -- whose query is + * largest. */ +{ +if (tg->items == NULL) + return NULL; +char *liftDb = trackDbSetting(tg->tdb, "quickLiftDb"); +char *db = (liftDb == NULL) ? database : liftDb; +boolean isNcbi = startsWith("ncbiRefSeq", trackHubSkipHubName(tg->table)); +char *table = isNcbi ? "ncbiRefSeqLink" : refSeqStatusTable; +char *keyCol = isNcbi ? "id" : "mrnaAcc"; +/* refSeqStatusTable is qualified with its database (usually hgFixed.refSeqStatus), + * so the existence test has to go through a connection like the old draw-time code + * did. hTableExists() looks a name up in db's own list of tables and would answer + * FALSE for any name carrying a database prefix, which would quietly drop the + * shading for every non-NCBI RefSeq track. */ +struct sqlConnection *conn = hAllocConn(db); +if (!sqlTableExists(conn, table)) + { + hFreeConn(&conn); + return NULL; + } +struct hash *hash = hashNew(0); +struct dyString *query = sqlDyStringCreate("select %s, status from %s where %s in (", + keyCol, table, keyCol); +struct linkedFeatures *lf; +boolean first = TRUE; +for (lf = tg->items; lf != NULL; lf = lf->next) + { + if (!first) + sqlDyStringPrintf(query, ","); + sqlDyStringPrintf(query, "'%s'", lf->name); + first = FALSE; + } +sqlDyStringPrintf(query, ")"); +struct sqlResult *sr = sqlGetResult(conn, query->string); +char **row; +while ((row = sqlNextRow(sr)) != NULL) + hashAdd(hash, row[0], cloneString(row[1])); +sqlFreeResult(&sr); +hFreeConn(&conn); +dyStringFree(&query); +return hash; +} + +static Color refGeneColorByStatus(struct track *tg, char *name, struct hvGfx *hvg) /* Get refseq gene color from refSeqStatus. * Reviewed, Validated -> normal, Provisional -> lighter, * Predicted, Inferred(other) -> lightest - * If no refSeqStatus, color it normally. - */ + * If no refSeqStatus, color it normally. Reads the status from the hash that + * refSeqStatusHashLoad built at load time and left on tg->customPt, so this + * does no database work while the image is being drawn. */ { int col = tg->ixColor; struct rgbColor *normal = &(tg->color); struct rgbColor lighter, lightest; -char *liftDb = cloneString(trackDbSetting(tg->tdb, "quickLiftDb")); -char *db = (liftDb == NULL) ? database : liftDb; -struct sqlConnection *conn = hAllocConn(db); -struct sqlResult *sr; -char **row; -char query[256]; - -if (startsWith("ncbiRefSeq", trackHubSkipHubName(tg->table))) - { - sqlSafef(query, sizeof query, "select status from ncbiRefSeqLink where id = '%s'", name); - } -else - sqlSafef(query, sizeof query, "select status from %s where mrnaAcc = '%s'", - refSeqStatusTable, name); -sr = sqlGetResult(conn, query); -if ((row = sqlNextRow(sr)) != NULL) +struct hash *statusHash = tg->customPt; +char *status = (statusHash != NULL) ? hashFindVal(statusHash, name) : NULL; +if (status != NULL) { - if (startsWith("Reviewed", row[0]) || startsWith("Validated", row[0])) + if (startsWith("Reviewed", status) || startsWith("Validated", status)) { /* Use the usual color */ } - else if (startsWith("Provisional", row[0])) + else if (startsWith("Provisional", status)) { lighter.r = (6*normal->r + 4*255) / 10; lighter.g = (6*normal->g + 4*255) / 10; lighter.b = (6*normal->b + 4*255) / 10; lighter.a = normal->a; col = hvGfxFindRgb(hvg, &lighter); } else { lightest.r = (1*normal->r + 2*255) / 3; lightest.g = (1*normal->g + 2*255) / 3; lightest.b = (1*normal->b + 2*255) / 3; lightest.a = normal->a; col = hvGfxFindRgb(hvg, &lightest); } } -sqlFreeResult(&sr); -hFreeConn(&conn); return col; } Color refGeneColor(struct track *tg, void *item, struct hvGfx *hvg) /* Return color to draw refseq gene in. */ { struct linkedFeatures *lf = item; /* allow itemAttr to override coloring */ if (lf->itemAttr != NULL) return hvGfxFindColorIx(hvg, lf->itemAttr->colorR, lf->itemAttr->colorG, lf->itemAttr->colorB); /* If refSeqStatus is available, use it to determine the color. * Reviewed, Validated -> normal, Provisional -> lighter, * Predicted, Inferred(other) -> lightest - * If no refSeqStatus, color it normally. - */ -char *liftDb = cloneString(trackDbSetting(tg->tdb, "quickLiftDb")); -char *db = (liftDb == NULL) ? database : liftDb; -struct sqlConnection *conn = hAllocConn(db); -Color color = tg->ixColor; -if (sqlTableExists(conn, refSeqStatusTable) || hTableExists(db, "ncbiRefSeqLink")) - color = refGeneColorByStatus(tg, lf->name, hvg); -hFreeConn(&conn); -return color; + * If no refSeqStatus, color it normally. The status comes from the hash + * refSeqStatusHashLoad built at load time, so this asks the database nothing. + * When no status table applied the hash is NULL and every item gets the + * normal color. */ +return refGeneColorByStatus(tg, lf->name, hvg); } void ncbiRefSeqMethods(struct track *tg) /* Make NCBI Genes track */ { tg->loadItems = loadNcbiRefSeq; tg->itemName = refGeneName; tg->mapItemName = ncbiRefGeneMapName; tg->itemColor = refGeneColor; } void refGeneMethods(struct track *tg) /* Make track of known genes from refSeq. */ { tg->loadItems = loadRefGene;