Post reply

Ridiculously STRANGE query - help please?

  • October 2, 2006 at 2:00 pm

    #169959

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

    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.

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

    Thanks!

    - Mike

  • October 2, 2006 at 2:07 pm

    #663711

    run the following and retry the view.

    UPDATE STATISTICS tablename

  • October 2, 2006 at 2:15 pm

    #663714

    Sorry, I should have already said that I had Updated the Statistics on all tables involved in the query in the view.

    I have also dropped, and recreated the empty PH2007 table - JIC, (and re-updated the Stats thereafter...)

    None of that had any affect...

  • October 2, 2006 at 2:23 pm

    #663716

    Try UNION ALL instead UNION.....

  • October 2, 2006 at 2:32 pm

    #663717

    Thanks - I have also tried that as well - with no result.  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.

    But, at any rate - the change had no effect...

    I am stumped...  :

  • October 2, 2006 at 3:42 pm

    #663734

    I posted this (perhaps more appropriately) on the T-SQL forum as well, but wanted to note some of my new findings..

    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 3, 2006 at 6:36 am

    #663811

    You could have a look and check what the collations are on the various char columns, if they are different on 1 table it _might_ make a difference?

     

     

  • October 3, 2006 at 8:20 am

    #663844

    This is not a solution as much as it is a comment on strange behavior.. (SQL, not mine).

    Try scripting the table that was created by your SELECT INTO.. and then DROP and try to recreate the table by using the scripted CREATE TABLE.  I have had it happen to me more than once when I took a "short-cut" and created a table by using the SELECT INTO method.  When I kept having problems I looked at the structure of the table and saw that some of my numeric fields were set to something like Numeric(29,9) . I scripted the table and tried to run it and it failed. Now, that's not the exact size, but just to illustrate the point that SQL was able to CREATE a table by SELECT INTO with a Numeric datatype that I was not able to create using T-SQL.

    Not sure what this has to do with your problem, probably nothing - except to say that there is definately something going on under the hood that can cause some very interesting and sometimes baffeling results.

  • October 3, 2006 at 8:45 am

    #663852

    Thanks all -

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

    I'll post again if I find anything..

    - Mike

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

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