Cursors vs. Queries: performance

Showing posts with label performance. Show all posts

Thursday, March 29, 2012

Cursor Vs. Performance

We have a program that is opening a recordset as a forward only cursor.
What we are experiencing is that the cursor is being opened thousands of
times per minute by our different clients. When this happens our entire
system slows down. I have asked that we not use cursors and return the
entire recordset and release it. During these periods of slow downs we are
experiencing a high number of locks and a high number of Work Tables being
created as well as a high number of Page Splits. The odd thing is that when
I run Profiler, during these slow times, I am not showing any Duration
issues. I do see a large amount of batches being processed (mostly curor
fetches).
I have told the programmers to fix the cursor problem, but I have been
tasked to prove that this is the cause of the slow downs. Any ideas?some thoughts...
1. You're right. This needs to change...
2. you may not see duration problems in profiler. Each individual cursor
(whether it's TSQL or API) might only take a MS or so. But... I just
finsihed working on a system doing over 1000 sp_cursorfetches per second.
Each call showed 0 or 10MS in Profiler. But the aggregate time for these
calls was taking close to 75% of CPU (note, Profiler will generally not show
a duration less than 10MS. SQL is fast, but it's not faster than the speed
of light and cursors take more than 0MS. I usually round to 2.5MS as an apx
time for cursor duration times...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> We have a program that is opening a recordset as a forward only cursor.
> What we are experiencing is that the cursor is being opened thousands of
> times per minute by our different clients. When this happens our entire
> system slows down. I have asked that we not use cursors and return the
> entire recordset and release it. During these periods of slow downs we
are
> experiencing a high number of locks and a high number of Work Tables being
> created as well as a high number of Page Splits. The odd thing is that
when
> I run Profiler, during these slow times, I am not showing any Duration
> issues. I do see a large amount of batches being processed (mostly curor
> fetches).
> I have told the programmers to fix the cursor problem, but I have been
> tasked to prove that this is the cause of the slow downs. Any ideas?
>|||One other strange thing, CPU was only running at about 30%.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> some thoughts...
> 1. You're right. This needs to change...
> 2. you may not see duration problems in profiler. Each individual cursor
> (whether it's TSQL or API) might only take a MS or so. But... I just
> finsihed working on a system doing over 1000 sp_cursorfetches per second.
> Each call showed 0 or 10MS in Profiler. But the aggregate time for these
> calls was taking close to 75% of CPU (note, Profiler will generally not
show
> a duration less than 10MS. SQL is fast, but it's not faster than the speed
> of light and cursors take more than 0MS. I usually round to 2.5MS as an
apx
> time for cursor duration times...
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Joe Jackson" <jj@.microsoft.com> wrote in message
> news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > We have a program that is opening a recordset as a forward only cursor.
> > What we are experiencing is that the cursor is being opened thousands of
> > times per minute by our different clients. When this happens our
entire
> > system slows down. I have asked that we not use cursors and return the
> > entire recordset and release it. During these periods of slow downs we
> are
> > experiencing a high number of locks and a high number of Work Tables
being
> > created as well as a high number of Page Splits. The odd thing is that
> when
> > I run Profiler, during these slow times, I am not showing any Duration
> > issues. I do see a large amount of batches being processed (mostly
curor
> > fetches).
> >
> > I have told the programmers to fix the cursor problem, but I have been
> > tasked to prove that this is the cause of the slow downs. Any ideas?
> >
> >
>|||With a very large number of cursor fetches, assuming your clients are on =separate system to the database, you will be generating a very large =number of network round-trips from the client, to SQl Server and back =again.
So I guess your current bottleneck may possibly be network traffic - =with large number of small packets carrying 1 row each. getting rid of =the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...
> One other strange thing, CPU was only running at about 30%.
> > > "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> > some thoughts...
> >
> > 1. You're right. This needs to change...
> > 2. you may not see duration problems in profiler. Each individual =cursor
> > (whether it's TSQL or API) might only take a MS or so. But... I just
> > finsihed working on a system doing over 1000 sp_cursorfetches per =second.
> > Each call showed 0 or 10MS in Profiler. But the aggregate time for =these
> > calls was taking close to 75% of CPU (note, Profiler will generally =not
> show
> > a duration less than 10MS. SQL is fast, but it's not faster than the =speed
> > of light and cursors take more than 0MS. I usually round to 2.5MS as =an
> apx
> > time for cursor duration times...
> >
> >
> > -- > >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Joe Jackson" <jj@.microsoft.com> wrote in message
> > news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > > We have a program that is opening a recordset as a forward only =cursor.
> > > What we are experiencing is that the cursor is being opened =thousands of
> > > times per minute by our different clients. When this happens our
> entire
> > > system slows down. I have asked that we not use cursors and =return the
> > > entire recordset and release it. During these periods of slow =downs we
> > are
> > > experiencing a high number of locks and a high number of Work =Tables
> being
> > > created as well as a high number of Page Splits. The odd thing is =that
> > when
> > > I run Profiler, during these slow times, I am not showing any =Duration
> > > issues. I do see a large amount of batches being processed =(mostly
> curor
> > > fetches).
> > >
> > > I have told the programmers to fix the cursor problem, but I have =been
> > > tasked to prove that this is the cause of the slow downs. Any =ideas?
> > >
> > >
> >
> >
> >

Cursor Vs. Performance

quote:

> We have a program that is opening a recordset as a forward only cursor.
> What we are experiencing is that the cursor is being opened thousands of
> times per minute by our different clients. When this happens our entire
> system slows down. I have asked that we not use cursors and return the
> entire recordset and release it. During these periods of slow downs we

are

quote:

> experiencing a high number of locks and a high number of Work Tables being
> created as well as a high number of Page Splits. The odd thing is that

when

quote:

> I run Profiler, during these slow times, I am not showing any Duration
> issues. I do see a large amount of batches being processed (mostly curor
> fetches).
> I have told the programmers to fix the cursor problem, but I have been
> tasked to prove that this is the cause of the slow downs. Any ideas?
>

|||One other strange thing, CPU was only running at about 30%.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...

quote:

> some thoughts...
> 1. You're right. This needs to change...
> 2. you may not see duration problems in profiler. Each individual cursor
> (whether it's TSQL or API) might only take a MS or so. But... I just
> finsihed working on a system doing over 1000 sp_cursorfetches per second.
> Each call showed 0 or 10MS in Profiler. But the aggregate time for these
> calls was taking close to 75% of CPU (note, Profiler will generally not

show

quote:

> a duration less than 10MS. SQL is fast, but it's not faster than the speed
> of light and cursors take more than 0MS. I usually round to 2.5MS as an

apx

quote:

> time for cursor duration times...
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Joe Jackson" <jj@.microsoft.com> wrote in message
> news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
entire[QUOTE]
> are
being[QUOTE]
> when
curor[QUOTE]
>

|||With a very large number of cursor fetches, assuming your clients are on =
separate system to the database, you will be generating a very large =
number of network round-trips from the client, to SQl Server and back =
again.=20
So I guess your current bottleneck may possibly be network traffic - =
with large number of small packets carrying 1 row each. getting rid of =
the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =
news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...

quote:

> One other strange thing, CPU was only running at about 30%.
>=20
>=20
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
cursor[QUOTE]
second.[QUOTE]
these[QUOTE]
not[QUOTE]
> show
speed[QUOTE]
an[QUOTE]
> apx
cursor.[QUOTE]
thousands of[QUOTE]
> entire
return the[QUOTE]
downs we[QUOTE]
Tables[QUOTE]
> being
that[QUOTE]
Duration[QUOTE]
(mostly[QUOTE]
> curor
been[QUOTE]
ideas?[QUOTE]
>=20
>

sql

Cursor vs. Multiple hits from the client

I know cursors are controversial and can cause performance, etc. issues.
The question I'm about to pose is because of performance concerns so please
hear me out.
I'm trying to get a better understanding of what the impact of round trips
from the client to the server are, not the impact on the client, but on the
server. If I have a query that returns 30 rows and then each of the 30 rows
needs to have some work done -- that can all be done in SQL Server and
doesn't require any outside resources -- and then have a final result set
returned, am I better off with A or B:
A Call proc1 from client and get 30 rows. Loop on client and make 30
consecutive calls to the server to proc2
B. Have one proc that does cursor over 30 rows and and then processes each
one and then returns final result set
I know an RDBMS isn't designed for procedural stuff and its more efficient
to do conditional and loop processing on a client. But the processing in
this proc is minimal. I can't do a set based solution because I'm working
with possibly locked records that I need to pass over using SET LOCK_TIMEOUT
0 (and I can't use READPAST because its very specific in nature and doesn't
work in this scenario).
So what it boils down to is how do I determine if the network layer/etc.
processing of 30 client requests is more expensive than having a very
minimal cursor (one int column, 30 rows max) and doing looping in the
procedure? I want to answer this question not only for this specific
instance but also to be able intelligently determine it for future cases.
Thanks for any help,
Mike<disclaimer> In general cursors are bad, very bad. That said they are a tool
and every tool has its purpose in life so choose your tools wisely.
</disclaimer>
From what you've described, I'd choose option B. For me, the situation
comes down to answering he question, why incur the added overhead of round
trips to the client, if they are not need? Just ensure that the transaction
scope managed appropriately for your situation and do the work on the
server.
--Brian
(Please reply to the newsgroups only.)
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23pGAbQTuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I know cursors are controversial and can cause performance, etc. issues.
>The question I'm about to pose is because of performance concerns so please
>hear me out.
> I'm trying to get a better understanding of what the impact of round trips
> from the client to the server are, not the impact on the client, but on
> the server. If I have a query that returns 30 rows and then each of the
> 30 rows needs to have some work done -- that can all be done in SQL Server
> and doesn't require any outside resources -- and then have a final result
> set returned, am I better off with A or B:
> A Call proc1 from client and get 30 rows. Loop on client and make 30
> consecutive calls to the server to proc2
> B. Have one proc that does cursor over 30 rows and and then processes each
> one and then returns final result set
> I know an RDBMS isn't designed for procedural stuff and its more efficient
> to do conditional and loop processing on a client. But the processing in
> this proc is minimal. I can't do a set based solution because I'm working
> with possibly locked records that I need to pass over using SET
> LOCK_TIMEOUT 0 (and I can't use READPAST because its very specific in
> nature and doesn't work in this scenario).
> So what it boils down to is how do I determine if the network layer/etc.
> processing of 30 client requests is more expensive than having a very
> minimal cursor (one int column, 30 rows max) and doing looping in the
> procedure? I want to answer this question not only for this specific
> instance but also to be able intelligently determine it for future cases.
> Thanks for any help,
> Mike
>|||> B. Have one proc that does cursor over 30 rows and and then processes each
> one and then returns final result set
If you can provide more specific details, I think it is quite possible that
you can remove this requirement of "processes each one" and treat the 30
rows as a set. Eliminating any kind of iteration / looping on both client
and server is really going to speed up this process. With "possibly locked
records" you can either set an isolation level that allows you to wait for
the current locks to be released, and still treat them as a set, or you can
come back to the client and say, couldn't lock all rows, try again. I'm
curious how you are going to handle the case where the fifth row you are
trying to process is locked. You're going to skip past it, and return to
the client, guess what, I updated 29 of 30 rows?
If the cursor really is required (in most cases it's not), then Brian is
right, you are probably better off avoiding the multiple round trips. But
even that is not guaranteed, so it comes down to testing in your actual
environment under realistic load.|||> If you can provide more specific details...
For ludicrous level details see: (make sure to read the quoted text at the
end which is the actual description)
http://groups.google.com/group/micr...1089d9f553f62a2
Yup. Its being used as a work queue. I grab up to 30 "candidate" records
using WITH (NOLOCK) and then SET LOCK_TIMEOUT 0 and use the cursor to
attempt to "grab" each record by updating a status. If I get the lock
timeout error, I just skip it. It will be tried again on a subsequent poll.
There's a lot more to it if you see the link above, but that's the basics
and should be enough to show why I need a cursor (or multiple calls from
client).
I don't use READPAST hint because its very specific in its application.
From my research and experimenting, the work queue table has to be designed
very specifically and accessed very specifically since READPAST only reads
past index locks on the same index its currently using, doesn't read past
data locks, and doesn't read past locks from non READ COMMITTED
transactions. I'm doing optimizations on an existing database and don't
have the luxury of doing major modifications to the structure currently to
make it work with READPAST.
Mike|||> and should be enough to show why I need a cursor (or multiple calls from
> client).
Okay, I'm not convinced but you seem to be, so test, test, test. If you
absolutely have to use a cursor here, I think it will be very difficult for
anyone to tell you "using cursor approach a will be better than cursor
approach b"... because there are far more variables involved than just the
number of rows and the two high-level methodologies.|||> Okay, I'm not convinced but you seem to be, so test, test, test. If you
> absolutely have to use a cursor here,
If I had an approach to do this without cursors or multiple clients calls, I
would do it in a flash.
I don't know any other way to "grab a set of rows, but only the rows that
aren't locked" (besides READPAST which I've already described doesn't work
in this scenario).

> I think it will be very difficult for anyone to tell you "using cursor
> approach a will be better than cursor approach b"... because there are far
> more variables involved than just the number of rows and the two
> high-level methodologies.
What I'm looking for specifically is a way to measure the overhead of client
calls vs. doing the same logic inside a stored proc using a cursor if
necessary. Can I measure the cpu and other cost of the various layers on
the server in between the client request (coming in on a socket) and the
actual execution in SQL? Or is this overhead so negligible that I shouldn't
worry about 30 client calls to the server? (the connection is already
established so there's no extra overhead there). We hear comments about
this performing badly and that performing badly, and I'm just trying to get
a grasp on what will perform badly in 30 client calls. I understand that
network latency can have performance effects on the overall transaction (if
an actual transaction is held across calls) and client performance (because
the overall time of 30 round trips may be much slower than 1 single call
over the network to the server). What I want to understand is the
effect/overhead on the _server_ of each client request.
If I can figure out how to monitor that kind of performance and compare it
to the performance/overhead of the cursor and the logic in the stored proc,
I can then make a case-by-case decision on which is better. Sorry if I
wasn't super clear initially. I think I'm just realizing myself what
exactly it is I'm looking for.
Thanks,
Mike|||> What I'm looking for specifically is a way to measure the overhead of
> client calls vs. doing the same logic inside a stored proc using a cursor
> if necessary. Can I measure the cpu and other cost of the various layers
> on the server in between the client request (coming in on a socket) and
> the actual execution in SQL?
Well, you could buy a testing suite which measures the performance end to
end in each scenario (http://www.aspfaq.com/2139) and on SQL Server directly
(http://www.aspfaq.com/2513). You could use Profiler / Performance Monitor
for the back-end side. In both cases, I think it might be tough to
disseminate between delays caused by round trips and client processing and
delays due to the different methods in SQL.
Also see http://www.aspfaq.com/2245 for some direct methods (though not all
are applicable).
A|||Thanks for the info.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwe6VQWuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Well, you could buy a testing suite which measures the performance end to
> end in each scenario (http://www.aspfaq.com/2139) and on SQL Server
> directly (http://www.aspfaq.com/2513). You could use Profiler /
> Performance Monitor for the back-end side. In both cases, I think it
> might be tough to disseminate between delays caused by round trips and
> client processing and delays due to the different methods in SQL.
> Also see http://www.aspfaq.com/2245 for some direct methods (though not
> all are applicable).
> A
>

Cursor update performance issue

I am using the following code which works fine except that when there are alot of rows being used (> 500) this performs really slow and I get a timeout error. Any ideas on how to make this faster since I need to update multiple rows, multiple times with multiple values?

<code>
DECLARE Item_Cursor CURSOR LOCAL FAST_FORWARD FOR
Select FileObjectId,CopyFileObjectId From FileObject
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId

WHILE @.@.FETCH_STATUS = 0
BEGIN
--update idhierarchies
Update FileObject Set IdHierarchy=Replace(IdHierarchy,'.'+Cast(@.OldId as varchar)+'.','.'+Cast(@.NewId as varchar)+'.')
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

--update parent ids
Update FileObject Set ParentId=@.NewId
Where ParentId=@.OldId
AND CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor

</code>I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?|||If you have any suggestions, let me know... I can't figure out how to do it in one or two update statements, that's definitely how I would prefer to do it and try to shoot for for every op. I just couldn't figur eout how to do that here.

Originally posted by Frettmaestro
I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?sql

Tuesday, March 27, 2012

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 performance

It seems to be a well known fact that you avoid cursors when looping
through data in TSQL, atleast that's what most performance tips tell
you. Using while loops and temporary tables, subqueries and so on.
So why does the MCAD/MCSE (SQL Server exam) preach the way of the
Cursor when they are perform so badly? Who is right in the matter?
Sorry posted to the wrong group
mrshrinkray@.googlemail.com wrote:
> It seems to be a well known fact that you avoid cursors when looping
> through data in TSQL, atleast that's what most performance tips tell
> you. Using while loops and temporary tables, subqueries and so on.
> So why does the MCAD/MCSE (SQL Server exam) preach the way of the
> Cursor when they are perform so badly? Who is right in the matter?
sql

Cursor performance

It seems to be a well known fact that you avoid cursors when looping
through data in TSQL, atleast that's what most performance tips tell
you. Using while loops and temporary tables, subqueries and so on.
So why does the MCAD/MCSE (SQL Server exam) preach the way of the
Cursor when they are perform so badly? Who is right in the matter?Sorry posted to the wrong group
mrshrinkray@.googlemail.com wrote:
> It seems to be a well known fact that you avoid cursors when looping
> through data in TSQL, atleast that's what most performance tips tell
> you. Using while loops and temporary tables, subqueries and so on.
> So why does the MCAD/MCSE (SQL Server exam) preach the way of the
> Cursor when they are perform so badly? Who is right in the matter?

Cursor performance

It seems to be a well known fact that you avoid cursors when looping
through data in TSQL, atleast that's what most performance tips tell
you. Using while loops and temporary tables, subqueries and so on.
So why does the MCAD/MCSE (SQL Server exam) preach the way of the
Cursor when they are perform so badly? Who is right in the matter?Sorry posted to the wrong group
mrshrinkray@.googlemail.com wrote:
> It seems to be a well known fact that you avoid cursors when looping
> through data in TSQL, atleast that's what most performance tips tell
> you. Using while loops and temporary tables, subqueries and so on.
> So why does the MCAD/MCSE (SQL Server exam) preach the way of the
> Cursor when they are perform so badly? Who is right in the matter?

Cursor or Temp table

I just want to know which one is more efficient cursor or temp table for looping through a record set?
I am basically looking for better performance and server utilization.I generally prefer set based solutions over cursors, so I'm more than 95% sure that I'd recommend the temp table solution, but I can't be sure about that without knowing more about the problem.

-PatP|||95%?

That low?|||For looping through a recordset i.e., record by record, you will need a cursor. Ofcourse, you can bring the data from many tables into a #temp table, and use a cursor on it, that is to say, the SELECT statement of the cursor could be on the #temp table.

It may affect the performance of the stored procedure, but it is negligible. It depends on what you want.

Roshmi Choudhury|||It partly depends upon what you plan on doing with the records. If you do a simple SELECT, then most-likely the DBMS is already creating a temporary-table to store the results. If you are requesting a live query-view for updating purposes, the SELECT is effectively a filter on the main table.

Your choice probably won't make any difference to the DBMS server; the impact, if any, will be upon the simultaneous users of the table. ("Large things" and "long-time things" are generally "bad things.")

My rule-of-thumb recommend is K.I.S.S. ... keep it simple. Choose the approach that makes the most sense to your application and let the DBMS take care of its own business. If in the future you can prove that it is a problem, then you can change it.|||I disagree, for looping thru a record set you most definitely do not need a cursor, using an IF WHILE loop is just as effective, and carries none of the problems associated with cursors.. Many shops - mine included - do not allow cursors on their servers...Seems like the only people who want to use a cursor are those people who can't write one that won't get hung and take a server down with it... Just my .02

Nick|||You don't ALLOW cursors? That's (unnecessarily) extreme.

If you truly need to "loop" through your dataset, then a cursor is an appropriate solution. The larger issue is WHETHER you need to loop through the dataset. I'm sure Pat would agree that "95%" of the time when somebody uses a cursor it's because they are not familiar enough with set-based solutions, and in these cases cursors are virtually always less efficient.

Post the problem you are trying to solve, and I'd bet that somebody here can show you how to solve it using set-base operations.

Thursday, March 22, 2012

Cursor and cursorless performance

I am doing the Serial Balance Report

The data is given in a range format i need to culculate the balance,for your information below is an example

Given Stock In Serial Range

10000 - 19999

Stock Out

15000 - 15999

Balance (this is what i calculate out)

10000-14999

16000 -19999

First Loop

BEGIN

--Given the Stock Out Get the Source Stock In and put it into another cursor(Normarlly just 1 record will found)

SECOND LOOP

BEGIN

--calculation

END

Previously Im using 2 cursor to loop and perform the calculation, due to the slow performance i change it to cursorless.

I have found that the first 1000 of 55492(2 Month) data its use almost 10 min to finish up the loop, 2000 used 30 min. The performance is decreasing, I have use up to 17 hour but still havent finish 55492 records.

Please Advice.

You are going to have to be more specific, I think, unless this exact problem reminds them of a problem they have. Can you build a small example and show how you have done it?|||

IF OBJECT_ID('tempdb..#tblBalance') IS NOT NULL
DROP Table #tblBalance

Create Table #tblBalance
(
RowID numeric(18,0) IDENTITY(1, 1) not null,
SerialNoFrom nvarchar(50),
SerialNoTo nvarchar(50))

Declare @.SerialNoFrom varchar(50)
Declare @.SerialNoTo varchar(50)
Declare @.FrontSerialNoFrom nvarchar(50)
Declare @.EndSerialNoTo nvarchar(50)
Declare @.FrontSerialNoFromNext nvarchar(50)
Declare @.EndSerialNoToNext nvarchar(50)
Declare @.SKUCode nvarchar(50)
Declare @.SLoc nvarchar(10)
Declare @.Tmp nvarchar(12)
Declare @.tmpCount integer
Declare @.sql varchar(100)

Declare @.count int
Declare @.iRow int
Declare @.countNext int
Declare @.iRowNext int

SET @.count = (SELECT MAX(RowID) FROM multicom_Qty_Minus)

SET @.iRow = 1

Print 'Total Count' + convert(varchar,@.count)
WHILE @.iRow <= @.count
BEGIN
PRINT 'Current Count ' + Convert(varchar,@.iRow)
PRINT 'GET SERIAL NO FROM'
SET @.SerialNoFrom = (SELECT SerialNoFrom FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SERIAL NO TO'
SET @.SerialNoTo = (SELECT SerialNoTo FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SLOC'
SET @.SLoc = (SELECT SLoc FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SKUCode'
SET @.SKUCode = (SELECT SKUCode FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)

PRINT @.SerialNoFrom + '-' + @.SerialNoTo
PRINT 'SKUCode ' + @.SKUCode
PRINT 'SLoc ' + @.SLoc

INSERT INTO #tblBalance
SELECT SerialNoFrom,SerialNoTo FROM multicom WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND
QuantityPlus > 0 AND
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) = Quantity AND
LEN(SerialNoTo) = LEN(@.SerialNoTo) AND LEN(SerialNoFrom) = LEN(@.SerialNoFrom) AND
SKUCode = @.SKUCode AND SLoc = @.SLoc AND
convert(numeric,SerialNoFrom) <> convert(numeric,@.SerialNoFrom) AND
convert(numeric,SerialNoTo)<>convert(numeric,@.SerialNoTo)AND
((CONVERT(numeric,SerialNoFrom) <= CONVERT(numeric,@.SerialNoFrom) AND
CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@.SerialNoTo)) OR
(CONVERT(numeric,SerialNoTo) >= CONVERT(numeric,@.SerialNoTo) AND
CONVERT(numeric,SerialNoFrom)<= CONVERT(numeric,@.SerialNoFrom)))

SET @.countNext = @.@.ROWCOUNT
SET @.iRowNext = 1

-IF @.countNext == 0 then put the Searil into the ErrorQtyMinus
/*
--
--
*/
IF @.CountNext = 0
BEGIN
INSERT INTO multicom_Error_Qty_Minus
SELECT Plnt,SLoc,
SLocDescription,SKUCode,MvT,PostDate,DocDate,[Serial No (from Material Doc.)],[Serial No (from Delivery Item)],
MatDoc,Item,InvNo,Quantity,Sign,Payer,UserID,PONo,SOff,DeliveryNo,PODate,SOrg,Salesman,MatGroup,DC,SoldTo,CoCd,InvoiceDt,
SerialNoTo,SerialNoFrom,SLocFrom,SLocTo,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,PrdGrp4,QuantityPlus,QuantityMinus
FROM multicom_Qty_Minus WHERE RowID = @.iRow
END
PRINT 'Total 2nd loop COUNT'
PRINT @.countNext
WHILE @.iRowNext <= @.countNext
BEGIN
Print 'Current 2nd loop Count ' + convert(varchar,@.iRowNext)
SET @.FrontSerialNoFrom = (SELECT SerialNoFrom From #tblBalance WHERE RowID = @.iRowNext)
SET @.EndSerialNoTo = (SELECT SerialNoTo From #tblBalance WHERE RowID = @.iRowNext)
Print @.FrontSerialNoFrom + '-' + @.EndSerialNoTo

IF ISNUMERIC(@.FrontSerialNoFrom) = 1 AND ISNUMERIC(@.EndSerialNoTo) = 1 AND ISNUMERIC(@.SerialNoFrom)=1 AND ISNUMERIC(@.SerialNoTo)= 1
BEGIN
IF CONVERT(numeric,@.SerialNoFrom) > CONVERT(numeric,@.FrontSerialNoFrom)
BEGIN
SET @.Tmp = (CONVERT(numeric,@.SerialNoFrom) -1)
SET @.Tmp = REPLICATE('0',(12 - (len(@.Tmp)))) + @.Tmp
PRINT 'BALANCE'
PRINT @.FrontSerialNoFrom + '-' + CONVERT(nvarchar(12),@.Tmp)

PRINT 'QUANTITY'
PRINT (Convert(numeric,@.Tmp) - (Convert(numeric,@.FrontSerialNoFrom)) + 1)

Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@.Tmp) - (Convert(numeric,@.FrontSerialNoFrom)) + 1),
Convert(nvarchar(12),@.Tmp),@.FrontSerialNoFrom,SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @.iRow
END

IF CONVERT(numeric,@.EndSerialNoTo) > CONVERT(numeric,@.SerialNoTo)
BEGIN
SET @.Tmp = (CONVERT(numeric,@.SerialNoTo) + 1)
SET @.Tmp = REPLICATE('0',(12 - (len(@.Tmp)))) + @.Tmp
PRINT 'Last Balance'
PRINT CONVERT(nvarchar(12),@.Tmp) + '-' + @.EndSerialNoTo

PRINT 'QUANTITY'
PRINT (Convert(numeric,@.EndSerialNoTo) - (Convert(numeric,@.Tmp)) + 1)
Insert INTO multicom_Balance
Select Plnt,SLoc,SLocDescription,SKUCode,MvT,PostDate,DocDate,MatDoc,Item,
(Convert(numeric,@.EndSerialNoTo) - (Convert(numeric,@.Tmp)) + 1),
@.EndSerialNoTo,CONVERT(nvarchar(12),@.Tmp),SalesOffDesc,PayerName1,PrdName,MvTDesc,StdCost,QuantityPlus,
QuantityMinus From multicom_Qty_Minus WHERE RowID = @.iRow
END
SET @.FrontSerialNoFrom = ''
SET @.EndSerialNoTo = ''
END
SET @.iRowNext = @.iRowNext + 1
END
Truncate Table #tblBalance
SET @.iRow = @.iRow + 1
END

I think It is my problem not SQL SERVER problem, Is there any way i can speed up the query?

|||

I don;t know what kind of logic you are using behind this query... But I am willing to say few generic comments..

Replace the following statements

SET @.SerialNoFrom = (SELECT SerialNoFrom FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SERIAL NO TO'
SET @.SerialNoTo = (SELECT SerialNoTo FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SLOC'
SET @.SLoc = (SELECT SLoc FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)
PRINT 'GET SKUCode'
SET @.SKUCode = (SELECT SKUCode FROM multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity)

with

SELECT
@.SerialNoFrom=SerialNoFrom,
@.SerialNoTo =SerialNoTo,
@.SLoc=SLoc,
@.SKUCode=SKUCode
FROM
multicom_Qty_Minus WHERE isnumeric(SerialNoTo) = 1 and isnumeric(SerialNoFrom) = 1 AND SerialNoFrom <> '' AND SerialNoTo <> '' AND RowID = @.iRow AND SKUCode <> '100000017' AND
Case when SerialNoFrom <> '' AND SerialNoFrom <> '' AND isnumeric(SerialNoTo) = 1 AND isnumeric(SerialNoFrom) = 1 then
((Convert(numeric,SerialNoTo)-Convert(numeric,SerialNoFrom))+1) END = Quantity

Replace the following statements

SET @.FrontSerialNoFrom = (SELECT SerialNoFrom From #tblBalance WHERE RowID = @.iRowNext)
SET @.EndSerialNoTo = (SELECT SerialNoTo From #tblBalance WHERE RowID = @.iRowNext)

with

SELECT @.FrontSerialNoFrom=SerialNoFrom, @.EndSerialNoTo=SerialNoTo From #tblBalance WHERE RowID = @.iRowNext

When you assing the values from the Select statement you need not to use SET statement; Advantage the same query executed more than once to fetch the value when you use SET, but the suggested query will be executed once and fetch all the required data....

|||

WOW,Thanks a lot.

Sunday, March 11, 2012

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? Sad

|||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency conversion wizard - performance

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? Sad

|||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Curious performance issue when running a query

Hi,
I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.
The table has approximately 5 million rows and includes the following
columns:
DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)
If I issue the following query:
SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
it takes about 23 seconds to return the 481 hits.
The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.
The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:
SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.
Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:
SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'
it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.
What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?
All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/
Thanks
Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.
"Paul Mateer" <p.mateer@.meridio.com> wrote in message
news:424f2ade.0312030409.f602b09@.posting.google.com...
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
>
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
>
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,|||The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.
For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.
I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.
Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:
SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'
Hope this helps,
Gert-Jan
Paul Mateer wrote:
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,

Curious performance issue when running a query

Hi,

I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.

The table has approximately 5 million rows and includes the following
columns:

DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)

If I issue the following query:

SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'

it takes about 23 seconds to return the 481 hits.

The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.

The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.

If I issue the following query:

SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'

it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.

Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.

All this is fine, however when I issue the following query:

SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'

it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.

What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?

All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/

Thanks

Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.

"Paul Mateer" <p.mateer@.meridio.com> wrote in message
news:424f2ade.0312030409.f602b09@.posting.google.co m...
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
>
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
>
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,|||Hi Uri. Thanks for the reply. I tried your suggestion of a covering
index and it worked.

I have a new problem however. In order to keep things simple the queries
that I posted were simplified versions of the actual query that I need
to execute. I cannot create a covering index for all of the required
columns because SQL 2000 limits the key length to 900 bytes and the
total length of all the column data involved in the query is over 1000
bytes in length.

Any suggestions on how to resolve this problem would be greatly
appreciated.

Paul Mateer
Meridio Limited

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.

For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.

I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.

Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:

SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'

Hope this helps,
Gert-Jan

Paul Mateer wrote:
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,

Curious performance experiment

I found an SP scanning a table of 100k rows which lacked the proper
index, doing a join to several other tables. Statistics IO reported
it took 26000 logical reads (to do it twice, btw).
I then added the proper index. Reads dropped to 1500. Good, huh? I
should have declared victory and gone home.
But no, not me. I then dropped the index and reran. It went up
again, of course, but only to 6700 reads.
Um, ... anyone ever see anything like this? I'm at something of a
loss. I thought I'd run it several times in each mode, and in fact
was running it in a loop of 100 from query analyzer to get a decent
clock-time idea of execution (but got the statistics IO from single
executions).
Josh
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.4ax.com...
>I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
>
Was the index perhaps clustered? If so the data would have have been
reorganized by clustering and unclustering it, perhaps making the joins more
efficient.
Also you may have ended up with better statistics after adding and dropping
the index, which enabled a better query plan.
David
|||On Wed, 3 Nov 2004 10:17:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>Was the index perhaps clustered? If so the data would have have been
>reorganized by clustering and unclustering it, perhaps making the joins more
>efficient.
Not clustered.

>Also you may have ended up with better statistics after adding and dropping
>the index, which enabled a better query plan.
But without the index, it has to scan.
It *appears* that the "logical reads" are not all that logical, and
are affected by the data cached. I've seen this phenomenon again
today. It is upsetting. Unless there is some larger principle at
work here I have not yet understood?
Josh
|||JXStern wrote:
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
What are these"reads"? Are you using STATISTS IO for the information? If
so, how many logical vs physical? You might consider using Profiler to
see the reads, CPU, and duration. Don't be alarmed that the reads in
Profiler do not match that of STATISTICS IO. They are reading different
structures and the SQL Trace API will normally report reads that are not
tracked by STATISTICS IO.
David Gugick
Imceda Software
www.imceda.com
|||JxStern
Before starting to tune the query after re-creating an index run the
following commands
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.4ax.com...
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
>
|||On Thu, 4 Nov 2004 09:26:32 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>JxStern
>Before starting to tune the query after re-creating an index run the
>following commands
>DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
>be recompiled
>if you want a stored procedure to be compiled you will need to use the WITH
>RECOMPILE option
>if you want to clear the data cache you will need to use DBCC
>DROPCLEANBUFFERS
>DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
>specific database on a SQL Server, not the entire SQL Server. The database
>ID number to be affected must be entered as part of the command.
All good suggestions.
I did use sp_recompile, but it didn't seem to make any difference.
Which is additionally odd, isn't it? Could it have cached the plan
and the old index? But it didn't return the old (with index) value,
but an intermediate one.
J.
|||On Wed, 3 Nov 2004 19:47:13 -0500, "David Gugick"
<davidg-nospam@.imceda.com> wrote:
>What are these"reads"? Are you using STATISTS IO for the information?
Yes, statistics io, and I'm looking at just the logical reads.

> If
>so, how many logical vs physical?
Lots of logical, very few physical.

>You might consider using Profiler to
>see the reads, CPU, and duration.
I've been running the profiler also, but didn't use it on these cases.

> Don't be alarmed that the reads in
>Profiler do not match that of STATISTICS IO. They are reading different
>structures and the SQL Trace API will normally report reads that are not
>tracked by STATISTICS IO.
I'm just comparing runs via statistics io at this point.
Interesting if profiler shows a different pattern, I'll try to post if
I get the chance to try it.
So, nobody is quite going with my theory that the logical reads aren't
purely logical?
J.

Curious performance experiment

I found an SP scanning a table of 100k rows which lacked the proper
index, doing a join to several other tables. Statistics IO reported
it took 26000 logical reads (to do it twice, btw).
I then added the proper index. Reads dropped to 1500. Good, huh? I
should have declared victory and gone home.
But no, not me. I then dropped the index and reran. It went up
again, of course, but only to 6700 reads.
Um, ... anyone ever see anything like this? I'm at something of a
loss. I thought I'd run it several times in each mode, and in fact
was running it in a loop of 100 from query analyzer to get a decent
clock-time idea of execution (but got the statistics IO from single
executions).
Josh"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.4ax.com...
>I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
>
Was the index perhaps clustered? If so the data would have have been
reorganized by clustering and unclustering it, perhaps making the joins more
efficient.
Also you may have ended up with better statistics after adding and dropping
the index, which enabled a better query plan.
David|||On Wed, 3 Nov 2004 10:17:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>Was the index perhaps clustered? If so the data would have have been
>reorganized by clustering and unclustering it, perhaps making the joins more
>efficient.
Not clustered.
>Also you may have ended up with better statistics after adding and dropping
>the index, which enabled a better query plan.
But without the index, it has to scan.
It *appears* that the "logical reads" are not all that logical, and
are affected by the data cached. I've seen this phenomenon again
today. It is upsetting. Unless there is some larger principle at
work here I have not yet understood?
Josh|||JXStern wrote:
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
What are these"reads"? Are you using STATISTS IO for the information? If
so, how many logical vs physical? You might consider using Profiler to
see the reads, CPU, and duration. Don't be alarmed that the reads in
Profiler do not match that of STATISTICS IO. They are reading different
structures and the SQL Trace API will normally report reads that are not
tracked by STATISTICS IO.
--
David Gugick
Imceda Software
www.imceda.com|||JxStern
Before starting to tune the query after re-creating an index run the
following commands
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.4ax.com...
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
>|||On Thu, 4 Nov 2004 09:26:32 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>JxStern
>Before starting to tune the query after re-creating an index run the
>following commands
>DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
>be recompiled
>if you want a stored procedure to be compiled you will need to use the WITH
>RECOMPILE option
>if you want to clear the data cache you will need to use DBCC
>DROPCLEANBUFFERS
>DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
>specific database on a SQL Server, not the entire SQL Server. The database
>ID number to be affected must be entered as part of the command.
All good suggestions.
I did use sp_recompile, but it didn't seem to make any difference.
Which is additionally odd, isn't it? Could it have cached the plan
and the old index? But it didn't return the old (with index) value,
but an intermediate one.
J.|||On Wed, 3 Nov 2004 19:47:13 -0500, "David Gugick"
<davidg-nospam@.imceda.com> wrote:
>What are these"reads"? Are you using STATISTS IO for the information?
Yes, statistics io, and I'm looking at just the logical reads.
> If
>so, how many logical vs physical?
Lots of logical, very few physical.
>You might consider using Profiler to
>see the reads, CPU, and duration.
I've been running the profiler also, but didn't use it on these cases.
> Don't be alarmed that the reads in
>Profiler do not match that of STATISTICS IO. They are reading different
>structures and the SQL Trace API will normally report reads that are not
>tracked by STATISTICS IO.
I'm just comparing runs via statistics io at this point.
Interesting if profiler shows a different pattern, I'll try to post if
I get the chance to try it.
So, nobody is quite going with my theory that the logical reads aren't
purely logical'
J.

Curious performance experiment

I found an SP scanning a table of 100k rows which lacked the proper
index, doing a join to several other tables. Statistics IO reported
it took 26000 logical reads (to do it twice, btw).
I then added the proper index. Reads dropped to 1500. Good, huh? I
should have declared victory and gone home.
But no, not me. I then dropped the index and reran. It went up
again, of course, but only to 6700 reads.
Um, ... anyone ever see anything like this? I'm at something of a
loss. I thought I'd run it several times in each mode, and in fact
was running it in a loop of 100 from query analyzer to get a decent
clock-time idea of execution (but got the statistics IO from single
executions).
Josh"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.
4ax.com...
>I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
>
Was the index perhaps clustered? If so the data would have have been
reorganized by clustering and unclustering it, perhaps making the joins more
efficient.
Also you may have ended up with better statistics after adding and dropping
the index, which enabled a better query plan.
David|||On Wed, 3 Nov 2004 10:17:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>Was the index perhaps clustered? If so the data would have have been
>reorganized by clustering and unclustering it, perhaps making the joins mor
e
>efficient.
Not clustered.

>Also you may have ended up with better statistics after adding and dropping
>the index, which enabled a better query plan.
But without the index, it has to scan.
It *appears* that the "logical reads" are not all that logical, and
are affected by the data cached. I've seen this phenomenon again
today. It is upsetting. Unless there is some larger principle at
work here I have not yet understood?
Josh|||JXStern wrote:
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
What are these"reads"? Are you using STATISTS IO for the information? If
so, how many logical vs physical? You might consider using Profiler to
see the reads, CPU, and duration. Don't be alarmed that the reads in
Profiler do not match that of STATISTICS IO. They are reading different
structures and the SQL Trace API will normally report reads that are not
tracked by STATISTICS IO.
David Gugick
Imceda Software
www.imceda.com|||JxStern
Before starting to tune the query after re-creating an index run the
following commands
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:kduho09q2pojpejabtn73p13d8m3lq2jnu@.
4ax.com...
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
> Josh
>|||On Thu, 4 Nov 2004 09:26:32 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>JxStern
>Before starting to tune the query after re-creating an index run the
>following commands
>DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
>be recompiled
>if you want a stored procedure to be compiled you will need to use the WITH
>RECOMPILE option
>if you want to clear the data cache you will need to use DBCC
>DROPCLEANBUFFERS
>DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
>specific database on a SQL Server, not the entire SQL Server. The database
>ID number to be affected must be entered as part of the command.
All good suggestions.
I did use sp_recompile, but it didn't seem to make any difference.
Which is additionally odd, isn't it? Could it have cached the plan
and the old index? But it didn't return the old (with index) value,
but an intermediate one.
J.|||On Wed, 3 Nov 2004 19:47:13 -0500, "David Gugick"
<davidg-nospam@.imceda.com> wrote:
>What are these"reads"? Are you using STATISTS IO for the information?
Yes, statistics io, and I'm looking at just the logical reads.

> If
>so, how many logical vs physical?
Lots of logical, very few physical.

>You might consider using Profiler to
>see the reads, CPU, and duration.
I've been running the profiler also, but didn't use it on these cases.

> Don't be alarmed that the reads in
>Profiler do not match that of STATISTICS IO. They are reading different
>structures and the SQL Trace API will normally report reads that are not
>tracked by STATISTICS IO.
I'm just comparing runs via statistics io at this point.
Interesting if profiler shows a different pattern, I'll try to post if
I get the chance to try it.
So, nobody is quite going with my theory that the logical reads aren't
purely logical'
J.

Thursday, March 29, 2012

Cursor Vs. Performance

Cursor Vs. Performance

Cursor vs. Multiple hits from the client

Cursor update performance issue

Tuesday, March 27, 2012

Cursor performance slows exponentially

Cursor performance

Cursor performance

Cursor performance

Cursor or Temp table

Thursday, March 22, 2012

Cursor and cursorless performance

Sunday, March 11, 2012

Currency conversion wizard - performance

Currency conversion wizard - performance

Curious performance issue when running a query

Curious performance issue when running a query

Curious performance experiment

Curious performance experiment

Curious performance experiment

Cursors vs. Queries

Blog Archive

About Me