Red Gate forums :: View topic - Link Static Data - Takes 5 Minutes to Compare Databases
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 2
SQL Source Control 2 forum

Link Static Data - Takes 5 Minutes to Compare Databases

Search in SQL Source Control 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JackAce



Joined: 08 Jul 2011
Posts: 45

PostPosted: Mon Jul 25, 2011 5:56 pm    Post subject: Link Static Data - Takes 5 Minutes to Compare Databases Reply with quote

We currently have 17 tables with about 115,000 rows statically linked via SQL Source Control. This is just a small fraction of the total list of tables that we would like to link. When clicking on the "Commit Changes" tab for the SSMS, it takes about 5 minutes before the change list shows up.

This is currently tolerable under optimal conditions. Under sub-optimal conditions (e.g. when you connect remotely via VPN), I have timeout issues.

Looking forward, I am not sure that we will be able to link all of the lookup tables that we would like to ultimately manage. We have dozens of tables and I'm afraid that the time it takes to view the change list would take over an hour, which would not be acceptable.

Are there ways to minimize the time that it takes to determine the change list when you are versioning the data in many large tables? We use these linked tables to generate the update scripts (via SQL Data Compare) from the Dev to QA to Staging to Production environments.

Some possible things that I was thinking of:
1) Link the tables and generate the scripts and then copy the scripts to a separate folder and then unlink the table.
Obviously, this is a clunky solution because you have to repeat the process every time you insert/update/delete a row in the table.

2) Don't link the tables at all. Just use the SQL Data Compare command line executable and hand pick the tables that you want to deploy via the /Include switch. I am afraid of doing this because it would be very easy to have test data accidentally make it to production, since developers aren't manually committing changes to source control.

3) In SSMS, you could create two Registered Database connections to the same database (using different logins, for example). One connection could just link the schema to one folder in source control (e.g. http://mysvnserver/myDbRepository/trunk/SchemaScripts). The other connection could link the Data to source control (http://mysvnserver/myDbRepository/trunk/DataScripts) where you filter out all objects except the tables involved (with no indexes, no foreign key constraints, no defaults) and just link the data. This seems like a viable solution, but a little clunky and awkward to say the least. This would also mean that you have the schema of the tables checked into multiple places.

Is there a more elegant solution for versioning monstrous tables?
Back to top
View user's profile Send private message
ccollins



Joined: 22 Jun 2005
Posts: 50

PostPosted: Wed Jul 27, 2011 3:40 pm    Post subject: performance issues with over 50 static data linked tables Reply with quote

From our experience, up to 25 tables is not so bad, between 25 and 50 it is a long inconvience, after 50 unbearable.

We have one database with 304 static data linked tables. The Calculating changes spinner has been going for two hours now... The get latest might take all day for our other programmers.
Back to top
View user's profile Send private message
JackAce



Joined: 08 Jul 2011
Posts: 45

PostPosted: Wed Jul 27, 2011 5:31 pm    Post subject: Re: performance issues with over 50 static data linked tables Reply with quote

ccollins wrote:
From our experience, up to 25 tables is not so bad, between 25 and 50 it is a long inconvience, after 50 unbearable.

We have one database with 304 static data linked tables. The Calculating changes spinner has been going for two hours now... The get latest might take all day for our other programmers.


So have you been doing anything besides just waiting the X hours for the change list to show up? I was hoping that others have figured out a workaround.

I'm even getting the "timeout expired" with another database that has very few linked tables. It's a small database and it's brand new, so there are just a few dozen tables and stored procedures. I'm still trying to figure out why.
Back to top
View user's profile Send private message
ccollins



Joined: 22 Jun 2005
Posts: 50

PostPosted: Wed Jul 27, 2011 6:50 pm    Post subject: still waiting for the changes to be calculated... Reply with quote

Last week I was getting timeouts. I believe the timeouts were due to either a disconnect from the tfs server or no response from the sql server, (i.e. sql timeout). I am not getting timeouts today.

I stopped the last attempt to commit of 304 tables around noon our time today. I started it back at 12:45PM CST/CDT. It gets to the calculating changes spinner in about two minutes and is spinning... I plan on letting this and nothing else run until.
Back to top
View user's profile Send private message
ccollins



Joined: 22 Jun 2005
Posts: 50

PostPosted: Thu Jul 28, 2011 1:09 pm    Post subject: calculating changes not spinning now... Reply with quote

Thursday morning and still at Calculating changes. The SSMS window does not have a (Not Responding), appended title. However when I click on the commit changes comment text box I receive a systray ballon stating that sql is busy.

I noticed that the Calculating changes is not spinning at this time and neither is the yy on the databases.

I believe ssms is stalled, locked up or otherwise waiting for something from rrssc.

I plan on stopping it soon if I don't have success in tracing or troubleshooting the issue.
Back to top
View user's profile Send private message
ccollins



Joined: 22 Jun 2005
Posts: 50

PostPosted: Thu Jul 28, 2011 1:44 pm    Post subject: DAD Reply with quote

Dead After a Day. I had to end the SSMS process, no other method worked.

I am going to attempt to add static linked tables in groups and check in. This may work around the check in issue, but probably not the get latest issue.

This is a wall, not a speed bump.
Back to top
View user's profile Send private message
ccollins



Joined: 22 Jun 2005
Posts: 50

PostPosted: Thu Jul 28, 2011 2:23 pm    Post subject: app hang information Reply with quote

Description:
A problem caused this program to stop interacting with Windows.

Problem signature:
Problem Event Name: AppHangB1
Application Name: Ssms.exe
Application Version: 2009.100.1600.1
Application Timestamp: 4bb679e7
Hang Signature: 5d4e
Hang Type: 6400
OS Version: 6.1.7600.2.0.0.256.1
Locale ID: 1033
Additional Hang Signature 1: 5d4e89e34b78d1eaec09604964415018
Additional Hang Signature 2: 7339
Additional Hang Signature 3: 7339e7c669c193a7ad276d15bf9f2609
Additional Hang Signature 4: 5d4e
Additional Hang Signature 5: 5d4e89e34b78d1eaec09604964415018
Additional Hang Signature 6: 7339
Additional Hang Signature 7: 7339e7c669c193a7ad276d15bf9f2609

Read our privacy statement online:
http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
C:\Windows\system32\en-US\erofflps.txt
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 941
Location: Red Gate Software

PostPosted: Thu Jul 28, 2011 6:29 pm    Post subject: Reply with quote

Hi ccollins

We need to log a support call for you regarding your issue.

Can you please send an e-mail to support@red-gate.com with your contact details and include a reference to this forum post?

We will then be able to create a support call for you that has the correct contact information. A member of the Product Support Team will then be able contact you and investigate your problem.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
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