Show data toEnd User Un-Interrupted.

Question

Show data toEnd User Un-Interrupted.

vineet_dubey1975

SSCrazy

Points: 2306
More actions
February 7, 2014 at 5:09 am

#282251

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

I initially thought of this solution but on production database , SQL Server won't allow me to drop the Origanal table as this might be accessed by some user at that point.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

vineet_dubey1975 (2/7/2014)
I initially thought of this solution but on production database , SQL Server won't allow me to drop the Origanal table as this might be accessed by some user at that point.

So put the drop in a loop, with the rename in the same transaction.

I've used the drop and rename technique for whole databases, as well as for individual tables, in active production systems, and never had a problem.

Tom

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 4

drop in a loop makes the table inaccessible to external user? Can you share some e.g. how we can apply the loop that should i iterate through.

Thanks for the help i am getting.

TomThomson SSC Guru Points: 104773 More actions · Answer 5

vineet_dubey1975 (2/10/2014)
drop in a loop makes the table inaccessible to external user? Can you share some e.g. how we can apply the loop that should i iterate through.
Thanks for the help i am getting.

If users have connections that are created and dropped, there's a good chance of finding a time when no-one is connected. If some app creates a connection and keeps it open for ever, you will never be able to drop the DB and will need either to fix the app to behave better or find some different technique.

Assuming no app needs fixing, or that you fix it if it does, the loop should look something like:-

-- pseudocode

finished = false

while not finished

begin

drop db

if drop succeded

begin

rename new db

if rename succeded set finished true

else raise nasty error

end

and you need to decide whether the risk of rename failing is low enough that you don't need to handle that nasty error.

Tom

happycat59 One Orange Chip Points: 29389 More actions · Answer 6

Rather than dropping/renaming tables, how about using the ALTER TABLE...SWITCH command. It will not leave you with any period when the table doesn't exist (SQL manages that for you) and it is one command so there is less chance of something going wrong.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

happycat59 (2/10/2014)
Rather than dropping/renaming tables, how about using the ALTER TABLE...SWITCH command. It will not leave you with any period when the table doesn't exist (SQL manages that for you) and it is one command so there is less chance of something going wrong.

This will only work if you truncate the table or delete all its rows before switching the new table into it, since ALTER TABLE new_table TO old_tableonly works if old_table is empty. So it isn't a single command instead of two, it is still two commands.

Another way (slightly more complicated, because you eventually need a drop statement anyway to tidy up) is to use two rename statements instead of a drop and a rename - this means you can keep the old contents until you are sure you won't need them, and rename back again if you do need them.

Tom

aaron.reese SSChampion Points: 13498 More actions · Answer 8

you could look at using the Optimise for first N rows hint. This will change the query plan so that the query can start returning data earlier, even though the whole query may take longer.

You could also look at query tuning and indexes to see if you are improve the response speed of the query.

How about not truncating, but inserting and updating: If only 3% of your data has changed, why rebuild it all

Seeing as you are populating the table using a stored procedure rather than a view,

You could always write the data to a temporary table and then truncate and insert to the target table. This is generally quite quick on the insert side.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

TomThomson (2/11/2014)
happycat59 (2/10/2014)
Rather than dropping/renaming tables, how about using the ALTER TABLE...SWITCH command. It will not leave you with any period when the table doesn't exist (SQL manages that for you) and it is one command so there is less chance of something going wrong.
This will only work if you truncate the table or delete all its rows before switching the new table into it, since ALTER TABLE new_table TO old_tableonly works if old_table is empty. So it isn't a single command instead of two, it is still two commands.
Another way (slightly more complicated, because you eventually need a drop statement anyway to tidy up) is to use two rename statements instead of a drop and a rename - this means you can keep the old contents until you are sure you won't need them, and rename back again if you do need them.

Another way to do it is to have two similarly named tables and a synonym. Simply repoint the synonym to the new data. Next iteration, just reverse the name.

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