3.8 RC "Reading object text" very slow

shadowbob

One thing I noticed after installing 3.8 RC, is that when refreshing a cache, the progress pauses for a long time on "Reading object text" -- much longer than the previous version. This makes the overall cache refresh take longer than before. Is Prompt doing more than it used to, or is there a bug?

shadowbob

Actually, I notice that my SQL Compare now also hangs on "Reading object text" for quite a while. I don't think anything changed in my database schema or network, and it definitely happened when I installed Prompt 3.8 RC. Any thoughts, or am I doing something wrong? Thanks.

Andras B

shadowbob wrote:

One thing I noticed after installing 3.8 RC, is that when refreshing a cache, the progress pauses for a long time on "Reading object text" -- much longer than the previous version. This makes the overall cache refresh take longer than before. Is Prompt doing more than it used to, or is there a bug?

Hi,
@version), whether there are any open transactions in this database (dbcc opentran). Are you including system objects in prompt? In theory prompt should not affect SQL Compare at all (unless it keeps a lock to certain tables, which it should not do). What version of SQL Compare do you have. Also, are there any locking issues (select * from sys.dm_tran_locks)?

Background information: Reading the object text is retrieving the definition of textual objects, like stored procedures, views, constraints, etc. On 2000 it is reading these from dbo.syscomments, on 2005 it is reading from sys.sql_modules. Could you have a look how long a simple query from these tables/views takes?

Regards,
Andras

shadowbob

SQL version: Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

There are no open transactions, and there aren't any very often as this is a pretty isolated dev box.

System objects are not included in Prompt for me.

I'm using SQL Compare 6.2.0.271.

SELECT * FROM sys.sql_modules took 18 seconds just now (this is across the Internet, so I certainly expect Prompt to take some time to refresh -- it just seems to have suddenly started to take longer). I'm guessing that since reading the modules is taking 18 seconds, that probably explains the issue. My schema must have grown to the point where I'm really noticing the slow down. That being said, the speed change really seemed to correlate to installing Prompt 3.8 RC, and my database schema really hasn't changed much recently. I'll examine server and/or network issues as the probable cause.

Thanks for the background information!

merk

sorry to reopen an old thread but I am having this same problem with prompt 3.9 - it hands for a long time on reading object text.

this is using sql express 2005. running select * from sys.sql_modules only returns about 35 records and it returns them virtually instantly.