[9.2.349 Pro] Extremely slow autocomplete/intellisense

[9.2.349 Pro] Extremely slow autocomplete/intellisense

SQL Assistant is extremely slow trying to match input.

We are talking minutes+ on databases with many objects (30,000+ in sys.objects) when SQL Server Management Studio is "Not Responding" due to autocompletion - when SQL Assistant is trying to match what you have started to type.

This happens when the name matching method is set to "Name Contains Characters from Key String, Order by Best Match" (DB Options -> SQL Server -> Auto Complete...). It works OK if I change it to "Name Contains Characters from Key String, Order Alphabetically", the ordering makes all the difference here. But "Best Match" has it uses when you start to adapt and type keywords instead of names so it is a nice feature but not possible to use on our production databases.

SQL Assistant Pro 9.2.349
SQL Management Studio 17.2 (14.0.17177.0)
SQL Server 2016 (13.0.4422.0)

Thank you very much for letting us know. Can you please confirm that you are observing this issue only when the name matching method is set to "Name Contains Characters from Key String, Order by Best Match" value?

PS. when working with large databases it's recommended to disable background operations such as background syntax checking and background query performance analysis.
DB Options -> SQL assistance -> SQL Server -> Automatic Syntax Checking
DB Options -> SQL assistance -> SQL Server -> Performance Analysis -> Analyse Query Performance

Hi,

Yes, as far as I can tell it is the "...Order by Best Match..." setting that is the culprit here. Switching to "..., Order Alphabetically" gives you an almost immediate autocomplete response while "Best Match" can take 30-60 seconds without any response whatsoever, SSMS flags the window as "Not Responding".

It stop directly when trying to find objects. "SELECT * FROM " and then stops when typing the space after "FROM".

Automatic syntax checking and performance analysis are already turned off - but thanks for the tip!

[This is on a hexacore Core i7-3930K 4.2GHz which is pretty snappy at other tasks, 16GB RAM]

Thank you for confirming this. I will need to open a ticket and have the development look into this. It sounds like a case of non-optimized code.

Ticket #SA0032272