SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.362 Standard] Freeze Issues in SSMS
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.362 Standard] Freeze Issues in SSMS
Author Message
worze



Joined: 24 Jan 2014
Posts: 8

Post [11.5.362 Standard] Freeze Issues in SSMS Reply with quote
Hi there.
I'm using Sql Assistant 11.5.362 Standard Edition and I have some freeze issues in IDE (Code Editor) is SSMS (2016 (v.13), 2018 (v.18))

When I've opened USP (1000+ lines) and started to write code it just freezes like 1-2 FPS.

Some tricks to fix this:
1. Format code with Sql Asisstant - works fine immediately.
2. Use older version (tried Sql Assistant v.10 trial) - works fine "from a box".

I've disabled all (I think that they're all) auto features in Sql Assisntant v.11.5.362 - but it isn't fixing this problem.

And yes, I've made clean install several times, no effect :(

Any suggestions?

Best regards,
Igor.
Mon Jul 05, 2021 3:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Do I get it correctly, if you format the code, you don't see freezing? What do you mean by FPS?
As the first step to narrow the scope of the investigation, we need to figure out what feature or interface is responsible for that, if it's something related to editor integration, or code parsing, or to the database background queries. Since you run Standard edition and don't have SQL Assistant Pro IDE, may I ask you try editing the same procedure in SQL Assistant's Light SQL Editor to see if the freezing is reproducible there too?
Mon Jul 05, 2021 10:08 am View user's profile Send private message
worze



Joined: 24 Jan 2014
Posts: 8

Post Reply with quote
Quote:
Do I get it correctly, if you format the code, you don't see freezing?
Yes.
Quote:
What do you mean by FPS?
Something like IDE inputs my 1 character per 2-3 seconds (frame per second (synonym from games))
Quote:
may I ask you try editing the same procedure in SQL Assistant's Light SQL Editor to see if the freezing is reproducible there too?
Just tried:
Sql Editor Pro x64 - no issues.
Sql Editor Light x86 - has same freeze issues while I'm trying to edit same USP on same DB, or same USP another DB.

Seems we've figured out the reason.
Tue Jul 06, 2021 1:13 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Thank you. If the issue is IDE specific, then it's unlikely that the performance is bound to the database, but still cannot be completely excluded. An editor specific optimization is used to minimize the number of database calls like syntax checking in large scripts. I suspect it grabs only part of the procedure code missing some important part that makes the operations very slow, and after code formatting that part of code gets pushed further down and goes out of scope, not causing an issue.

May I ask you now to try again in SSMS and comment out large blocks of the procedure code to see if the issue can be isolated to a particular block of code? Please start from the end and try going backward to ward the beginning.
Tue Jul 06, 2021 9:20 am View user's profile Send private message
worze



Joined: 24 Jan 2014
Posts: 8

Post Reply with quote
I've commented whole USP but it doesn't helped.
Code editor, all IDE (SSMS totally freezes)

And nothing happens 10-20 seconds:


This USP (approx 1000 lines) has alot of dynamic Sql. Probably this cause a problem.

Just tried to edit USP with 5000-7000 lines without dynamic Sql - all is fine.

I'd like to menton that I hadn't such problems on Sql Assistant v. 10.x, v. 9.x

Quote:
I suspect it grabs only part of the procedure code missing some important part that makes the operations very slow, and after code formatting that part of code gets pushed further down and goes out of scope, not causing an issue.

1. I format code by Sql Assistant.
2. Revert all changes.
3. Input my code to the USP normally for some time (a few minutes and then freezes come again)
Tue Jul 06, 2021 9:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Just wanted to say that this issue hasn't been forgotten, I will get back to you with the next step very soon.
Thu Jul 08, 2021 9:26 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
While we are waiting for the next step instructions, may I ask you if your troubling procedure contains code lines starting with like "--#" ? If yes, what follows these line prefixes?
Does deactivating SQL Preprocessor resolve the issue?
Thu Jul 08, 2021 9:33 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
I spoke with developers and unfortunately they say they cannot tell what may cause they they are asking for a copy of the procedure so that they can reproduce the issue.

If my suggestion for trying to turn off SQL Preprocessor doesn't help, please email a copy of your procedure to our support email (you can find support email on the About tab in the Options dialog)
Thu Jul 08, 2021 11:39 am View user's profile Send private message
worze



Joined: 24 Jan 2014
Posts: 8

Post Reply with quote
Hello.
Quote:
may I ask you if your troubling procedure contains code lines starting with like "--#" ?
Current USP, that causes freezing contains such begining:
Code:
ALTER PROCEDURE [dbo].[proc_DoSomething] (@filter varchar(max) = '')
AS
-- =============================================
-- 01.01.2000 Author  Comment
-- Alot of comments
-- ...
begin
  set nocount on

 --#region variables_definition
   declare @filterXml Xml = cast(@filter as Xml);
   declare @someString varchar(50)
   declare @someId INT
   declare @..
  -- here we declaring alot of variables.
Quote:
Does deactivating SQL Preprocessor resolve the issue?

It's already deactivated. And unfortunately no, it dosen't helps.

I've removed all regions from this USP and code writing works fine.
But! This isn't a solution for us.

Tried to edit another USP with code about for 300-400 lines and 5 regions and no dynamic Sql.
Code writing/autocomplete works about 30%-40% slower from usual. It dosen't freezes, but code writing isn't comfortable.

Tried to edit first USP with regions and dynamic Sql in Notepad++ with Sql Assistant - same freeze issues.

Quote:
I my suggestion for trying to turn off SQL Preprocessor doesn't help, please email a copy of your procedure to our support email (you can find support email on the About tab in the Options dialog)

Should I share code in this case?

Another question to you.
Is it possible to use my Sql Assistant Std Edition v.11 license with Sql Assistand Std Edition V.10?
The 10th version of Sql Assistant dosen't cause these issues as I remember. (should to recheck this point)

P.S.: I hope you'll can reproduce the problem with this information on your side.
Fri Jul 09, 2021 1:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Quote:
Is it possible to use my Sql Assistant Std Edition v.11 license with Sql Assistand Std Edition V.10?
The 10th version of Sql Assistant dosen't cause these issues as I remember. (should to recheck this point)


Yes of course, if you don't have your license key for version 10, please contact support.

I'm sure we all want to figure out and fix the route cause so it doesn't bother you in v11. Hopefully the issue can be reproduced quickly.
Fri Jul 09, 2021 7:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Please email the code that's causing issues to support and open a case. They need to reproduce the issue so that it can be fixed.
Fri Jul 09, 2021 8:51 am View user's profile Send private message
worze



Joined: 24 Jan 2014
Posts: 8

Post Reply with quote
I've already made contact with support via mail to keep in touch by this cause.
Thanks.
Fri Jul 09, 2021 11:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
The freezing issue is caused by script regions referenced in comments. Code formatting indirectly aborts internal code scanning operations which cause that temporary freezing effect. Our developers suggested replacing '#region' with '#_region' in the code, they say it should resolve the issue.
Wed Jul 14, 2021 6:27 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1914

Post Reply with quote
But won't that disable code folding for regions?
Thu Jul 15, 2021 2:56 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 709
Country: United States

Post Reply with quote
Weird, when I do

Code:

--#region

--#endregion (or #region)


I get folding

but if I insert any actual code between the region comments, folding vanishes. SSMS 18.9.1
Thu Jul 15, 2021 12:41 pm 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
Goto page 1, 2  Next
Page 1 of 2

 
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.