Post reply

Optimizing query

  • October 31, 2007 at 4:30 am

    #70441

    I need to make this query run quicker. Would anyone have any solutions?

    Thanks in advance...

    UPDATE E

    SET RecordStatus = 'Q'

    FROM RecordHeaderTable E

    WHERE RecordStatus is null

    AND RecordHeaderID IN

    (SELECT TOP (100) RecordHeaderID

    FROM RecordHeaderTable

    WHERE RecordStatus is null

    AND ProductCode NOT IN

    (SELECT ProductCode FROMTblProductCodes)

    AND TYPECODE= 'vinyl'

    ORDER BY RecordHeaderID asc

    UNION

    SELECT TOP (100) RecordHeaderID

    FROM RecordHeaderTable

    WHERE RecordStatus is null

    AND ProductCode NOT IN

    (SELECT ProductCode FROM TblProductCodes)

    AND TYPECODE != 'vinyl'

    ORDER BY RecordHeaderID asc

    Basically when it is busy I only want to let 200 record go through my cashier system when it is busy so when that is the case I want to queue the rest. But I 2 types of records to go in. Also I dont want certain products to be processed.

    thanks again community.

    M

  • October 31, 2007 at 5:40 am

    #747006

    You could give a try on this....

    ; WITHRecordHeaderCTE( RowNum, RecordHeaderID, RecordStatus )

    AS

    (

    SELECTROW_NUMBER() OVER( PARTITION BY TypeNo ORDER BY RecordHeaderID ) AS RowNum,

    RecordHeaderID

    FROM(

    SELECTRecordHeaderID, TYPECODE, ( CASE WHEN TYPECODE = 'vinyl' THEN 1 ELSE 2 END ) AS TypeNo

    FROMRecordHeaderTable

    WHERERecordStatus is null

    AND ProductCode NOT IN( SELECT ProductCode FROM TblProductCodes )

    ) R

    )

    UPDATERecordHeaderCTE

    SETRecordStatus = 'Q'

    WHERERowNum <= 100;

    --Ramesh

  • October 31, 2007 at 6:03 am

    #747016

    Hi Ramesh thanks for the reply.

    But now I am getting the error

    Msg 8159, Level 16, State 1, Line 1

    'RecordHeaderCTE' has fewer columns than were specified in the column list.

    Any suggestions.

    thanks

    M

  • October 31, 2007 at 6:06 am

    #747017

    I think I may have gotten that but why is the R at the end of the statement?

    thanks again?

  • October 31, 2007 at 6:19 am

    #747022

    It's just an alias for the derived table. You could also use S, T, CatsAndDogs or (almost) anything you choose.

    John

  • October 31, 2007 at 10:12 am

    #747155

    ma (10/31/2007)

    Msg 8159, Level 16, State 1, Line 1

    'RecordHeaderCTE' has fewer columns than were specified in the column list.

    I missed one thing....

    ; WITHRecordHeaderCTE( RowNum, RecordHeaderID, RecordStatus )

    AS

    (

    SELECTROW_NUMBER() OVER( PARTITION BY TypeNo ORDER BY RecordHeaderID ) AS RowNum,

    RecordHeaderID, RecordStatus

    FROM(

    SELECTRecordHeaderID, TYPECODE, ( CASE WHEN TYPECODE = 'vinyl' THEN 1 ELSE 2 END ) AS TypeNo

    FROMRecordHeaderTable

    WHERERecordStatus is null

    AND ProductCode NOT IN( SELECT ProductCode FROM TblProductCodes )

    ) R

    )

    UPDATERecordHeaderCTE

    SETRecordStatus = 'Q'

    WHERERowNum <= 100;

    --Ramesh

  • October 31, 2007 at 12:03 pm

    #747201

    Thanks a million Ramesh

    M

  • November 1, 2007 at 1:19 am

    #747331

    Thanks for the appreciation...

    --Ramesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply