Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2010-02-24 : 07:36:19
|
| Hi there, I'm just looking for some advice to help me decide on what action to take. I know this may seem like a long post but please bear with me! I only think it's best to explain in a bit of detail for clarity.I'm a SSRS report developer and recently I've been asked to develop a complex report on financial data. It has to be as real time as possible.To do this, I've decided to replicate the database (CEP) that stores the Project names, financial data etc. However there is a process that is carried out on the data before it can be reported on...The process already exists and is executed each night to export the data to a dedicated financial system (FIN) using SPs that insert the data using Views. The financail system is update only at night so no real time there.This is the bit where I'd like to ask for advice... To make this as real time as possible, I'd need to find a way of using the Veiws to update a reporting table either by triggers or to just read from the views.If the report reads from the views it will take far too long. So I was thinking about triggers against the replicated database. There's about 10 views and each have different criteria to export the data in the correct format. I can run one of these views and it can take up to 20 seconds to bring back 8 rows. If I create a trigger on the replicated database I'd possibly need to use all the criteria (where clauses etc) from all views to decide how the data goes into a reporting table.I'm concerned this will be too much bottle neck on the server (to add, it's SQL Server 2000 which will use the transaction log for Inserted, deleted). Is it possible that something might fail if there's too much pressure on the replicate db or will cause possible bottle necks on the production db?Any help on this would be appreciated:) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-24 : 11:51:35
|
| We use staging tables for this type of thing.Ideally all rows will have UpdateDate/Time columns on them.For each table we get MAX(UpdateDate) from the staging table (i.e. most recent date we have) and then UpSert the staging table with anything newer in the Main Table but ONLY if one, or more, of the columns in the staging table has changed (our staging table sometimes has less columns than the main table; also records may be Updated in the maintable - which updates the UpdateDate but no other columns are changed).If you do not have UpdateDate column you can just compare the whole main-table against the staging table UpSerting just the rows that are different.We have an additional DateTime column in the Staging table with the date/time when it was changed (so this will be later than the UpdateDate column in the Main-table).We transfer newer rows in Staging Table to remote server/database. Same type of system: we find the MAX(UpdateDate) in the remote staging table and then transfer anything newer across.However, slightly different, we can delete all rows in the remote staging table that match the ID of rows with newer UpdateDate in the Local staging table, and then just insert all those rows into the Remote table.From the Remote Table the newer rows can then be inserted into the remote Main Table.The benefit of this is that the number of rows involved at each stage is the absolute minimum. No rows are transferred which have not changed. So it is quick to do. We tend to run this every 15 - 30 minutes on our systems, so the data is "close to real time".If this may be useful to you I can find a more detailed description. |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-02-25 : 11:20:12
|
| Hi Kristen and thanks for responding.I'll get back to you on this one very shortly.:) |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-02-26 : 06:13:17
|
| Hi Kristen,This will be useful to me! if you have any articles or links that may elaborate on this, I'd be very grateful!Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-26 : 08:22:45
|
| Don't think I've got any links, what I have got is home-grown stuff, but I will be able to write something.If I don't reply in a day or two give me a nudge pls. Bit busy at the moment. |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-03-01 : 05:16:24
|
| Hi Kristen, it was just to find out if you might have time to help me on this at some point in the week!Thanks again |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-02 : 13:51:05
|
Are you in a position to send me the DDL for your table(s)? If its not too long you can post it here, otherwise send it to me in a PM.I've got some code that will auto-generate the code I'm taking about, and I think just processing your tables into that code will be the best starting point (and takes me much less time that trying to write and article, or explain it in more detail  |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-03-05 : 05:13:00
|
| Hi Kristen,Just writing to ensure you received the code I PM'd you?CheersPaul |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-05 : 13:37:28
|
Yes, got it thanks. I have sorted out the Template we use, and generated a "table-xfer" script (mechanically generated).We have been offered a very large and prestigious contract this week, and that's overshadowing everything else; having to arrange contractors to do all the DEV work we were expecting to do ourselves, over the next 3-6 months, so we can clear the decks for this contract.I will try to put the code into some sort of shape that, hopefully, you will be able to use, or at least gain some insight from, over the weekend. But that will be all that I will be able to do, this time around, I'm afraid.Hassle me again if you don't hear anything please. I ain't ignoring you, but I am fire fighting! and reminding me that you are waiting will help  (Best to send me a PM 'coz that will get to my inbox, whereas I will only see a post here when I have a moment to follow up on any replies to threads - which is all I have time to do for the time being ....) |
|
|
|
|
|
|
|
|
Trigger advice on Replicated database