Post reply

Please help! Really STRANGE query behavior!

  • October 2, 2006 at 2:40 pm

    #115870

    All - Thanks in advance for ANY light anyone can shine on this new problem we just encountered.

    (I mistakenly posted this perhaps in the Admin forum - It may be more appropriate here...)

    We have a single view on one database, as follows:

    ALTER VIEW dbo.PayHist_Current_DEBUG

    AS

    SELECT DLN, VendorCode, VendorName, Station + Pat+SP AS PO, TC, MemRef, InvoiceNumber AS invoicenum,

    CONVERT(varchar, VoucherAmount) AS Amt, SubmitSta as SSta, DiscountPct, DiscountDays,

    'Pending' as Pending, ScheduledDate, 'UPV' as State, OverRideMsg

    FROM prod_admin.dbo.unpaidvoucher WITH (NOLOCK)

    UNION

    SELECT DLN, VendorId, Name, PO, RefDocID, TC, InvoiceNum, CONVERT(varchar, Amount) AS Amt,

    SSta, PCT, Days as DiscountDays, PCode, FCP, 'PH2007' as State, OverRideMsg

    FROM prod_admin.dbo.ph2007 WITH (NOLOCK)

    UNION

    SELECT DLN, VendorId, Name, PO, RefDocID, TC, InvoiceNum, CONVERT(varchar, Amount) AS Amt,

    SSta, PCT, Days as DiscountDays, PCode, FCP, 'PH2006' as State, OverRideMsg

    FROM prod_admin.dbo.ph2006 WITH (NOLOCK)

    Now, the query has existed for a year without the middle select on the PH2007 table - and indeed runs quickly without it. However, when the SELECT PH2007 UNION phrase is added, the query runs practically forever. We cancel it before it ever finishes.

    Now the strange part: The PH2007 table is empty - no rows. And it is identical in structure, permissions, etc with the PH2006 table.

    Now the even stranger part: These same tables and same view are also on a development server in a copy database - and the query on the view (with the troublesome table included) runs correctly and quickly.

    By the way - I have already updated Statistics on all tables involved. I have also dropped, recreated and updated stats on the empty PH2007 table.

    And just for the fun of it, I tried UNION ALL instead of UNION just to be grasping at straws, However, as I understand it - the only difference between UNION and the UNION ALL clauses is that the UNION ALL will include multiples/duplicates found between the tables. The UNION statement by itself eliminates duplicates.

    Does anyone have any suggestions as to what might be causing this behavior?

    Thanks!  I'm stumped...

    - Mike

  • October 2, 2006 at 3:07 pm

    #663725

    I'm grasping at straws here too, but can you add one row to PH2007 and see if that works? 

    I wasn't born stupid - I had to study.

  • October 2, 2006 at 3:39 pm

    #663733

    Sorry, tried that one too - with no success.

    However, I have some good news but also a continuation of the Grand Puzzle.

    I had previously dropped and recreated the PH2007 table by scripting out the PH2006 table with all of its Constraints, Indexes, defaults, etc.

    Now, however, I tried dropping and recreating the table via the following:

    drop table ph2007

    select * into ph2007

    from ph2006 where 1 != 1

    then I manually added all of the constraints, indexes, etc, etc by executing each of the commands from the first script I had created.

    And - guess what - I works!

    Uhhmmm.... but the question now is - WHY?

    The tables are still identical (best I can tell anyway) and the only difference now is how they were created.  Originally as a Drop->Create new which would result in a query in the view running forever.  Now as a simple Drop->Select Into.  Which is gloriously successful with the same select view.

    Is there some deep sacred knowledge what anyone can impart that might shed some light on this puzzle?

    Thanks!

    - Mike

  • October 2, 2006 at 7:51 pm

    #663753

    I ran into something similar... if you drop and recreate a table, the views associated with that table must be recreated, as well.  Are you sure you didn't recreate the view somewhere along the line?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2006 at 2:01 am

    #663784

    Is the development server single processor and production multi? I've had problems with parallel execution plans in the past. You can set the maximum degree of parallelism to 1 with sp_configure but that's server-wide : not a good thing on a production server! I don't how or if you can do it for a single query. If this is the issue, you may need to rewrite the query.

  • October 3, 2006 at 3:25 am

    #663794

    To set parallelism to 1 for a single query, use the option command

    silly example:

    select * from syscolumns a

    union

    select * from syscolumns b option (maxdop 1)

    in a union it need to go on the last query. see BOL (lookup "OPTION Clause") for more.

    HTH

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • October 3, 2006 at 8:44 am

    #663851

    Thanks all -

    I'll look a little closer at some of your suggestions.

    I'll post again if I find anything..

    - Mike

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

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