1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Query

Discussion in 'HTML, Graphics & Programming' started by sfx, 27 Jan 2006.

  1. sfx

    Hitman

    Joined: 13 Dec 2004

    Posts: 926

    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
     
  2. happytechie

    Soldato

    Joined: 18 Oct 2002

    Posts: 5,601

    Location: Surrey

    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
     
  3. sfx

    Hitman

    Joined: 13 Dec 2004

    Posts: 926

    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
     
  4. happytechie

    Soldato

    Joined: 18 Oct 2002

    Posts: 5,601

    Location: Surrey

    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
     
  5. Haircut

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,926

    Location: SW London

    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.
     
  6. sfx

    Hitman

    Joined: 13 Dec 2004

    Posts: 926

    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