Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Alter Table Problem - over 125 million rows in table and query takes to long.

Posted on 2010-09-06
4
Medium Priority
?
327 Views
Last Modified: 2013-11-10
Hi,
I have a table with 125million rows in it on the live db
i replicate ths db to a mysql db daily
For reporting reaons, i want to pull in the data from this table into my SQL SERVER db.
i only pull in the previous days entries to the SQL SERVER.

however to do this i want to add a colunm called DW_TIMESTAMP

So i would use this syntax
ALTER TABLE MYTABLENAME
ADD COLUMN dw_timestamp TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

however because there are soo many rows this is taking forever, in fact it stopped the Mysql server because i believe it timmed out.

Can anyone suggest how i can speed up this process.

I was thinking if i could add a where clause but i don't believe that would work.

Kind Regards,
Putoch
0
Comment
Question by:Putoch
[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
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33610603
please clarify: is this MS SQL Server, or MySQL?
which version / sp ?

this "alter table" should not take "forever", unless it's blocked all the time by other processes issuing schema locks on the table ...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 33610616
Maybe if you add the column, allowing nulls first.

Then assign the default value as a separate transaction.

If this is a always live DB, then you may just have to wait it out. You should only need to do it once.

0
 

Author Comment

by:Putoch
ID: 33616711
Its a MYSQL db (version 5.1.44 )

i was able to alter any other table i needed to with out any trouble, it seems to only be this table.
i will try to do it seperatly now and see how this works.

Something strange though happened with the other tables.
i set up the timestamp to Default current_timestamp on update.
however when i updated all those other tables the timestamp defaulted to 0000-00-00.

Why would this happen do you know?
Is there something i'm missing on my syntax.
the timestamp is set up as NOT NULL.

ALTER TABLE MYTABLENAME
ADD COLUMN dw_timestamp TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Thanks a million,
putoch
0
 
LVL 1

Accepted Solution

by:
colin_o_brien earned 2000 total points
ID: 34053516
you could try renaming the table,  adding the new column using a SELECT INTO statement into MYTABLENAME, and then re-adding whatever indexes/constraints you have.  
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question