Execution/compilation

What is the difference beteween execution och compilation in SQL Server?
I suspect that SELECT-statements aren't compiled while CREATE
TABLE-statements are? Is that correct?Have I understood you right if Query execution is the same as Query
compilation?
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F4760AF.C87F25CD@.toomuchspamalready.nl...
> When you submit a SELECT statement, SQL-Server will go through different
> stages before it returns the resultset.
> Parsing: first the statement will be analysed to see if it is
> syntactically correct, and if all object names can be found in the
> schema.
> Query optimizing: during this stage, SQL-Server will create execution
> plans that will give the correct result. There are usually many
> different query plans that give the same result, but one query plan
> might be much more efficient than the other. It is also possible, that
> the query plan of an earlier compilation is still in cache. In that
> case, this fase is skipped.
> Query executing: the fastest query plan will be executed. This is where
> the actual rows are read from disk (or buffer cache) and all 'real'
> processing is done.
> The steps above are all relevant for DML (such as SELECT, UPDATE, etc.)
>
> When it comes to DDL (such as CREATE TABLE statements), it is not
> possible to optimize the query. The statements are straightforward and
> can be executed immediately after parsing.
> Gert-Jan
>
> Stefan wrote:
> >
> > What is the difference beteween execution och compilation in SQL Server?
> >
> > I suspect that SELECT-statements aren't compiled while CREATE
> > TABLE-statements are? Is that correct?

Execution 'xxxxxx' not found

I am having an issue where I can't get any reports to run no matter how
simple (eliminating the possibility of it being a session timeout problem).
I constantly get an execution not found error as soon as the report starts to
run. It seems to make no difference where I run any report from or if it is
in Sharepoint integrated mode or not.On Sep 10, 1:34 pm, MP <M...@.discussions.microsoft.com> wrote:
> I am having an issue where I can't get any reports to run no matter how
> simple (eliminating the possibility of it being a session timeout problem).
> I constantly get an execution not found error as soon as the report starts to
> run. It seems to make no difference where I run any report from or if it is
> in Sharepoint integrated mode or not.
This link might be helpful.
http://blogs.msdn.com/sharepoint/archive/2007/08/02/microsoft-sql-server-reporting-services-installation-and-configuration-guide-for-sharepoint-integration-mode.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Tried everything I could think of and I couldn't track this down. In the end
I think it has something to do with the fact that the WebUsersGroup name is
too long and it replaced it with a id.
The weird thing is when I use a webpart and view the reports that way they
work.
"MP" wrote:
> I am having an issue where I can't get any reports to run no matter how
> simple (eliminating the possibility of it being a session timeout problem).
> I constantly get an execution not found error as soon as the report starts to
> run. It seems to make no difference where I run any report from or if it is
> in Sharepoint integrated mode or not.
>

Execution TIme Problem

I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too.

Hope to get ur response soon.

Hello romita

Do you mind sharing some code. It would be helpful to try and figure out the problem

Also are you able to run SQL Profiler to check that statments that are hitting the databse from the aps.net application.

regards

G

|||

Hi Gonzo11,

Regarding the same problem posted yesterday, am enclosing the code of procedure below for ur reference. Please check n let me know if theres any solution.

CREATE PROCEDURE proc_userroyalty
@.month int, @.year int, @.joins int output, @.royalties int output, @.royaltyamt decimal output, @.netamt decimal output

as

declare @.joinamt bigint, @.user varchar(50), @.left int, @.middle int, @.right int, @.royalty decimal, @.regdate datetime , @.totalpairs int, @.lsdate varchar(50), @.bdate datetime, @.maxamt decimal, @.nxtmon int, @.totalroyal decimal, @.nxtdt datetime, @.nxtyr int

if exists(select * from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year and @.month<month(getdate()) and @.year<=year(getdate()) )
begin
select @.joins = count(*) from db_users where month(us_regdate)=@.month and yeaR(us_regdate)=@.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year
if @.month<8 and @.year <=2007
begin
select @.joinamt=@.joins * 100
end
else
begin
select @.joinamt=@.joins * 120
end

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year group by bd_amount

end
else
begin
delete from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

select @.joins=count(*) from db_users where datepart(month,us_regdate) = @.month and datepart(year,us_regdate) = @.year and us_status='Y'

exec proc_royal @.month, @.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year

select @.joinamt=@.joins * 120

if @.royalties > 0
begin

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.lsdate=str(@.month) + '-' + '28' + '-' + str(@.year)
select @.bdate=convert(datetime,@.lsdate)


declare cur_bonus cursor
for select temp_user,temp_regdt from db_tempbonus where temp_month<=@.month andtemp_year=@.year

open cur_bonus
fetch next from cur_bonus into @.user,@.regdate

while @.@.fetch_status = 0
begin
select @.nxtdt=dateadd(mm,3,@.regdate)

if datediff(dd,@.nxtdt,getdate()) > 0
begin
select @.nxtmon=month(@.nxtdt)
select @.nxtyr=year(@.nxtdt)

SELECT @.totalpairs= SUM(mp_lcount) + SUM(mp_mcount) + SUM(mp_rcount)
FROM db_monthlypairs
WHERE (mp_month<=@.nxtmon ) and (mp_year <=@.nxtyr) AND (mp_user = @.user)

if @.totalpairs< 30
begin
select @.maxamt=25000
end
else
begin
select @.maxamt=50000
end
end
else
begin
select @.maxamt=50000
end

select @.totalroyal=sum(bd_amount) from db_bonusdetails wherebd_user=@.user and bd_purpose='R'

if @.totalroyal <@.maxamt or @.totalroyal is null
begin
insert into db_bonusdetails values(@.bdate,@.user,'R',@.royaltyamt)
end

fetch next from cur_bonus into @.user,@.regdate
end

close cur_bonus
deallocate cur_bonus

select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

end
else
begin
select @.royalties=0
select @.royaltyamt=0
select @.netamt=0
end
end
GO

PROCEUDRE 2 - proc_royal to be executed within above one.

CREATE PROCEDURE proc_royal
@.month int , @.year int
AS

declare @.user varchar(50), @.regdt datetime, @.lcount int , @.mcount int, @.rcount int, @.uspos char(1), @.rows int, @.mainuser varchar(50), @.regdate datetime

declare cur1 cursor
for select mp_user, us_regdate from db_users,db_monthlypairs where mp_user=us_login and mp_month<=@.month and mp_year<=@.year and mp_user not in(select temp_user from db_tempbonus where temp_month<=@.month and temp_year<=@.year)
group by mp_user, us_regdate having sum(mp_lcount)>=1 and sum(mp_mcount) >=1 and sum(mp_rcount)>=1
open cur1
fetch cur1 into @.mainuser, @.regdate
while @.@.fetch_status=0
begin
declare cur2 cursor

for select us_login, us_regdate, us_position from db_users whereus_reference=@.mainuser and month(us_regdate) <= @.month and year(us_regdate)<=@.year
open cur2
fetch cur2 into @.user, @.regdt, @.uspos
while @.@.fetch_status=0
begin
if @.uspos ='L'
begin
select @.lcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.lcount = @.lcount + 1
end
else
begin
if @.uspos ='M'
begin
select @.mcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.mcount = @.mcount + 1
end
else
begin
select @.rcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.rcount = @.rcount + 1
end

end
fetch cur2 into @.user, @.regdt, @.uspos
end

close cur2
deallocate cur2

if @.lcount>=4 and @.mcount>=4 and @.rcount >=4
begin
insert into db_tempbonus values(@.mainuser, @.regdate, @.month, @.year)
end
fetch cur1 into @.mainuser, @.regdate
end

close cur1
deallocate cur1
GO

proc_royal is to add new members satisfying the condtitions to a table eah month. In the main procedure based on all records in this table that many records will be inserted in another one. This part came as updation in the project, so its very risky to change other codes as it might affect existing program.

Hope u got the idea of the procedure.

If theres any simple way with less execution time please let me know. By each month records can increase too, so solution has to be one for furture user too.

Awaiting ur response,

Regards,

Romita

|||

Basedon your post, the error occurs when the execution time beyond TimeoutProperty. When you are trying to connect or access to a Database tablewhich is having large volume of data, query execution time will bemore. There are two main Timeout property in ADO.NET.

1.Connection Timeout for Connection. It could be solved by settingConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can setCommandTimeoutproperty to Command object.I recommend you set CommandTimeOut propertyto bigger one value. Try this.

Please let me know whether this canhandle this problem or not.If you have any further questions, please feel free to let me know.

|||

Hello Romita,

Thank you for you reply. I will need some time to go over you SP.

In the meantime are you able to run the SQL Profiler and check what is actually hitting the DB and what is taking the longest time.

regards,

G

|||

Hi!

I have tried setting command timeout and connection timeout properties, but still this Time Out Expired exists. Just setting the Timeout property of each object is enough or some other changes are also reqd.

Actually i had tried executing the procedure without calling another procedure within, it didn;t solve my problem. Time is taking to insert 400-500 records at a time, but it cannot be programmed in other way too.

Pls let me know, if theres more to do in Timeout settings or if theres another solution.

Regards,

Romita

|||

Hello Romita,

I have looked at the code that you have sent but i fyou say that it is inserting 400-500 rows that generaly should not take a very long time.

You have to see which parts of the SP are taking up most of the time and address them first.

So, you would have to run SQL Server Profiler, that will help you identify what is hitting the database and what is taking the most time.
Once you identify that you can try to address that issue. For example, very often creating an index for a table ususaly helps.

Hope this helps

regards,
G

Execution Time Out in Sql Reporting Service 2000

Hi

How do I set the Report execution Time Out Property of Report Server using ReportingService.asmx.I am using Sql Reporting Services 2000?

I don't beleive it has changed in 2005 so you should just call SetSystemProperties setting SystemReportTimeout.

See info here:

http://msdn2.microsoft.com/en-us/library/ms155025.aspx

Execution time of outer join versus inner

I've got two "heavy" queries here below, that I think should give the same
result.
I expected the first to be slower, and Query Analyzer agrees with me in the
estimated cost in the execution plans it generates for the two queries, but
in reality it's quite the opposite - now I'd like to know why ;)
What they do is join a large table onto itself, with a subquery on the same
table in the join conditions.
The table has 988,123 rows, LogIndex is primary key, CardNo is indexed.
The difference is in the method of the join: INNER versus LEFT OUTER.
Query 1:
- estimated by Query Analyzer: cost 12015, rows: 312617
- reality: finishes in 19 seconds, returns 3148 rows
SELECT {some fields}
FROM CardLog t1 LEFT OUTER JOIN CardLog t2
ON t1.CardNo = t2.CardNo
AND ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
FROM CardLog T3
WHERE t3.cardno = t1.cardno
AND t3.logindex > t1.logindex)
WHERE (t2.[Action] IN (2, 3, 4, 5))
ORDER BY t2.LogIndex
Query 2:
- estimated by Query Analyzer: cost 245, rows: 1325
- reality: doesn't finish in 17 minutes (that's the longest I let it run)
SELECT {some fields...}
FROM CardLog t1 INNER JOIN CardLog t2
ON t1.CardNo = t2.CardNo
AND ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
FROM CardLog T3
WHERE t3.cardno = t1.cardno
AND t3.logindex > t1.logindex)
WHERE (t2.[Action] IN (2, 3, 4, 5))
ORDER BY t2.LogIndexHi
Have you looked at the actual execution plans to see what is happening?
You may want to also make sure that the statistics are up-to-date.
You may also want to look at adding t2.logindex > t1.logindex to your join
criteria and seeing if that makes a difference and also possibly seeing if a
composite index helps. You may want to check that moving to the where clause
does not have any effect ABS(t1.Credit - t2.Credit) <> t2.Amount
John
"Lucvdv" <replace_name@.null.net> wrote in message
news:9c6rh1dgk3332ot7amhig4bromis937a4g@.
4ax.com...
> I've got two "heavy" queries here below, that I think should give the same
> result.
> I expected the first to be slower, and Query Analyzer agrees with me in
> the
> estimated cost in the execution plans it generates for the two queries,
> but
> in reality it's quite the opposite - now I'd like to know why ;)
> What they do is join a large table onto itself, with a subquery on the
> same
> table in the join conditions.
> The table has 988,123 rows, LogIndex is primary key, CardNo is indexed.
> The difference is in the method of the join: INNER versus LEFT OUTER.
> Query 1:
> - estimated by Query Analyzer: cost 12015, rows: 312617
> - reality: finishes in 19 seconds, returns 3148 rows
> SELECT {some fields}
> FROM CardLog t1 LEFT OUTER JOIN CardLog t2
> ON t1.CardNo = t2.CardNo
> AND ABS(t1.Credit - t2.Credit) <> t2.Amount
> AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
> FROM CardLog T3
> WHERE t3.cardno = t1.cardno
> AND t3.logindex > t1.logindex)
> WHERE (t2.[Action] IN (2, 3, 4, 5))
> ORDER BY t2.LogIndex
> Query 2:
> - estimated by Query Analyzer: cost 245, rows: 1325
> - reality: doesn't finish in 17 minutes (that's the longest I let it run)
> SELECT {some fields...}
> FROM CardLog t1 INNER JOIN CardLog t2
> ON t1.CardNo = t2.CardNo
> AND ABS(t1.Credit - t2.Credit) <> t2.Amount
> AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
> FROM CardLog T3
> WHERE t3.cardno = t1.cardno
> AND t3.logindex > t1.logindex)
> WHERE (t2.[Action] IN (2, 3, 4, 5))
> ORDER BY t2.LogIndex|||Try to separate the subquery using a temporary table, like this:
CREATE TABLE #CardLogSequence (
LogIndex int PRIMARY KEY,
NextLogIndex int NOT NULL UNIQUE
)
INSERT INTO #CardLogSequence
SELECT LogIndex, NextLogIndex
FROM (
SELECT t1.LogIndex, (
SELECT MIN(t3.LogIndex)
FROM CardLog t3
WHERE t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
) as NextLogIndex
FROM CardLog t1
) X WHERE NextLogIndex IS NOT NULL
SELECT {some columns...}
FROM #CardLogSequence s
INNER JOIN CardLog t1 ON t1.LogIndex=s.LogIndex
INNER JOIN CardLog t2 ON t2.LogIndex=s.NextLogIndex
WHERE ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.Action IN (2,3,4,5)
ORDER BY t2.LogIndex
DROP TABLE #CardLogSequence
(the above code is untested, because you didn't provide DDL as CREATE
TABLE statements)
What times are you getting with the above queries ?
Razvan|||On 7 Sep 2005 02:22:01 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> Try to separate the subquery using a temporary table, like this:
<snip>
> What times are you getting with the above queries ?
1 minute 45 seconds on the first run, 32 seconds if I restart it
immediately.
The LEFT version without a subquery still finishes in 17 seconds.
I had expected some difference when running the query a second time
(everything cached in RAM), but not that much.|||On Wed, 7 Sep 2005 10:10:00 +0100, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:

> Hi
> Have you looked at the actual execution plans to see what is happening?
Yes, and even there it looks like the LEFT version should be slower
(looking at what it's doing and how many times).

> You may want to check that moving to the where clause
> does not have any effect ABS(t1.Credit - t2.Credit) <> t2.Amount
I had put it there initially, but that was in Enterprise Manager, and it
moved it into the join criteria by itself when I started the query.
Now I checked it both ways in QA, and the execution plans it generates are
identical.|||Execute sp_updatestats to make sure the statistics are up-to-date.
Which of the two statements in my previous post is taking more time
(the insert or the final select) ?
Please post the execution plan, obtained using SET SHOWPLAN_TEXT. The
graphical execution plan produced by Query Analyzer would be easier to
understand (and contains more information, like the number of rows
affected by each step), but make sure it's a small file (preferably,
PNG) if you post it as an attachment.
Razvan|||On 7 Sep 2005 22:10:37 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> Execute sp_updatestats to make sure the statistics are up-to-date.
Done.

> Which of the two statements in my previous post is taking more time
> (the insert or the final select) ?
The insert takes nearly all. In a few runs:
Insert: 28-31 sec
Total: 35-37 sec
That's after changing to a fixed table instead of a temporary (see below,
at execution plan).

> Please post the execution plan, obtained using SET SHOWPLAN_TEXT. The
> graphical execution plan produced by Query Analyzer would be easier to
> understand (and contains more information, like the number of rows
> affected by each step), but make sure it's a small file (preferably,
> PNG) if you post it as an attachment.
Thanks for the help so far.
But don't waste too much time on it unless you're interested yourself, it's
not as if my job depends on it.
I'll mix some results from the graph into the text version.
The | at the left margin are just there to tell my newsreader not to wrap
those lines, if you have one where you can turn wrapping off it might be
better readable that way.
1) LEFT join:
| |--Parallelism(Gather Streams, ORDER BY:([t2].[LogIndex] ASC))
Estimated row count: 312,617
Estimated subtree cost: 12,041
| |--Sort(ORDER BY:([t2].[LogIndex] ASC))
| |--Filter(WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2)
)
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[Credit], [t1].[LogIndex],
[t1].[CardNo]))
Estimated row count: 988,123
Estimated row size: 110
Estimated CPU cost: 2.07
Estimated number of executes: 1
Estimated cost: 2.065804
Estimated subtree cost: 11989
| |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__C
ardLog__1A14E395] AS [t1]))`
Est row count: 988,123
Est row size: 53
Est I/O cost: 4.15
Est CPU cost: 0.453
Est # executes: 1.0
Est subtree cost: 4.69
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1
003]))
Est row count: 1
Est row size: 66
Est CPU cost: 0.00004
Est # executes: 988,123
Est cost: 5.30
Est subtree cost: 11982
| |--Stream Aggregate(DEFINE:([Expr1003]=MIN([T3].[LogIndex
])))
| | |--Top(1)
| | |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [T3]), SEEK:([T3].[CardNo]=[t1].[
CardNo] AND [T3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est row count: 1
Est row size: 33
Est # executes: 988,123
Est cost: 5789
| |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t2]), SEEK:([t2].[LogIn
dex]=[Expr1003]), WHERE:([t1].[CardNo]=[t2].[CardNo] AND abs([t1].[Credit]-[t2].[Credit])<>[t2].[Amount]) ORDERED
FOR
Est row count: 1
Est row size: 61
Est # executes: 988,123
Est cost: 6188
2) INNER join, my version:
| |--Parallelism(Gather Streams, ORDER BY:([t2].[LogIndex] ASC))
Estimated row count: 1325
Estimated subtree cost: 245
| |--Sort(ORDER BY:([t2].[LogIndex] ASC))
| |--Filter(WHERE:([Expr1003]=[t2].[LogIndex]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[LogIndex], [t1].[CardNo
]))
Est row count: 1,337,987
Est row size: 62
Est CPU cost: 2.80
Est # executes: 1
Est cost: 2.80
Est subtree cost: 244
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([t1].[LogIndex]
, [t1].[CardNo]))
Est row count: 1,337,987
Est row size: 58
Est CPU cost: 12.1
Est # executes: 1
Est cost: 12.0
Est subtree cost: 119
| | |--Hash Match(Inner Join, HASH:([t2].[CardNo])=([t1].[CardNo]), RESIDUAL:(abs([t1].[Cre
dit]-[t2].[Credit])<>[t2].[Amount]))
Est CPU cost: 90.6
Est cost: 90.6
Est subtree cost: 107
| | |--Parallelism(Repartition Streams, PARTITION COLU
MNS:([t2].[CardNo]))
| | | |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395]
AS [t2]), WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2))
Est row count: 312,617
Est row size: 61
Est CPU cost: 0.543
Est I/O cost: 4.15
Est cost: 4.69
| | |--Parallelism(Repartition Streams, PARTITION COLU
MNS:([t1].[CardNo]))
| | |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[
CardLog].[PK__CardLog__1A14E395] AS [t1]))
Est row count: 988,123
Est row size: 53
Est I/O cost: 4.15
Est CPU cost: 0.543
Est cost: 4.69
| |--Hash Match(Cache, HASH:([t1].[LogIndex], [t1].[CardNo]), RESIDUAL:([t1].[LogIndex]=[t1].[LogIndex] AND
[t1].[CardNo]=[t1].[CardNo]))
Est row count: 1
Est row size: 11
Est CPU cost: 7.73
Est # executes: 1,339,789
Est cost: 13.8
Est subtree cost: 122
| |--Stream Aggregate(DEFINE:([Expr1003]=MIN([T3].[LogIndex
])))
| |--Top(1)
| |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [T3]), SEEK:([T3].[CardNo]=[t1].[
CardNo] AND [T3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est row count: 1
Est row size: 33
Est I/O cost: 0.00632
Est CPU cost: 0.000360
Est # executes: 18529
Est cost: 109
(3) Temporary table
I couldn't get it to work with a temporary table, it gave "Invalid object
name" when trying to get an execution plan.
I worked around that by creating it as a normal table, and TRUNCATEing it
before the SELECT INTO.
Creating (filling) the table: estimated cost 5873.
| |--Index Insert(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[UQ__CLSEQ__47FBA9D6]), SET:([LogIndex1009]=[CLSEQ].[LogIndex], [NextLog
Index1008]=[CLSEQ].[NextLogIndex], [IdxBmk1007]=[Bmk1006]))
Est Cost: 6.29, subtree: 5873
| |--Sort(ORDER BY:([CLSEQ].[NextLogIndex] ASC))
Est Cost: 66.15, subtree: 5867
| |--Clustered Index Insert(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[PK__CLSEQ__4707859D]), SET:([CLSEQ].[NextLogIndex]=[Expr1002]
, [CLSEQ].[LogIndex]=[t1].[LogIndex]))
Est cost: 1.00, subtree: 5801
| |--Top(ROWCOUNT est 0)
| |--Parallelism(Gather Streams, ORDER BY:([t1].[LogIndex] ASC))
Est cost: 3.58, subtree: 5800
| |--Filter(WHERE:([Expr1002]<>NULL))
Est cost: 0.23, subtree: 5796
| |--Compute Scalar(DEFINE:([Expr1002]=[Expr
1002]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[Log
Index], [t1].[CardNo]))
Est cost: 2.07, subtree: 5796
| |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[d
bo].[CardLog].[PK__CardLog__1A14E395] AS [t1]), ORDERED FORWARD)
Est cost: 4.69
| |--Stream Aggregate(DEFINE:([Expr1002]=MIN
([t3].[LogIndex])))
Est cost: 0.0986, subtree: 5789
| |--Top(1)
| |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [t3]), SEEK:([t3].
[CardNo]=[t1].[CardNo] AND [t3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est cost: 5789
Final select query: estimated cost 0.0634
| |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[LogIndex], [t2].[Amount], [t2].[Credit]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[NextLogIndex]))
| | |--Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[UQ__CLSEQ__47FBA9D6] AS [s])
, ORDERED FORWARD)
| | |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t2]), SEEK:([t2].[LogIndex]=[s].[NextLogIndex])
, WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2) ORDERED FORWARD)
| |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t1]), SEEK:([t1].[LogIndex]=[s].[LogInde
x]), WHERE:(abs([t1].[Credit]-[t2].[Credit])<>[t2].[Amount]) ORDERED FORWARD)|||1. Create a unique index on (CardNo, LogIndex), run sp_updatestats
again and re-try all three queries (I believe your IxCardLogCard index
on these columns, but it is not unique);
2. Also try this query:
INSERT INTO #CardLogSequence
SELECT t1.LogIndex, MIN(t3.LogIndex)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
GROUP BY t1.LogIndex
3. The actual execution plan produced by Query Analyzer is much more
useful than the estimated query plan (use Ctrl+K instead of Ctrl+L),
because when a query performs poorly it's probable that this is due to
wrong estimations of row counts (so we need the actual row counts)
4. Is is true that the condition "t2.Action IN (2,3,4,5)" greatly
restricts the number of rows (i.e. the number of rows that satisfy this
condition is much lower than the total number of rows) ? If so, you
should try moving this condition to the first query (i.e: add "AND
t3.Action IN (2,3,4,5)")
Razvan|||On 8 Sep 2005 22:28:45 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> 1. Create a unique index on (CardNo, LogIndex), run sp_updatestats
> again and re-try all three queries (I believe your IxCardLogCard index
> on these columns, but it is not unique);
That's correct, the index on CardNo can not be unique.
I created the unique index and updated the stats with "sample all rows".
Execution times remain the same (I aborted the INNER join after 6 minutes).
The original LEFT join actually went up to 19 seconds (from 17).
I also tried a (non-unique) index on CardNo/Action/Amount/Credit, it
doesn't change anything either.
That could be because all of the table's fields are in the query (at least
in the SELECT clause), scanning the table shouldn't be much harder than
scanning a 4-column index.

> 2. Also try this query:
> INSERT INTO #CardLogSequence
> SELECT t1.LogIndex, MIN(t3.LogIndex)
> FROM CardLog t1 INNER JOIN CardLog t3
> ON t3.CardNo = t1.CardNo
> AND t3.LogIndex > t1.LogIndex
> GROUP BY t1.LogIndex
It seems to go the same way as my own INNER join version.
I aborted it after 3 minutes and some seconds.
I think I know why it performs so badly though, see below.

> 3. The actual execution plan produced by Query Analyzer is much more
> useful than the estimated query plan (use Ctrl+K instead of Ctrl+L),
> because when a query performs poorly it's probable that this is due to
> wrong estimations of row counts (so we need the actual row counts)
I'm being called away into a meeting, I'll look into this later.

> 4. Is is true that the condition "t2.Action IN (2,3,4,5)" greatly
> restricts the number of rows (i.e. the number of rows that satisfy this
> condition is much lower than the total number of rows) ? If so, you
> should try moving this condition to the first query (i.e: add "AND
> t3.Action IN (2,3,4,5)")
No. It filters out less than half of the results.
Most of it is done by the ABS() condition.
Looking at the queries, this part of the join:
SELECT count(*)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
would return a *huge* number of rows (this query actually caused an
arithmetic overflow).
When I add the next line, it also seems to run forever:
SELECT count(*)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
Could the problem be the high frequency of some CardNo values blowing up
intermediate results?
There are 1715 total, but some occur a lot more than others.
The top 5 occur more than 13,000 times each; the halfway point is 255
times, and about 1/3 occur less than 100 times.
BTW, just in case it wasn't clear from my first post: I _expected_ long
execution times, and 17 or 19 seconds is better than I hoped for.
I was only wondering why changing "LEFT" into "INNER" made such an enormous
difference.|||Hi
Why do you need to use ABS in
ABS(t1.Credit - t2.Credit) <> t2.Amount
Try t1.Credit <> t2.Amount + t2.Credit
and index Amount an Credit
John
"Lucvdv" <replace_name@.null.net> wrote in message
news:ud5uh1hm95lm2p3fso23jaqpm4o4q9l81p@.
4ax.com...
> On Wed, 7 Sep 2005 10:10:00 +0100, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>
> Yes, and even there it looks like the LEFT version should be slower
> (looking at what it's doing and how many times).
>
> I had put it there initially, but that was in Enterprise Manager, and it
> moved it into the join criteria by itself when I started the query.
> Now I checked it both ways in QA, and the execution plans it generates are
> identical.

Execution Time is different

I am having a table ObjectDetail which is having following columns
ObjectTypeId, ObjectId,StatusId,StateId,Release, LastStatusUpdDt
ObjectTypeId,ObjectId is Primary Key.
This table contains about 1 million records in it.
1. When i removed the Primary Key and kept the Clustered Index on
ObjectTypeId,ObjectId and LastStatusUpd
the below query is taking about 1.5 sec.
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster SM (NOLOCK) ON SM.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD(nolock) on WO.Id = OD.objectid AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = @.ResourceId
AND ( SM.IsInDashboard = 1
OR ( SM.IsTerminal = 0 AND @.SELDATE <= (OD.LastStatusUpdDt +
SM.DaysInDashBoard) )
OR ( SM.IsTerminal = 1 AND @.SELDATE <= OD.LastStatusUpdDt )
But when i created the view and created the clustered index on
ObjectTypeId,ObjectId and LastStatusUpd. It is taking about 300 ms to
350 ms.
I am not understanding why it is taking much time when we define the
index on the table rather than on the view. Does the Execution Time
differs having the index on the table and view ?
*** Sent via Developersdex http://www.examnotes.net ***Well, look at the number of rows in the table and the number of rows returne
d
by the view. Are they the same?
Have you compared the execution plans? Have you traced the execution in SQL
Profiler to more accurately see the difference in CPU time and the number of
reads?
The answer is much closer than you think. :)
ML
http://milambda.blogspot.com/|||ramnadh nalluri (ramnadh_nalluri@.semanticspace.com) writes:
> I am having a table ObjectDetail which is having following columns
> ObjectTypeId, ObjectId,StatusId,StateId,Release, LastStatusUpdDt
> ObjectTypeId,ObjectId is Primary Key.
> This table contains about 1 million records in it.
> 1. When i removed the Primary Key and kept the Clustered Index on
> ObjectTypeId,ObjectId and LastStatusUpd
> the below query is taking about 1.5 sec.
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster SM (NOLOCK) ON SM.Id = WO.StatusId
> INNER JOIN dbo.ObjectDetail OD(nolock) on WO.Id = OD.objectid AND
> OD.ObjectTypeId = 10
> WHERE WO.AssignedTo = @.ResourceId
> AND ( SM.IsInDashboard = 1
> OR ( SM.IsTerminal = 0 AND @.SELDATE <= (OD.LastStatusUpdDt +
> SM.DaysInDashBoard) )
> OR ( SM.IsTerminal = 1 AND @.SELDATE <= OD.LastStatusUpdDt )
> But when i created the view and created the clustered index on
> ObjectTypeId,ObjectId and LastStatusUpd. It is taking about 300 ms to
> 350 ms.
> I am not understanding why it is taking much time when we define the
> index on the table rather than on the view. Does the Execution Time
> differs having the index on the table and view ?
What view? Do you create a view from the query above?
It's very difficult to answer the question without full knowledge of
the tables. (And it may not be that easy, even with that information,
as the optimizer's work is a lot about estimates.) You give some information
on ObjectDetail, but you are silent on the other tables.
Looking at the query, I would maybe prefer to write it as:
SELECT T.Id AS TaskId
FROM dbo.Task T
WHRE EXISTS (SELECT *
FROM dbo.WorkOrder WO
JOIN dbo.StateMaster SM ON SM.Id = WO.StatusId
JOIN dbo.ObjectDetail OD(nolock) on
WO.Id = OD.objectid
AND OD.ObjectTypeId = 10
WHERE T.WorkOrderId = WO.Id
AND WO.AssignedTo = @.ResourceId
AND (SM.IsInDashboard = 1 OR
SM.IsTerminal = 0 AND
@.SELDATE <= OD.LastStatusUpdDt + SM.DaysInDashBoard
OR
SM.IsTerminal = 1 AND @.SELDATE <=
OD.LastStatusUpdDt)
But that is mainly a question of esthetics; to have the query more clearly
express what the purpose is. (And ensure that I don't get duplicates in the
result set.) Performance may be better or worse.
As ML suggestions, comparing query plans, and look at estimates may give
some clues.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execution time

Hi
When I run an sp or a complex query in the query analyzer, it shows a world
spining and an execution time at the bottom while the query is still
working.
Is there a way to get that execution time so I can show it inside a program
code like Delphi?
How does that counter work?
Also for the world pic.
Thanks
It's just a counter. It's not generated by SQL Server, it's purely within
Query Analyzer. It starts ticking when QA submits the query and ends when
the query returns.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:uPZomUi4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi
> When I run an sp or a complex query in the query analyzer, it shows a
world
> spining and an execution time at the bottom while the query is still
> working.
> Is there a way to get that execution time so I can show it inside a
program
> code like Delphi?
> How does that counter work?
> Also for the world pic.
> Thanks
>
|||Gonzalo Torres wrote:
> Hi
> When I run an sp or a complex query in the query analyzer, it shows a
> world spining and an execution time at the bottom while the query is
> still working.
> Is there a way to get that execution time so I can show it inside a
> program code like Delphi?
> How does that counter work?
> Also for the world pic.
> Thanks
You'll need to run your query asychronously from Delphi and then you can
just update a running counter and show any fancy graphic you need to
keep the user interested.
David Gugick
Imceda Software
www.imceda.com

Execution Time

Is there any way to estimate the time required to execute a T-SQL
statement?
I need to set the command time out by calculating the time for T-SQL
statement. Can any one give an example?
-SARADHIPredicted Time:
Use Northwind
SET STATISTICS TIME ON
Select * from Orders
SET STATISTICS TIME OFF
HTH, Jens Suessmeyer.
"Saradhi" <upadrasta@.inooga.com> schrieb im Newsbeitrag
news:eXsjwcmSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Is there any way to estimate the time required to execute a T-SQL
> statement?
> I need to set the command time out by calculating the time for T-SQL
> statement. Can any one give an example?
>
> -SARADHI
>|||I need to get it into a varibale and set the Timeout value for the Command
to execute.....
How can I do it'
-SARADHI
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uVvPkgmSFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Predicted Time:
> Use Northwind
> SET STATISTICS TIME ON
> Select * from Orders
> SET STATISTICS TIME OFF
>
> HTH, Jens Suessmeyer.
> "Saradhi" <upadrasta@.inooga.com> schrieb im Newsbeitrag
> news:eXsjwcmSFHA.1384@.TK2MSFTNGP09.phx.gbl...
>|||Hi
Maybe something like that
CREATE TABLE #Test (col INT)
SET NOCOUNT ON
DECLARE @.dt DATETIME,@.i INT
SET @.dt=GETDATE()
SET @.i=1
WHILE @.i<10000
BEGIN
INSERT INTO #Test VALUES (@.i)
SET @.i=@.i+1
END
SELECT DATEDIFF(second,@.dt,GETDATE()) AS 'Time process'
DROP TABLE #Test
"Saradhi" <upadrasta@.inooga.com> wrote in message
news:OUgyyjmSFHA.3356@.TK2MSFTNGP12.phx.gbl...
> I need to get it into a varibale and set the Timeout value for the Command
> to execute.....
> How can I do it'
> -SARADHI
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:uVvPkgmSFHA.3096@.TK2MSFTNGP12.phx.gbl...
>

Execution time

Hi
When I run an sp or a complex query in the query analyzer, it shows a world
spining and an execution time at the bottom while the query is still
working.
Is there a way to get that execution time so I can show it inside a program
code like Delphi?
How does that counter work?
Also for the world pic.
ThanksIt's just a counter. It's not generated by SQL Server, it's purely within
Query Analyzer. It starts ticking when QA submits the query and ends when
the query returns.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:uPZomUi4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi
> When I run an sp or a complex query in the query analyzer, it shows a
world
> spining and an execution time at the bottom while the query is still
> working.
> Is there a way to get that execution time so I can show it inside a
program
> code like Delphi?
> How does that counter work?
> Also for the world pic.
> Thanks
>|||Gonzalo Torres wrote:
> Hi
> When I run an sp or a complex query in the query analyzer, it shows a
> world spining and an execution time at the bottom while the query is
> still working.
> Is there a way to get that execution time so I can show it inside a
> program code like Delphi?
> How does that counter work?
> Also for the world pic.
> Thanks
You'll need to run your query asychronously from Delphi and then you can
just update a running counter and show any fancy graphic you need to
keep the user interested.
--
David Gugick
Imceda Software
www.imceda.com

Execution speed mystery

I've written a T-SQL routine that computes medians in a real estate application. When I execute the routine from Query Analyzer, it computes about 500 medians/sec for my circumstances. I want to include the routine as one step in a nightly scheduled job, but the code exceeds the size limitation for a single step in SQL Server's job scheduler. And re-formulating the routine to fit isn't feasible.

So, I turned the routine into a stored procedure which I intended to call from the appropriate job step. To my amazement, the 500 medians/sec slowed precipitously to about 35 medians/sec!! Terrible! Then when I executed the stored procedure from Query Analyzer, it ran at 500/sec?!

Does anyone know why the identical code run as a step from within a scheduled job would perform so much more poorly than when run from Query Analyzer?Assuming precautions to be certain you were not just looking at cached vs. non-cached performance were taken, there may still be reasonable explanations (forced recompile, transient locks being waited on, etc.,), particularly if this is on a busy production server / DB. You may wish to schedule several runs within a minute or two and monitor the performance results, and / or investigate further, for example with profiler.

Execution Snaption parameters

I am setting up an execution snapshot to run a query every day at 2:00 am to run a long query and store the report as a snapshot.

My question is my two parameters are Starting Date and Ending Date. I want the parameters to have the value of the previous date (being that the report is run at 2:00 am). Is is possible to set up an expression such as =today-1 for these parameters?

Thanks for the information.

that's true, = Today - 1 will be easy

I suppose set the "default" value for BeginDate to a DataSet query
where you select the previous date in the query?
SELECT getdate() - 1|||Thanks...that worked and I did not even think about applying the query actually as the default report parameter.

Execution snapshots not being added to report history

Hello,
I'm trying to generate a snapshot everytime a report is viewed. I've setup
the properties of the report to "Store all report execution snapshots in
history" but no report is being created under the History tab.
The datasource uses hardcoded credentials.
The only thing I have found in the log file that may relate to this is the
following (but it does not consistently occur every time):
INFO: LoadSnapshot: Item with session: [session id], reportPath: [report
path], userName: [my username] not found in the database
Can anyone help?
ThanksDo you have the report set up to run on an Execution snapshot? That setting
is on the Execution subtab (Under the properties tab). If not then no
execution snapshots are being created, so none are being stored.
There is no setting to create a report execution snapshot on each viewing.
Execution snapshots can only be created manually (via the SOAP Api or
checking the check box in Report Manager) or on a schedule.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ian F" <IanF@.discussions.microsoft.com> wrote in message
news:CF1B4CBA-B0B1-4A6B-9433-DE65CB817749@.microsoft.com...
> Hello,
> I'm trying to generate a snapshot everytime a report is viewed. I've
setup
> the properties of the report to "Store all report execution snapshots in
> history" but no report is being created under the History tab.
> The datasource uses hardcoded credentials.
> The only thing I have found in the log file that may relate to this is the
> following (but it does not consistently occur every time):
> INFO: LoadSnapshot: Item with session: [session id], reportPath: [report
> path], userName: [my username] not found in the database
> Can anyone help?
> Thanks

Execution snapshots and datetime

Hello, I need some help with a problem i'm encountering:
I have a parameter named "startDate" defined in a report. The data type for
this parameter is DateTime and the default value I set in the report is
Non-queried with =Now() as the value. This value is sent to a SQL sProc.
When I run the report through my ASP.net page, it displays nicely.
I'm trying to use execution snapshots for this report with nightly email
subscriptions because the report returns large amounts of data and we can't
have it process the report at peak business hours. However when I try to set
up execution snapshots with this report, and set the startDate parameter
default value to =Now() in Report Manager, I get the error of "The property
of report parameter startDate doesn't have the expected type."
So then I changed the data type for parameter startDate to String and set
the value to =Now() in Report manager. At least now it takes the value. But
when trying to setup the snapshot again, I receive "Syntax error converting
datetime from character string" even though I use the convert function in
the SQL sProc. I need the snapshot to generate reports on a per week basis
and I can't hardcode a date in the parameter field like "9/9/2004". It has
to be =Now() so it can generate a snapshot per week automatically with that
weeks data. I've tried everything and i can't get it to work. Is there
anyway I can get Now() or DateAdd() to work in a datetime (or string if I
have to) field using execution snapshots? thank youYou cannot change an expression-based default value once the report is
published on the report server. When you type in =Now, then it is
interpreted as string "=Now" and *not* as expression.
You will need delete the report from report server, load the RDL file in
report designer, set the expression-based default value there (e.g. =Now or
=Today) and then republish the report to the report server.
Then you should be able to create execution snapshots without any issues.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark" <idroppeddabomb@.hotmail.com> wrote in message
news:eq5E2bplEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hello, I need some help with a problem i'm encountering:
> I have a parameter named "startDate" defined in a report. The data type
for
> this parameter is DateTime and the default value I set in the report is
> Non-queried with =Now() as the value. This value is sent to a SQL sProc.
> When I run the report through my ASP.net page, it displays nicely.
> I'm trying to use execution snapshots for this report with nightly email
> subscriptions because the report returns large amounts of data and we
can't
> have it process the report at peak business hours. However when I try to
set
> up execution snapshots with this report, and set the startDate parameter
> default value to =Now() in Report Manager, I get the error of "The
property
> of report parameter startDate doesn't have the expected type."
> So then I changed the data type for parameter startDate to String and set
> the value to =Now() in Report manager. At least now it takes the value.
But
> when trying to setup the snapshot again, I receive "Syntax error
converting
> datetime from character string" even though I use the convert function in
> the SQL sProc. I need the snapshot to generate reports on a per week basis
> and I can't hardcode a date in the parameter field like "9/9/2004". It has
> to be =Now() so it can generate a snapshot per week automatically with
that
> weeks data. I've tried everything and i can't get it to work. Is there
> anyway I can get Now() or DateAdd() to work in a datetime (or string if I
> have to) field using execution snapshots? thank you
>

Execution snapshots

Does the scheduler for executing a snapshot just simply not work?
Is there a known issue with this? I've tried everything, daily, weekly, on
and on...the only way i've ever gotten a snapshot to execute ever, is by
checking the "execute a snapshot when the apply button is clicked".
Anyone?
Thanks,
BrianSorry for the obvious question, but is SQL Server Agent started?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"G" <brian.grant@.si-intl-kc.com> wrote in message news:O5way6JrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Does the scheduler for executing a snapshot just simply not work?
> Is there a known issue with this? I've tried everything, daily, weekly, on
> and on...the only way i've ever gotten a snapshot to execute ever, is by
> checking the "execute a snapshot when the apply button is clicked".
> Anyone?
> Thanks,
> Brian
>|||I've having the same symptoms.
- SQL Server Agent is running
- The tasks that are created in SQL are running successfully
- The reports don't show up in the history and the updates are not
reflected in the snapshot data in the report
Everything looks like it SHOULD be working. No errors in the event log.
Nothing of value I could find in the Execution log. Just no new data.
ARGH.
"Tibor Karaszi" wrote:
> Sorry for the obvious question, but is SQL Server Agent started?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message news:O5way6JrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> >
> > Does the scheduler for executing a snapshot just simply not work?
> >
> > Is there a known issue with this? I've tried everything, daily, weekly, on
> > and on...the only way i've ever gotten a snapshot to execute ever, is by
> > checking the "execute a snapshot when the apply button is clicked".
> >
> > Anyone?
> >
> > Thanks,
> >
> > Brian
> >
> >
>
>

execution snapshot: Invalid_Viewstate

I am having problems when I choose "Render this report from an
execution snapshot" and select "Create a snapshot of the report
when the apply button is selected".
I get an Invalid_Viewstate message unless I clear all connections.
I use the following to clear connections.
Alter Database ReportServer set SINGLE_USER with Rollback Immediate
Alter Database ReportServer set MULTI_USER
We are running the report server on a web farm consisting of three
machines and the database server is on a different machine.
I recently moved the database to a new machine. Also, we have a custom
C Sharp (C#) application using the web service API to run some of our
reports.
Do you guys have any ideas on how to track this bug down?
Actual Error:
Invalid_Viewstate Client IP: 12.345.67.89 Port: 3856 User-Agent:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)
ViewState: dDw1MzgxO3Q8O2w8aTwxPjs+O........Actually I got it to work by trying several times in a row, so I am not
sure if clearing all connections is a relevant fact or not. I still
get the invalid_viewstate error most of the time.|||Actually I got it to work by trying several times in a row, so I am not
sure if clearing all connections is a relevant fact or not. I still
get the invalid_viewstate error most of the time.|||Any ideas guys'
I am really stuck!
daveg.01@.gmail.com wrote:
> Actually I got it to work by trying several times in a row, so I am not
> sure if clearing all connections is a relevant fact or not. I still
> get the invalid_viewstate error most of the time.|||We are still getting the error. I also got a couple different errors
today while trying to manually generate a snapshot. One of them said
something about an XML file (I didn=E2=80=99t write it down =EF=81=8C
I did notice one thing though. Yesterday, after I restarted the
windows service (ReportServer) I was able to generate a snapshot.
Also, a snapshot was generated this morning according to the schedule.
However I did receive the errors above after beating on the report for
a bit.
I restarted the windows service again (this time on all 3 machines in
the farm) and I was able to manually generate snapshots with no error
again.
Any ideas on what is going on?
daveg.01@.gmail.com wrote:
> Any ideas guys'
> I am really stuck!
>
> daveg.01@.gmail.com wrote:
> > Actually I got it to work by trying several times in a row, so I am not
> > sure if clearing all connections is a relevant fact or not. I still
> > get the invalid_viewstate error most of the time.

Execution Snapshot - Parameter "gray out"

Hi Experts,
I got a report that takes in a parameter and run normally 20 min to produce
the result. So I decide to take advantage of Execution Snapshot feature. I
create a shared schedule and set my Execution Snapshot to execute every
morning around 1 am with default parameter. The next day I go into the report
and run it and wow it just takes second to produce the result, I'm impressed.
But I noticed something strange: the report runs using my default parameter
and the parameter box is "gray out". So I cannot run the report again using
different parameter value other than the default one.
I'm using Reporting Services Standard edition. Is this behavior normal for
this edition? Does the Enterprise edition allow me to change the parameter
once it's already scheduled for Execution Snapshot.
Any idea?
ThanhThanh,
It is normal behavior. Snapshots run for the default parameter values and
stores the intermediate report format /data in cache. When you go back to
the report, it grabs the intermediate format from cache and presents it,
preventing another trip to the database. If it allowed you to change the
parameters, it would need to re-execute the report with the new query
parameters.
If you want the parameters to be available, you need to move from using
query parameters to using report filters. There are plusses and minuses to
using report filters.
Books on line has some good info on how to use report filters.
--
Andy Potter
blog: http://sqlreportingservices.spaces.live.com/
"Thanh Nguyen" wrote:
> Hi Experts,
> I got a report that takes in a parameter and run normally 20 min to produce
> the result. So I decide to take advantage of Execution Snapshot feature. I
> create a shared schedule and set my Execution Snapshot to execute every
> morning around 1 am with default parameter. The next day I go into the report
> and run it and wow it just takes second to produce the result, I'm impressed.
> But I noticed something strange: the report runs using my default parameter
> and the parameter box is "gray out". So I cannot run the report again using
> different parameter value other than the default one.
> I'm using Reporting Services Standard edition. Is this behavior normal for
> this edition? Does the Enterprise edition allow me to change the parameter
> once it's already scheduled for Execution Snapshot.
> Any idea?
> Thanh

Execution Schedule

I have a Reporting Services report which is scheduled to execute on using snapshot on a 3-month cycle, on the 15^{th of Feb, May, Aug and Nov. This report was originally programmed to execute monthly on the 15th so we just changed the same schedule to execute choosing only Feb, May, Aug. and Nov but it executed on March 15. Shouldn't this have change it, would i need to create a new schedule and delete the old one?}

If I were you, I would double check your schedule through Report Manager, then go check the SQL Agent Job. You have to make sure you hit Apply to the frequency and Ok on the Execution. Anyway, if both of those still show the correct schedule, try to delete it and re-create a new one.

Jarret

Execution 'rgaerh55bisinzr1nlmr13jk' cannot be found (rsExecutionN

I have a SSRS 2005 SP1 running on Windows 2003 SRS. On reports with
parameters we are getting the following error.
Execution 'rgaerh55bisinzr1nlmr13jk' cannot be found (rsExecutionNotFound).
Anyone else seen this? Any suggestions on how to resolve this?
Thanks.Yes, we are seeing the same thing.
In our case, the report runs the first time, with the default parameters --
which says that there's an aweful lot of setup that is functioning just fine.
Then, upon changing a parameter and pressing "View Report", the following
obscure error shows up:
Execution 'qr1hx055hlpkx445xluqd4yv' cannot be found (rsExecutionNotFound)
It also occurs when simply pressing "View Report", with no changes to
parameters.
"rdwill1" wrote:
> I have a SSRS 2005 SP1 running on Windows 2003 SRS. On reports with
> parameters we are getting the following error.
> Execution 'rgaerh55bisinzr1nlmr13jk' cannot be found (rsExecutionNotFound).
> Anyone else seen this? Any suggestions on how to resolve this?
> Thanks.

Execution procedure stored during execution of the report .

Hello :

How to execute a procedure stored during execution of the report, that is before the poster the data.

Thnak you.

You need to provide a clearer explanation of what you are trying to do and why.

|||

Yes :

I have a request which put very for a long time executing, thus I want to launch (call) a stored procedure which makes the ALTER SESSION (oracle) before executing the report.

I hope that I am clear. !!!

|||

I think I understand. There is no way as such to make RS execute a SQL statement that does nothing for the report. Many people have asked for this to be able to do custom usage logging.

As a work-around you could try to implement this as a dataset query for an internal parameter. if you then make other parameters dependent on this one then this will ensure that the statement you want will be executed first.

I'm not sure what ALTER SESSION does in Oracle, but don't assume that queries will be executed on the same Session/Connection/Process. I'm not sure exactly how it works but that would be a poor assumption I feel.

Execution Plans/Performance Issues: Variables vs. Literals

I have a problem w/ two very simple queries yielding largely varying
execution plans and performance. One uses a variable (the fast one), and
one uses a literal (the slow one).
Here's the info:
@.@.version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
--QUERY 1: USING VARIABLE (FAST)
declare @.UserID int
select @.UserID = 54462
select Thread.ThreadID
from Thread
join PrivateThreadUser
on PrivateThreadUser.ThreadID = Thread.ThreadID
where PrivateThreadUser.UserID = @.UserID
--QUERY 2: USING LITERAL VALUE (SLOW)
select Thread.ThreadID
from Thread
join PrivateThreadUser
on PrivateThreadUser.ThreadID = Thread.ThreadID
where PrivateThreadUser.UserID = 54462
And here are the query costs between the two:
Query 1: Query cost (relative to batch): 5.60%
Query 2: Query cost (relative to batch): 94.40%
To see the execution plans for the two, see these files:
Text file:
http://www.animalcrossingcommunity.com/ExecPlan.txt
Excel spreadsheet (full details):
http://www.animalcrossingcommunity.com/ExecPlan.xls
Why in the world does Query 2 yield a MUCH -- 17 times -- less efficient
query plan?
This originally started out as a stored proc issue, which I later found that
may be related to parameter sniffing. But now that I've narrowed it down to
two straight queries in QA -- one using a variable, one using a literal --
this doesn't seem to be an issue of parameter sniffing, as I've read this is
only related to stored proc parameters.
Any insight as to the logic SQL Server is using here would be appreciated.
Also, any insight as to what logic I should use when trying to
diagnose/address these issues would be appreciated as well.
Thanks in advance.
JeradJerad,
There could be several reasons for this behavior:
1) Maybe your statistics are not up to date. When in doubt, run UPDATE
STATISTICS, preferably WITH FULLSCAN
2) The distribution of the UserID and/or ThreadID column could be
skewed. If the query returns just a few rows for UserID=54462, but many
rows for a different UserID, then the optimizer will optimize for the
worst case scenario when using variables, because it will have no prior
knowledge of the actual @.UserID it will be running with.
HTH,
Gert-Jan
Jerad Rose wrote:
> I have a problem w/ two very simple queries yielding largely varying
> execution plans and performance. One uses a variable (the fast one), and
> one uses a literal (the slow one).
> Here's the info:
> @.@.version:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windo
ws
> NT 5.0 (Build 2195: Service Pack 4)
> --QUERY 1: USING VARIABLE (FAST)
> declare @.UserID int
> select @.UserID = 54462
> select Thread.ThreadID
> from Thread
> join PrivateThreadUser
> on PrivateThreadUser.ThreadID = Thread.ThreadID
> where PrivateThreadUser.UserID = @.UserID
> --QUERY 2: USING LITERAL VALUE (SLOW)
> select Thread.ThreadID
> from Thread
> join PrivateThreadUser
> on PrivateThreadUser.ThreadID = Thread.ThreadID
> where PrivateThreadUser.UserID = 54462
> And here are the query costs between the two:
> Query 1: Query cost (relative to batch): 5.60%
> Query 2: Query cost (relative to batch): 94.40%
> To see the execution plans for the two, see these files:
> Text file:
> http://www.animalcrossingcommunity.com/ExecPlan.txt
> Excel spreadsheet (full details):
> http://www.animalcrossingcommunity.com/ExecPlan.xls
> Why in the world does Query 2 yield a MUCH -- 17 times -- less efficient
> query plan?
> This originally started out as a stored proc issue, which I later found th
at
> may be related to parameter sniffing. But now that I've narrowed it down
to
> two straight queries in QA -- one using a variable, one using a literal --
> this doesn't seem to be an issue of parameter sniffing, as I've read this
is
> only related to stored proc parameters.
> Any insight as to the logic SQL Server is using here would be appreciated.
> Also, any insight as to what logic I should use when trying to
> diagnose/address these issues would be appreciated as well.
> Thanks in advance.
> Jerad|||1. Are you absolutely certain you don't have the fast and slow
reversed? Frequently we see the variable version slower than a
literal version.
2. What is the datatype of PrivateThreadUser.UserID? I don't see an
explicit CONVERT in your execution plans, but even slightly misaligned
datatypes can cause the plan to go wild.
J.
On Sat, 17 Sep 2005 21:39:29 -0400, "Jerad Rose" <no@.spam.com> wrote:
>I have a problem w/ two very simple queries yielding largely varying
>execution plans and performance. One uses a variable (the fast one), and
>one uses a literal (the slow one).
>Here's the info:
>@.@.version:
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Window
s
>NT 5.0 (Build 2195: Service Pack 4)
>--QUERY 1: USING VARIABLE (FAST)
>declare @.UserID int
>select @.UserID = 54462
>select Thread.ThreadID
>from Thread
>join PrivateThreadUser
>on PrivateThreadUser.ThreadID = Thread.ThreadID
>where PrivateThreadUser.UserID = @.UserID
>--QUERY 2: USING LITERAL VALUE (SLOW)
>select Thread.ThreadID
>from Thread
>join PrivateThreadUser
>on PrivateThreadUser.ThreadID = Thread.ThreadID
>where PrivateThreadUser.UserID = 54462
>And here are the query costs between the two:
>Query 1: Query cost (relative to batch): 5.60%
>Query 2: Query cost (relative to batch): 94.40%
>To see the execution plans for the two, see these files:
>Text file:
>http://www.animalcrossingcommunity.com/ExecPlan.txt
>Excel spreadsheet (full details):
>http://www.animalcrossingcommunity.com/ExecPlan.xls
>Why in the world does Query 2 yield a MUCH -- 17 times -- less efficient
>query plan?
>This originally started out as a stored proc issue, which I later found tha
t
>may be related to parameter sniffing. But now that I've narrowed it down t
o
>two straight queries in QA -- one using a variable, one using a literal --
>this doesn't seem to be an issue of parameter sniffing, as I've read this i
s
>only related to stored proc parameters.
>Any insight as to the logic SQL Server is using here would be appreciated.
>Also, any insight as to what logic I should use when trying to
>diagnose/address these issues would be appreciated as well.
>Thanks in advance.
>Jerad
>|||Hi Gert-Han.
1) I actually tried UPDATE STATISTICS already (meant to post that), but I
went ahead and tried it again WITH FULLSCAN, and still got the same results.
2) The highest saturation for one user is 913 of 26540 rows (3.44%
saturation), so I wouldn't think this would be the case.
Any other ideas?
Thanks for your help.
Jerad
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:432D51BC.E14A51BA@.toomuchspamalready.nl...[vbcol=seagreen]
> Jerad,
> There could be several reasons for this behavior:
> 1) Maybe your statistics are not up to date. When in doubt, run UPDATE
> STATISTICS, preferably WITH FULLSCAN
> 2) The distribution of the UserID and/or ThreadID column could be
> skewed. If the query returns just a few rows for UserID=54462, but many
> rows for a different UserID, then the optimizer will optimize for the
> worst case scenario when using variables, because it will have no prior
> knowledge of the actual @.UserID it will be running with.
> HTH,
> Gert-Jan
>
> Jerad Rose wrote:|||Hi J.
1) Positive. I thought this too, as I had read about parameter sniffing
causing problems, but in this case, the literal is the slow one. Also, if
you look at the execution plan (text file), you'll see that it's using an
Index Scan on the literal query.
2) It is an int (64-bit). And referencial integrity is enforced on these
tables, so it is required that the datatypes match between the FKs and PKs.
Any other ideas?
Thanks for your help.
Jerad
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:8rhri19u3e8e988cj2sih5qha8iltstjdj@.
4ax.com...
> 1. Are you absolutely certain you don't have the fast and slow
> reversed? Frequently we see the variable version slower than a
> literal version.
> 2. What is the datatype of PrivateThreadUser.UserID? I don't see an
> explicit CONVERT in your execution plans, but even slightly misaligned
> datatypes can cause the plan to go wild.
> J.
>
> On Sat, 17 Sep 2005 21:39:29 -0400, "Jerad Rose" <no@.spam.com> wrote:
>|||On Mon, 19 Sep 2005 01:11:50 -0400, "Jerad Rose" <no@.spam.com> wrote:
>Hi J.
>1) Positive. I thought this too, as I had read about parameter sniffing
>causing problems, but in this case, the literal is the slow one. Also, if
>you look at the execution plan (text file), you'll see that it's using an
>Index Scan on the literal query.
Hmmm.

>2) It is an int (64-bit). And referencial integrity is enforced on these
>tables, so it is required that the datatypes match between the FKs and PKs.
So, your variable is an int (32), and your column is an int (64)? But
that's the fast one. Hmm.
Well, heck, I guess you can just use the variable. Maybe I should try
that on some of my own code? Hmm, ...
J.|||That is remarkable. You must be having a hot cache for the Literal query
plan to actually run *slower* than the Variable query plan.
One possible reason could be that with a cold cache (no relevant data
pages in memory) the Literal query plan is in fact faster. If you have a
narrow index, a wide table and many rows, then 3.44% could be enough to
switch from Lookups to an index scan. Especially since the nonclustered
index is covering. If you add one other column of table Thread to the
query this might also change things.
You could test this by running the following lines before each
execution:
--clear proc cache
DBCC FREEPROCCACHE
--write dirty pages to disk
CHECKPOINT
--free clean buffers
DBCC DROPCLEANBUFFERS
If you still consider it to be a problem (for example because you know
you will always be running with a hot cache), then you could add a LOOP
join hint. If not, then I would simply accept the less-then-optimal
query plan.
HTH,
Gert-Jan
Jerad Rose wrote:[vbcol=seagreen]
> Hi Gert-Han.
> 1) I actually tried UPDATE STATISTICS already (meant to post that), but I
> went ahead and tried it again WITH FULLSCAN, and still got the same result
s.
> 2) The highest saturation for one user is 913 of 26540 rows (3.44%
> saturation), so I wouldn't think this would be the case.
> Any other ideas?
> Thanks for your help.
> Jerad
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:432D51BC.E14A51BA@.toomuchspamalready.nl...