Cursor Process Coding Problem

Hi guys,

It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop

But aren't you worried about datatypes...and what's wrong with nulls annyway

(Here we go again)|||Ok,

I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

Here is the updated code:

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO

SELECT * FROM myTable99
GO

DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname

SET @.TABLE_NAME = 'myTable99'

SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME

SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist

SELECT @.sql

EXEC(@.sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||OK,

I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

Dave|||We resolved it in-house. Here is the answer! Thanks again.

================================================== =====

DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech

Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

Davesql

Cursor performance slows exponentially

I have a strange problem. I am running a process that unfortunately
requires that I analyze each record of a table which has [currently]
led me to using a cursor.
I noticed some performance problems and so I ran some tests and it
seems that the cursor performance is getting exponentially slower, for
example, the first 500 records of the cursor process in about 1733ms
but the records 10000-10500 takes about 13516ms and records 20000-20500
takes 22500ms.
Is this normal behaviour with cursors? I know that they are not
optimal for performance, however, I didn't expect the performance to
degrade as it was running.
Below is a snip from the code. At first I thought that perhaps the
processing of the records was growing because it was counting values in
a table that is growing but when I ran a timer around the processing
logic it showed little if any substantial difference.
Aside: I'm using '##temp' tables because I want to be able to inspect
the tables after the query runs, other wise I just use '#temp' tables.
Thanks.
WHILE (@.@.FETCH_STATUS=0)
BEGIN
-- Reset environment
SET @.processFlag = 0
SET @.resetBalanceFlag = 0
SET @.setEndDateFlag = 0
IF(SELECT COUNT(*)
FROM ##tempHistory
WHERE AgencyID=@.AgyID
AND CollectionInventoryType=@.HwyID) >= 1
BEGIN
SET @.resetBalanceFlag = 1
SET @.setEndDateFlag = 1
END
IF(@.HwyID=@.prevHwyID)
SET @.processFlag = 0
ELSE
BEGIN
SET @.processFlag = 1
SET @.setEndDateFlag = 1
END
IF((@.AgyID=@.prevAgyID) AND @.RecordType='R')
SET @.processFlag = 0
ELSE
BEGIN
SET @.processFlag = 1
SET @.setEndDateFlag = 1
END
IF(@.processFlag=1)
BEGIN
IF(@.resetBalanceFlag = 1)
BEGIN
-- Reset assign balance to $0 for all records on temp table that
match
-- the agyid, hwyid and RAN
UPDATE ##tempHistory
SET AssignBalance = 0
WHERE CollectInvCode=@.CollectInvCode
AND AgencyID=@.AgyID
AND CollectionInventoryType=@.HwyID
END
IF(@.setEndDateFlag = 1)
BEGIN
UPDATE ##tempHistory
SET EndDate=@.ProcessedDate
WHERE CollectInvCode=@.CollectInvCode
AND EndDate IS NULL
END
INSERT INTO
##tempHistory(CollectInvCode,AccountCode
,AgencyID,StartDate,EndDate,AssignBa
lance,ProductCatID,CollectionInventoryTy
pe)
VALUES(@.CollectInvCode,@.AccountCode,@.Agy
ID,@.ProcessedDate,NULL,@.Balance,@.Pro
dID,@.HwyID)
END
SET @.prevHwyID = @.HwyID
SET @.prevAgyID = @.AgyID
FETCH NEXT FROM cur_ProcessRAN INTO
@.RAN,@.CollectInvCode,@.AccountCode,@.ProdI
D,@.RecordType,@.Balance,@.ProcessedDat
e,@.HwyID,@.AgyID
-- remove after testing
set @.cnt=@.cnt+1
if(@.cnt%500)=0
begin
print cast(@.cnt as varchar) + ':' + cast(datediff(ms,@.ttime,getdate())
as varchar)
set @.ttime=getdate()
end
-- end: remove
END -- END: Loop through all non-"U" records for the given RANHi
It's pretty hard to suggest something without seeing a whole ddl + sample
data.
Cursors are almost bad in terms of performance ,hence try to re-write as set
based solution.
<mrpubnight@.hotmail.com> wrote in message
news:1124678427.316826.197680@.g14g2000cwa.googlegroups.com...
>I have a strange problem. I am running a process that unfortunately
> requires that I analyze each record of a table which has [currently]
> led me to using a cursor.
> I noticed some performance problems and so I ran some tests and it
> seems that the cursor performance is getting exponentially slower, for
> example, the first 500 records of the cursor process in about 1733ms
> but the records 10000-10500 takes about 13516ms and records 20000-20500
> takes 22500ms.
> Is this normal behaviour with cursors? I know that they are not
> optimal for performance, however, I didn't expect the performance to
> degrade as it was running.
> Below is a snip from the code. At first I thought that perhaps the
> processing of the records was growing because it was counting values in
> a table that is growing but when I ran a timer around the processing
> logic it showed little if any substantial difference.
> Aside: I'm using '##temp' tables because I want to be able to inspect
> the tables after the query runs, other wise I just use '#temp' tables.
> Thanks.
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> -- Reset environment
> SET @.processFlag = 0
> SET @.resetBalanceFlag = 0
> SET @.setEndDateFlag = 0
> IF(SELECT COUNT(*)
> FROM ##tempHistory
> WHERE AgencyID=@.AgyID
> AND CollectionInventoryType=@.HwyID) >= 1
> BEGIN
> SET @.resetBalanceFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF(@.HwyID=@.prevHwyID)
> SET @.processFlag = 0
> ELSE
> BEGIN
> SET @.processFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF((@.AgyID=@.prevAgyID) AND @.RecordType='R')
> SET @.processFlag = 0
> ELSE
> BEGIN
> SET @.processFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF(@.processFlag=1)
> BEGIN
> IF(@.resetBalanceFlag = 1)
> BEGIN
> -- Reset assign balance to $0 for all records on temp table that
> match
> -- the agyid, hwyid and RAN
> UPDATE ##tempHistory
> SET AssignBalance = 0
> WHERE CollectInvCode=@.CollectInvCode
> AND AgencyID=@.AgyID
> AND CollectionInventoryType=@.HwyID
> END
> IF(@.setEndDateFlag = 1)
> BEGIN
> UPDATE ##tempHistory
> SET EndDate=@.ProcessedDate
> WHERE CollectInvCode=@.CollectInvCode
> AND EndDate IS NULL
> END
> INSERT INTO
> ##tempHistory(CollectInvCode,AccountCode
,AgencyID,StartDate,EndDate,Assign
Balance,ProductCatID,CollectionInventory
Type)
> VALUES(@.CollectInvCode,@.AccountCode,@.Agy
ID,@.ProcessedDate,NULL,@.Balance,@.P
rodID,@.HwyID)
> END
> SET @.prevHwyID = @.HwyID
> SET @.prevAgyID = @.AgyID
> FETCH NEXT FROM cur_ProcessRAN INTO
> @.RAN,@.CollectInvCode,@.AccountCode,@.ProdI
D,@.RecordType,@.Balance,@.ProcessedD
ate,@.HwyID,@.AgyID
> -- remove after testing
> set @.cnt=@.cnt+1
> if(@.cnt%500)=0
> begin
> print cast(@.cnt as varchar) + ':' + cast(datediff(ms,@.ttime,getdate())
> as varchar)
> set @.ttime=getdate()
> end
> -- end: remove
> END -- END: Loop through all non-"U" records for the given RAN
>|||Do you have indexes on ##temp? If not, then that's your problem. Without
indexes, SELECT COUNT(*) and the two UPDATE statements will execute a table
scan, which will obviously take a lot longer as the table fills up. In an
aside, you should use EXISTS instead of COUNT(*) to determine whether a row
exists, because EXISTS shortcircuits as soon as it gets a hit, whereas
COUNT(*) must complete a scan.
To maximize the performance of a cursor you should minimize the reads and
writes within the fetch loop. To minimize reads, you can usually alter the
select statement of the cursor to include all information required to
perform the calculation, or you can create a separate cursor with a similar
ORDER BY clause and scan both tables synchronously (similar to a merge
join). In this way all of the information needed for calculation is
obtained using set-based operations before the fetch loop begins. To
minimize writes, I prefer to only execute INSERTs to table variables or temp
tables within the fetch loop. UPDATEs and DELETEs should be deferred
whenever possible. INSERTs into a table without any indexes is extremely
fast, and INSERTs into a table with a clustered index on an IDENTITY column
is almost as fast. The whole idea is to scan through the cursor spooling
changes so that they can be written using set-based operations outside of
the fetch loop.
Set-based change operations are always faster than iterated change
operations, because triggers only fire once, indexes can be updated en-mass,
and the amount of information written to the transaction log is minimized.
If foreign key constraints exist, then performance of set based operations
can sometimes be further optimized by escalating to a clustered index scan.
(A clustered index scan is not always a bad thing. It starts out with a
clustered index s to find the first row and then uses the linked-list
between leaf pages within the clustered index to find the subsequent rows.)
<mrpubnight@.hotmail.com> wrote in message
news:1124678427.316826.197680@.g14g2000cwa.googlegroups.com...
> I have a strange problem. I am running a process that unfortunately
> requires that I analyze each record of a table which has [currently]
> led me to using a cursor.
> I noticed some performance problems and so I ran some tests and it
> seems that the cursor performance is getting exponentially slower, for
> example, the first 500 records of the cursor process in about 1733ms
> but the records 10000-10500 takes about 13516ms and records 20000-20500
> takes 22500ms.
> Is this normal behaviour with cursors? I know that they are not
> optimal for performance, however, I didn't expect the performance to
> degrade as it was running.
> Below is a snip from the code. At first I thought that perhaps the
> processing of the records was growing because it was counting values in
> a table that is growing but when I ran a timer around the processing
> logic it showed little if any substantial difference.
> Aside: I'm using '##temp' tables because I want to be able to inspect
> the tables after the query runs, other wise I just use '#temp' tables.
> Thanks.
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> -- Reset environment
> SET @.processFlag = 0
> SET @.resetBalanceFlag = 0
> SET @.setEndDateFlag = 0
> IF(SELECT COUNT(*)
> FROM ##tempHistory
> WHERE AgencyID=@.AgyID
> AND CollectionInventoryType=@.HwyID) >= 1
> BEGIN
> SET @.resetBalanceFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF(@.HwyID=@.prevHwyID)
> SET @.processFlag = 0
> ELSE
> BEGIN
> SET @.processFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF((@.AgyID=@.prevAgyID) AND @.RecordType='R')
> SET @.processFlag = 0
> ELSE
> BEGIN
> SET @.processFlag = 1
> SET @.setEndDateFlag = 1
> END
> IF(@.processFlag=1)
> BEGIN
> IF(@.resetBalanceFlag = 1)
> BEGIN
> -- Reset assign balance to $0 for all records on temp table that
> match
> -- the agyid, hwyid and RAN
> UPDATE ##tempHistory
> SET AssignBalance = 0
> WHERE CollectInvCode=@.CollectInvCode
> AND AgencyID=@.AgyID
> AND CollectionInventoryType=@.HwyID
> END
> IF(@.setEndDateFlag = 1)
> BEGIN
> UPDATE ##tempHistory
> SET EndDate=@.ProcessedDate
> WHERE CollectInvCode=@.CollectInvCode
> AND EndDate IS NULL
> END
> INSERT INTO
>
##tempHistory(CollectInvCode,AccountCode
,AgencyID,StartDate,EndDate,AssignBa
lance,ProductCatID,CollectionInventoryTy
pe)
>
VALUES(@.CollectInvCode,@.AccountCode,@.Agy
ID,@.ProcessedDate,NULL,@.Balance,@.Pro
dID,@.HwyID)
> END
> SET @.prevHwyID = @.HwyID
> SET @.prevAgyID = @.AgyID
> FETCH NEXT FROM cur_ProcessRAN INTO
>
@.RAN,@.CollectInvCode,@.AccountCode,@.ProdI
D,@.RecordType,@.Balance,@.ProcessedDat
e,@.HwyID,@.AgyID
> -- remove after testing
> set @.cnt=@.cnt+1
> if(@.cnt%500)=0
> begin
> print cast(@.cnt as varchar) + ':' + cast(datediff(ms,@.ttime,getdate())
> as varchar)
> set @.ttime=getdate()
> end
> -- end: remove
> END -- END: Loop through all non-"U" records for the given RAN
>

Cursor is the only choice?

If I want to do a process in a sequence order, is Cursor my only choice?
for example,
----
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from table
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoSomething @.ID
end
close MyCursor
deallocate MyCursor
----
In fact, I have found a way which does NOT work as followed,
select @.Null=dbo.DoSomething(id)
from table
where created>thedate
order by created
As I just metioned, it does not work. There are too many restrictions in the
function declaration. No newid(), no xxxxxx, no xxxxxx, too many
restrictions! But the code is short and clear. I like.
So I would like to know are there another choices for me in this simple
example?Hi, Frank
You can do something this:
declare @.ID int
set @.ID=(
select top 1 id from table
where created>thedate
order by created, id
)
while @.ID is not null begin
exec DoSomething(@.ID)
set @.ID=(
select top 1 id from table
where created>thedate
and id>@.ID
order by created, id
)
end
But this method would not have a better performance than a cursor. The
real solution is to rewrite the DoSomething procedure to process all
rows at once. That's what SQL is for: set-based operations. Please tell
us what DoSomething is supposed to do, or post the actual procedure,
along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
...), so we can really help.
Razvan|||Frank Lee wrote:

> If I want to do a process in a sequence order, is Cursor my only choice?
That depends what the process is. What is the thing represented by
"DoSomething" in your example code? If it is just more data
manipulation then there's a good chance that it can be done without
using a cursor.
David Portas
SQL Server MVP
--|||Go to http://www.sciencecartoonsplus.com/gallery.htm. That is what
your "exec DoSomething @.ID" is like!
I doubt you really need a cursor. In my career (`20 years of SQL
coding, 35 years total) I have written five cursors in production SQL
code; I know that if I had the CASE expression back in the old days, I
know I could have avoided three of them.
We will need more details and probably have to re-write your DDL (good
SQL programmers do not use uniqueidentifier, and names like "id" or
"@.null" even in pseudo-code).|||The Scenario for me is an (I Called) asynchornous Request-and-Process model.
--DDL
Create table Request (
ID uniqueidentifier primary key default newid(),
types int not null, --RequestType, depend on use case, not
important here.
data varchar(255) not null, --pseudo code, maybe many columns,
depend.
created datetime not null default getdate(),
done bit not null default 0
)
go
create trigger xxxxxxxxxxx on Request for insert as
begin
--Do nothing or DoSomethings which will complete in short time
--And then activate a job which process the Request
exec sp_start job xxxxxxxxxxxxxxxxxxxx
end
go
Client-Side (C/S or asp.net) insert something by using
insert into Request (types, data)
The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
or activate by Request_Insert_Trigger. There is nothing to do for the job
but just exec MyProcessSP. In MyProcessSP, there is a loop to call
DoMyProcessSP @.ID as descripted in last post.
create procedure DoMyProcessSP (@.ID uniqueidentifier) as
begin
declare @.types int
declare @.data varchar(255)
--pseudo code
select @.types=types, @.data=data
from Request
where id=@.id
if @.types=0
begin
insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else if @.type=1
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
update request
set Done=1
where id=@.id
end
go
create procedure MyProcessSP as --A caller in sequent order
begin
--pseudo code
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from request
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoMyProcessSP @.ID
end
close MyCursor
deallocate MyCursor
end
go
"Razvan Socol" <rsocol@.gmail.com>
'?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
> Hi, Frank
> You can do something this:
> declare @.ID int
> set @.ID=(
> select top 1 id from table
> where created>thedate
> order by created, id
> )
> while @.ID is not null begin
> exec DoSomething(@.ID)
> set @.ID=(
> select top 1 id from table
> where created>thedate
> and id>@.ID
> order by created, id
> )
> end
> But this method would not have a better performance than a cursor. The
> real solution is to rewrite the DoSomething procedure to process all
> rows at once. That's what SQL is for: set-based operations. Please tell
> us what DoSomething is supposed to do, or post the actual procedure,
> along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
> ...), so we can really help.
> Razvan
>|||There is a more detail example code in the post I reply to Razvan.
If you do have a good suggestion, please read that post. thx.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136020809.428204.141060@.o13g2000cwo.googlegroups.com...
> Frank Lee wrote:
>
> That depends what the process is. What is the thing represented by
> "DoSomething" in your example code? If it is just more data
> manipulation then there's a good chance that it can be done without
> using a cursor.
> --
> David Portas
> SQL Server MVP
> --
>|||Frank Lee (Reply@.to.newsgroup) writes:
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
>...
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
>...
So we still can't tell whether you can rewrite this into set-based code.
However, it cannot be denied that there is a trade-off. Even in T-SQL
it is easier to express logic scalarly, handling one row at a time. For
starters, if you want to split code between stored procedures, it's
easy to pass parameters, but you can only pass scalar parameters, not
tables. (Procedures can still share data over tables, see my article
http://www.sommarskog.se/share_data.html for some techniques.)
Rewriting existing code that uses iterative processesing into set-based
can for complex cases be quite an effort. I rewrote a central procedure
of our system in October/November, and it spent over 80 hours on that,
including testing. We have more that we need to rewrite, and the total
estimate is over 500 hours.
Obviously, there is a trade-off. As long as performance is acceptable
for the task, it can be difficult to justify a rewrite, but be prepared
that in some point in the life-time of the system, the situation may
become untenable.
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|||Frank Lee wrote:
> The Scenario for me is an (I Called) asynchornous Request-and-Process mode
l.
> --DDL
> Create table Request (
> ID uniqueidentifier primary key default newid(),
> types int not null, --RequestType, depend on use case, not
> important here.
> data varchar(255) not null, --pseudo code, maybe many columns,
> depend.
> created datetime not null default getdate(),
> done bit not null default 0
> )
> go
> create trigger xxxxxxxxxxx on Request for insert as
> begin
> --Do nothing or DoSomethings which will complete in short time
> --And then activate a job which process the Request
> exec sp_start job xxxxxxxxxxxxxxxxxxxx
> end
> go
> Client-Side (C/S or asp.net) insert something by using
> insert into Request (types, data)
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
> create procedure DoMyProcessSP (@.ID uniqueidentifier) as
> begin
> declare @.types int
> declare @.data varchar(255)
> --pseudo code
> select @.types=types, @.data=data
> from Request
> where id=@.id
> if @.types=0
> begin
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else if @.type=1
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> update request
> set Done=1
> where id=@.id
> end
> go
> create procedure MyProcessSP as --A caller in sequent order
> begin
> --pseudo code
> declare @.ID uniqueidentifier
> declare MyCursor cursor local for
> select id
> from request
> where created>thedate
> order by created
> for read only
> open MyCursor
> while (1=1)
> begin
> fetch next from MyCursor into @.ID
> if @.@.fetch_status<>0
> break
> exec DoMyProcessSP @.ID
> end
> close MyCursor
> deallocate MyCursor
> end
> go
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
Here's a slightly different approach that has worked for me in the
past. Use your scheduled job or some other scheduled prgram to pull
processes from your request table one at a time. That way you can scale
it by spawning new threads, each of which will take the next pending
request. Also it's probably easier to handle and debug errors if each
job execution is associated with only a single request at a time. You
can use TOP 1 to retrieve the next request. If your processes need to
be serialized then you can use an extra attribute to group those so
that they are taken as a sequence.
I am assuming that these requests are some unrelated and inherently
procedural tasks rather than straight data-manipulation, otherwise
there may be better solutions without cursors or procedural code.
In SQL Server 2005 we have the Service Broker architecture to take care
of messaging and queueing functionality. Take a look at Service Broker
if you haven't already.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136119382.878745.243360@.f14g2000cwb.googlegroups.com...
> Here's a slightly different approach that has worked for me in the
> past. Use your scheduled job or some other scheduled prgram to pull
> processes from your request table one at a time.
Thanks, I use SQL Agent Job.

> That way you can scale
> it by spawning new threads, each of which will take the next pending
> request. Also it's probably easier to handle and debug errors if each
> job execution is associated with only a single request at a time.
Yes, I agree.

> You
> can use TOP 1 to retrieve the next request. If your processes need to
> be serialized then you can use an extra attribute to group those so
> that they are taken as a sequence.
>
Good point. Thx.

> I am assuming that these requests are some unrelated and inherently
> procedural tasks rather than straight data-manipulation, otherwise
> there may be better solutions without cursors or procedural code.
>
Yes, they are unrelated.

>
> In SQL Server 2005 we have the Service Broker architecture to take care
> of messaging and queueing functionality. Take a look at Service Broker
> if you haven't already.
>
Yes, I know. I have test it, and try to rewrite one implementation which
used to use SQL Agent job to implement. However, I find Service Broker is
too BIG for me. I would like, and am planning, to use it to do another big
things. Thanks anyway.

> --
> David Portas
> SQL Server MVP
> --
>sql

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I need to process some data from a read-only MSAccess table named "Padron"
>> that has 350000+ records.
>> I declare the linked server:
>> EXEC sp_addlinkedserver
>> @.server = 'PADRONELECTORAL',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
>> The problem is that when I open the cursor, the operation takes too long:
>> DECLARE tabla CURSOR LOCAL FOR
>> SELECT * FROM
>> PADRONELECTORAL...Padron
>> OPEN tabla
>> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
>> solve the problem.
>> Note: If I try to open the cursor with a table that has a few of records,
>> the operation executes immediately, so I guess the problem is related with
>> record count.
>> Thanks!
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!
This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>
|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data th
en
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Current Process

Hi
I've been told that I can use the following SQL expressions to find out the
current SQL being executed under a certain SPID, but for some reason I
cannot get this to work.
Is there something wrong, in what I have written?
--*************************************
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@.Handle)
--*************************************
Kind Regards
Ricky
(WIN2K/SQL2K-SP4)It should work if the specified SPId is still running.
Try this
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @.@.SPID
SELECT * FROM ::fn_get_sql(@.Handle)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:O08GR61mGHA.4212@.TK2MSFTNGP04.phx.gbl...
> Hi
> I've been told that I can use the following SQL expressions to find out
> the
> current SQL being executed under a certain SPID, but for some reason I
> cannot get this to work.
> Is there something wrong, in what I have written?
> --*************************************
> DECLARE @.Handle binary(20)
> SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
> SELECT * FROM ::fn_get_sql(@.Handle)
> --*************************************
> Kind Regards
> Ricky
> (WIN2K/SQL2K-SP4)
>

current activity takes 15 minutes to refresh

Can anyone think of a reason that it would take 15 minutes
for the Current Activity/process info to refresh? The
only thing going on right now is replication. This server
is the secondary/subscriber to two different publishers.
The distributer is also running on this machine. Other
than the replication processes, nothing else is
running... Thanks.Have you checked Task Manager/Perfmon to see if something else is chewing up
the CPU unrelated to SQl Server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:208bd01c45958$120666a0$a101280a@.phx
.gbl...
> Can anyone think of a reason that it would take 15 minutes
> for the Current Activity/process info to refresh? The
> only thing going on right now is replication. This server
> is the secondary/subscriber to two different publishers.
> The distributer is also running on this machine. Other
> than the replication processes, nothing else is
> running... Thanks.|||No because the server guys have everything secured
extremely tight. I can't run anything remotely. Strange,
I know.

>--Original Message--
>Have you checked Task Manager/Perfmon to see if something
else is chewing up
>the CPU unrelated to SQl Server?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:208bd01c45958$120666a0$a101280a@.phx
.gbl...
minutes[vbcol=seagreen]
server[vbcol=seagreen]
>
>.
>|||Not really...happens all the time.
Have you cheked activity via QA instead of EM, using SP_Who, sp_who2,
SP_Lock, dbcc opentran, etc to see if something is hanging out there?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
<anonymous@.discussions.microsoft.com> wrote in message
news:205b401c4595a$de0f7460$a301280a@.phx
.gbl...[vbcol=seagreen]
> No because the server guys have everything secured
> extremely tight. I can't run anything remotely. Strange,
> I know.
>
> else is chewing up
> Worth area DBAs.
> message
> minutes
> server|||I ran sp_who2 and the only thing that seems to be eating
CPU is the two distribution agents. Although sp_lock is
coming back with over 1.1 millon rows... That doesn't
sound right for a server with no production activity and
only the replication agents running. Strange.
>--Original Message--
>Not really...happens all the time.
>Have you cheked activity via QA instead of EM, using
SP_Who, sp_who2,
>SP_Lock, dbcc opentran, etc to see if something is
hanging out there?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
><anonymous@.discussions.microsoft.com> wrote in message
> news:205b401c4595a$de0f7460$a301280a@.phx
.gbl...
Strange,[vbcol=seagreen]
something[vbcol=seagreen]
The[vbcol=seagreen]
publishers.[vbcol=seagreen]
Other[vbcol=seagreen]
>
>.
>|||Check the spid or spids for the locks when you execute
sp_lock. Then check what the spids are doing with dbcc
inputbuffer(SpidNumber)
-Sue
On Wed, 23 Jun 2004 13:07:02 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I ran sp_who2 and the only thing that seems to be eating
>CPU is the two distribution agents. Although sp_lock is
>coming back with over 1.1 millon rows... That doesn't
>sound right for a server with no production activity and
>only the replication agents running. Strange.
>SP_Who, sp_who2,
>hanging out there?
>Worth area DBAs.
>Strange,
>something
>The
>publishers.
>Other

current activity takes 15 minutes to refresh

Can anyone think of a reason that it would take 15 minutes
for the Current Activity/process info to refresh? The
only thing going on right now is replication. This server
is the secondary/subscriber to two different publishers.
The distributer is also running on this machine. Other
than the replication processes, nothing else is
running... Thanks.
Have you checked Task Manager/Perfmon to see if something else is chewing up
the CPU unrelated to SQl Server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:208bd01c45958$120666a0$a101280a@.phx.gbl...
> Can anyone think of a reason that it would take 15 minutes
> for the Current Activity/process info to refresh? The
> only thing going on right now is replication. This server
> is the secondary/subscriber to two different publishers.
> The distributer is also running on this machine. Other
> than the replication processes, nothing else is
> running... Thanks.
|||No because the server guys have everything secured
extremely tight. I can't run anything remotely. Strange,
I know.

>--Original Message--
>Have you checked Task Manager/Perfmon to see if something
else is chewing up
>the CPU unrelated to SQl Server?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:208bd01c45958$120666a0$a101280a@.phx.gbl...
minutes[vbcol=seagreen]
server
>
>.
>
|||Not really...happens all the time.
Have you cheked activity via QA instead of EM, using SP_Who, sp_who2,
SP_Lock, dbcc opentran, etc to see if something is hanging out there?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
<anonymous@.discussions.microsoft.com> wrote in message
news:205b401c4595a$de0f7460$a301280a@.phx.gbl...[vbcol=seagreen]
> No because the server guys have everything secured
> extremely tight. I can't run anything remotely. Strange,
> I know.
> else is chewing up
> Worth area DBAs.
> message
> minutes
> server
|||I ran sp_who2 and the only thing that seems to be eating
CPU is the two distribution agents. Although sp_lock is
coming back with over 1.1 millon rows... That doesn't
sound right for a server with no production activity and
only the replication agents running. Strange.
>--Original Message--
>Not really...happens all the time.
>Have you cheked activity via QA instead of EM, using
SP_Who, sp_who2,
>SP_Lock, dbcc opentran, etc to see if something is
hanging out there?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.[vbcol=seagreen]
><anonymous@.discussions.microsoft.com> wrote in message
>news:205b401c4595a$de0f7460$a301280a@.phx.gbl...
Strange,[vbcol=seagreen]
something[vbcol=seagreen]
The[vbcol=seagreen]
publishers.[vbcol=seagreen]
Other
>
>.
>
|||Check the spid or spids for the locks when you execute
sp_lock. Then check what the spids are doing with dbcc
inputbuffer(SpidNumber)
-Sue
On Wed, 23 Jun 2004 13:07:02 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I ran sp_who2 and the only thing that seems to be eating
>CPU is the two distribution agents. Although sp_lock is
>coming back with over 1.1 millon rows... That doesn't
>sound right for a server with no production activity and
>only the replication agents running. Strange.
>SP_Who, sp_who2,
>hanging out there?
>Worth area DBAs.
>Strange,
>something
>The
>publishers.
>Other

current activity takes 15 minutes to refresh

Can anyone think of a reason that it would take 15 minutes
for the Current Activity/process info to refresh? The
only thing going on right now is replication. This server
is the secondary/subscriber to two different publishers.
The distributer is also running on this machine. Other
than the replication processes, nothing else is
running... Thanks.Have you checked Task Manager/Perfmon to see if something else is chewing up
the CPU unrelated to SQl Server?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:208bd01c45958$120666a0$a101280a@.phx.gbl...
> Can anyone think of a reason that it would take 15 minutes
> for the Current Activity/process info to refresh? The
> only thing going on right now is replication. This server
> is the secondary/subscriber to two different publishers.
> The distributer is also running on this machine. Other
> than the replication processes, nothing else is
> running... Thanks.|||No because the server guys have everything secured
extremely tight. I can't run anything remotely. Strange,
I know.
>--Original Message--
>Have you checked Task Manager/Perfmon to see if something
else is chewing up
>the CPU unrelated to SQl Server?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:208bd01c45958$120666a0$a101280a@.phx.gbl...
>> Can anyone think of a reason that it would take 15
minutes
>> for the Current Activity/process info to refresh? The
>> only thing going on right now is replication. This
server
>> is the secondary/subscriber to two different publishers.
>> The distributer is also running on this machine. Other
>> than the replication processes, nothing else is
>> running... Thanks.
>
>.
>|||Not really...happens all the time.
Have you cheked activity via QA instead of EM, using SP_Who, sp_who2,
SP_Lock, dbcc opentran, etc to see if something is hanging out there?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
<anonymous@.discussions.microsoft.com> wrote in message
news:205b401c4595a$de0f7460$a301280a@.phx.gbl...
> No because the server guys have everything secured
> extremely tight. I can't run anything remotely. Strange,
> I know.
> >--Original Message--
> >Have you checked Task Manager/Perfmon to see if something
> else is chewing up
> >the CPU unrelated to SQl Server?
> >
> >--
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
> Worth area DBAs.
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:208bd01c45958$120666a0$a101280a@.phx.gbl...
> >> Can anyone think of a reason that it would take 15
> minutes
> >> for the Current Activity/process info to refresh? The
> >> only thing going on right now is replication. This
> server
> >> is the secondary/subscriber to two different publishers.
> >> The distributer is also running on this machine. Other
> >> than the replication processes, nothing else is
> >> running... Thanks.
> >
> >
> >.
> >|||I ran sp_who2 and the only thing that seems to be eating
CPU is the two distribution agents. Although sp_lock is
coming back with over 1.1 millon rows... That doesn't
sound right for a server with no production activity and
only the replication agents running. Strange.
>--Original Message--
>Not really...happens all the time.
>Have you cheked activity via QA instead of EM, using
SP_Who, sp_who2,
>SP_Lock, dbcc opentran, etc to see if something is
hanging out there?
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
><anonymous@.discussions.microsoft.com> wrote in message
>news:205b401c4595a$de0f7460$a301280a@.phx.gbl...
>> No because the server guys have everything secured
>> extremely tight. I can't run anything remotely.
Strange,
>> I know.
>> >--Original Message--
>> >Have you checked Task Manager/Perfmon to see if
something
>> else is chewing up
>> >the CPU unrelated to SQl Server?
>> >
>> >--
>> >Kevin Hill
>> >President
>> >3NF Consulting
>> >
>> >www.3nf-inc.com/NewsGroups.htm
>> >www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
>> Worth area DBAs.
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:208bd01c45958$120666a0$a101280a@.phx.gbl...
>> >> Can anyone think of a reason that it would take 15
>> minutes
>> >> for the Current Activity/process info to refresh?
The
>> >> only thing going on right now is replication. This
>> server
>> >> is the secondary/subscriber to two different
publishers.
>> >> The distributer is also running on this machine.
Other
>> >> than the replication processes, nothing else is
>> >> running... Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Check the spid or spids for the locks when you execute
sp_lock. Then check what the spids are doing with dbcc
inputbuffer(SpidNumber)
-Sue
On Wed, 23 Jun 2004 13:07:02 -0700,
<anonymous@.discussions.microsoft.com> wrote:
>I ran sp_who2 and the only thing that seems to be eating
>CPU is the two distribution agents. Although sp_lock is
>coming back with over 1.1 millon rows... That doesn't
>sound right for a server with no production activity and
>only the replication agents running. Strange.
>>--Original Message--
>>Not really...happens all the time.
>>Have you cheked activity via QA instead of EM, using
>SP_Who, sp_who2,
>>SP_Lock, dbcc opentran, etc to see if something is
>hanging out there?
>>--
>>Kevin Hill
>>President
>>3NF Consulting
>>www.3nf-inc.com/NewsGroups.htm
>>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
>Worth area DBAs.
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:205b401c4595a$de0f7460$a301280a@.phx.gbl...
>> No because the server guys have everything secured
>> extremely tight. I can't run anything remotely.
>Strange,
>> I know.
>> >--Original Message--
>> >Have you checked Task Manager/Perfmon to see if
>something
>> else is chewing up
>> >the CPU unrelated to SQl Server?
>> >
>> >--
>> >Kevin Hill
>> >President
>> >3NF Consulting
>> >
>> >www.3nf-inc.com/NewsGroups.htm
>> >www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
>> Worth area DBAs.
>> >
>> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:208bd01c45958$120666a0$a101280a@.phx.gbl...
>> >> Can anyone think of a reason that it would take 15
>> minutes
>> >> for the Current Activity/process info to refresh?
>The
>> >> only thing going on right now is replication. This
>> server
>> >> is the secondary/subscriber to two different
>publishers.
>> >> The distributer is also running on this machine.
>Other
>> >> than the replication processes, nothing else is
>> >> running... Thanks.
>> >
>> >
>> >.
>> >
>>
>>.

Current Activity - Process Info

Hi, please i need to know where sql save (table) the info that i see in Managment/Current Activity - Process Info
thanks so muchTry master..sysprocesses|||hi, thanks for your reply, but how can i obtain the info of the last tsql command executed?, in the table sysprocess i only have the number of spid

thanks so much|||Maybe this helps: http://www.dbforums.com/showthread.php?t=1613608|||use command dbcc inputbuffer(spid)

Cubes process forever

So everything has been working fine for 2 months now. Last night some updates got installed. SQL service pack 2 being one of them and some standard windows updates. The server is still running Windows server 2003 service pack 1. I now can't process my cubes or deploy them. It's the only thing that has changed so I am taking it as an assumption. Is there any other reason for cubes to suddenly just sit in the processing state forever?

This is a big problem for me and I am hoping that installing Service pack 2 for the windows server will sort it out but if it doesn't I am pretty much lost. So if you have any ideas please let me know.

Thanks in advance guys

Regards

RyanN

Do you make full process? how meny partitions has your database in all cubes together?

I had a similar problem as your one.

It was solved through increasing of <ThreadPool><Process><MaxThreads>

|||

I can't answer your question but I have a similar problem. My processing doesn't take forever, but at least much longer with SP2 (see thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1472639&SiteID=1). I was thinking that maybe SP2 use (much) more memory than SP1, and therefore slows down my processing (or in fact all steps in our ETL process). I gonna set up perfmon to monitor memory, cpu and disks.

Vladimir: Was this after you installed SP2? Does <ThreadPool><Process><MaxThreads> apply to SQL Server Standard aswell?

|||

Thanks, this is one partition. If I create a new cube it deploys and processes perfectly, maybe there is something wrong with the cube database, I don't actually know. But I am sure there is a solution out there. I will definitely post it once I have found it.

Thanks for your help guys. I am definitely exploring all the suggestions posted to me.

|||Well for those of you who want to know. I uninstalled and re-installed, went onto sp1 with all the hotfixes and it works perfectly now. I am going to set up a test environment and try duplicate the scenario and see what went wrong where. It doesn't seem to be too common, so I will se what went wrong|||

Hi RyanN,

Something I did was to replace the named queries in the DSV, with index queries ("materialized queries") at the database level. This helps because the DB "precalculates" and actual stores / caches the views resultset, meaning that when the DSV references that indexed query, that there's no "heavy lifting" for the DB to do, so processing is quicker. However, if the named query in your DSV is already pretty basic, and the tables it's referencing are appropriately linked, this approach probably won't buy you much benefit.

HTH

Greg Withers

|||

Sorry - my preceding reply to RyanN should have said

"and if the tables are appropriately indexed"

|||

Hi HappyCow

Sure, the problem came with SP2 on x86. But on x64 it was on SP1 too.

I can't answer you regardin Std. edition. I use the Enterprise only.

|||Nope, can't believe that installing a new service should make me have to change the design of my cubes and cause me work at all. If anything it should make things more optimal. I am going to duplicate the steps I took and how the cubes reacted. This should never have happened.

Cubes process forever

So everything has been working fine for 2 months now. Last night some updates got installed. SQL service pack 2 being one of them and some standard windows updates. The server is still running Windows server 2003 service pack 1. I now can't process my cubes or deploy them. It's the only thing that has changed so I am taking it as an assumption. Is there any other reason for cubes to suddenly just sit in the processing state forever?

This is a big problem for me and I am hoping that installing Service pack 2 for the windows server will sort it out but if it doesn't I am pretty much lost. So if you have any ideas please let me know.

Thanks in advance guys

Regards

RyanN

Do you make full process? how meny partitions has your database in all cubes together?

I had a similar problem as your one.

It was solved through increasing of <ThreadPool><Process><MaxThreads>

|||

I can't answer your question but I have a similar problem. My processing doesn't take forever, but at least much longer with SP2 (see thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1472639&SiteID=1). I was thinking that maybe SP2 use (much) more memory than SP1, and therefore slows down my processing (or in fact all steps in our ETL process). I gonna set up perfmon to monitor memory, cpu and disks.

Vladimir: Was this after you installed SP2? Does <ThreadPool><Process><MaxThreads> apply to SQL Server Standard aswell?

|||

Thanks, this is one partition. If I create a new cube it deploys and processes perfectly, maybe there is something wrong with the cube database, I don't actually know. But I am sure there is a solution out there. I will definitely post it once I have found it.

Thanks for your help guys. I am definitely exploring all the suggestions posted to me.

|||Well for those of you who want to know. I uninstalled and re-installed, went onto sp1 with all the hotfixes and it works perfectly now. I am going to set up a test environment and try duplicate the scenario and see what went wrong where. It doesn't seem to be too common, so I will se what went wrong|||

Hi RyanN,

Something I did was to replace the named queries in the DSV, with index queries ("materialized queries") at the database level. This helps because the DB "precalculates" and actual stores / caches the views resultset, meaning that when the DSV references that indexed query, that there's no "heavy lifting" for the DB to do, so processing is quicker. However, if the named query in your DSV is already pretty basic, and the tables it's referencing are appropriately linked, this approach probably won't buy you much benefit.

HTH

Greg Withers

|||

Sorry - my preceding reply to RyanN should have said

"and if the tables are appropriately indexed"

|||

Hi HappyCow

Sure, the problem came with SP2 on x86. But on x64 it was on SP1 too.

I can't answer you regardin Std. edition. I use the Enterprise only.

|||Nope, can't believe that installing a new service should make me have to change the design of my cubes and cause me work at all. If anything it should make things more optimal. I am going to duplicate the steps I took and how the cubes reacted. This should never have happened.

Cubes process error

Hi all!

After processing my cubes I have this error: Server error : Process error [object doesn't exist] 'Partner' ; ?

Someone can help me?
Thanks!

Try to do this - go to Business Intelligence development studio, open your data source view and then on the diagram do right mouse click choose option "Refresh...". This will tell you what changes are between what you have in SSAS and what is really in database. It looks like one of your dimensions or measure groups is referencing table that does not more exists in source database.

Vidas Matelis

My Blog: http://www.ssas-info.com/content/blogcategory/14/36/

Cubes not available after processing

The problem is that the cube is not available for queries. But process structure has been made, a full process of the dimensions and of the measure groups.

This is what I did do:
I did Import the SSAS Project from my live server to my local machine (laptop). I did some changings in the project in the dimensions and measure groups and deployed the solution to the server. After this I did a process structure. I logged into Analysis Server, opened a mdx script and the cube was available. Then I did a FULL Process of all the dimensions to be sure that everything is allright with them. Then I did reprocess all my Measure Groups.

Now the cube is not available. This happend the second time now. I process the cube by using ascmd.exe executing xmla commands.
After I do a process structure again it works. I can see the cube and the measure data is available after processing the measure groups.

I cannot explain this behaviour. Can you?

Best regards,
Stefoon

What happens is that when you do a full process of a dimension. The structures of any related cubes become "unprocessed". Processing a measure group under the cube does not implicitly re-process the structure of the cube.

After you do a full process of a dimension you will need to reprocess the structure again.

|||

Hi,

I think you are right. I did forget about the fact that the full process destroys the maps auf the dimensions for the cube construct.

Best regards,

Stefoon

Cubes Generation error. different between MOLAP, ROLAP

I am using one cubes for my sales analysis.
When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
create error with something related to fail to create index.
I didn't change the cube design.
What information I need to be careful ?
Hi Kam.
This error is usally related to Real-Time OLAP. Are you trying to re-design
the storage mode of your cube as a Real-Time? This feature requires special
settings in the relational database.
You may find more information here
http://msdn.microsoft.com/library/de...eties_0o4z.asp
Hope that helps.
"Kam" wrote:

> I am using one cubes for my sales analysis.
> When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
> create error with something related to fail to create index.
> I didn't change the cube design.
> What information I need to be careful ?

Cubes Generation error. different between MOLAP, ROLAP

I am using one cubes for my sales analysis.
When I try to redesign storage and Process the cube from MOLAP to ROLAP, it
create error with something related to fail to create index.
I didn't change the cube design.
What information I need to be careful ?Hi Kam.
This error is usally related to Real-Time OLAP. Are you trying to re-design
the storage mode of your cube as a Real-Time? This feature requires special
settings in the relational database.
You may find more information here
http://msdn.microsoft.com/library/d...ieties_0o4z.asp
Hope that helps.
"Kam" wrote:

> I am using one cubes for my sales analysis.
> When I try to redesign storage and Process the cube from MOLAP to ROLAP, i
t
> create error with something related to fail to create index.
> I didn't change the cube design.
> What information I need to be careful ?

Cube, Build, Deploy, Process

Dear all,

I'd like to get simple and clear explanation of the cube in data mining, and 3 notions we encounter a lot : Build, Deploy, and Process.

(1) What is the cube that is created when we deploy a mining solution/project?
I wonder what type of cubes they are because although the dialog on deploy/process
show that cube, after successful deployment we still don't see the cube in Cubes folder
of the project.

(2) Why the SQL Server created that cube? Even though we process only one table
and only use case-table (without nested table)

(3) Can someone explain these 3 concepts with CLEAR differences between them?
(A) Build
(B) Deploy
(C) Process

As far as I know, the stages are like that : build, then deploy, then process. Also, it seems
to me that those operations do not create objects inside 'Relational' database, but create
objects (binary and text, with text files usually in XMLA programming language) in the
related project's folders and subfolders. Any good explanation is appreciated.

Bernaridho

1 & 2: The cube is an internal object used as a data structures in processing the mining structure and models. It is not visible externally and should be ignored. The only reason the cube is visible during processing is to surface potential processing errors, such as type mismatches.

3: Build - build takes the project's objects and builds a script that can be sent to the Analysis Services server. As with any "build" process, validation occurs to verify if any ovbious errors are present in the project

Deploy - Deploy sends the project to a server and creates any objects in that project on the server. Since the unit of deploymet is a database, any same-named database will be overwritten.

Process: Process is when the objects actually consume data and become useful. Prior to processing, the objects are "empty" and con not be queired.

|||Hi Jamie,

Thank you for replying. I'd like to add something about 'Process' that I found in your
book 'Data Mining with SQL Server 2005'. In the context of Data Mining, Process
also means 'Train'. That is, it's during 'Process' that the Analysis Service (background
process) trains the mining model. Hopefully this is useful for other member of this forum.

Thank you,

Bernaridho

Cube, Build, Deploy, Process

Dear all,

I'd like to get simple and clear explanation of the cube in data mining, and 3 notions we encounter a lot : Build, Deploy, and Process.

(1) What is the cube that is created when we deploy a mining solution/project?
I wonder what type of cubes they are because although the dialog on deploy/process
show that cube, after successful deployment we still don't see the cube in Cubes folder
of the project.

(2) Why the SQL Server created that cube? Even though we process only one table
and only use case-table (without nested table)

(3) Can someone explain these 3 concepts with CLEAR differences between them?
(A) Build
(B) Deploy
(C) Process

As far as I know, the stages are like that : build, then deploy, then process. Also, it seems
to me that those operations do not create objects inside 'Relational' database, but create
objects (binary and text, with text files usually in XMLA programming language) in the
related project's folders and subfolders. Any good explanation is appreciated.

Bernaridho

1 & 2: The cube is an internal object used as a data structures in processing the mining structure and models. It is not visible externally and should be ignored. The only reason the cube is visible during processing is to surface potential processing errors, such as type mismatches.

3: Build - build takes the project's objects and builds a script that can be sent to the Analysis Services server. As with any "build" process, validation occurs to verify if any ovbious errors are present in the project

Deploy - Deploy sends the project to a server and creates any objects in that project on the server. Since the unit of deploymet is a database, any same-named database will be overwritten.

Process: Process is when the objects actually consume data and become useful. Prior to processing, the objects are "empty" and con not be queired.

|||Hi Jamie,

Thank you for replying. I'd like to add something about 'Process' that I found in your
book 'Data Mining with SQL Server 2005'. In the context of Data Mining, Process
also means 'Train'. That is, it's during 'Process' that the Analysis Service (background
process) trains the mining model. Hopefully this is useful for other member of this forum.

Thank you,

Bernaridho

Cube won't process in SSIS, but will process thru Mgmt Studio?

Hello,

SSAS newbie here. I have an AS database called "AS_Sales" with numerous cubes, including one called "CP Sales". It has one measure group (also called "CP Sales") that is partitioned by fiscal quarter.

I can right-click the cube in management studio and hit "Process" and it indicates that everything completes successfully. However, I set up an Analysis Services task in SSIS to do the same thing, and I receive errors.

SSIS package consists of two AS tasks -- Refresh Dimensions & Reprocess Cube. The task fails during the dimension reprocessing, with the following error messages:

Information: 0x40016041 at AS_Sales_Cubes_Refresh: The package is attempting to configure from the XML file "E:\SSIS\Config\AS_Sales_Cubes_Refresh.dtsConfig".

SSIS package "AS_Sales_Cubes_Refresh.dtsx" starting.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2001_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2003_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR2' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2007_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2006_QTR1' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Task failed: Rebuild Dimension Structure

SSIS package "AS_Sales_Cubes_Refresh.dtsx" finished: Failure.

It only seems to be failing for a handful of the partitions, which makes the problem all that more confusing. Does anyone have any ideas about what I'm doing wrong?

Thanks in advance,

Jamie

if you have defined your attribute relationships as rigid you cannot use refresh for your dimensions. Try full process of each dimension and see what happens. Place the processing(full) of the dimensions before processing the cubes/measure groups.

HTH

Thomas Ivarsson