Showing posts with label required. Show all posts
Showing posts with label required. Show all posts

Tuesday, March 20, 2012

Quer runs well on its own but when put into stored procedure, it runs slowly

Hi,

Appreciate if any one can provide some pointers on this issue on a stored procedure that I have.

The required indexes (both clustered and non-clustered) have been created in the database tables.

When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and returns the results back within seconds.

However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

When the execution plan is examined for both, they are both different and it appears that when executing the stored procedure, it doesn't make use of the indexes, whereas in the first case of executing the same T-SQL statements directly, it does make use of the indexes.

We tried using the WITH RECOMPILE attribute to refresh the execution plan, but it doesn't seem to work for the stored procedure.

Wonder if anyone got any ideas or encounter anything similar?

Thanks,

Desmond

Can you post the SP and the DDL/Indexes?

|||

Hi Desmond T,

> When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and

> returns the results back within seconds.

> However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

Can you show us some light, please?

Look in BOL for auto-parameterization and also about the statistics used by the query optimizer. If you executed the "select" statement from QA or SSMS, using variables to simulate the parameters in the stored procedure, like in:

Code Snippet

use northwind

go

select orderid, customerid, orderdate

into dbo.t1

from dbo.orders

go

create index t1_orderdate_nu_nc_ix

on dbo.t1(orderdate)

go

dbcc show_statistics('dbo.t1', 't1_orderdate_nu_nc_ix') with STAT_HEADER, DENSITY_VECTOR, HISTOGRAM

go

create procedure dbo.usp_p1

@.orderdate datetime

as

set nocount on

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

declare @.orderdate datetime

set @.orderdate = '19960704'

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

exec dbo.usp_p1 '19960704'

go

drop table dbo.t1

go

drop procedure dbo.usp_p1

go

They seem to be the same, but the query optimizer uses different statistics to estimate cardinality and based on those statistics, it will choose an execution plan. In the sp case, the query procesor will use the values from the histogram, but for the query, it will use the "All density" value for that column and could be that those values yield different execution plan.

In my box, I got these values for "Estimated Number of Rows".

sp --> 1

query using variables --> 1.72917 (0.002083333 * 830) See "All density" value from the result of dbcc

The query optimizer chose to scan the table for the query using variables and an "index seek" operation followed by bookmark lookup for the sp.

One way of testing the query in QA / SSMS is using sp_executesql, parameterizing the statement.

Code Snippet

declare @.sql nvarchar(4000)

set @.sql = N'select orderid, customerid, orderdate from dbo.t1 where orderdate = @.orderdate'

exec sp_executesql @.sql, N'@.orderdate datetime', '19960704'

go

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB

|||hi,

i had a similar problem before, can you post your proc?, i resolved my problem by tweaking the proc a bit.

/rh4m1ll3

Monday, March 12, 2012

QP is missing and it is required to evaluate default expressions?

Hi everyone

I have a really odd problem that has manifested its self. I have a Windows Mobile 6 device that I debugging my application on, the application uses a local Sql Compact sdf file, if I just run the app, when it comes to executing some ado.net against the database I get the following error:-

QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)

If however I open the the database using query analyzer first on the device then everything is ok until I next soft reset the device

Has anyone else come across this?

Thanks in advance

Dan

Could be some out-of-memory issue. Are both the mentioned files present in the \Windows folder on the device. (use the device File manager to check, remember to select "Show All Files" from the menu.)|||

Hi

Yeah all the files are present and correct. Its really odd, it seems once the connection has been made by something else its happy.

|||I'm having the same issue...|||Did you find the solution for this problem? We are getting this problem too.

Rajesh