Toad World® Forums

Refreshing of Stored Procedures editing in Editor


#1

If I edit a stored procedure in the editor and then look at the script for the
stored procedure in the object viewer, it shows the old version of the stored
procedure until I manually refresh the item (by clicking on refresh item). Has
TOAD always done this? I could have sworn I didn’t use to have to manually
refresh the stored procedure in the object viewing after making the edit in the
editor.

Thanks,


Charles Haines
Senior Software Developer
P: (410) 535-5590 x1196

Recorded Books, LLC
http://www.recorded books.com


#2

Yes, Object Explorer does not refresh after you altered the procedure in the
editor and Toad has been doing so for quite a while. But it automatically picks
up changes made in the editor when you open the alter stored procedure dialog
for changing the same procedure again.

Igor.


#3

Yes, Object Explorer does not refresh after you altered the procedure in the
editor and Toad has been doing so for quite a while. But it automatically picks
up changes made in the editor when you open the alter stored procedure dialog
for changing the same procedure again.

Igor.


#4

Would it be possible to have some setting to autorefresh the stored procedure
when you click on the stored procedure or something like that? Would that
affect performance too much?


Charles Haines
Senior Software Developer
P: (410) 535-5590 x1196

Recorded Books, LLC
http://www.recorded books.com

On Tue, May 18, 2010 at 2:52 PM, Igor Manokhin
wrote:

Yes, Object Explorer does not refresh after you altered the procedure in the
editor and Toad has been doing so for quite a while. But it automatically
picks up changes made in the editor when you open the alter stored procedure
dialog for changing the same procedure again.

 

Igor.

#5

We are looking for new approaches here all the time but so far we didn’t
find any that wouldn’t hurt all-data-cached paradigm Toad is built around.

Igor.


#6

Data caching in this type of application does only damage, with minimal,
practically insignificant benefits (savings on db workload).

Caching is appropriate when cached data is rarely changed (there are many many
reads per one change). Even then, there must be a mechanism that detects cache
is invalid (gives old, not current data).

Developers change the db code all the time. Since toad is developer’s tool, it’s
totally inapropriate to use caching there. To cache something that is changing
all the time, without mechanism of detecting that cached data is old is, to be
polite, very bad idea. Result is I do not want to use toad because i can’t rely
on deceiving results it gives. I constantly have to check if the procedure (and
other objects) i see is not some old version of code and not the current one.

This is severe omission.

Regards,

Vedran Kesegić, mr.sc.

SQL Server 2008 MCP, MCTS, MCITP

HRPro, Croatia

Mensa


#7

Well I don’t think it’s that bad. Igor said that if you right click to
edit the stored procedure the most recent one is always pulled down for you to
edit. So really the only caching that is happen is if you view the stored
procedure. While I agree I don’t like that I don’t think it’s
necessarily the end of the world.


Charles Haines
Senior Software Developer
P: (410) 535-5590 x1196

Recorded Books, LLC
http://www.recorded books.com

2010/5/19 vedran.kesegic@ hrpro.hr

Data caching in this type of application does only damage, with minimal,
practically insignificant benefits (savings on db workload).

Caching is appropriate when cached data is rarely changed (there are many
many reads per one change). Even then, there must be a mechanism that
detects cache is invalid (gives old, not current data).

Developers change the db code all the time. Since toad is developer's
tool, it's totally inapropriate to use caching there. To cache something
that is changing all the time, without mechanism of detecting that cached
data is old is, to be polite, very bad idea. Result is I do not want to use
toad because i can't rely on deceiving results it gives. I constantly
have to check if the procedure (and other objects) i see is not some old
version of code and not the current one.

This is severe omission.

Regards,

Vedran Kesegić, mr.sc .

SQL Server 2008 MCP, MCTS, MCITP

HRPro, Croatia

Mensa

#8

ToadSS is not pure development tool and though for a developer working with his
own copy of the (local) database it can be a way to go, I strongly disbelieve
that a production DBA would be happy with this. And even for a developer –
we are talking about changing metadata, right? Changing database objects
definition, not data itself. Is it a real life scenario when two or more
developers change, say, some table definition at the same time? If it is, then
it’s obvious that the mechanism of detecting if data is obsolete should be
implemented on the server side…

I don’t want to say that cache implementation ToadSS has is ideal. I just
want to say that pure caching and not caching at all are two extremes and as
usual, the truth is somewhere in between.

Thank you for your comments,

Igor.


#9

„it’s obvious that the mechanism of detecting if data is obsolete
should be implemented on the server side“

Well, it already IS THERE on the server, you just have to ask it from you app
(toad).

It look so obvious to me, trivial.

I call this mechanism „smart caching“, here it is:

Allways store creation and modification date of every object you read from
database.

– creation and modification dates of all objects. Works from SQL2005 on

SELECT NAME , create_date , modify_date FROM sys . all_objects

First time your cache is empty, so you gather full data about object (e.g.
table: its columns, indexes, foreign keys, constraints, comments etc)

and store it in the cache, together with modify_date.

Next time you just check modify_date in sys.all_objects for just relevant ID’s
for that object (table, its constraints, etc). If modify_date is still the same,
display cached data.

If it is not the same, data is obsolete: gather full data about object again
(refresh object data).

If it is not in sys.all_objects at all, object was deleted, and remove it from
the cache.

For SQL2000 you don’t have modify_date, so you allways have to gather full data.
That is way better than displying WRONG data.

Like many pro’s I don’t use wizard to create objects (e.g. procedures). I write
code directly and TOAD currently does’n see that objects were changed by sql and
diplays wrong, old version from obsoleted cache.

DBA that has so fragile production that can’t handle single SELECT, should not
give toad or similar tools to connect to their databases at all. Measure the
impact, don’t assume. But with smart caching, impact is really minimal,
virtually none. Without it’ impact is so low, that is insignificant percentage
comparing to production full load DB takes regularly. To get CORRECT data is
more important than super-quickly get WRONG data.

Even for sql2000 you can have some optimisations in checking for new and deleted
objects:

– check for new objects. Works from SQL2000 on

SELECT name , crdate FROM sysobjects

Regards,

Vedran


#10

Sorry, I’ve just read my previous message and saw that I did some
modifications to it right before posting and that correction obscured what I
actually wanted to say. 1st statement should read, “Of course, I can
imagine turning cache off, but ToadSS is not pure development tool and though
for a developer working with his own copy of the (local) database it can be a
way to go, I strongly disbelieve that a production DBA would be happy with this.

When I was talking about “ the mechanism of detecting if data is obsolete
should be implemented on the server side… ” I meant slightly more than
just centralized storage of all database objects modification dates.
You’re right, this is obvious and very simple to see that any particular
object was changed. What I meant was rather a lack of mechanism on the SQL
Server side which would broadcast that those changes WERE made ( it look so
obvious to me, trivial J ). You have actual data when you alter/alter in the
editor even if the previous modification was made in the editor. So, you
actually want the changes that you (or your peer from another computer) made in
the editor or another tool to be reflected in the Object Explorer immediately,
right? And here is the question: In this case, HOW should Toad learn that it
should go to sys.all_objects and analyze all those dates that are stored there?

Also, do I understand correctly that you cannot use SSMS for your work either?

Thank you,

Igor.


#11

SQL Server doesn’t need to broadcast anything. It is possible (one DB trigger
that fires on obj change/creation/ del + one CLR procedure that sends that info
to subscribers + subscription mechanism), but that is very bad concept - imagine
waisted traffick to clients that is useless because clients are interested in
maybe just few objects not whole db, and in just short moments and not whole of
time. Not to mention that DBA would not be happy to install that mechanism (or
anything, why should they?) in their db. Broadcasting is turbo-wrong approach.

Already described much simpler and much more effective algorithm, but here it
goes again:

When you are about to display object info, instead of reading full details from
db (like no cache), you read just sys.all_objects’ s modification date for that
(one) object ID from db. If modification date is different, read full info from
db, if it is same, display cached data. That simple.

Impact on db is minimal, and you have always correct info about objects.

When you said that for single user toad works ok, I did my homework and checked
that:

Created a table:

After creating that object, I wanted to see what details toad will give me about
it:

And here they are:

In other words: no, toad’s currect cache DIDN’T detect object was created
through sql editor window.

Regards,

Vedran
image002.png


#12

SQL Server doesn’t need to broadcast anything. It is possible (one DB trigger
that fires on obj change/creation/ del + one CLR procedure that sends that info
to subscribers + subscription mechanism), but that is very bad concept - imagine
waisted traffick to clients that is useless because clients are interested in
maybe just few objects not whole db, and in just short moments and not whole of
time. Not to mention that DBA would not be happy to install that mechanism (or
anything, why should they?) in their db. Broadcasting is turbo-wrong approach.

Already described much simpler and much more effective algorithm, but here it
goes again:

When you are about to display object info, instead of reading full details from
db (like no cache), you read just sys.all_objects’ s modification date for that
(one) object ID from db. If modification date is different, read full info from
db, if it is same, display cached data. That simple.

Impact on db is minimal, and you have always correct info about objects.

When you said that for single user toad works ok, I did my homework and checked
that:

Created a table:

After creating that object, I wanted to see what details toad will give me about
it:

And here they are:

In other words: no, toad’s currect cache DIDN’T detect object was created
through sql editor window.

Regards,

Vedran
image003.png


#13

SQL Server doesn’t need to broadcast anything. It is possible (one DB trigger
that fires on obj change/creation/ del + one CLR procedure that sends that info
to subscribers + subscription mechanism), but that is very bad concept - imagine
waisted traffick to clients that is useless because clients are interested in
maybe just few objects not whole db, and in just short moments and not whole of
time. Not to mention that DBA would not be happy to install that mechanism (or
anything, why should they?) in their db. Broadcasting is turbo-wrong approach.

Already described much simpler and much more effective algorithm, but here it
goes again:

When you are about to display object info, instead of reading full details from
db (like no cache), you read just sys.all_objects’ s modification date for that
(one) object ID from db. If modification date is different, read full info from
db, if it is same, display cached data. That simple.

Impact on db is minimal, and you have always correct info about objects.

When you said that for single user toad works ok, I did my homework and checked
that:

Created a table:

After creating that object, I wanted to see what details toad will give me about
it:

And here they are:

In other words: no, toad’s currect cache DIDN’T detect object was created
through sql editor window.

Regards,

Vedran
image001.png