SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Refresh cache only on demand ?

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Refresh cache only on demand ?
Author Message
stephh



Joined: 29 Jul 2021
Posts: 30

Post Refresh cache only on demand ? Reply with quote
Hello,

I'd like to know if is it possible to refresh the cache only on demand and not when I connect to a SQL Server with SSMS ?

I optimized some BD Queries and even disabled some others.
But when I connect to SQL Server outside the local network (Production servers), S.A. can takes 45-60 seconds to refresh the cache (versus 4-5 seconds in local network).This is due to a large amount objects in databases (1000 tables, 6000 stored procedures, ....). And that's not necessary because objects are updated only 2-3 times per year, and each server has the same structure.

Thanks.
Fri Aug 13, 2021 2:39 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1914

Post Reply with quote
As for the manual refreshing, this button sitting at the top right corner of the SA Database Explorer might invoke refreshing of the cache on demand.

Regarding not refreshing it on connect, I'm not sure how that could be done. I doubt (though it could happen) that SA stores that cache anywhere locally it could restore it from, other than rebuilding it from the server upon connecting to it. I can think of a few nasty ways by ravaging and mutilating the existing DB queries so that they would retrieve their results in some well-indexed tables instead of querying the system tables/views and only update those tables using the original queries when they are considered outdated. That could speed things up a bit, but the performance improvement might not be worth the effort. Furthermore, that table would still be remote (not local) as I don't think you could write to anything else than the server (and possibly the linked servers on that server) when executing the code in DB Queries. It's also not a very good idea to store such things on a production server.

But the idea is intriguing, indeed. The data that cache consists of could be stored for hundreds of servers and databases neatly in a local SQLite database and accessed quite fast (though still relatively slow when compared to storing it in the memory). Provided the users are willing to risk working with the outdated cache. Which could also be remedied by frequent updates of the mentioned cache. Which in turn would re-establish the original problem. Hell, I'm so good at this :)
Fri Aug 13, 2021 1:50 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Just an idea. You can replace code for the Objects query with structurally compatible SELECT * FROM my_ojects_cache_table. Create a scheduled task using SQL Assistant's Scheduled Tasks (it will run on your computer) or using SQL Agent (it will run on the server) to refresh my_ojects_cache_table table periodically, perhaps once a day.That refresh will not delay anything. And the query against static my_ojects_cache_table will be very fast. You can also always kick off cache table refresh manually if you need to force its refresh.
Fri Aug 13, 2021 10:28 pm View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 30

Post Reply with quote
Thanks for all suggestions.
I'll try the query "SELECT * FROM my_objects_cache_table".
Thanks
Mon Aug 16, 2021 1:48 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 30

Post Reply with quote
Hello,

It works great !
Thanks for the suggestion SysOp !
You save my life, or at least my patience ! :-)


For those are interested, here what I did :

1) Save the DB Query "Objects" into a table for each databases you want to improve the speed cache, with 2 new columns : schema_id and db_name
2) In SQL Assistant, "BD Options" tab, change the DB Query "Objects" like this :

Code:
IF EXISTS(SELECT 1 FROM MYDB.sys.tables WHERE name = 'SQLAssistant_Objects')

AND '$DB_NAME$' in ( 'db1' , 'db2' )
BEGIN

    select s.name , s.[type] , s.[object_id], s.create_date, s.modify_date
        from MYDB.dbo.SQLAssistant_Objects s
        where s.[schema_id] = :SCHEMA_ID
        and s.[db_name] = '$DB_NAME$'

END
ELSE
BEGIN

    -- original query   
    SELECT DISTINCT
      o.name,
      ....
      ....
END


3) Deploy a new job to refresh the table


Last edited by stephh on Wed Aug 18, 2021 7:55 am; edited 1 time in total
Tue Aug 17, 2021 8:32 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7414

Post Reply with quote
Great. Thank you.

If there is a large number of databases, it might be a good idea to create an index on MYDB.dbo.SQLAssistant_Objects ([db_name], [schema_id])
Tue Aug 17, 2021 10:24 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.