Post reply

Selecting from temp table outside SP?

  • January 8, 2009 at 6:11 am

    #127870

    Hi

    I have a SP that has been running for 408 hours, computing Citrix statistics. The SP writes to a temporary table called #tmpResults, and I would like to know if it's possible to SELECT from this table while the SP is still running?

    In the tempdb database, theres a table named

    #tmpResults____..._______0001000006C8

    Doing

    SELECT * FROM [tempdb].[dbo].[#tmpResults____..._______0001000006C8]

    gives me the error "Database name 'tempdb' ignored, referencing object in tempdb."

    How can I do this?

    Thanks in advance.

  • January 8, 2009 at 12:26 pm

    #923627

    #table is only visible to the SESSION

    ##table is global temp. table, visible to all, until the last session/user disconnects I believe

    So replace #table with ##table, you should be able to SELECT from it while SP is running, and hopefully before it disappears

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • January 9, 2009 at 12:46 am

    #923946

    That makes sense.

    Guess I have to run the SP again. Too bad 🙂

    Thanks for you answer.

  • January 9, 2009 at 1:00 am

    #923952

    If you still have the session from where you executed the SP then fire the select query from that session.

    -Vikas Bindra

  • January 9, 2009 at 6:03 am

    #924072

    thj (1/8/2009)

    Hi

    I have a SP that has been running for 408 hours, computing Citrix statistics. The SP writes to a temporary table called #tmpResults, and I would like to know if it's possible to SELECT from this table while the SP is still running?

    In the tempdb database, theres a table named

    #tmpResults____..._______0001000006C8

    Doing

    SELECT * FROM [tempdb].[dbo].[#tmpResults____..._______0001000006C8]

    gives me the error "Database name 'tempdb' ignored, referencing object in tempdb."

    How can I do this?

    Thanks in advance.

    Holy Moly! 408 hours? That's 17 days! It is possible for some blocking to last forever. You may want to check for blocking before you stop the run.

    If you have to stop the code, take an hour or two and add some logging that feeds a permanent table that you can easily query over time.

    Of course, you know the other recommendation, but you probably don't have the time for it right now... you're probably in a crunch to get year end reporting and the like done.

    --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)

  • January 9, 2009 at 8:42 am

    #924254

    Hi Jeff

    Yea - 17 days and you're correct, it is for a year report and I don't have time to stop it and then run it again 🙂

    How do I check for blocking?

  • January 9, 2009 at 9:49 am

    #924338

    sp_who and sp_who2 show blocks.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 9, 2009 at 9:59 am

    #924352

    Jack is correct.

    Switching gears a bit... If blocking is present and you don't want to kill the job, you may have to get people to understand that you need to kill their jobs until this bad boy is done.

    Also, appoligies for stating the obvious... but I need to say it out loud... someone needs to spend part of this next year rewritting this code so you don't have the same problem next year... of course, you could build a temporary server just for this code to run on. 🙂

    Please don't get mad... just having a little fun with this. We've all been where you're at right now and there's not much that can be done if the job must continue.

    --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)

  • January 9, 2009 at 10:35 am

    #924389

    If you really want to look at the contents you could try backing up tempdb to a file and restoring it to another database name. Maybe then you could perform a query for the objects in existance and write queries agains those really 'weird' table names.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 13, 2009 at 6:51 am

    #925825

    Thanks for all the answers. Luckily, the SP finished last sunday so I don't have the problem anymore. It only ran for ~470 hours 🙂

  • January 13, 2009 at 8:40 pm

    #926353

    So! Did it work? Enquiring minds want to know! 😛

    Holy moly... you are a very patient human... 470 hours... 19.58 days. And to think I get ticked if something takes longer than 19.58 seconds. 😛

    And, the real key here is... does it save it's own output? :hehe:

    --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)

  • January 14, 2009 at 12:18 am

    #926397

    Jeff Moden (1/13/2009)

    So! Did it work? Enquiring minds want to know! 😛

    Holy moly... you are a very patient human... 470 hours... 19.58 days. And to think I get ticked if something takes longer than 19.58 seconds. 😛

    And, the real key here is... does it save it's own output? :hehe:

    I guess you should issue this:

    exec sp_configure 'patient human timeout', 1692000

    reconfigure

    :w00t:

    Anyways, this process needs to be checked with regards to performance !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 14, 2009 at 1:52 am

    #926414

    Haha guys 🙂

    I had lots of other work that I could do while the job was running, so it was no problem really. But when the running time got over 400 hours, I got nervous if there was something wrong. But everything seemed to be good, and it was. The job produced the correct results - 4380 rows, 12 for each customer each day in 2008.

    If I run the SP for a single day (the 470 hours job was for 365 days), it takes 10 minutes. So it does not really make any sense why it takes 470 hours to do it for 365 days? Should it not be (365*10)/60 ~= 61 hours, or lets say 100 hours, and NOT 470?

    Do you have any idea why this is? Are you interested in seeing the SP?

    I should say, that the SP was running on a SQL 2000 server, on a VIRTUAL machine. I am well aware of that this is not an optimal (far from) system for a SQL server. Running it on a physical SQL 2005/8 server, it takes 2 minutes for a single day (10 min. on the virtuel SQL 2000).

  • January 15, 2009 at 6:14 am

    #927213

    thj (1/14/2009)

    Haha guys 🙂

    I had lots of other work that I could do while the job was running, so it was no problem really. But when the running time got over 400 hours, I got nervous if there was something wrong. But everything seemed to be good, and it was. The job produced the correct results - 4380 rows, 12 for each customer each day in 2008.

    If I run the SP for a single day (the 470 hours job was for 365 days), it takes 10 minutes. So it does not really make any sense why it takes 470 hours to do it for 365 days? Should it not be (365*10)/60 ~= 61 hours, or lets say 100 hours, and NOT 470?

    Do you have any idea why this is? Are you interested in seeing the SP?

    I should say, that the SP was running on a SQL 2000 server, on a VIRTUAL machine. I am well aware of that this is not an optimal (far from) system for a SQL server. Running it on a physical SQL 2005/8 server, it takes 2 minutes for a single day (10 min. on the virtuel SQL 2000).

    470 hours for 4380 rows, that's about 6.5 minutes for one row!

    Yes, please post the sp, I have a small hunch there's something we can do to help you out a little.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • January 15, 2009 at 3:07 pm

    #927753

    Yes,

    DO post the infamous stored proc.

    I remember way back when I began programming. How to find the determinant of a matrix. In this example, the guy tried it on a 3 x 3 matrix and everything was just fine. He then put in the actual 40 x 40 matrix he wanted to solve. This being on a rented mainframe, after 3 days he gave up and had the job killed.

    After using a different algorithm, things got decent again, even on large matrices.

    In your case, know that triangular joins do not scale in a decent manner. You might have some thing like this. Jeff Moden did describe the issue somewhere in this forum. You should also watch out for ReBAR.

    My view on 470 hour run is that you are either asking the wrong question or going the wrong way about it. How many zillion CPU cycles does that represent ? And can you really bank on on everything going well everytime during such an era (in terms of cpu) .

    I am definitely interested in what might be a success story if this pans out. When generating the Universal Product Code for 200,000 products, requiring that the parity digit be recalculated for each I went from:

    1. looping within the application code: 6 hours

    2. looping in a stored proc: 35 minutes

    3. using a tally table and inserting all 200,000 records in one fell swoop and

    ...computing the check digit with a single massive update: 43 seconds...

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

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