exec sp_helptext takes a long time

Hi,

I'm working with an SQL Server over VPN (latency > 100ms) and we have a lot of stored procedures (unfortunately, been working on refactoring those). When I press CTRL-SPACE to get a table lookup while writing a SQL query, Heidi starts querying help texts for all of those stored procedures. With 200 stored procedures at 100 ms wait, it takes about 20 seconds before I can continue working.

You could theoretically send all of those exec sp_helptext in one query and receive a large number of result sets, I suppose. Just tested it for 298 exec's in one query, and while it took HeidiSQL a little while to paint all those result sets, the actual query took only 1.079 seconds.

Regards
Matt

Makes sense. Only the call to EXEC sp_helptext is so deep in the code paths of HeidiSQL, I would have to implement an entirely new approach for analyzing functions and procedures. I'm not sure if that's doable without much effort.

*cough* refactoring *cough*

;)

I just introduced some sort of query caching, deeply in HeidiSQL's connection code. This is highly experimental, and we should test it thoroughly before the next official release. Just update to the latest build, which is r4990.

This query caching is yet only used in the case you described - when the completion proposal pops up and fires certain queries for each routine in a database. This is done in MSSQL for the EXEC sp_helptext query, and in MySQL for SHOW CREATE FUNCTION/PROCEDURE queries. Not more yet, but I'm open for other use cases here.

Just for the records: This is not a dumb query cache, but a fire-multiple-queries-in-one-go logic. So this is not a real cache, more a "prefetch" thing, or whatever...

I've just tested it briefly on a couple of databases and it seems to work, as far as I can see.

However - it highlights another issue with HeidiSQLStored procedures are part of a schema, "dbo" being the normal of course. But when HeidiSQL lists stored procedures in the schema "Maintenance", which is part of our database, the schema disappears. As a result, if the SP is named 'CleanUp', it tries to run the query
EXEC sp_helptext '.CleanUp'
where it actually should have run
EXEC sp_helptext 'Maintenance.CleanUp'

All the dbo SP's work fine, though.

That has always been an issue in widespread parts of HeidiSQL. r4991 attempts to use the database name as prefix if the schema is empty. Terminilogy may be wrong here, but the same code works fine in other places. Please update and check if that fixes the issue.

I just tested it and it seems to work! Thanks :)