[SA 9.0.176 Pro] - Database Explorer in SSMS VS SQL Editor

[SA 9.0.176 Pro] - Database Explorer in SSMS VS SQL Editor

After solving the extremely slow database explorer problem by turning off extended attributes I was reasonably happy with the speed of Database Explorer in SQL Editor.

One thing that always still struck me as odd is when in SQL Editor I would expand "dbo" it would take a second or two to show me the folders below it, but I managed. But when it did this for every new tab every time I opened a tab was a little inconveniencing.

Now that I'm testing SSMS 2016, I'm also checking Database explorer there as well and I have ZERO lag opening the dbo schema here. Like this is ROCKET fast.

They are both using the same ADO connection to the same server, at least settings wise.

Best guess for me is that Database Explorer perhaps uses the existing connection in SSMS where perhaps it invokes it's own in SQL Editor?

A video:

http://screencast.com/t/JrjYTKbzV5qW

SSMS is the first section, SQL Editor is the second.

Maybe this has to do with ARITHABORT again? Gemisigio?

Well I threw SET ANSI_WARNINGS ON and SET ARITHABORT ON to the top of the DB Query "Objects (MSSQL) + Typed Synonyms" which seems to be the one that runs when I expand dbo (at least that's what I think when I was profiling) and it didn't seem to make a difference.

Perhaps another hint: If I hit the refresh button in Database Explorer (SSMS) and expand DBO it takes about as long as it does on SQL Editor.

Another hint:

I added a table to my database called 1_Table so it would show up top.

I opened a new SSMS tab and expanded database explorer down dbo to tables.. no 1_Table existed until I hit refresh.

I did the same thing with SQL Editor's session of Database explorer and it caught the change right away.

It seems SSMS's Database explorer may open with a cached version where SQL Editor never does.

Same thing, if I delete the table and check in a new SSMS tab.. it still shows as being there. Then if I do the same in SQL Editor it's gone without having to do a manual refresh.

	Quote:
	it's gone without having to do a manual refresh.

That's because SA monitors executed queries and whenever it senses DDL it automatically refreshes explorer in the current editor. SSMS doesn't do it. If you don't like that feature, I believe you can disable it in the Options

SysOp wrote:

	Quote:
	it's gone without having to do a manual refresh.

That's because SA monitors executed queries and whenever it senses DDL it automatically refreshes explorer in the current editor. SSMS doesn't do it. If you don't like that feature, I believe you can disable it in the Options

But I executed the query in SSMS, not SQL Editor Database explorer (in SQL Editor) still picked it up when I opened a new tab (seemingly because it's fully querying the DB and not caching last result?). The caches between SSMS and SQL Editor do seem to be separate... I have seen before when adding a column in SQL Editor via a query execution that Database Explorer showed the new column on the table I had expanded. That's not what I'm getting at.

Still trying to hash this out..