Optimize this Sql query?

Ricky White
Ricky White used Ask the Experts™
We are usng Sql Server 2000.
I have 2 tables A and B each having about 100,000 records in it.

I am running the following query but it takes a minute or so to run and as a result the user has to wait for the data table to be populated on the screen everytime an account has to be searched.

Is there a way to optimize this query to reduce the response time?
SELECT A.col1, A.col2, A.col3, B.col4
FROM A inner JOIN B ON A.AccountNumber = B.AccountNumber
where A.col1 = 'something' or A.col2 = 'something' or A.col3 = 'something'


As you can see, Table B is used only in select and not in where.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
do you have indexes on a.accountnumber and b.accountnumber?

also indexes  on a.col1 and a.col2 would be helpful.
Add index on (AccountNum,Col4) on table B.

As for table A it's more complicated. Most likely you can end up with the index on (AccountNumber, Col1, Col2, Col3) although it depends on the data.

I also assume that Col* columns are relatively small and it makes sense to add them to covering index.
Most Valuable Expert 2011
Top Expert 2012

is accountnumber unique in B ?

will every row in A have a row in B or is the inner join acting as a filter?


Yes every row in A will have a row in B.

The account number in B is not unique.

i think there is only one way might solve your issue.

SELECT A.col1, A.col2, A.col3,A.AccountNumber  into temptable
where A.col1 = 'something' or A.col2 = 'something' or A.col3 = 'something'

create clustered index index1 on temptable (AccountNumber)

SELECT t.col1, t.col2, t.col3, t.col4
FROM temptable t inner JOIN B ON t.AccountNumber = B.AccountNumber


Thanks all!


Sorry, I meant to mark DWKor's answer as the best answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial