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 12»»

SQL Server High Avilabilty Solutions Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 2:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:00 AM
Points: 51, Visits: 544
Hi,

We have financial database size of around 400Gb and we do daily batch updates which takes nearly 30mint to 45mints.During this time one of very important Price table is locked half of this upload process becuase it has to populate calculated columns.

Our management asking me that we need our database which should be used on 24/7 and 365 days but aslo we have to get into daily updates? How can be possible?Users should not be blocked any point in time and aslo they should not getting dirty reads.

Can some one please suggest me best way of achieving this problem.

Thanks
Nick



Post #1038133
Posted Wednesday, December 22, 2010 5:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 4:14 AM
Points: 563, Visits: 971
What is happening that the Price table is locked for so long? Price updates? Where do the changes come from?

I would consider a 2 table approach... 1 online table (current) and 1 offline table (can have updates and changes made). You will need some sort of control flag (additional table, perhaps) that tells which pricing table to use.

IF flag = 1
EXEC proc1
ELSE IF flag = 2
EXEC proc2

Something like that....
Post #1038184
Posted Wednesday, December 22, 2010 5:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:00 AM
Points: 51, Visits: 544
Thanks for your advise.

On price table it does :

if Price Date exists with different value then moves data from Main table to archieve table
then Updates with new values on Main table
then insert complete new values on Main table.

I source of data comes from differnt SQL database.

In your solution when do you sync both tables becuase our updates are happens on daily basis and which required lot of changes in application level.

Thanks
Nick

Thanks
Post #1038191
Posted Wednesday, December 22, 2010 5:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 4:14 AM
Points: 563, Visits: 971
It depends on the process, but, if the update is done once a day, then on day 1 you update table 1 while table 2 is still in use. You then switch over to use table 1 and can then update table 2, that way both tables should always be the same.

There are other options with regards to "trickle feed" of the data from the other SQL DB, etc.
Post #1038194
Posted Wednesday, December 22, 2010 8:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 7:00 AM
Points: 51, Visits: 544
Can you please let me know what are other solutions?

What is the trickle feed?

Thanks
Nick
Post #1038309
Posted Wednesday, December 29, 2010 2:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 24, 2014 11:30 AM
Points: 44, Visits: 387
Nick,

Delete unchanged records from the staging table prior to the mass update. This removes the "has price changed" logic from the Product table update.

Dan
Post #1040594
Posted Wednesday, December 29, 2010 2:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
Have you considered one of the snapshot isolation levels?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1040598
Posted Monday, January 03, 2011 3:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:11 AM
Points: 206, Visits: 756
The usual easy way to fix this problem is to upsert your data in small batches so the lock time are tiny and users won't notice anything (if you have a clustered index and manages to use only the smallest lock possible).

The total run time of your job might be longer but you'll not lock down everything while you do it.

You could also have a look at partitioned tables (load the new data in a table and then switch it to the main table once it's ready).
Post #1041750
Posted Monday, January 03, 2011 6:12 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
Nick123-481228 (12/22/2010)
Thanks for your advise.

On price table it does :

if Price Date exists with different value then moves data from Main table to archieve table
then Updates with new values on Main table
then insert complete new values on Main table.

I source of data comes from differnt SQL database.

In your solution when do you sync both tables becuase our updates are happens on daily basis and which required lot of changes in application level.

Thanks
Nick

Thanks

Gosh... I feel your pain but there's still not much anyone knows about your data or meta data. I recommend you start by taking a look at the article at the last link in my signature line below. You'll probably get better help that way. Otherwise, one guess is as good as another. Please be sure to tell us which version (Enterprise, Standard, ???) you're using, as well.


--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." -- 04 August 2013
(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 #1041790
Posted Monday, January 03, 2011 6:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
how about using a staging table to prepare all your new data and only at the very last merge the data into your target table ?

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1041793
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse