SQL Query

sfx · 27 Jan 2006 at 11:00

Hi All,

I need some help speeding up this SQL Query. The problem is with this join

Code:

LEFT JOIN
	part P1 ON J1.partnumber = P1.partnumber

The Query only returns 7 records as that is all there is but the part table is quite large and takes some time.

Here is the full Query

Code:

SELECT
	C1.apptdate, 
	C1.callnumber,
	C1.custtitle,
	C1.custsurname, 
	C1.custaddress1,
	C1.custaddress4,
	C1.custpostcode,
	P1.partdescription,
	J1.partnumber, 
	J1.quantity 
FROM
	call C1
LEFT JOIN
	jobpart J1 ON C1.callsurrogate = J1.callsurrogate 
LEFT JOIN
	part P1 ON J1.partnumber = P1.partnumber
WHERE
	C1.apptdate BETWEEN '20010101' AND '20060101'
AND
	C1.callsurrogate 
IN
	(
	SELECT 
		J1.callsurrogate 
	FROM 
		jobpart J1
	)
ORDER BY
	C1.apptdate asc,
	C1.callnumber asc
;\g

Any help will be great.

Cheers,

sfx

happytechie · 27 Jan 2006 at 11:18

are you sure that the problem is with the join and not the statement:

IN
(
SELECT
J1.callsurrogate
FROM
jobpart J1
)

sub queries like this are always slow.

HT

sfx · 27 Jan 2006 at 11:32

happytechie said:
are you sure that the problem is with the join and not the statement:

IN
(
SELECT
J1.callsurrogate
FROM
jobpart J1
)

sub queries like this are always slow.

HT

Well to be honest I am not 100% sure where it is coming from. But until I added the JOIN to get the partdescription from part it ran ok.

Any suggestions?

Thanks,

sfx

happytechie · 27 Jan 2006 at 12:10

If you type the query in query analyser there is a show execution plan option under the query window. This will show you where abouts the time is being taken.

HT

Haircut · 27 Jan 2006 at 19:56

sfx said:

Code:

SELECT
	C1.apptdate, 
	C1.callnumber,
	C1.custtitle,
	C1.custsurname, 
	C1.custaddress1,
	C1.custaddress4,
	C1.custpostcode,
	P1.partdescription,
	J1.partnumber, 
	J1.quantity 
FROM
	call C1
LEFT JOIN
	jobpart J1 ON C1.callsurrogate = J1.callsurrogate 
LEFT JOIN
	part P1 ON J1.partnumber = P1.partnumber
WHERE
	C1.apptdate BETWEEN '20010101' AND '20060101'
AND
	C1.callsurrogate 
IN
	(
	SELECT 
		J1.callsurrogate 
	FROM 
		jobpart J1
	)
ORDER BY
	C1.apptdate asc,
	C1.callnumber asc
;\g

Cheers,

sfx

What's the point of having the subquery?

If you are joining on callsurrogate between jobpart and call then surely the subquery is redundant?

It could be slow because you are filtering on the date condition. How many rows are in the call table? Is there an index on the apptdate field?
Also, if the part table is large then make sure there is an index on partnumber - really I would make sure there are indexes on all the fields in the join conditions.

sfx · 28 Jan 2006 at 10:41

Cheers for the help guys. You are right I did not need the sub query. The problem was with getting the part description from the part table. partdescription is not indexed so it took about 17secs to get the part description. On jobpart and part there is a suppliercode column which is indexed. I used that instead and it now takes 10ms.

Cheers,

sfx