Re: Newbie performance/design question
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/19/05
- Next message: MGFoster: "Re: Newbie performance/design question"
- Previous message: SQL Apprentice: "how to move master,model,msdb to another drive"
- In reply to: larzeb: "Newbie performance/design question"
- Next in thread: MGFoster: "Re: Newbie performance/design question"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 19 Jan 2005 16:37:06 -0600
> My first thought is to create a new field composed of the above,
> concatenated together and separated by perhaps a '|' vertical bar
> character.
>
Yes, don't do this :) Seriously, have you looked at the plan for your
query?
> select *
> from addressvalid
> WHERE HouseNo = '123' AND
> PreDir = 'N' AND
> Streetname = 'Main' AND
> StreetSuffix = 'St'
I don't know that 27 reads is not good. Is it taking a long time? Post some
more information about the plan, and how you plan to use the data. If you
just need to speed up this particular query, a non-unique index on these
columns might help, though it does depend on how many possible matches you
have to this query. Realize that if you have an index with all of the
columns of the table, other than a the primary key, and you issue a query
with *, it will have to figure out the rows and then go hit the table to get
the rows, causing the ever expensive bookmark lookup.
Consider also how you have your index structured. If PreDir is the first
column listed, and it only has a few values, your index might not be all
that useful for lookups. Put the most used/most selective things first.
StreetName would probably be good choices, since there will not be a large
amount of duplication. You may need several indexes to cover common
queries.
Again though, if you need to retrieve more than a couple of rows you are
likely to end up scanning the table anyhow.
Either way, more information is needed about how you will be using stuff.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "larzeb" <larzeb@community.nospam> wrote in message news:mbjtu05jgrp8olgrpinjk95ucd68euac06@4ax.com... >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
- Next message: MGFoster: "Re: Newbie performance/design question"
- Previous message: SQL Apprentice: "how to move master,model,msdb to another drive"
- In reply to: larzeb: "Newbie performance/design question"
- Next in thread: MGFoster: "Re: Newbie performance/design question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
