Prev: delete a record
Next: SQL Server administration scritpts for a new server?
From: Cindy Lee on 21 Jun 2010 01:28
On Jun 20, 1:46 am, Jeroen Mostert <jmost...(a)xs4all.nl> wrote:
> On 2010-06-19 22:14, Cindy Lee wrote:> Yeah, I'm using forward only cursors.  Problem is I need to know if
> > it's the last row before i fetch.  I'm splitting money by percentages
> > and I round up on the first few rows, and the the last row gets what
> > every money is left over.  Otherwise there will be an extra penny.
> > I can either check if the amount left is less than the amount by
> > percentage, or check to see if it's the last row and then just take
> > the amount left.
> > I'm subtracting from the total amount everytime someone gets a
> > percentage.
> > I think it would be easier to just check if it's the last row rather
> > than if the amount left is less than the percentage amount.
>
> You can always complicate the logic by processing row N after fetching row N
> + 1. The last row is then processed after all the others.
>
>    FETCH NEXT FROM c INTO @a1, @b1, @c1, @d1, @e1;
>    WHILE @@FETCH_STATUS = 0 BEGIN
>      FETCH NEXT FROM c INTO @a2, @b2, @c2, @d2, @e2;
>      IF @@FETCH_STATUS <> 0 BREAK;
>      -- Process a1, b1, c1, d1, e1
>      SELECT @a1 = @a2, @b1 = @b2, @c1 = @c2, @d1 = @d2, @e1 = @e2;
>    END
>    -- Process a1, b1, c1, d1, e1 as the last row -- if there were no rows at
> all, the values will be all NULL
>
> Erland's solution with ROW_NUMBER() is preferable from a maintainability
> point of view, though -- my approach is harder to initially understand and
> duplicates code, both of which is bad. Also, a forward ROW_NUMBER() gives
> you an easy way of telling whether you're on "the first few rows".
>
> Because ROW_NUMBER() with a different ORDER BY than the main ORDER BY clause
> requires an additional sort step, the solution with an additional FETCH may
> perform better, but this should not be the overriding concern -- especially
> not since you're already using cursors, which will probably dwarf everything
> else. I'm just offering it as a possible alternative.
>
> Apropos that: judicious use of ROW_NUMBER() and CTEs/subqueries may solve
> your problem without the use of cursors at all, which is possibly better
> (possibly, because cursors can actually be a faster solution for problems
> like these). For example, you can calculate the total amount of money you're
> going to be allocating to rows and then add the leftover to the last row:
>
>    WITH x AS (
>      SELECT
>         money * percentage AS share,
>         CASE WHEN ROW_NUMBER() OVER (ORDER BY a DESC) = 1 THEN 1 ELSE 0 END AS last_row
>      FROM t
>      WHERE ...
>    )
>    SELECT share + CASE last_row WHEN 1 THEN totalmoney - (SELECT SUM(share)
> FROM x) ELSE 0 END
>    FROM x
>    ORDER BY a
>
> The problem here is that the last row determination and total sum require
> extra passes through the data. Cursors may or may not be faster here. The
> query does have the benefit of expressing the logic succinctly, which
> improves maintainability.
>
> --
> J.

Thanks for the responses. I think I like fetch the next one and keep
the old results. Only thing is it will create a few extra variables
which probably doesn't matter to me. That looks like the faster one,
I'm guessing. I'm using forward read only cursors and my query looks
a little like below, but a little more complicated. The main concern
here is speed, the table has millions of rows. I'm acutally opening
up 2 cursors. Thoughts?


Declare cursor C for
select billNumber amount from bills where customerId=50

FETCH NEXT FROM C into @billNumber, @amount
@amountLeft=(a)amount
WHILE @@FETCH_STATUS <> 0 BEGIN

Declare cursor BillSplitCursor for
select workerId, percentage from billsSplit where
billNumber=(a)billNumber
FETCH NEXT FROM BillSplitCursor into @workerId, @percentage
WHILE @@FETCH_STATUS <> 0 BEGIN
--IF LAST ROW
@workerAmount=(a)amountLeft
--else
@workerAmount = @percentage*@amount
@amountleft=(a)amountleft-@workerAmount


--Insert into a temp table or select to output
select @billNumber, @workerId, @workAmount


FETCH NEXT FROM BillSplitCursor into @workerId, @percentage

END


FETCH NEXT FROM C into @billNumber, @amount

END
From: Tom Cooper on 21 Jun 2010 03:27
Comment inline.

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D9CE68E328B0Yazorman(a)127.0.0.1...
> Cindy Lee (danparker276(a)yahoo.com) writes:
>> Yeah, I'm using forward only cursors. Problem is I need to know if
>> it's the last row before i fetch. I'm splitting money by percentages
>> and I round up on the first few rows, and the the last row gets what
>> every money is left over. Otherwise there will be an extra penny.
>> I can either check if the amount left is less than the amount by
>> percentage, or check to see if it's the last row and then just take
>> the amount left.
>> I'm subtracting from the total amount everytime someone gets a
>> percentage.
>> I think it would be easier to just check if it's the last row rather
>> than if the amount left is less than the percentage amount.
>>
>> So there is no way to tell what the total count will be?
>
> You could save @@cursor_rows into a variable, and then decrement it,
> but better is to add this column to your query:
>
> row_number() OVER(ORDER BY ... )
>
> The columns where are the same as you order on in your ORDER BY clause,
> but the order should be the reverse. That is, if the cursor has this
> ORDER BY clause:
>
> ORDER BY col1, col2 DESC, col3
>
> Your OVER clause needs to be:
>
> row_number() OVER(col1 DESC, col2 ASC, col3 DESC)
>
> When you get 1 in the varible that you populate from this column,
> you are on the last row in the cursor.

If you use this technique, you must be sure your order by cannot generate
ties. That is, in Erland's example no two rows have the same col1, same
col2, and same col3. If you are not guarenteed this, then you are not
guarenteed that you are on the last row when the row_number() column = 1.
Of course, if your cursor query has this problem you could fix it by adding
the primary key to the ORDER BY clause and the row_number() expression. So,
if PKcol was your primary key, you could do
ORDER BY col1, col2 DESC, col3, PKcol
and the row_number expression as
row_number() OVER(ORDER BY col1 DESC, col2 ASC, col3 DESC, PKcol ASC)

Tom


>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

From: Erland Sommarskog on 21 Jun 2010 16:51
Tom Cooper (tomcooper(a)comcast.net) writes:
> If you use this technique, you must be sure your order by cannot generate
> ties. That is, in Erland's example no two rows have the same col1, same
> col2, and same col3. If you are not guarenteed this, then you are not
> guarenteed that you are on the last row when the row_number() column = 1.
> Of course, if your cursor query has this problem you could fix it by
> adding the primary key to the ORDER BY clause and the row_number()
> expression.
> So,
> if PKcol was your primary key, you could do
> ORDER BY col1, col2 DESC, col3, PKcol
> and the row_number expression as
> row_number() OVER(ORDER BY col1 DESC, col2 ASC, col3 DESC, PKcol ASC)

Good point. Yes, you must ORDER BY on something which is unique for
this to work.

In most cases you probably want a unique ORDER BY condition anyway, since
else you will not be guaranteed a deterministic result set.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 21 Jun 2010 16:57
Cindy Lee (danparker276(a)yahoo.com) writes:
> Thanks for the responses. I think I like fetch the next one and keep
> the old results. Only thing is it will create a few extra variables
> which probably doesn't matter to me. That looks like the faster one,
> I'm guessing. I'm using forward read only cursors and my query looks
> a little like below, but a little more complicated. The main concern
> here is speed, the table has millions of rows. I'm acutally opening
> up 2 cursors. Thoughts?

Ouch!

My reflection is that if there is a good set-based solution, performance
may improve drastically. But given your vague description so far, it may
be one of these problems where cursors after all are faster. I may also
be a problem where set-based iteration is faster.

If you can find the time, please post:

o CREATE TABLE statement(s) for the table(s) involved, preferably
simplified only to the pertinent columns.
o INSERT statements with sample data, enough to show the gist of
the problem, I guess that this is less than 10 rows.
o The desired result given the sample.
o The version of SQL Server you are using.

We might be able to find a better solution for you.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Cindy Lee on 21 Jun 2010 20:49
On Jun 21, 1:57 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Cindy Lee (danparker...(a)yahoo.com) writes:
> > Thanks for the responses.  I think I like fetch the next one and keep
> > the old results. Only thing is it will create a few extra variables
> > which probably doesn't matter to me.  That looks like the faster one,
> > I'm guessing.  I'm using forward read only cursors and my query looks
> > a little like below, but a little more complicated.  The main concern
> > here is speed, the table has millions of rows.  I'm acutally opening
> > up 2 cursors.  Thoughts?
>
> Ouch!
>
> My reflection is that if there is a good set-based solution, performance
> may improve drastically. But given your vague description so far, it may
> be one of these problems where cursors after all are faster. I may also
> be a problem where set-based iteration is faster.
>
> If you can find the time, please post:
>
> o   CREATE TABLE statement(s) for the table(s) involved, preferably
>     simplified only to the pertinent columns.
> o   INSERT statements with sample data, enough to show the gist of
>     the problem, I guess that this is less than 10 rows.
> o   The desired result given the sample.
> o   The version of SQL Server you are using.
>
> We might be able to find a better solution for you.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks so much for the responses. I think I'll probably go with
cursors, but here is some more information below if you feel like
looking at it. I'm working with a very old legacy database derived
from rpg programming. The database isn't normalized and it uses
char(6) for ids. I can't change the database tables.

Also, each bill can be split on either the client or the matter,
matter takes priority. So if one split check fails, you have to do
another split check.

There are a few clients that are still on sql server 2000, it's best
if it covers 2000-2008, but if there were a huge difference, I could
have 2 different ones. Most of the columns are char(6) for Ids
(Clients and Matters) and decimal(11,2) for the hours and amount.

Oh the, top 10 was just for debugging, it will return 1000s. This
query will take time (which is ok). I think from the information
below I you'll say I should use cursors.



--it's a very large dynamic select statement for TestCursor joining a
few different tables

OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @gdtrtp,@clientId, @clientIdSub,
@matterId, @matterIdSub, @invoice,

@gdpsdt,@gdtrdt,@gdbldt,@amount,@hours,@gdtkdb,@tmsort,@tlnam,@blsort,@blnam,
@rlsort,@rlnam,@olsort,@olnam,@mttype,@gdseq#,@gdType

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @timekeeper char(6),
@roundedAmt decimal(11,2),@amountLeft decimal(11,2),
@perc decimal(11,2), @rowsLeft int

DECLARE SplitCursorMatter CURSOR FORWARD_ONLY STATIC LOCAL FOR

select aspcnt as perc, astkpr, ROUND((@amount * aspcnt),2,0) as
RoundedAmt
from SplitsTable
where asclid=(a)clientId and asclsb=(a)clientIdSub and asmtid=(a)matterId
and asmtsb=(a)matterIdSub
where asclid=(a)clientId and asmtid=(a)matterId
OPEN SplitCursorMatter


FETCH NEXT FROM SplitCursorMatter INTO @perc, @timekeeper,
@roundedAmt
IF @@FETCH_STATUS <> 0
BEGIN
--if no rows for matter then check if it could be split on a client
level

DECLARE SplitCursorClient CURSOR FORWARD_ONLY STATIC LOCAL FOR
select aspcnt as perc, astkpr, ROUND((@amount * aspcnt),2,0) as
RoundedAmt
from SplitsTable
where asclid=(a)clientId and asclsb=(a)clientIdSub

FETCH NEXT FROM SplitCursorClient INTO @perc, @timekeeper,
@roundedAmt
IF @@FETCH_STATUS <> 0
BEGIN
select 'call insert'
END
ELSE

BEGIN
SET @amountLeft=(a)amount
SET @rowsleft=@@cursor_rows

WHILE @@FETCH_STATUS = 0
BEGIN
--migh have to see who gets the larger amount later
IF @rowsLeft=1
BEGIN
SET @roundedAmt= @amountLeft
END
ELSE
BEGIN
--not going to add logic, if the 2nd to last row could be off
also
--it will probably never happen
SET @amountLeft=(a)amountLeft - @roundedAmt

END

select @amount as DebugTotal,@rowsLeft as rowsLeft,
@matterId as matterId, @clientId as CLIENTID,
@roundedAmt as RoundedAmt,
@timekeeper as timeKeeper


SET @rowsLeft=(a)rowsLeft-1
FETCH NEXT FROM SplitCursorClient INTO
@perc,@timekeeper,@roundedAmt
END

END

CLOSE SplitCursorMatter

DEALLOCATE SplitCursorMatter


END
ELSE
BEGIN
SET @amountLeft=(a)amount
SET @rowsleft=@@cursor_rows

WHILE @@FETCH_STATUS = 0
BEGIN
IF @rowsLeft=1
BEGIN
SET @roundedAmt= @amountLeft
END
ELSE
BEGIN
--not going to add logic, if the 2nd to last row could be off
also
--it will probably never happen
SET @amountLeft=(a)amountLeft - @roundedAmt

END


select @amount as DebugTotal,@rowsLeft as rowsLeft,
@matterId as MATTERID, @clientId as clientId,
@roundedAmt as RoundedAmt,
@timekeeper as timeKeeper




--insert into the table here
--or pass back cus this will be a stored procedure


SET @rowsLeft=(a)rowsLeft-1
FETCH NEXT FROM SplitCursorMatter INTO
@perc,@timekeeper,@roundedAmt
END
END

CLOSE SplitCursorMatter

DEALLOCATE SplitCursorMatter



RETURN








First  |  Prev  | 
Pages: 1 2
Prev: delete a record
Next: SQL Server administration scritpts for a new server?