40K rows, slow on lookups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

40K rows, slow on lookups

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-18 : 12:17:08

Hi,

I have a 40 000 row table that is very slow.

It has a primary key Identity column, identity auto increment.
It also has a integer column (parentid) that I do lookups in my sql qeuries like:

select *
from mytable
where parentid = 4

parentid is a integer column, it contains values from the PK identity column.

ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-18 : 12:22:08

Is there in index on the parentID column?

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-18 : 12:22:10

create an index on parentid column ?
1-2-

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-18 : 12:23:40

put a clustered index on id and parentid. that'll probably speed things up.
you have to have both column indexed. best if you play with different index settings to see which is best.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-18 : 12:24:05

i'll add another

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-18 : 12:34:33

wow! 2 seconds! that has to be a record!

- Jeff

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-18 : 12:37:17

>>put a clustered index on id and parentid

If querying based on parentid, this proposed clustered index won't be used if id is the 1st column in it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-18 : 12:44:18

true. my bad. maybe non clustered would be a better choice

Go with the flow & have fun! Else fight the flow

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-18 : 12:54:56

Clustered or non isn't the point - point is column order in the index, and a proposed index starting with 'id' will never be used for the query given at the start of this thread.

I'd cluster on 'id' and non-cluster on 'parentid'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources