Timouts when searching records in any order besides Index/Primary Key 2

[Rexolio]

I have a table with about 350,000 records. The Index/Primary Key field is called NameID. If I perform a query without specifying order, the results pop right in. If I do a query where I ask it to list the records in any order other than the NameID (i.e. RecordDate, LastName & FirstName, City, etc.) nothing displays and eventually I get a timeout.

I figured this was the best place to come for answers! Thanks in advance!


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[ArtieChoke]

You don't say how you're runnig this query. If it's in query analyzer, check the query timeout option. Are you pulling in all 350k records with this query?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Rexolio]

It happens from Query Analyzer, or from querying directly from the table, or via ASP.


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[ArtieChoke]

I suspect you're running into a server timeout setting. There doesn't appear to be a SQL time-out option for a query (like with a SET statement). If you need to retrieve that order frequently, you might want to add those fields to an index.

Do you know how to do a query execution plan (it might be called something different)? It will tell you where all the time is being spent in the query. Try that (in QA) and see what it tells you. The server might be creating a temp table to do the sorting first - and your tempdb might not be big enough to hold it all.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Rexolio]

In that table, I have the following fields:

NameID
CreateDate
FName
LName
Address
Apt
City
State
Zip
DayPhone
EvePhone
Password
Email
Updates (Yes or No field)
MatchID (this is a combination of FName, LName, St Number & Zip to formulate a matchid so that we can avoid adding duplicates from our online registration)

I hardly ever need to sort by NameID, but that's what ties other tables with information related to these records, so its always been the index. When opening up the tables without querying, the information appears quickly with hardly any delay.

The thing I'm wondering is, you say maybe I should consider creating indexes for some of the other fields I need to query? But the thing is, I query by various things - sometimes by the name, sometimes by the creation date or the updates field, sometimes by the email address, and sometimes a combination. Should I make all of those fields an index?



rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[SQLSister]

Yes you should index the things you query on or have order by clauses on.

 

[Rexolio]

So for each field I might possibly query eventually, I should

a) Make sure it doesn't allow nulls (otherwise I can't create an index on that field)
b) Right click in design view and choose "Indexes/Keys"
c) Under the Index Name field, choose each field/column I wish to add as an index

If this is what I am to do, the only other question I have is that when you add a field to the index, it sets the order by default to ascending. If I may also wish to query those fields in descending order, should I had each field TWICE, one for asc and one for desc?

Sorry - I appreciate your patience in helping me through this.


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

Sorry, but one more question I have... it says that a field cannot allow nulls if you're adding it to the index, but some of these fields MUST contain nulls - for instance not everyone in my database has an email address, but yet I still need to be able to query by that. So what do I do about that? Not add it to the index? Or add something like "none" or whatever to that field to indicate there isn't an email address for that user?


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[SQLSister]

I've created indexes on fields with null values. Where you run into a problem is in creating a unique index because it will only allow one null value.

 

[Rexolio]

When creating an index, why are you able to add more than one column for one index? I created a separate index for the following fields:

LastName
FirstName
Address
City
State
Email
Updates
Password
CreateDate
MatchID

Just trying to understand why you're able to do that, and therefore should I not have created so many?


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[ArtieChoke]

The reason you can have multiple fields in an index is so when you use all the fields in the index, the server can look up the record directly.

So when creating indexes, you want to include most or all of the fields you expect to order by, in your case. So you could create an index on the fields you specified in your post: RecordDate, LastName & FirstName and City.

Try that and see if it works better.

Another thing to do is ask the dba to up the query timeout paramete on the server - although this might impact others if your query hogs all the resources!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[tektipdjango]

LastName yes
FirstName ?
Address No
City yes
State ---> move to another city,state table
Email ?
Updates ?
Password No
CreateDate Yes
MatchID Yes

I think Address is not a good candidate for index as the text in this field will not have a strict content.
Email is useless except if you're using it as a connectionId when the person is entering your website
For password and FirstName is it useful to sort or select on these items (maybe for FirstName to send an email following the saint of the day?)
updates which can match only 2 values is not a candidate for index, except if there is one of the two values which has a very large number of person/the other value. (if 90%yes and 10%No a select of the NOs will be efficient, but if you're around 50-50 the index is of no use

You are enforcing the correct design of a database for the city, state information.
If you really want to select either on state or city, you need to have a separate table with city, state (indexed) and just put in your main table the foreign key (indexed) to this city table.

The design you have is correct only if city and state are used to retrieve the adress for a given person.


[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict