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.6k293444650

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:

×240
×20
×9

question asked: 27 Oct '14, 23:42

question was seen: 285 times

last updated: 28 Oct '14, 04:28