Procs/funcitions code in cache

EsioN

Hello.

Just some ideas:

I'd commented this in some old posts (archieved), but now I want to discuss this again.

When I type a alter procedure statement, SP brings to me the stored procedure code. It's great, but I think we have some problems with this:

1. The time used by SP to pick all my codes and the memory used to store this can be a problem in large databases, with too many code.

2. If I use the alter procedure statement, change a proc, and other user, that alredy has that procedure in SP cache, procedes with the same step, he can change the old procedure code.

I understang SP staff has a good reason to mantain code in cache, but for me it can be dangerous (specially the topic 2).

If we think the main SP purpose is to provide hints/intelissense for dml and not for ddl, maybe you shold consider not put procs/functions in cache, but, getting the code every time the user type alter proc.... Because to retrive only the texts from one procedure, from syscomments, it will not take too much time/resources, and we can be sure we have the real procedure code. And of course, the memory use will be less expensive for SP.

For the proc "header" -> the parameter etc, these can be in cache, to able SP to continue with the function Insert parameters for functions and stored procedures. Like table definitions, procedures parameters are changes so few times, and I consider these (table/triggers defintions and proc parameters) can be cached at memory.

Just my opinion.

Best regards.

Bart Read

Hi Esio,


I may or may not have explained this before but the problem is that our hands are somewhat tied for the time being until we change our meta-data model. In order to try to cut down the development time we employed some timely code reuse and pressed the SQL Compare engine into service as our meta-data store.

Now that's fine, but it doesn't allow us to do things like this, and making those kinds of changes to it isn't something we want to do at this point since four or five of our other products use it, and SQL Compare just isn't the right place to make those changes. Ultimately what SQL Prompt needs is its own meta-data model that's entirely distinct from SQL Compare, but that's obviously a lot of work to get right because obviously we want to improve things like the memory footprint, pull stuff back lazily, be able to gracefully handle partial data, and generally have it behave in a way that's completely different to the current model. All of that is exceptionally unlikely to happen before SQL Prompt 4. My understanding is actually that SQL Compare does cache much of that information on disk anyway.

Your problem appears to be one of cache synchronization, and in the situation you're talking about I'd suggest that one thing you can do to alleviate the problem is to enable automatic updating of the cache. It's not a perfect solution, since it only updates databases when you start a new editor session, but it will certainly help.


Thanks,
Bart

EsioN

Ok Bart.
It's now clear for me what is happening.

Well, for me, it isn't the perfect environment, but of course, this little problem will not decrease all benefit SP brought.
We can survive with this ;-)

Thanks a lot for your clearly response.

Best regards

Ésio