My joins seem too slow!

Hi,

I am plodding through our sql statements to try and optimise them but I worry im chasing my tail a bit.  The following is a simple example I would like a sanity check on :-

select * from BOOKS
INNER JOIN ticketheaders ON
TICKETHEADERS.ticketnumber = BOOKS.ticketnumber
INNER JOIN results on
results.ticketnumber = books.ticketnumber and
results.userid = books.userid and
results.bookname = books.bookname
where BOOKS.userid = 'NEIL' and BOOKS.bookname = 'all desks'

Books and Results have approx 258,000 records and TicketHeaders has approx 57,000.

The result set should consists of all the ticketheaders so it should be around the 57,000 mark.

When I run the query it takes approx. 7 seconds to execute (which I think is too long - RAID 5 with 5 drives, 2gb RAM, dial xeon procs) with the following text plan :-

  |--Merge Join(Inner Join, MERGE:([Results].[TicketNumber])=([TicketHeaders].[TicketNumber]), RESIDUAL:([TicketHeaders].[TicketNumber]=[Results].[TicketNumber]))
       |--Merge Join(Inner Join, MERGE:([Books].[TicketNumber])=([Results].[TicketNumber]), RESIDUAL:([Books].[TicketNumber]=[Results].[TicketNumber]))
       |    |--Clustered Index Seek(OBJECT:([Reality].[dbo].[Books].[PK_Books]), SEEK:([Books].[UserID]='NEIL' AND [Books].[BookName]='all desks') ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([Reality].[dbo].[Results].[PK_Results]), SEEK:([Results].[UserID]='NEIL' AND [Results].[BookName]='all desks') ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([Reality].[dbo].[TicketHeaders].[PK_TicketHeaders]), ORDERED FORWARD)

I just want some confirmation on where you would put the indexes and what you would expect the plan to show (ie. seek instead of scan etc).  I really thought all my stuff was optimised pretty well but I just want it verified on this simple example.

Many thanks.

James.


JAMESAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The query plan looks good.  

About the only thing I would suggest is changing the "SELECT *" to list specific columns, including, of course, the table prefix on each column.  That's especially true since some columns will be duplicates anyway (all the join columns on the row must match, so the values will always be duplicates of each other).
0
 
Jeff CertainConnect With a Mentor Commented:
As a rule, every field that is being joined should be indexed. If you're performing composite joins, you'll want a composite index to match.

As a separate issue, what about filtering results.bookname as well, instead of joining that field?
select * from BOOKS
INNER JOIN ticketheaders ON
TICKETHEADERS.ticketnumber = BOOKS.ticketnumber
INNER JOIN results on
results.ticketnumber = books.ticketnumber and
results.userid = books.userid and
where BOOKS.userid = 'NEIL' and BOOKS.bookname = 'all desks' and results.bookname ='all desks'
0
 
JAMESAuthor Commented:
Have already tried moving the ON to the WHERE clause but it didnt make any different.

My indexes are as suggested...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jeff CertainCommented:
Are you using clustered indexes?
0
 
JAMESAuthor Commented:
Each table has a clustered index but not necessarily being used here.  Does the text plan (above) tell us?
0
 
JAMESAuthor Commented:
Thanks Scott but one question - why did you repost the query?  I have been picking it apart incase you have changed something!

Should I be content it does a table scan on the ticketheaders table and not a seek?

0
 
Scott PletcherSenior DBACommented:
D'OH ... actually I didn't mean to re-post it, I had just copied it there initially in case I was going to make more specific suggestions ... I'll remove the code.


>> Should I be content it does a table scan on the ticketheaders table and not a seek? <<

I think so, since you're listing the whole table, you really don't have a choice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.