[progress Communities] [progress Openedge Abl] Forum Post: Re: Pro2: Data In The Ms Sql...

Status
Not open for further replies.
V

Valeriy Bashkatov

Guest
In the first example I used CHAR type to match with Progress - now I know that I was wrong. Thank you for the clarification, Keith! But initially Pro2SQL tool generates fields with varchar. Exemple from real db: CREATE TABLE [DBO].[qc_mstr] ( [prrowid] varchar(36) not null PRIMARY KEY NONCLUSTERED, [qc_cmtindx] integer null, [qc_due_date] datetime null, [qc_insp_loc] varchar(80) null, [qc_inv_stat] varchar(80) null, [qc_lead_time] decimal(28,10) null, [qc_loc] varchar(80) null, [qc_lot] varchar(80) null, [qc_nbr] varchar(30) null, [qc_ord_date] datetime null, [qc_part] varchar(30) null, [qc_project] varchar(80) null, [qc_qty_comp] decimal(28,10) null, [qc_qty_ord] decimal(28,10) null, [qc_qty_rjct] decimal(28,10) null, [qc_ref] varchar(80) null, [qc_rel_date] datetime null, [qc_rmks] varchar(80) null, [qc_serial] varchar(50) null, [qc_site] varchar(80) null, [qc_status] varchar(30) null, [qc_teststep] varchar(30) null, [qc_type] varchar(30) null, [qc_user1] varchar(80) null, [qc_user2] varchar(80) null, [qc__chr01] varchar(80) null, [qc__chr02] varchar(80) null, [qc__chr03] varchar(80) null, [qc__chr04] varchar(80) null, [qc__chr05] varchar(80) null, [qc__dec01] decimal(28,10) null, [qc__dec02] decimal(28,10) null, [qc__dte01] datetime null, [qc__dte02] datetime null, [qc__log01] bit null, [qc_autoissue] bit null, [qc_job] varchar(80) null, [qc_domain] varchar(8) null, [oid_qc_mstr] decimal(28,10) null, [Pro2SrcPDB] varchar(12) null, [pro2created] datetime null, [pro2modified] datetime null, ) GO CREATE INDEX [qc_mstr##qc_due_part] ON [DBO].[qc_mstr] ( [qc_domain] ASC, [qc_due_date] ASC, [qc_part] ASC, [qc_site] ASC ) GO CREATE INDEX [qc_mstr##qc_lot] ON [DBO].[qc_mstr] ( [qc_domain] ASC, [qc_lot] ASC ) GO CREATE INDEX [qc_mstr##qc_nbr] ON [DBO].[qc_mstr] ( [qc_domain] ASC, [qc_nbr] ASC, [qc_lot] ASC ) GO CREATE INDEX [qc_mstr##qc_part] ON [DBO].[qc_mstr] ( [qc_domain] ASC, [qc_part] ASC, [qc_site] ASC, [qc_due_date] ASC ) GO CREATE INDEX [qc_mstr##qc_type_nbr] ON [DBO].[qc_mstr] ( [qc_domain] ASC, [qc_type] ASC, [qc_nbr] ASC, [qc_lot] ASC ) GO In the Progress dbanalys the size of this table is PUB.qc_mstr 408863 57.6M 98 195 147 408863 1.0 1.0 I tried to calculate the size of the table in SQL based on this article: https://technet.microsoft.com/en-us/library/aa933068(v=sql.80).aspx I get the same size using the formula provided in the article: ~57.4 M As far as I understand now, all previously used scripts showed me a real physical space, or the wrong size (row lenght). This table in MS SQL takes physically (sp_spaceused) 130568 KB. it is equal to 130568 KB / 8KB (page size) = 16321 pages. But according to the calculations it should take only 7301 pages. Other words physically used twice as much space. And it's even more confusing me [:S] Keith, what method for calculating the size of the data in the SQL used you? Regards, Valeriy Bashkatov P.S. I just need to formulate a comprehensive explanation to the customer about why the physical size of the SQL-database (created by Pro2) more than Progress-database.

Continue reading...
 
Status
Not open for further replies.
Top