February 19, 2003 at 9:13 am
I manage a large DB of 67 gig with 200+ tables.
Majority of the data in the DB is loaded/imported from mainframe text files with fixed length fields. If the mainfarme data field is defined as 15 characters in the text file I get is the value plus SPACES to the defined field length (we do this because our mainframe allows in the input of SPACE as a valid entry (bad edit checks IMHO)). Right now I store the data as NVARCHAR any need/advantage to going to VARCHAR or CHAR??
If I do change them any fear of data loss?
Thanks
February 19, 2003 at 10:18 am
I just changed my DTS PACKAGE to store the data in a new table setting all the NVARCHAR fields to VARCHAR. These tables have the same data in them, indexes and user permissions - they only difference is the NVARCHAR fields are VARCHAR.
Below are the DBCC SHOWCONTIG data for them:
Table with NVARCHAR Fields:
DBCC SHOWCONTIG scanning 'cnsn0000' table...
Table: 'cnsn0000' (1467490094); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 23508
- Extents Scanned..............................: 2969
- Extent Switches..............................: 2968
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.99% [2939:2969]
- Logical Scan Fragmentation ..................: 0.57%
- Extent Scan Fragmentation ...................: 15.29%
- Avg. Bytes Free per Page.....................: 61.4
- Avg. Page Density (full).....................: 99.24%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table with VARCHAR fields:
DBCC SHOWCONTIG scanning 'cnsn0000_test' table...
Table: 'cnsn0000_test' (168009467); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 18471
- Extents Scanned..............................: 2331
- Extent Switches..............................: 2330
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.06% [2309:2331]
- Logical Scan Fragmentation ..................: 1.47%
- Extent Scan Fragmentation ...................: 16.52%
- Avg. Bytes Free per Page.....................: 320.2
- Avg. Page Density (full).....................: 96.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It appears the VARCHAR field table takes up less space but will this equal better performance?
Thanks,
Randy
February 19, 2003 at 11:27 am
quote:
It appears the VARCHAR field table takes up less space but will this equal better performance?
Technically, yes, the VARCHAR will perform marginally better, since the storage requirement is half that of the NVARCHAR table. That means double the records can be scanned in a single read. However, depending on the length of the columns, you may not see that much of a difference in performance. From your first post, the only data loss you could see is if the mainframe text files contain unicode characters in the text fields, in which case you would lose such characters when converting to the ASCII varchar format.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply