query analyzer VS. Stored procedure - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

query analyzer VS. Stored procedure

query analyzer VS. Stored procedure# Database - 数据库

j*n2008-04-02 07:04

1 楼

今儿我们组里的一哥们遇到一个奇怪的现象：
在query analyzer 里面运行一个query,花了15分钟得到结果，
然后放到把这个query做成一个stored procedure，除了把where 里面的条件换成了变
量而已，结果需要30分钟才行。
我们的环境是用local的query analyzer 连到 DB server 上。
为什么会有这么大的区别呢？

w*e2008-04-02 07:04

2 楼

那sproc里有啥?

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

: 今儿我们组里的一哥们遇到一个奇怪的现象：
: 在query analyzer 里面运行一个query,花了15分钟得到结果，
: 然后放到把这个query做成一个stored procedure，除了把where 里面的条件换成了变
: 量而已，结果需要30分钟才行。
: 我们的环境是用local的query analyzer 连到 DB server 上。
: 为什么会有这么大的区别呢？

j*n2008-04-02 07:04

3 楼

简单点说吧：
in QA:
SELECT * FROM tbl where startDate BETWEEN '2001-1-1', '2001-6-1'
in SP:
SELECT * FROM tbl where startDate BETWEEN @startDate, @endDate

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

: 那sproc里有啥?

c*t2008-04-02 07:04

4 楼

在 PostgreSQL 上，prepared plan 可能会比直接叫 sql 慢：
There is a disadvantage to using parameters: since the planner does
not know the values that will be supplied for the parameters, it might
make worse planning choices than it would make for a normal command
with all constants visible.

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

: 简单点说吧：
: in QA:
: SELECT * FROM tbl where startDate BETWEEN '2001-1-1', '2001-6-1'
: in SP:
: SELECT * FROM tbl where startDate BETWEEN @startDate, @endDate

j*n2008-04-02 07:04

5 楼

it would not be the case in MSSQL. except dynamic SQL, SP is pre-compiled
and the execution plan is fixed unless statistics changes.

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

: 在 PostgreSQL 上，prepared plan 可能会比直接叫 sql 慢：
: There is a disadvantage to using parameters: since the planner does
: not know the values that will be supplied for the parameters, it might
: make worse planning choices than it would make for a normal command
: with all constants visible.

c*t2008-04-02 07:04

6 楼

No, you misunderstood. Compiled plan doesn't mean its execution plan
would be exactly the same as the direct SQL query execution plan. The
difference thus can lead to performance difference.
I'd suggest you to write a stored procedure that does direct query
and compare.

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

: it would not be the case in MSSQL. except dynamic SQL, SP is pre-compiled
: and the execution plan is fixed unless statistics changes.

j*n2008-04-02 07:04

7 楼

I did not... I checked both execution plans, they are same...
however, the duration is different.

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

: No, you misunderstood. Compiled plan doesn't mean its execution plan
: would be exactly the same as the direct SQL query execution plan. The
: difference thus can lead to performance difference.
: I'd suggest you to write a stored procedure that does direct query
: and compare.

a*t2008-04-02 07:04

8 楼

parameter sniffing
it's a pretty oftenly seen problem but it's not very commonly known... M$ is
hidding this from the public...
one of the articles talked about it
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
bascially, you can create a local variable
create procedure usp_myProc
@startdate_in datetime
as
declare @startdate datetime
set @startdate = @startdate_in

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

a*t2008-04-02 07:04

9 楼

you can also force the SP to be recompiled if you think the plan is out-
dated
but I suspect your problem is not about this

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

: it would not be the case in MSSQL. except dynamic SQL, SP is pre-compiled
: and the execution plan is fixed unless statistics changes.

j*n2008-04-02 07:04

10 楼

yeah, that's should be the right answer.
thanks a lot.

is

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

: parameter sniffing
: it's a pretty oftenly seen problem but it's not very commonly known... M$ is
: hidding this from the public...
: one of the articles talked about it
: http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
: bascially, you can create a local variable
: create procedure usp_myProc
: @startdate_in datetime
: as
: declare @startdate datetime