SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.355 Std] - Fetching schema objects is too frequent
Goto page 1, 2, 3  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.355 Std] - Fetching schema objects is too frequent
Author Message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post [11.5.355 Std] - Fetching schema objects is too frequent Reply with quote
We have a fixed database schema so it does not change. Whenever we change databases then SQL Assistant will lock up the user interface and flash a pop-up message:
Fetching <databaseName> schema objects.



The delay is about 30 seconds and really painful because it happens whenever we switch databases and as I mentioned in our case is completely pointless.

Is there a way to only refresh the objects on demand and stop it automatically doing this?

In the settings I applied the setting "DB Options: Automatic Cache Refresh = No" but it makes no difference. It still blocks me and freezes the UI every time I move to a different database.

I see there is a hotkey defined for "Refresh Cache" which appears to do what we are looking for, we just need it to stop also automatically doing a cache refresh by itself.


(spelling error in tooltip!)
Tue Mar 02, 2021 1:02 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Hi.

How big is your schema?
Tue Mar 02, 2021 4:13 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Hi Brendon,

The issue seems to be in a different place. Fetching schema object names
from the catalogue shouldn't take that long, normally it takes anywhere from
several milliseconds to a couple of seconds, so that shouldn't be a
bottleneck. There are some ways to optimize the catalogue queries, but let's
troubleshoot first why you're experiencing such a bad performance result.

How many objects do you have in your databases roughly?

Do you use NamedPipes of TCP/IP connection? NamedPipes are typically much
slower with remote databases.

Is the performance result reproducible in SQL Assistant SQL Editor?
If you don't have Professional Edition featuring the professional IDE, you
can still test it with the light SQL Editor version available in the
Standard Edition). We need to see if the issue is specific to connection
type and source application.


Sincerely,
Technical Support at SoftTree Technologies, Inc.
Tue Mar 02, 2021 9:23 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
I used the SQL Profiler to capture the metadata query used by SQL Assistant. It is quite long so is copied below at the end. It returns 12,131 rows, most of which are stored procedure names but there are lots of tables, triggers and functions as well.
Can we configure the metadata query to remove some of the types that we don't need?

When SQL Assistant runs it the query takes 56 seconds. When I copy the query text into MS Server Management Studio it takes just 2 seconds to run.
Do you know why the SQL Assistant is so much slower?

One difference is that the SQL Assistant runs with event class = RPC but when I do it myself the class is SQL:Batch.

Some information about RPC v Batch is here: https://dba.stackexchange.com/questions/34484/difference-between-sql-batch-statement-and-rpc

I guess SQL Assistant's call is flagged as RPC because it uses "sp_executesql" but I don't know why my version of the same call is flagged as BATCH when I run it from the Management Studio script editor. I don't know if this explains the difference in speed.

If we can get the SQL Assistant's call to run at 2 seconds then that would be OK and a great improvement on currently circa 1 minute!

Code:
exec sp_executesql N'SELECT DISTINCT
   o.name,
   CASE WHEN o.type = ''SN'' THEN ''^'' +
    CASE CAST(OBJECTPROPERTYEX(o.object_id,''BaseType'') AS VARCHAR)
    WHEN ''AF'' THEN ''F''
    WHEN ''FN'' THEN ''F''
    WHEN ''FS'' THEN ''F''
    WHEN ''FT'' THEN ''U''
    WHEN ''IF'' THEN ''U''
    WHEN ''IT'' THEN ''T''
    WHEN ''P'' THEN ''P''
    WHEN ''PC'' THEN ''P''
    WHEN ''S'' THEN ''T''
    WHEN ''SQ'' THEN ''I''
    WHEN ''TF'' THEN ''U''
    WHEN ''TT'' THEN ''K''
    WHEN ''U'' THEN ''T''
    WHEN ''V'' THEN ''V''
    WHEN ''X'' THEN ''P''
    ELSE '' ''
    END
   ELSE
   CASE
     WHEN p.[object_id] IS NOT NULL THEN ''TP''
     ELSE o.TYPE
   END END,
   o.object_id,
   o.create_date,
   o.modify_date 
from
   [Eurodat507_EUNZCH].sys.all_objects o WITH (NOLOCK)
   LEFT JOIN [Eurodat507_EUNZCH].sys.partitions AS p WITH (NOLOCK)
     ON o.[type] = ''U'' AND p.[object_id] = o.[object_id] AND p.partition_number = 2
where
   type in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'',''SN'',''PC'',''FT'',''FS'',''TR'',''SO'')
   and (
       o.schema_id = @b0       
       or (
          1 = @b1       
          and user_name() = ''dbo''
          and o.schema_id = 4
          and o.name in (''sysobjects'',''sysindexes'',''syscolumns'',''systypes'',''syscomments'',
         ''sysfiles1'',''syspermissions'',''sysusers'',''sysproperties'',
         ''sysdepends'',''sysreferences'',''sysfulltextcatalogs'',''sysindexkeys'',''sysforeignkeys'',
         ''sysmembers'',''sysprotects'',''sysfulltextnotify'',''sysfiles'',''sysfilegroups'')
         )
      )
   and not (o.name = ''dtproperties'')
   and not (o.name like ''dt%'' and o.type = ''P'')

union all

SELECT t.NAME + COALESCE(CHAR(0) + t1.name
   + case
   when t1.name in (''varchar'', ''char'', ''binary'', ''varbinary'')
     then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length) end + '')''
   when t1.name in (''nvarchar'', ''nchar'')
     then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length / 2) end + '')''
   when t1.name in (''decimal'', ''numeric'')
     then ''('' + convert(varchar, t1.[precision]) + '','' + convert(varchar, t1.scale) + '')''
   else ''''
   END, ''''),
   COALESCE(o.type, ''TD''),
   COALESCE(tt.type_table_object_id, t.user_type_id),
   o.create_date, o.modify_date
FROM [Eurodat507_EUNZCH].sys.types t WITH (NOLOCK)
LEFT JOIN [Eurodat507_EUNZCH].sys.types t1 WITH (NOLOCK) ON t.system_type_id = t1.user_type_id
LEFT JOIN [Eurodat507_EUNZCH].sys.table_types tt WITH (NOLOCK) ON tt.name = t.name
LEFT JOIN [Eurodat507_EUNZCH].sys.objects o WITH (NOLOCK) ON tt.type_table_object_id = o.[object_id]
WHERE t.is_user_defined = 1 and t.schema_id = @b2       

',N'@b0 int,@b1 int,@b2 int',@b0=1,@b1=1,@b2=1
[/code]
Tue Mar 02, 2021 9:40 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
here is a screenshot from the SQL Profiler

SQL Assistant runs as RPC and takes 55840 milliseconds.

Running the same query from the Microsoft SQL Server Management Studio in BATCH mode takes just 1474 milliseconds.


Tue Mar 02, 2021 9:47 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
I checked the "SQL Editor", also from SoftTree and bundled with SQL Assistant.
It has the same problems with refreshing the cache as in Microsoft SQL Server Management Studio. I can take between 40 and 90 seconds and runs as RPC call.

Screenshot:

Tue Mar 02, 2021 9:58 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Our database server configuration is using TCP/IP for network connections and Named Pipes are disabled.
I believe that is the recommended configuration.
Wed Mar 03, 2021 12:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
2 seconds sounds to me like reasonable timing. SQL Assistant should be actually able to retrieve the result with 12,131 rows faster than SQL Server Management Studio (SSMS) maybe in under a second. Unlike SSMS it internally doesn't fetches result set row by row, it fetches large blocks of rows.

Please try the following. In SQL Assistant Options, set the filter at the bottom to Show options for SQL Server. Then activate DB Options tab and expand DB Queries section. Locate Context Context (MSSQL) query, and in the beginning of that query add the following line

SET ARITHABORT ON


Click Ok to save the changes and test if that makes a difference for SQL Intellisense. Please let us know how it goes. The next step depends on the result of that test.
Wed Mar 03, 2021 4:03 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Using ARITHABORT definitely helps! It gets the execution time down from 58 to just 14 seconds which is a palpably faster!

The effect is reproducible and I can revert it by removing ARITHABORT.

Running the query in MS SQL Server Management Studio is still faster though at exactly 2 seconds so I think there is still room for improvement.

Some screenshots from the profiler to show the timing effects:

Previously:


With ARITHABORT

Wed Mar 03, 2021 4:30 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Great. The fact that it makes a difference indicates some dependency on the database connection parameters that make the database choose inefficient execution plan. But also a worry that SQL Assistant opens a separate connection not shared with SSMS. Please give me a little while to research this and get back to you with specific steps.
Wed Mar 03, 2021 10:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Can you please check in SQL Assistant Options if Targets -> SQL Server Management Studio -> Advanced -> Register SQL Assistant add-on option is set to Yes?
Wed Mar 03, 2021 10:33 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Hi SysOp,
the add-on option was NOT registered in my system. I changed the setting to YES and on saving with OK there was a system UAC prompt to install it. I supplied this and then rebooted the machine.

One big, big difference now that the add-on is registered is that it only needs to refresh the cache once for each database as long as I keep MS SQL Server Management Studio open.
If I hop back and forwards between two databases then there is only a single cache refresh for each one the first time the SQL Assistant sees it and then if I keep swapping back and forth it doesn't constantly do a cache refreshes each subsequent time.
This is the behaviour I was looking for when starting this topic so that is great!

Now the speed of refreshing the cache is still problematic. The first time I open the application it does it for the default database in 1-2 seconds. For other databases it is either the same 1-2 seconds or occasionally 45 - 60 seconds.
It seems like there are two different modes it operates in; one is good and fast, the other incredibly slow and I can't predict which mode it will use each time.

Confusing!

You mentioned the properties of the connection are probably to blame. Here is a screenshot of the connection I am using:


As you can see it is just the default ADO.NET connection created automatically by SQL assistant.

So the core problem seems to be that the add-on needs to be registered for it to remember the cache of each database and I will keep observing how it behaves over the next few days.

Thanks, Brendon
Thu Mar 04, 2021 6:30 am View user's profile Send private message
Mindflux



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

Post Reply with quote
Do you happen to have any linked servers that are remote to the connected SQL Server instance?

I had cache refresh/queries (occasionally) with linked servers. I think support helped me modify one of the cache queries to no longer scan linked server databases for their layouts. It made a massive difference.
Thu Mar 04, 2021 11:03 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Thank you for the details. The provided screenshot is for SQL Assistant. If the addon is installed correctly, you should not see SQL Assistant's connection prompts. It should transparently use connections you open in SQL Server Management Studio. If you get separate connection prompts, we need to work on fixing that issue. It can also explain sudden delays when switching between databases, which might be a result of expiring idle connections or something causing it internally to reconnect and that's where the extra time is spent. If you don't see separate connection prompts, and it's not because the "default" connection is selected, then we need to look into other factors. As Mindflux suggested, it could be caused by remote linked servers registered in the database server, and in this case we would want to filter them out and ignore in Intellisense if they slow down code editing and internal cache refreshes.
Thu Mar 04, 2021 1:17 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
The connections window doesn't open automatically. I just went into SQL Assistant to show you my current settings. Normally the connections are managed silently in the background and there are no prompts.

We do have a linked server in a remote location so yes, please do explain how to modify the cache queries to exclude this.
Thu Mar 04, 2021 5:40 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, 3  Next
Page 1 of 3

 
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.