October 26, 2004 at 2:25 pm
I`m experiencing some problems when running a query called by sp_executeSQL. When this happens, the query runs for more than 1 hour (I never could wait till its end). But if I run the same query without being called by sp_executeSQL, it takes about 20s. This query runs only once a day, and looking in the syscacheobjects, I couldn`t find its cached exec plan.
In my experiments I could observe that this can be fixed updating statistics, but I don`t understand why this is happening... Would someone help me out?
exec sp_executesql N'select
VW_1.Field_1,
VW_1.Field_2,
VW_1.Field_3,
VW_1.Field_4,
VW_1.Field_5,
VW_1.Field_6,
VW_1.Field_7,
VW_1.Field_8,
VW_1.Field_9,
VW_1.Field_10,
VW_1.Field_11,
VW_1.Field_12,
VW_1.Field_13,
case
when TB_1.Field_1 like ''%CDB%'' and VW_1.Field_5 = 1 then 1
when TB_1.Field_1 like ''%CDI%'' and VW_1.Field_5 = 1 then 2
end as TpoPapel
from
VW_1
join
(SELECT
Field_1 DtaSaldo,
Field_2 SeqTitulo
FROM
VW_2
WHERE
Field_3 CodInstituicao = @P1 AND
Field_4 DtaSaldo = @P2
GROUP BY
Field_4 DtaSaldo,
Field_2 SeqTitulo) AS InnerQry SaldoEstoque
on
VW_1.Field_1 = InnerQry.Field_2 and
VW_1.Field_14 = InnerQry.Field_1
join
TB_1
on
VW_1.Field_15 = TB_1.Field_1
where
VW_1.Field_14 = @P3 and
VW_1.Field_14 between VW_1.Field_2 and VW_1.Field_3 - 1 and
VW_1.Field_6 <= 2 and
(@P4 <= 0 or coalesce(VW_1.Field_1, -1) = @P5)
', N'@P1 int,@P2 datetime,@P3 datetime,@P4 int,@P5 int', 43, 'Oct 21 2004 12:00:00:000AM', 'Oct 21 2004 12:00:00:000AM', 0, 0
October 26, 2004 at 3:29 pm
I am shooting in the breeze here, but it looks like you are using Views and not actual tables. Dynamic SQL cannot generate a plan in the SQL Server, that plus the execution of Views in its own SPID may be the reason...
Can you try printing this and seeing how well that created SQL runs?
I wasn't born stupid - I had to study.
October 26, 2004 at 3:53 pm
Yes, i`m querying views and tables... Looking in the syscacheobjects table, after running the query a few times, here is the return:
cacheobjtype objtype uid refcounts usecounts pagesused setopts
------------ -------- --- --------- --------- --------- -------
Compiled Plan Prepared 1 1 3 34 4345
status sqlbytes
------ --------
0 3788
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply