Results 1 to 4 of 4

Thread: Too Many Joins?

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: Too Many Joins?

    Microsoft SQL Server 2000:

    I am not a SQL programmer, but I am trying to use data collected in our Job Costing system to feed data to a crystal report that summarizes the current department name and the last date each job was scanned into a department.
    I used aliases of the Process table to extract the max date for each department.

    This statement works fine, but sometimes it hangs and locks my process table. I am not sure what event causes the lock, but I think it has to do with the users aborting the report during the SQL extract or multiple users trying to report at the same time (8am when they first arrive at work).

    I showed this to a consultant and he showed me that the MAX statement in the 3rd to last join was eating most of the execution time. Also he told me I am using too many joins and should look into Table Views. The statement completes in 15 to 45 seconds, depending on the workload.

    What can I do to improve the performance of this code and to avoid the locks?

    SELECT
    OH.JobNumber,
    OH.PlantID,
    OH.CreateOpr,
    OC.ComponentNumber,
    PJN.ProductionCode as ProductionMax,
    P2.ProcessCode as MaxCC,
    PS.Description AS MaxCCDesc,
    PJ1.ProductionCode,
    P.ProcessCode as ProdCC,
    P51.CreateDatim AS SchCCDate,
    P53.CreateDatim AS TypCCDate,
    P55.CreateDatim AS OPrCCDate,
    P57.CreateDatim AS HPrCCDate,
    P59.CreateDatim AS CRmCCDate,
    P61.CreateDatim AS CCeCCDate,
    P63.CreateDatim AS PRmCCDate,
    P65.CreateDatim AS BinCCDate,
    P67.CreateDatim AS JbOCCDate,
    P69.CreateDatim AS OnDCCDate,
    P71.CreateDatim AS ShpCCDate,
    PS1.Description AS CCDesc,
    P.CreateDatim AS CCDate,
    OQT.Quantity,
    OH.JobDescription,
    OH.FormNumber,
    OH.JobDescription,
    OH.USERDEFINED1 AS JobType,
    OH.CustAccount,
    OH.CustName,
    (select OrderHeader.DueDate from OrderHeader where OrderHeader.JobNumber = OH.JobNumber and OH.NoDueDate = 0) as DueDate,
    OH.ProofDate,
    OH.OrderDate,
    OH.SalesRepCode,
    OH.PONumber,
    OH.PrevPONumber,
    OH.NoDueDate,
    OC.UserDefined1,
    OC.Description as ComponentDescription
    FROM OrderComponent OC
    INNER JOIN OrderHeader OH ON OC.JobNumber = OH.JobNumber
    INNER JOIN OrderQtyTable OQT ON OC.JobNumber = OQT.JobNumber
    and OC.ComponentNumber = OQT.ComponentNumber
    and OC.QtyOrdIndex = OQT.QuantityLineNo
    LEFT JOIN ProductionJobNumber PJ1 ON PJ1.JobNumber = OH.JobNumber and PJ1.ComponentNumber = OC.ComponentNumber
    LEFT JOIN Production P ON PJ1.ProductionCode = P.Code
    LEFT JOIN Production P51 ON PJ1.ProductionCode = P51.Code AND P51.ProcessCode = 9151
    LEFT JOIN Production P53 ON PJ1.ProductionCode = P53.Code AND P53.ProcessCode = 9153
    LEFT JOIN Production P55 ON PJ1.ProductionCode = P55.Code AND P55.ProcessCode = 9155
    LEFT JOIN Production P57 ON PJ1.ProductionCode = P57.Code AND P57.ProcessCode = 9157
    LEFT JOIN Production P59 ON PJ1.ProductionCode = P59.Code AND P59.ProcessCode = 9159
    LEFT JOIN Production P61 ON PJ1.ProductionCode = P61.Code AND P61.ProcessCode = 9161
    LEFT JOIN Production P63 ON PJ1.ProductionCode = P63.Code AND P63.ProcessCode = 9163
    LEFT JOIN Production P65 ON PJ1.ProductionCode = P65.Code AND P65.ProcessCode = 9165
    LEFT JOIN Production P67 ON PJ1.ProductionCode = P67.Code AND P67.ProcessCode = 9167
    LEFT JOIN Production P69 ON PJ1.ProductionCode = P69.Code AND P69.ProcessCode = 9169
    LEFT JOIN Production P71 ON PJ1.ProductionCode = P71.Code AND P71.ProcessCode = 9171
    LEFT JOIN Process PS1 ON P.ProcessCode = PS1.ProcessCode
    LEFT JOIN ProductionJobNumber PJN ON PJN.ProductionCode =
    (select MAX(ProductionJobNumber.ProductionCode)
    From ProductionJobNumber
    where OH.Jobnumber = ProductionJobNumber.JobNumber
    AND OC.ComponentNumber = ProductionJobNumber.ComponentNumber)
    LEFT JOIN Production P2 ON PJN.ProductionCode = P2.Code
    LEFT JOIN Process PS ON P2.ProcessCode = PS.ProcessCode
    WHERE OH.JobStatus = 'IN PROCESS'

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd have to know at least a kazillion (thats a million, million gadzillions) more things about your database/server/clients/etc to give you an iron-clad answer. There are just too many variables for a "one size fits all" answer.

    SQL Server can tolerate a lot of joins, and it does them pretty well in most cases. A view just "encapsulates" the joins, it doesn't actually get rid of them. I don't think you can improve much here, unless you can completely remove a table from the query

    I'd run the query, using the Show Execution Plan option. Look at the output, especially the "fat" lines. Those are often the culprits in this kind of problem.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Each SELECT places a shared lock on a table that is in the FROM list. Crystal is using default (possibly configurable) query timeout value (I think it's 60 seconds, but may be wrong). Once your SELECT exceeds this value, - the client gets Timeout-ed, but Crystal may fail to notify the server with dbcancel-type flag that the result is no longer needed. So the server looks for invalid pipe (file) to write to, while still holding the shared locks. At this point other users are trying to run the same report and get timed out for the same reason. The problem continues until there is enough time for the server to stop looking for/writing to non-existing client and the locks to be released.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Get that subquery out of your SELECT list. And what are you trying to do with it?:

    (select OrderHeader.DueDate from OrderHeader where OrderHeader.JobNumber = OH.JobNumber and OH.NoDueDate = 0) as DueDate

    You already have OrderHeader aliased as OH, so it appears that you are just saying you want a null value if NoDueDate <> 0. You don't need a subquery for that.

    This could probably stand a rewrite too:
    LEFT JOIN ProductionJobNumber PJN ON PJN.ProductionCode =
    (select MAX(ProductionJobNumber.ProductionCode)
    From ProductionJobNumber
    where OH.Jobnumber = ProductionJobNumber.JobNumber
    AND OC.ComponentNumber = ProductionJobNumber.ComponentNumber)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •