Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Slow select in one of my tables

Status
Not open for further replies.

bmacbmac

IS-IT--Management
Jan 26, 2006
392
US
Hi. I have an SQL 2000 database. Mytable1 and Mytable2 both have approximatey the same number of records.. roughly 1 million records.

MyTable1 was created and set up by a software that we use.
MyTable2 was created by importing a comma delimited text file.

When I select * from MyTable1 where bookno = '3', the six resulting rows show up in less than a second.

When I select * from MyTable2 where bookno = '3', the six resulting rows show up in about 4 minutes.

I tried running DBCC DBREINDEX (MyTable2, '', 70), which completed in less than a second, but it still didn't help.

Any idea what I can look for as a difference between MyTable1 and MyTable2 so I can replicate and make MyTable2 select statements run faster?
 
Can you post what indexes do you have on both tables and both tables DDL? If you try to list some fields in the second query, will it work faster than select * ?

Also post both execution plans.

PluralSight Learning Library
 
I agree with markros. It's probably an index problem. To list the indexes on the tables, do this (in a query window):

Code:
sp_helpindex 'MyTable1'
sp_helpindex 'MyTable2'



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
thanks Markros. This made me realize the MyTable2 didn't have any indexes. I thought they were created automatically. I created the indexes and it helped a ton!

Thanks!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top