Adding a nullable column to a table took 5+ hours

[Former Member]

Hello,

We use SQL Anywhere 12. We have a table defined as below called reservation. I added a column promotion_rn using the alter table statement below and this took an hour on my local development machine but when I ran the add column sql in production the statement took 5+ hours. Could anyone offer any advice on why this may have happened and possibly what I can do to improve this time when adding columns to this table in future? I ran all the sql statements in iSQL.

The reservation table contains approximately 750,000 rows.

The result of "call sa_table_fragmentation( 'reservation' )" gives the following:

TableName	rows	row_segments	segs_per_row
reservation	751,254	752,554	1.00173044003759

alter table reservation add promotion_rn unsigned int references promotion(promotion_rn) null;

CREATE TABLE "reservation" (
"reservation_rn" UNSIGNED INT NOT NULL DEFAULT AUTOINCREMENT,
"reservation_id" VARCHAR(255) NOT NULL UNIQUE,
"reservation_dscrptn" "text" NOT NULL,
"reservation_cntct_nme_frst" "text" NOT NULL,
"reservation_cntct_nme_lst" "text" NOT NULL,
"reservation_cntct_eml" "text" NOT NULL,
"reservation_cntct_phne" "text" NOT NULL,
"reservation_dte" "datetime" NOT NULL,
"reservation_dte_crtd" "datetime" NOT NULL,
"reservation_rfrnce" "text" NOT NULL,
"reservation_actve" "BOOL" NOT NULL DEFAULT '1',
"activity_rn" UNSIGNED INT NOT NULL,
"webuser_rn" UNSIGNED INT NOT NULL,
"reservation_pckp_lctn" "text" NOT NULL,
"reservation_pckp_dte" VARCHAR(50) NOT NULL,
"reservation_dte_cnclld" "datetime" NOT NULL,
"reservation_gsts" INTEGER NOT NULL,
"reservation_pymnt_mthd" VARCHAR(30) NOT NULL,
"agent_rn" UNSIGNED INT NOT NULL,
"reservation_cntct_agnt_nme" VARCHAR(255) NULL,
"reservation_rte" VARCHAR(255) NULL,
"reservation_chldrn" VARCHAR(255) NULL,
"reservation_nts" "text" NULL,
"reservation_ntnlty" VARCHAR(255) NULL,
"reservation_usd" CHAR(1) NULL,
"operator_agency_rn" INTEGER NULL,
"reservation_cstmr_type" "text" NULL,
"referral_operator_id" "text" NULL,
"referral_activity_id" "text" NULL,
"reservation_othr_agncy" "text" NULL,
"reservation_rfrnce_extra" "text" NULL,
"reservation_duration" INTEGER NULL DEFAULT 0,
"reservation_srce" VARCHAR(100) NULL,
"reservation_dte_end" TIMESTAMP NULL,
"reservation_latitude" VARCHAR(32) NULL DEFAULT '0',
"reservation_longitude" VARCHAR(32) NULL DEFAULT '0',
"reservation_origin_rn" UNSIGNED INT NULL,
"account_rn" UNSIGNED INT NULL,
"reservation_hide_pricing_on_ticket" "BOOL" NOT NULL DEFAULT 0,
"pickup_rn" UNSIGNED INT NULL,
"reservation_promo_codes" "text" NULL,
"customer_rn" BIGINT NULL,
"itinerary_rn" UNSIGNED INT NULL,
"promotion_rn" UNSIGNED INT NULL,
"reservation_prmtn_dscnt_amnt" "MONETARY" NULL,
"reservation_superseded_on" "datetime" NULL,
PRIMARY KEY ( "reservation_rn" ASC )


);