[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

My joins seem too slow!

Posted on 2005-05-17
8
Medium Priority
?
161 Views
Last Modified: 2010-05-18
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.


0
Comment
Question by:JAMES
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Assisted Solution

by:Jeff Certain
Jeff Certain earned 1000 total points
ID: 14021227
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
 

Author Comment

by:JAMES
ID: 14021258
Have already tried moving the ON to the WHERE clause but it didnt make any different.

My indexes are as suggested...
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 14021489
Are you using clustered indexes?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JAMES
ID: 14021518
Each table has a clustered index but not necessarily being used here.  Does the text plan (above) tell us?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 14021529
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
 

Author Comment

by:JAMES
ID: 14021606
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14022025
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question