Post reply

Application selects nothing from a table

  • January 4, 2013 at 2:43 pm

    #268912

    Here is a problem.

    We have a table which is getting truncated and loaded anew every 10 min by a scheduled job. The table is of medium size, from 20 to 50 k records.

    There is also an application which periodically selects data from that table. If it happen to select data exactly at the moment when our table is already truncated but not populated yet, the application returns nothing. Which is obvious.

    I am tasked to eliminate such cases. I enabled database for snapshot and set snapshot isolation level in a stored procedure body. However it did not seem it helped a lot. What other options I can do ? Currently I am thinking about replacing truncate with delete. Will it help ? Any other ideas ?

    Thanks

  • January 4, 2013 at 5:30 pm

    #1573997

    SQL Guy 1 (1/4/2013)

    Here is a problem.

    We have a table which is getting truncated and loaded anew every 10 min by a scheduled job. The table is of medium size, from 20 to 50 k records.

    There is also an application which periodically selects data from that table. If it happen to select data exactly at the moment when our table is already truncated but not populated yet, the application returns nothing. Which is obvious.

    I am tasked to eliminate such cases. I enabled database for snapshot and set snapshot isolation level in a stored procedure body. However it did not seem it helped a lot. What other options I can do ? Currently I am thinking about replacing truncate with delete. Will it help ? Any other ideas ?

    Thanks

    So you need to display the old data untill the new data completely loaded ? Is that correct ?

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • January 4, 2013 at 8:53 pm

    #1574014

    Load the data into a different table. Drop the current table, then rename this secondary table to what the dropped table was (the one you are currently truncating etc).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 4, 2013 at 10:04 pm

    #1574017

    How about doing both tasks truncate and loading in a single transaction and commit at the end ? Does this makes any difference?

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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