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 |
|
hypo
Starting Member
8 Posts |
 Posted - 2006-03-18 : 07:21:17
|
| Hi, I'm transferring lots of rows (each row is a problem) from an oltp database to a datawarehouse. These problems (rows) are transferred to the fact table. And a problem has attributes like status and logdate and solved_date (default null) and a sequence number. I also check every day for new problems in the source. Now the thing is ... I can write an SQL query to incrementally add new rows by using the sequence number as parameter. This is to lower querying time (BECAUSe the problem table has about 2.000.000 rows)The Actual Question: I also need to write a query to update the tables in the Datawarehouse. For example when a status of a problem changes. When status changes from "unsolved" to "solved" it gets a date in the solved_date attribute. Now ... How can I write a query that goes fast through those 2000000 rows? Now I can't use the sequence_number because its not known when the problem gets solved. There is the fact that I run a query every day, so maybe I need to look at problems where a solved date is added on that day? Im just a novice in sql so maybe you guys know ways to do fast lookups in many rows? |
|
|
Bertrandkis
Starting Member
8 Posts |
Posted - 2006-03-20 : 04:03:57
|
| Your question:I can write an SQL query to incrementally add new rows by using the sequence number as parameter.Answer: Yes, you can create a stored procedure that takes a input parameterand in the stored procedure you write the code to insert the new record.Your question:I also need to write a query to update the tables in the DatawarehouseAnswer:In your fact table of the datawarehouse,I assume that you have a primarykey, but you also need to have primary column(say OlpttablePrimarykey) of your oltp table in the fact table of your warehouse(say ProblemPK). That is the only way you can relate reliably rows from you olpt table to those in the datawarehouse.You should avoid looping through large tables, with 2million rows, your loop will take forever and will affect the performance of the server.you can write an update statement like this to update all problems whose statuses have been changed.Update MyFactTable set Status=x Where ProblemPK in(Select OlpttablePrimarykey from Olpttable where .....)Now, you subquery (Select OlpttablePrimarykey from Olpttable where .....)must only pickup the rows that have been updated on the day. You should have either a dateUpdated column in you table to identitfy them or some other mechanism to track the update)Hope this helps.Le Bon vivant |
 |
|
|
|
|
|
SSIS data lookup question