更新 - 我可以做更好的陈述吗？ [英] Update - can I do better statement?
问题描述
我创建了一个包含存储桶的表，用于为每位招生官员保留
注册的活动，以便每天进行一次
注册会话。我有一个UPDATE来构建滚动总计和
更新所述表。我从一张包含
注册信息的桌子中算出了独特的学生。
一切正常，但运行得很慢，我想知道是否
那里是编写SQL的更好方法。 SQL的引擎调整
顾问有0％的改进。
我会感谢任何关于更好地实现目标的建议。
或者，如果没有更好的方法，请赞美我的天才！ * grin *
UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURR ENT - （RT =
rollingtotal）
SET NEW_REGISTRATION_COUNT =
（SELECT COUNT（DISTINCT STUDENT_ID）
FROM
（SELECT aa.session_id，aa.student_id，student_status
来自F_BI_REGISTRATION_TRACKING AA
LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
AA.STUDENT_ID = BB.STUDENT_ID和AA.SESSION_ID = BB.SESSION_ID
LEFT OUTER JOIN D_BI_STUDENT CC
ON AA.STUDENT_ID = CC.STUDENT_ID和CC.CURR_IND
=''Y''
WHERE AA.SESSION_ID = OA.SESSION_ID
AND ACTIVITY_DT< = OA.SESSION_DT
AND CC.TALISMA_AO_ID = OA.ADMREP_ID
AND STUDENT_STATUS =''NEW''
GROUP BY AA.SESSION_ID，aa.STUDENT_ID，STUDENT_STATUS
有和（ACTIVITY_COUNT）0
）为ppp
）
来自F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_ SESSION_rt_curr ent OA
，50K行，大约需要5分钟。但是，我几乎有
9,000,000行历史。 （四季度会议的15年）。如果这个
构建是线性的，则需要1.25小时。
我的关系和依赖关系存放在我的ETL工具中。
//连接：目标
创建表
" dbo"。" F_BI_Admissions_Officer_Summary_By_Session_ RT_Current"
（
" AO_SUMMARY_KEY" VARCHAR（43）NULL，
" ADMREP_ID" VARCHAR（15）NULL，
" ADMREP_SKEY" BIGINT NULL，
" ADMREP_CAPTION" VARCHAR（20）NULL，
" ADMREP_CAMPUS_ID" VARCHAR（10）NULL，
" ADMREP_CAMPUS_CAPTION" VARCHAR（30） NULL，
" ADMREP_REGION_CAPTION" VARCHAR（20）NULL，
" ADMREP_TEAM_CAPTION" VARCHAR（57）NULL，
" ADMREP_EXPIRED_FLAG" VARCHAR（5）NULL，
" SESSION_ID" CHAR（6）NULL，
" SESSION_DAY" INTEGER NULL，
" SESSION_DT" ; DATETIME NULL，
" LEAD_COU NT" INTEGER NULL，
" INTERVIEW_COUNT" INTEGER NULL，
" APPLICATION_COUNT" INTEGER NULL，
" LY_SESSION_ID" VARCHAR（10）NULL，
" LY_LEAD_COUNT" SMALLINT NULL，
" LY_INTERVIEW_COUNT" SMALLINT NULL，
" LY_APPLICATION_COUNT" SMALLINT NULL，
" LY_ALL_REGISTRATION_COUNT" SMALLINT NULL，
" NEW_READMIT_REGISTRATION_COUNT" SMALLINT NULL，
" ALL_REGISTRATION_COUNT" INTEGER NOT NULL，
" NEW_REGISTRATION_COUNT" INTEGER NOT NULL，
" READMIT_REGISTRATION_COUNT" INTEGER NOT NULL，
" CONTINUING_REGISTRATION_COUNT" INTEGER NOT NULL，
" CANCELLED_REGISTRATION_COUNT" INTEGER NOT NULL，
" LY_NEW_REGISTRATION_COUNT" SMALLINT NULL，
" LY_READMIT_REGISTRATION_COUNT" SMALLINT NULL，
" LY_CONTINUING_REGISTRATION_COUNT" SMALLINT NULL，
" LY_CANCELLED_REGISTRATION_COUNT" SMALLINT NULL，
" TALISMA_CALLS_IN" SMALLINT NULL，
" TALISMA_CALLS_OUT" SMALLINT NULL，
" AVAYA_CALLS_IN" SMALLINT NULL，
" AVAYA_CALLS_OUT" SMALLINT NULL，
" LEAD_FORECAST" SMALLINT NULL，
" INTERVIEW_FORECAST" SMALLINT NULL，
" APPLICATION_FORECAST" SMALLINT NULL，
" NEW_REGISTRATION_FORECAST" SMALLINT NULL，
" READMIT_REGISTRATION_FORECAST" SMALLINT NULL，
" CONTINUING_REGISTRATION_FORECAST" SMALLINT NULL
）
;
//连接：目标
创建TABLEdbo。F_BI_Registration_Tracking
（
" UNIQUE_KEY" VARCHAR（87）NULL，
" REGISTRATION_KEY" VARCHAR （40）NULL，
" REGTRACK_ID" VARCHAR（47）NULL，
" CLASSES_OFFERED_ID" VARCHAR（23）NULL，
" ; STUDENT_ID" CHAR（20）NULL，
" SESSION_ID" CHAR（6）NULL，
" FULL_CLASS_ID" VARCHAR（15）NULL，
" CAMPUS_ID" VARCHAR（10）NULL，
" ACTIVITY_DT" DATETIME NULL，
" ACTIVITY_CODE" VARCHAR（1）NULL，
" LOAD_DT" DATETIME NULL，
" SOURCE" VARCHAR（4）NULL，
" ACTIVITY_COUNT" SMALLINT NULL，
" CLASS_DROP_DT" DATETIME NULL，
" CLASS_DROP_COUNT" SMALLINT NULL，
" CLASS_ADD_DT" DA TETIME NULL，
" CLASS_ADD_COUNT" SMALLINT NULL，
" BEFORE_D0_CLASS_COUNT" SMALLINT NULL，
" DAY0_CLASS_COUNT" SMALLINT NULL，
" AFTER_D0_CLASS_COUNT" SMALLINT NULL，
" ALL_CLASS_COUNT" SMALLINT NULL，
" BEFORE_D0_ONLINE_CLASS_COUNT" SMALLINT NULL，
" DAY0_ONLINE_CLASS_COUNT" SMALLINT NULL，
" AFTER_D0_ONLINE_CLASS_COUNT" SMALLINT NULL，
" ALL_ONLINE_CLASS_COUNT" SMALLINT NULL，
" VM_POS" BIGINT NULL，
" SOURCE_FILE" SMALLINT NULL，
" BANDED_ID" BIGINT NULL，
" CLASSES_OFFERED_SKEY" BIGINT NULL，
" STUDENT_SKEY" BIGINT NULL，
" SESSION_SKEY" BIGINT NULL，
" CLASS_CAMPUS_SKEY" BIGINT NULL，
" COMPUTED_DT" DATETIME NULL，
" SESSION_DAY" BIGINT NULL，
" Count_Down" BIGINT NULL，
" AFTER_DAY0_FIRST_REG_FLAG" SMALLINT NULL
）
;
这是一团糟。我会重新开始并把它弄好。
1）为什么要使用全部大写字母？这是一个尖叫的婊子
读取和我们这样做是因为我们在1950年代没有选择
打卡。
2）为什么你的桌子里没有钥匙？大多数列都是NULL，一个
肯定表示你有一个烂数据模型（如果你有一个）。
..实际上你有更多的NULL比一个主要的b
汽车公司的整个数据库。
3）为什么要在表格中存储可计算的总数？这就是我们用文件系统做b $ b的方式;在SQL中，我们使用VIEW。或者我们使用前端
报表应用程序。
4）为什么选择不可读的字母别名？也就是说，CC为什么CC提醒读者基表是D_Bi_Student？
5）为什么你使用了_key？后缀？这告诉我们如何使用属性
，而不是数据元素是什么。另外，想想多么愚蠢
" unique_key"是 - 是否有任何非唯一键???
6）是curr_ind其实是一面旗帜？我们不用SQL中的汇编写入
级别位标志。在正确设计的架构中它是多余的。
状态是根据基本数据的当前状态计算的。
7）为什么你使用这么多专有数据类型？
8）为什么在同一个表中混合汇总数据和基础数据？你知道1NF和桌子的定义是什么吗？
9）同时拥有session_dt和session_dt和session_day有点
冗余。
10）为什么要在数据表中保留字幕？标题
是显示的一部分，显示在前端完成，从不在
数据库中。
11）为什么会这样您的许多代码VARCHAR（n）？大多数代码是固定的
长度。
12）看起来您需要拆分表格并规范化
架构。考虑一下你工作的基本实体 -
学生，考生，招生人员等等，然后一旦他们在桌子上，就想想他们的关系。 br />
Celko，当我看到你的回复时有时候我会这么做。
1.我使用ETL工具，它创建了DDL，我只是复制和粘贴。我的
极度道歉。
2.我使用ETL工具，它处理关系和数据验证。
我没有放表中的那些并且依赖于
工具中的验证。
3.这是一个数据仓库，而不是一个操作系统。我建立了
数据以便快速报告，而不是数据输入。
4.有效点，我需要更好的hygen与alisases。
5。这可能是我的SQL技能的弱点。例。我试图更新的表的业务键
是ADMREP_ID，SESSION_ID和
SESSION_DAY。我将它们合并到一个字段中，所以我有一个独特的
键。我想这将是我的主键。
6.是的，这是一个Y / N字段。缓慢变化的表格
有start_date，end_date，update_date和curr_ind。 Y是当前
记录，因为它在操作系统中。
7.这是一个我希望在
$内有更多控制权的区域b $ b ETL工具。它查看数据并确定要创建的类型。
当然我可以提前创建表格，而不是让工具
为我创建它们。
8.表格I我想更新的是汇总表。不确定
你是什么意思。
9.是的，如果这是一个可操作的数据库。这是一个数据仓库
用于报告。我希望将飞行计算保持在最低限度。
此外，session_day的计算方法是将当前日期减去最后一天的
添加下降。这允许我们通过
会话比较给定的一天会话。 对于会话200703的第0天如何与第0天的比较为
会话200603。第0天（添加或删除课程的最后一天）每年变为
年和会话会话。
10.此数据用于数据仓库。我有3个数据库，SOURCE，
STAGING和TARGET。说明就像你描述的那样，我的招生官
维度有密钥和标题，学生表中有
招生官密钥，但没有关于招生的任何信息< br $> b $ b官员。当我在Target中构建最终数据时，我有很多
冗余并在最终表格中加入字幕。
11.好点，我有点儿将它们转换为char。
12.数据仓库，最终数据被去规范化。 （我的供应商我们的主管
管理系统说同样的事情，我提醒他们我不是建立数据进行数据输入，而是用于报告。这也是为什么我可以用我的数据和Cognos工具吸取他们的本土报告。
感谢Celko的建设性建议。我已经学到了很多关于
如何更改UPdate以便更快地运行...没有等待一分钟，你
刚刚完成了我的责任并且刚刚排序叫我傻...
没关系。
--CELKO--写道：
这是一个烂摊子。我会重新开始并把它弄好。
1）为什么要使用全部大写字母？这是一个尖叫的婊子
读取和我们这样做是因为我们在1950年代没有选择
打卡。
2）为什么你的桌子里没有钥匙？大多数列都是空的，一个
肯定表示你有一个烂数据模型（如果你有一个）。
。事实上，对于一家主要的b
汽车公司，你有比整个数据库更多的NULL。
3）为什么你在表中存储可计算的总数？这就是我们用文件系统做b $ b的方式;在SQL中，我们使用VIEW。或者我们使用前端
报表应用程序。
4）为什么选择不可读的字母别名？也就是说，CC为什么CC提醒读者基表是D_Bi_Student？
5）为什么你使用了_key？后缀？这告诉我们如何使用属性
，而不是数据元素是什么。另外，想想多么愚蠢
" unique_key"是 - 是否有任何非唯一键???
6）是curr_ind其实是一面旗帜？我们不用SQL中的汇编写入
级别位标志。在正确设计的架构中它是多余的。
状态是根据基本数据的当前状态计算的。
7）为什么你使用这么多专有数据类型？
8）为什么在同一个表中混合汇总数据和基础数据？你知道1NF和桌子的定义是什么吗？
9）同时拥有session_dt和session_dt和session_day有点
冗余。
10）为什么要在数据表中保留字幕？标题
是显示的一部分，显示在前端完成，从不在
数据库中。
11）为什么会这样您的许多代码VARCHAR（n）？大多数代码是固定的
长度。
12）看起来您需要拆分表格并规范化
架构。考虑一下你工作的基本实体 -
学生，考生，招生人员等等，然后一旦他们在桌子上，就想想他们的关系。
rcamarda写道：
UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURR ENT - （RT =
rollingtotal）
SET NEW_REGISTRATION_COUNT =
（SELECT COUNT（DISTINCT STUDENT_ID）
FROM
（ SELECT aa.session_id，aa.student_id，student_status
来自F_BI_REGISTRATION_TRACKING AA
LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
AA.STUDENT_ID = BB.STUDENT_ID AND AA.SESSION_ID = BB.SESSION_ID
LEFT OUTER JOIN D_BI_STUDENT CC
ON AA.STUDENT_ID = CC.STUDENT_ID AND CC.CURR_IND
= ''Y''
WHERE AA.SESSION_ID = OA.SESSION_ID
AND ACTIVITY_DT< = OA.SESSION_DT
AND CC.TALISMA_AO_ID = OA.ADMREP_I D $ / $
AND STUDENT_STATUS =''NEW''
GROUP BY AA.SESSION_ID，aa.STUDENT_ID，STUDENT_STATUS
有SUM（ACTIVITY_COUNT）0
）as ppp
）
来自F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_rt_curr ent OA
你们在所有适当的列上都有索引？
哪个表包含STUDENT_STATUS？
D_BI_STUDENT的连接是否需要保留在外部？那么
加入F_BI_STUDENT_SUMMARY_SESSION怎么样？如果是这样，你可以通过在这些表中添加虚拟行（带有某种虚拟行
指示符）来消除
需求吗？ />
你可以替换这个表格（从原文中删除）：
SELECT COUNT（DISTINCT STUDENT_ID）FROM（
SELECT SESSION_ID，STUDENT_ID，STUDENT_STATUS
FROM ...
GROUP BY SESSION_ID，STUDENT_ID，STUDENT_STATUS
有和（ACTIVITY_COUNT）0
）
这个？
SELECT COUNT（*）FROM（
SELECT STUDENT_ID
FROM ...
GROUP BY STUDENT_ID
有SUM（ACTIVITY_COUNT）0
）
I have created a table that contains buckets to hold activitives of
enrollment for each of our admissions officer for each day of an
enrollment session. I have an UPDATE that builds rolling totals and
updates said table. I count unique students from a table that contains
registration information.
Everything works, however it runs pretty slowly and I am wondering if
there is a better approach to writing the SQL. SQL''s Engine Tuning
advisor has 0% improvment.
I''d appreciate any suggestions as to better ways to accomplish my goal.
Or, compliments on my genius if there isnt a better way! *grin*
UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURR ENT -- (RT =
rollingtotal)
SET NEW_REGISTRATION_COUNT =
(SELECT COUNT(DISTINCT STUDENT_ID)
FROM
(SELECT aa.session_id, aa.student_id, student_status
FROM F_BI_REGISTRATION_TRACKING AA
LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
AA.STUDENT_ID=BB.STUDENT_ID AND AA.SESSION_ID=BB.SESSION_ID
LEFT OUTER JOIN D_BI_STUDENT CC
ON AA.STUDENT_ID = CC.STUDENT_ID AND CC.CURR_IND
= ''Y''
WHERE AA.SESSION_ID = OA.SESSION_ID
AND ACTIVITY_DT <= OA.SESSION_DT
AND CC.TALISMA_AO_ID = OA.ADMREP_ID
AND STUDENT_STATUS = ''NEW''
GROUP BY AA.SESSION_ID, aa.STUDENT_ID, STUDENT_STATUS
HAVING SUM(ACTIVITY_COUNT) 0
) as ppp
)
FROM F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_rt_curr ent OA
with 50K rows, this takes about 5 minutes. However I have almost
9,000,000 rows of history. (15 years of 4 quarter sessions). If this
build is linear, it would take 1.25 hours.
My relationships and dependencies are housed in my ETL tool.
// Connection: Target
CREATE TABLE
"dbo"."F_BI_Admissions_Officer_Summary_By_Session_ RT_Current"
(
"AO_SUMMARY_KEY" VARCHAR(43) NULL,
"ADMREP_ID" VARCHAR(15) NULL,
"ADMREP_SKEY" BIGINT NULL,
"ADMREP_CAPTION" VARCHAR(20) NULL,
"ADMREP_CAMPUS_ID" VARCHAR(10) NULL,
"ADMREP_CAMPUS_CAPTION" VARCHAR(30) NULL,
"ADMREP_REGION_CAPTION" VARCHAR(20) NULL,
"ADMREP_TEAM_CAPTION" VARCHAR(57) NULL,
"ADMREP_EXPIRED_FLAG" VARCHAR(5) NULL,
"SESSION_ID" CHAR(6) NULL,
"SESSION_DAY" INTEGER NULL,
"SESSION_DT" DATETIME NULL,
"LEAD_COUNT" INTEGER NULL,
"INTERVIEW_COUNT" INTEGER NULL,
"APPLICATION_COUNT" INTEGER NULL,
"LY_SESSION_ID" VARCHAR(10) NULL,
"LY_LEAD_COUNT" SMALLINT NULL,
"LY_INTERVIEW_COUNT" SMALLINT NULL,
"LY_APPLICATION_COUNT" SMALLINT NULL,
"LY_ALL_REGISTRATION_COUNT" SMALLINT NULL,
"NEW_READMIT_REGISTRATION_COUNT" SMALLINT NULL,
"ALL_REGISTRATION_COUNT" INTEGER NOT NULL,
"NEW_REGISTRATION_COUNT" INTEGER NOT NULL,
"READMIT_REGISTRATION_COUNT" INTEGER NOT NULL,
"CONTINUING_REGISTRATION_COUNT" INTEGER NOT NULL,
"CANCELLED_REGISTRATION_COUNT" INTEGER NOT NULL,
"LY_NEW_REGISTRATION_COUNT" SMALLINT NULL,
"LY_READMIT_REGISTRATION_COUNT" SMALLINT NULL,
"LY_CONTINUING_REGISTRATION_COUNT" SMALLINT NULL,
"LY_CANCELLED_REGISTRATION_COUNT" SMALLINT NULL,
"TALISMA_CALLS_IN" SMALLINT NULL,
"TALISMA_CALLS_OUT" SMALLINT NULL,
"AVAYA_CALLS_IN" SMALLINT NULL,
"AVAYA_CALLS_OUT" SMALLINT NULL,
"LEAD_FORECAST" SMALLINT NULL,
"INTERVIEW_FORECAST" SMALLINT NULL,
"APPLICATION_FORECAST" SMALLINT NULL,
"NEW_REGISTRATION_FORECAST" SMALLINT NULL,
"READMIT_REGISTRATION_FORECAST" SMALLINT NULL,
"CONTINUING_REGISTRATION_FORECAST" SMALLINT NULL
)
;
// Connection: Target
CREATE TABLE "dbo"."F_BI_Registration_Tracking"
(
"UNIQUE_KEY" VARCHAR(87) NULL,
"REGISTRATION_KEY" VARCHAR(40) NULL,
"REGTRACK_ID" VARCHAR(47) NULL,
"CLASSES_OFFERED_ID" VARCHAR(23) NULL,
"STUDENT_ID" CHAR(20) NULL,
"SESSION_ID" CHAR(6) NULL,
"FULL_CLASS_ID" VARCHAR(15) NULL,
"CAMPUS_ID" VARCHAR(10) NULL,
"ACTIVITY_DT" DATETIME NULL,
"ACTIVITY_CODE" VARCHAR(1) NULL,
"LOAD_DT" DATETIME NULL,
"SOURCE" VARCHAR(4) NULL,
"ACTIVITY_COUNT" SMALLINT NULL,
"CLASS_DROP_DT" DATETIME NULL,
"CLASS_DROP_COUNT" SMALLINT NULL,
"CLASS_ADD_DT" DATETIME NULL,
"CLASS_ADD_COUNT" SMALLINT NULL,
"BEFORE_D0_CLASS_COUNT" SMALLINT NULL,
"DAY0_CLASS_COUNT" SMALLINT NULL,
"AFTER_D0_CLASS_COUNT" SMALLINT NULL,
"ALL_CLASS_COUNT" SMALLINT NULL,
"BEFORE_D0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"DAY0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"AFTER_D0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"ALL_ONLINE_CLASS_COUNT" SMALLINT NULL,
"VM_POS" BIGINT NULL,
"SOURCE_FILE" SMALLINT NULL,
"BANDED_ID" BIGINT NULL,
"CLASSES_OFFERED_SKEY" BIGINT NULL,
"STUDENT_SKEY" BIGINT NULL,
"SESSION_SKEY" BIGINT NULL,
"CLASS_CAMPUS_SKEY" BIGINT NULL,
"COMPUTED_DT" DATETIME NULL,
"SESSION_DAY" BIGINT NULL,
"Count_Down" BIGINT NULL,
"AFTER_DAY0_FIRST_REG_FLAG" SMALLINT NULL
)
;
This is a mess. I woudl start over and get it right.
1) Why do you use all uppercase letters? It is a screaming bitch to
read and we did this because we had no choice in the 1950''s with
punch cards.
2) Why do you have no keys in any table? Most columns are NULL-able, a
sure sign that you have a rotten data model (if you have one at all).
.. In fact you have more NULLs than the entire database for a major
automobile company.
3) Why are you storing computable totals in a table? That is how we
did it with file systems; in SQL, we use a VIEW. Or we use a front-end
report application.
4) Why did you pick the unreadable alphabetic aliases? That is, why
does CC remind the reader that the base table is "D_Bi_Student"?
5) Why did you use a "_key" suffix? That tells us HOW an attribute
is use, not what the data element IS. Also, think about how stupid
"unique_key" is - are there any non-unique keys???
6) Is "curr_ind" actually a flag? We do not write with assembly
level bit flags in SQL. It is redundant in a properly designed schema.
The status is computed from the current state of the basic data.
7) Why did you use so many proprietary data types?
8) Why did you mix summary data and base data in the same table? Do
you know what 1NF and the definition of a table are?
9) Having both "session_dt" and "session_day" is a bit
redundant.
10) Why are you keeping captions in a table with the data? A caption
is part of a display and display is done in the front end, never in the
database.
11) Why are so many of your codes VARCHAR(n)? Most codes are fixed
length.
12) It looks like you need to split up your tables and normalize the
schema. Think about the basic entities with which you are working -
students, candidates, admissions personnel, etc. and then once those
are in tables, think about their relationships.
Celko, sometimes I cring when I see your replies.
1. I use an ETL tool, it creates the DDL, I just copied and pasted. My
extreme apologies.
2. I use an ETL tool, it handles the relationships and data validation.
I haven''t put those in the tables and have relied on validation within
the tool.
3. This is a data warehouse, not an operational system. I build the
data for speedy reporting, not data entry.
4. Valid point, I need better hygen with alisases.
5. this might be weakness in my SQL skills. Example. the business keys
for the table im trying to update is ADMREP_ID, SESSION_ID and
SESSION_DAY. I concatinate them into a single field so I have a unique
key. I guess this would be my primary key.
6. Yes, it is a Y/N field. Tables that are slowly changing dimensions
have start_date, end_date, update_date and curr_ind. Y is the current
record as it is in the operational system.
7. This is an area that I would like to have more control within the
ETL tool. it looks at the data and determines what type to create. Of
course I could create the tables ahead of time, and not let the tool
create them for me.
8. the table I am trying to update is the summary table. Not sure what
you are meaning.
9. yes, if this were an operational database. This is a data warehouse
for reporting. I want to keep the on the fly computations to a minimum.
Also, the session_day is computed by taking the current date minus the
last day to add drop. This allows us to compare a given day session by
session. "How does day 0 for session 200703 compare to day 0 for
session 200603". Day 0 (last day to add or drop classes) varies year to
year and session to session.
10. This data is for a data warehouse. I have 3 databases, SOURCE,
STAGING and TARGET. Stating is as you describe, My admissions officer
dimension has the keys and captions, where the student table has the
admissions officer key, but not any information about the admissions
officer. When I build the final data in Target, I have a lot of
redundancy and pull in captions into the final tables.
11. Good point, i''ve ment to convert those to char.
12. Data warehouse, final data is de-normalized. (My vendor of our lead
managment system says the same thing, which I remind them that I am not
building data for data entry, but for reporting. This is also why I can
smoke their home grown reporting with my data and Cognos tools).
Thanks for the constructive suggestions Celko. I''ve learned a lot on
how I might change the UPdate to run faster...no wait a minute, you
just finished berating me and came just sort of calling me stupid...
Never mind.
--CELKO-- wrote:This is a mess. I would start over and get it right.
1) Why do you use all uppercase letters? It is a screaming bitch to
read and we did this because we had no choice in the 1950''s with
punch cards.
2) Why do you have no keys in any table? Most columns are NULL-able, a
sure sign that you have a rotten data model (if you have one at all).
. In fact you have more NULLs than the entire database for a major
automobile company.
3) Why are you storing computable totals in a table? That is how we
did it with file systems; in SQL, we use a VIEW. Or we use a front-end
report application.
4) Why did you pick the unreadable alphabetic aliases? That is, why
does CC remind the reader that the base table is "D_Bi_Student"?
5) Why did you use a "_key" suffix? That tells us HOW an attribute
is use, not what the data element IS. Also, think about how stupid
"unique_key" is - are there any non-unique keys???
6) Is "curr_ind" actually a flag? We do not write with assembly
level bit flags in SQL. It is redundant in a properly designed schema.
The status is computed from the current state of the basic data.
7) Why did you use so many proprietary data types?
8) Why did you mix summary data and base data in the same table? Do
you know what 1NF and the definition of a table are?
9) Having both "session_dt" and "session_day" is a bit
redundant.
10) Why are you keeping captions in a table with the data? A caption
is part of a display and display is done in the front end, never in the
database.
11) Why are so many of your codes VARCHAR(n)? Most codes are fixed
length.
12) It looks like you need to split up your tables and normalize the
schema. Think about the basic entities with which you are working -
students, candidates, admissions personnel, etc. and then once those
are in tables, think about their relationships.
rcamarda wrote:
UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURR ENT -- (RT =
rollingtotal)
SET NEW_REGISTRATION_COUNT =
(SELECT COUNT(DISTINCT STUDENT_ID)
FROM
(SELECT aa.session_id, aa.student_id, student_status
FROM F_BI_REGISTRATION_TRACKING AA
LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
AA.STUDENT_ID=BB.STUDENT_ID AND AA.SESSION_ID=BB.SESSION_ID
LEFT OUTER JOIN D_BI_STUDENT CC
ON AA.STUDENT_ID = CC.STUDENT_ID AND CC.CURR_IND
= ''Y''
WHERE AA.SESSION_ID = OA.SESSION_ID
AND ACTIVITY_DT <= OA.SESSION_DT
AND CC.TALISMA_AO_ID = OA.ADMREP_ID
AND STUDENT_STATUS = ''NEW''
GROUP BY AA.SESSION_ID, aa.STUDENT_ID, STUDENT_STATUS
HAVING SUM(ACTIVITY_COUNT) 0
) as ppp
)
FROM F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_rt_curr ent OADo you have indexes on all appropriate columns?
Which table contains STUDENT_STATUS?
Does the join to D_BI_STUDENT need to be left outer? What about the
join to F_BI_STUDENT_SUMMARY_SESSION? If so, can you eliminate the
need by adding dummy rows (with some sort of this-is-a-dummy-row
indicator) to those tables?
Can you replace this form (boiled down from the original):
SELECT COUNT(DISTINCT STUDENT_ID) FROM (
SELECT SESSION_ID, STUDENT_ID, STUDENT_STATUS
FROM ...
GROUP BY SESSION_ID, STUDENT_ID, STUDENT_STATUS
HAVING SUM(ACTIVITY_COUNT) 0
)
with this?
SELECT COUNT(*) FROM (
SELECT STUDENT_ID
FROM ...
GROUP BY STUDENT_ID
HAVING SUM(ACTIVITY_COUNT) 0
)
这篇关于更新 - 我可以做更好的陈述吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
