Home Forums Database Design Design Ideas and Questions Show data toEnd User Un-Interrupted. RE: Show data toEnd User Un-Interrupted.

  • February 7, 2014 at 5:38 am

    #1686986

    vineet_dubey1975 (2/7/2014)

    I am serving data to the end user(WebPage) via table 'ATABLE'. The data in the table got refreshed after every 1 hr. through a SQL Job (Truncate and refill)

    The issue is while i truncate and refill the table take some time approx.3 min.During that time end user gets page empty data.

    How can i fix this ? Is there any technique / Trick i can limit this time.Either put some type of lock on table or replication etc.

    Please suggest and help.

    Thanks

    vD

    don't truncate; insert into a new table instead; that might take your three minutes of ETL time; then you can drop the original table, and rename the new table to the correct table; that will take something like three or six milliseconds, and you'd be back up and running much faster.

    here's some peudocode to visualize it

    --CREATE THE staging table:

    SELECT *

    INTO ATABLE_Staging

    FROM ATABLE

    WHERE 1=2 --just the schema/no data

    INSERT INTO ATABLE_Staging

    --ETL Process inserts it's data here each hour.

    SELECT ColumnList From OtherSources

    DROP TABLE ATABLE

    EXEC sp_rename 'dbo.ATABLE_Staging','ATABLE'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!