SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[9.2.349 Pro] Extremely slow autocomplete/intellisense

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[9.2.349 Pro] Extremely slow autocomplete/intellisense
Author Message
lmfs



Joined: 01 Apr 2009
Posts: 9
Country: Sweden

Post [9.2.349 Pro] Extremely slow autocomplete/intellisense Reply with quote
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)
Tue Sep 26, 2017 3:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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
Tue Sep 26, 2017 10:20 am View user's profile Send private message
lmfs



Joined: 01 Apr 2009
Posts: 9
Country: Sweden

Post Reply with quote
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]

Fri Sep 29, 2017 7:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Fri Sep 29, 2017 9:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Ticket #SA0032272
Fri Sep 29, 2017 9:57 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.