Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show data toEnd User Un-Interrupted. Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 5:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 470, Visits: 414
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
Post #1539103
Posted Friday, February 7, 2014 5:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 12,910, Visits: 32,026
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539109
Posted Friday, February 7, 2014 6:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 470, Visits: 414
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.
Post #1539141
Posted Friday, February 7, 2014 11:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1539320
Posted Monday, February 10, 2014 7:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 470, Visits: 414
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.
Post #1539775
Posted Monday, February 10, 2014 7:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
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
Post #1540011
Posted Monday, February 10, 2014 7:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:03 PM
Points: 2,967, Visits: 2,569
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.


Post #1540015
Posted Tuesday, February 11, 2014 3:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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_table

only 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
Post #1540466
Posted Monday, March 24, 2014 11:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.

Post #1554159
Posted Monday, March 24, 2014 11:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
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_table

only 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse