more HELP: how to make this sql more efficient? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

more HELP: how to make this sql more efficient?

more HELP: how to make this sql more efficient?# Database - 数据库

x*52008-11-13 08:11

1 楼

https://www.rd.com/offer/rd/10for5_2008/index.jsp?trkid=rd_500_gift
Note that you cannot send it to yourself. Instead send it to your spouse or
someone who lives with you.

c*o2008-11-13 08:11

2 楼

i got a timeout from my asp page in one questionnaire. how can i improve it?
or mostly no other ways? the checking of "AnswerUsed" took a little bit
long.
thanx thanx thanx!!!!!
DECLARE @QuestionnaireID int
SET @QuestionnaireID=1029
SELECT
Questions.QuestionID,
Questions.QuestionText,
Answers.AnswerText,
Answers.CorrectAnswer,
Answers.PointValue AS AnswerPointValue,
CASE WHEN EXISTS
(
SELECT 1
FROM
Scores S

c*t2008-11-13 08:11

3 楼

打回出重写

it?

【在 c****o 的大作中提到】

: i got a timeout from my asp page in one questionnaire. how can i improve it?
: or mostly no other ways? the checking of "AnswerUsed" took a little bit
: long.
: thanx thanx thanx!!!!!
: DECLARE @QuestionnaireID int
: SET @QuestionnaireID=1029
: SELECT
: Questions.QuestionID,
: Questions.QuestionText,
: Answers.AnswerText,

c*o2008-11-13 08:11

4 楼

you should let me know why
what you want me to improve?

【在 c**t 的大作中提到】

: 打回出重写
:
: it?

w*r2008-11-13 08:11

5 楼

Your query sucks, give this a try, you better have index on isdeleted/
deleteds on both questions and
answers table. EV table also needs index. Otherwise, it will suck anyway.
SELECT
Questions.QuestionID,
Questions.QuestionText,
Answers.AnswerText,
Answers.CorrectAnswer,
Answers.PointValue AS AnswerPointValue,
CASE WHEN S1.AnswerID ID NOT NULL
THEN 1
ELSE 0
END AS AnswerUsed
FROM Questions
INNER JOIN
Answers
ON
(Questions.QuestionID = Answers.QuestionID
AND ANSWE

【在 c****o 的大作中提到】

c*o2008-11-13 08:11

6 楼

多谢指教!
讨论一下几个问题
1。你把where里的条件尽量放在了join里，其实我也式过，得到的exe plan 好像一样
，可能sql server自己会做这样的优化(?)
2。我们每个表基本都有isdeleted这样的东西，查询常用到。但index似乎只在key上有
, 我一个developer要求改变数据库结构恐怕不行。我不懂的问一下，这个index重要在
哪里？如果确实必要，我就去找manager问一下...
3。exsists为什么不好？你改过的sql我想要加distinct的，而distinct好像比较
expensive.更麻烦的是distinct有些东西不能比较，比如text类型

【在 w*r 的大作中提到】

: Your query sucks, give this a try, you better have index on isdeleted/
: deleteds on both questions and
: answers table. EV table also needs index. Otherwise, it will suck anyway.
: SELECT
: Questions.QuestionID,
: Questions.QuestionText,
: Answers.AnswerText,
: Answers.CorrectAnswer,
: Answers.PointValue AS AnswerPointValue,
: CASE WHEN S1.AnswerID ID NOT NULL

w*r2008-11-13 08:11

7 楼

some server can optimize the push down predicate better than others,
in your query, I believe all tables listed are "tables" rather than
complicated
business views. In some ad-hoc environment, complicated BI view hide
business logic and the 3-nr model beneath, therefore, when you select from
it and build joins on it, the pushdown predicate may or may not works fine.
This is the reason I tried to get all condition as close as to the join
condition.
since you are testing NULL for several columns,

【在 c****o 的大作中提到】

: 多谢指教!
: 讨论一下几个问题
: 1。你把where里的条件尽量放在了join里，其实我也式过，得到的exe plan 好像一样
: ，可能sql server自己会做这样的优化(?)
: 2。我们每个表基本都有isdeleted这样的东西，查询常用到。但index似乎只在key上有
: , 我一个developer要求改变数据库结构恐怕不行。我不懂的问一下，这个index重要在
: 哪里？如果确实必要，我就去找manager问一下...
: 3。exsists为什么不好？你改过的sql我想要加distinct的，而distinct好像比较
: expensive.更麻烦的是distinct有些东西不能比较，比如text类型
:

z*y2008-11-13 08:11

8 楼

Can you run the following script and send me the result...or just post here:
SET SHOWPLAN_ALL ON;
GO
your query here:
SET SHOWPLAN_ALL OFF;
GO
单从表面看，可能是有两个或两个以上的hash join, 另外你可能有index scan。这
都说不准。你把结果贴出来，大家给你搂搂。

it?

【在 c****o 的大作中提到】

j*n2008-11-13 08:11

9 楼

try to use UNION ALL

c*o2008-11-13 08:11

10 楼

thank you!
in fact this query is an abbreviated one from another query. but i include
the main skeleton.
the format will be very ugly if i post the exe plan directly. so i am using
some attachments.
planforthis.xls is for the query i posted
fullquery.sql is the original query
planfull.xls is the plan for the fullquery.

here:

【在 z***y 的大作中提到】

: Can you run the following script and send me the result...or just post here:
: SET SHOWPLAN_ALL ON;
: GO
: your query here:
: SET SHOWPLAN_ALL OFF;
: GO
: 单从表面看，可能是有两个或两个以上的hash join, 另外你可能有index scan。这
: 都说不准。你把结果贴出来，大家给你搂搂。
:
: it?

c*o2008-11-13 08:11

11 楼

你们是不是经常要看这个plan？幸亏我不是专门做这个不然怎么活呀。。。
index scan 和 hash join 都应该避免？很难吧。。。
加index基本不大可能也不懂。对改这个query不是很乐观。。。可能最后结果会把丫的拆了返回结果

here:

【在 z***y 的大作中提到】

c*o2008-11-13 08:11

12 楼

我只在table上操作，没有view的级别亚
我刚才看天书一样看了一下exe plan，好像是像你说的，where里的东西自己优化到
join里了。
至于exists好像也用join自己优化了，而且sql server不知怎么可以用到返回scalar的
东西，所以应该比自己用distinct好点。这个answerused字段是判断有没有evaluation
用过这个answer，所以当有若干evaluation用过这个answer的时候就会返回多次，所以
按你的该法需要distinct.
关于index的问题我得再研究研究。。

.

【在 w*r 的大作中提到】

: some server can optimize the push down predicate better than others,
: in your query, I believe all tables listed are "tables" rather than
: complicated
: business views. In some ad-hoc environment, complicated BI view hide
: business logic and the 3-nr model beneath, therefore, when you select from
: it and build joins on it, the pushdown predicate may or may not works fine.
: This is the reason I tried to get all condition as close as to the join
: condition.
: since you are testing NULL for several columns,

c*o2008-11-13 08:11

13 楼

在哪儿用union all?thanks!

【在 j*****n 的大作中提到】

: try to use UNION ALL

z*y2008-11-13 08:11

14 楼

给你回了。
这个得一步步来。如果方向对头，再进行下一步。

using

【在 c****o 的大作中提到】

: thank you!
: in fact this query is an abbreviated one from another query. but i include
: the main skeleton.
: the format will be very ugly if i post the exe plan directly. so i am using
: some attachments.
: planforthis.xls is for the query i posted
: fullquery.sql is the original query
: planfull.xls is the plan for the fullquery.
:
: here: