Re: Newbie performance/design question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Robert Harmon (robert_harmonnospam_at_hotmail.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 14:34:05 -0800

If the identity field is neccessary, drop the unique index you
created, and create a unique constraint as it doesn't have the IO
overhead that the unique index does

On Wed, 19 Jan 2005 13:25:21 -0800, larzeb <larzeb@community.nospam>
wrote:

>I have defined a table called Address containing property information.
>There is a identity primary key and some other fields. I do not want
>property duplicates. The following fields uniquely define a property
>in the United State:
>
> [houseNo] [varchar] (10)_AS NULL ,
> [preDir] [char] (2)_AS NULL , --Pre Direction
> [streetName] [varchar] (28)_AS NULL ,
> [streetSuffix] [char] (4)_AS NULL ,
> [postDir] [char] (2)_AS NULL , --Post Direction
> [city] [varchar] (28)_AS NULL ,
> [state] [char] (2)_AS NULL ,
> [zip5] [char] (5)_AS NULL ,
> [zip4] [char] (4)_AS NULL ,
> [sud] [char] (4)_AS NULL , --Unit Designator
> [unitNum] [varchar] (8)_AS NULL --Unit number
>
>and I created a unique non-clustered index over these fields.
>
>After about 250,000 records, loading new addresses takes a long time.
>When I issue the following SQL command in SQL Analyzer:
>
>select *
>from addressvalid
>WHERE HouseNo = '123' AND
> PreDir = 'N' AND
> Streetname = 'Main' AND
> StreetSuffix = 'St'
>
>the SQL:StmtCompleted event did 27 reads.
>
>My first thought is to create a new field composed of the above,
>concatenated together and separated by perhaps a '|' vertical bar
>character.
>
>Any ideas? Thanks, Lars