Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more
COURSE of the MONTH
14 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Applying constraints to a large table
Posted on 2016-08-04
I Have had to rebuild a database for a customer and whilst most of this process has worked well I have come up against a problem. On three tables when I run code similar to that below:
It is taking an inordinate amount of time to do 6 million rows. Is there a way top improve this performance?
The SQL SERVER 2012 installation is on a virtual server which I have no administrative rights to and the customer has fairly limited IT resources too.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_GroupUserMap_User]') AND parent_object_id = OBJECT_ID(N'[GroupUserMap]'))
ALTER TABLE [GroupUserMap] WITH NOCHECK ADD CONSTRAINT [FK_GroupUserMap_User] FOREIGN KEY([UserInfoKey])
REFERENCES [UserInfo] ([UserInfoKey])
It is taking an inordinate amount of time to do 6 million rows. Is there a way top improve this performance?
The SQL SERVER 2012 installation is on a virtual server which I have no administrative rights to and the customer has fairly limited IT resources too.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
- +1
7 Comments
Active today
Why? The existence of the foreign key is not checked for 6 million rows but only a few rows of metadata in sys.foreign_keys and since your alter table adds a constraint with NOCHECK, this shouldn't take long, as it doesn't check the 6 million rows.
If anything does take long, then it's not this code but something else you don't show here.
Bye, Olaf.
If anything does take long, then it's not this code but something else you don't show here.
Bye, Olaf.
Hey Olaf
That was my thoughts, but there is nothing else in the script that affects this code just more of the same. and yet it runs for a weekend and no completion :( I have pretty much read around the subject as completely is I think possible I am hoping someone else has had a similar problem and found a solution.
That was my thoughts, but there is nothing else in the script that affects this code just more of the same. and yet it runs for a weekend and no completion :( I have pretty much read around the subject as completely is I think possible I am hoping someone else has had a similar problem and found a solution.
Active today
Sure, you don't end with something like ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL?
I still stand to this, a constraint added with NOCHECK does not check rows, so the size of the table will not matter. You need to look for something else.
Bye, Olaf.
I still stand to this, a constraint added with NOCHECK does not check rows, so the size of the table will not matter. You need to look for something else.
Bye, Olaf.
No Olaf I highlighted the code as written and execute it and I agree with you, I was hoping for someone to have had this experience and found a way around it. That may never happen, I just need to give this a fair amount of time before I decide what to do next.
Active today
There must be a lock involved. I recommend you to install Adam Machanic's sp_whoisactive stored procedure and while you run that ALTER statement in a separate query window you will execute :
EXEC sp_whoisactive
and check for the blocking_session_id column. If there is a session_id in that column in the row that shows your ALTER statement then you will have to kill/terminate that process.
Here is the procedcure:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
Download here: http://sqlblog.com/files/folders/beta/entry42453.aspx
It is free.
EXEC sp_whoisactive
and check for the blocking_session_id column. If there is a session_id in that column in the row that shows your ALTER statement then you will have to kill/terminate that process.
Here is the procedcure:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
Download here: http://sqlblog.com/files/folders/beta/entry42453.aspx
It is free.
Active today
A lock should be a good explanation but what make a table to be locked for a full weekend?
Do you have any job running? Something like an Import/Export or even a maintenance job scheduled for weekends?
Also if you don't want to use sp_WhoIsActive you can use the built in sp_who2 and check for the rows with blocked column <> 0.
Do you have any job running? Something like an Import/Export or even a maintenance job scheduled for weekends?
Also if you don't want to use sp_WhoIsActive you can use the built in sp_who2 and check for the rows with blocked column <> 0.
Featured Post
Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month14 days, 20 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.