|
|
Posted by rcamarda on 11/29/70 11:52
Hello,
I am using SQL 2005 and Cognos' Data Manager. It is an ETL tool for
data warehousing.
I have a problem with time it takes to load new changes, and I am
seeking advice on a better way to manage the data.
I have a table that tracks student attendance and it contains about 13
million records. On a daily basis, there are 5,000 - 20,000 inserts and
10,000 - 50,000 updates.
The daily data comes for two different text files from my operation
system; current and historical (CLSFIL and CLSHIS).
The data is loaded into a staging area from the operational system,
where data cleansing and other fields are added to the table.
The final step is delivering the table to my target database, which is
used for reporting.
Heres the situation: I find it takes 45 minutes to do a relational
update, where only the records that changed in the last day will be
loaded. However, if I choose the native API load instead of a
Relational Load, it can load all 13M records in 7 minutes. The table is
heavly indexed
At some point, the API load will take more time than the relational
load, (the changes and new records will remain a constant, but the file
will continue to grow).
I'm seeking another solution is more efficient. I'm considering two
tables for history and current and creating a view for reporting via a
union.
This a good idea? How can I make the view effeicent to use the where
clause? Looking to bounce around ideas.
Other Ideas?
Thanks in Advance
Rob
(I maintain the key relationships in the tool, not the tables. I know
I have lots to learn and improvments)
CREATE TABLE "dbo"."F_BI_Class_Attendance_Detail"
(
"CLASS_ATTENDANCE_ID" VARCHAR(50) NULL,
"CLASSES_OFFERED_ID" VARCHAR(26) NULL,
"CLASS_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ID" CHAR(20) NULL,
"FULL_CLASS_ID" CHAR(15) NOT NULL,
"SESSION_ID" CHAR(10) NULL,
"SECTION_ID" VARCHAR(5) NULL,
"MEET_DT" DATETIME NULL,
"MEETING" SMALLINT NULL,
"PRESENT" CHAR(2) NOT NULL,
"SESSION_SKEY" BIGINT NULL,
"STUDENT_SKEY" BIGINT NULL,
"CLASS_CAMPUS_SKEY" BIGINT NULL,
"CLASSES_OFFERED_SKEY" BIGINT NULL,
"LOAD_DT" DATETIME NULL,
"COMPUTED_DT" DATETIME NULL
)
;
Navigation:
[Reply to this message]
|