The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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:

TableNamerowsrow_segmentssegs_per_row
reservation751,254752,5541.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 )

);

asked 27 Oct '14, 23:42

JohnKC's gravatar image

JohnKC
16124
accept rate: 0%

closed 28 Oct '14, 04:28

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644

The question has been closed for the following reason "Cf. this almost identical [question](http://sqlanywhere-forum.sap.com/questions/23266/adding-a-column-to-a-table-took-5-hours)..." by Volker Barth 28 Oct '14, 04:28

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×238
×20
×9

question asked: 27 Oct '14, 23:42

question was seen: 251 times

last updated: 28 Oct '14, 04:28