Forum Replies Created

Viewing 15 posts - 1 through 15 (of 32 total)

  • Reply To: Create a View with Index

    Thank you for all the inputs - the execution time on this and a number of other queries has been greatly improved.

    It seems that there is not always 1 solution...

    July 15, 2021 at 3:22 pm

    #3910305

  • Reply To: Create a View with Index

    Update - all ctes removed - execution 00:00:02

    SELECT      jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat, 
       ...

    July 14, 2021 at 1:56 pm

    #3907839

  • Reply To: Create a View with Index

    Update - I have rewritten SubQuery2 and SubQuery3 as OUTER APPLY and tested.

    Results set is correct and execution is 00:00:01.

    The removal of the last cte is proving more problematic and...

    July 14, 2021 at 11:24 am

    #3907757

  • Reply To: Create a View with Index

    Update - figured out where to place the code snippet:

    Using the Outer Apply 28,000 records 00:00:01

    Using the SubQuery 28,000 records 00:00:02

    July 14, 2021 at 9:33 am

    #3907726

  • Reply To: Create a View with Index

    From a performance point of view I'm honestly not sure but from a logical point of view this can be simplified to jh.ProdQty > jh.QtyCompleted.

     

    July 14, 2021 at 7:49 am

    #3907687

  • Reply To: Create a View with Index

    Apologies - not sure where to place:

    WITH SubQuery AS
    (
    SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, 
                CAST(CASE WHEN...

    July 13, 2021 at 11:24 pm

    #3907601

  • Reply To: Create a View with Index

    As part of testing I removed the JobHead and JobAsmbl tables from both SubQuery statements but the execution jumped from 00:00:01 to return 28,800 records to 00:00:06.

    The JobOper and JobMtl...

    July 13, 2021 at 8:42 pm

    #3907561

  • Reply To: Create a View with Index

    Not sure where to place this code segment - can you advise.

    I receive and error: Incorrect syntax near the keyword 'ON' having slotted it after SubQuery and part of SubQuery2.

  • This reply was modified 3 years, 4 months ago by  robertopmorris.

July 13, 2021 at 8:32 pm

#3907559

  • Reply To: Create a View with Index

    Execution on the cte is 00:00:00 returning 1662 rows.

    My biggest issue now is building up a data set with multiple aggregate values and accomplishing this without reducing performance.

    I am currently...

    July 13, 2021 at 2:08 pm

    #3907414

  • Reply To: Create a View with Index

    The cte as follows:

    WITH SubQuery1 AS
    (
    SELECT        0 AS ProductLevel, Company, ParentJob AS FinishedJob, ParentJob, AssemblySeq, ProdQty, QtyCompleted, ParentPart, MtlSeq, ReqDate,...

    July 13, 2021 at 2:03 pm

    #3907412

  • Reply To: Create a View with Index

    This is my 1st view where I build up the data:

    SELECT        jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS...

    July 13, 2021 at 2:03 pm

    #3907411

  • Reply To: Create a View with Index

    Gotcha, my understanding of a cte was a recursive cte using UNOIN ALL.

    July 13, 2021 at 1:47 pm

    #3907396

  • Reply To: Create a View with Index

    Apologies - I'm not sure what you mean - there is no cte in the above SQL statement.

    July 13, 2021 at 1:39 pm

    #3907393

  • Reply To: Create a View with Index

    I am still using the cte - but the execution has reduced to 00:00:00 with 1635 records.

    But the cte adopts the OUTER APPLY that you advised earlier.

    July 13, 2021 at 1:34 pm

    #3907390

  • Reply To: Create a View with Index

    The above SQL executes in around 00:00:02 and returns 28,801 records.

    My overall cte report now executes quickly but I'm unsure of how to bring into this SQL Code additional aggregate...

    July 13, 2021 at 1:32 pm

    #3907385

    • Viewing 15 posts - 1 through 15 (of 32 total)