October 8, 2007 at 3:20 pm
I try to use Update Statement in situations that I need field values from previous record.
This approach have much more speed than cursor or subqueries.
For instance (not a real application), I'm going to record a continuous count for each date.
This code works OK, but with medium tables with joins, even using
clustered index in updated table and MAXDOP 1 option, doesn't works anymore!!!!
Why? Somebody can help me?
I'm using SQL Server 2000, with all service packs.
Declare @dt SmallDateTime
Declare @Ord Int
Drop Table #TVen
Create Table #TVen (Dt SmallDateTime, Venda Numeric(10,2), Ord Int)
Create Clustered Index #TVen On #TVen(Dt, Venda)
Insert Into #TVen Select '20071001', 200,0
Insert Into #TVen Select '20071001', 100,0
Insert Into #TVen Select '20071001', 300,0
Insert Into #TVen Select '20071002', 400,0
Insert Into #TVen Select '20071002', 200,0
Insert Into #TVen Select '20071002', 300,0
Set @Ord = 0
Update T
Set @Ord = (Case When @dt<>Dt Then 0 Else @Ord End) +1,
@dt = Dt,
Ord = @Ord
From #TVen As T
Select * From #TVen
October 8, 2007 at 3:47 pm
you're missing a line:
Update T
Set @Ord = (Case When @dt<>Dt Then 0 Else @Ord End) +1,
@dt = Dt,
Ord = @Ord
From #TVen As T
WITH (INDEX(#TVen),tablock) -- The index here is the key - only way to sneak an order in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2007 at 3:54 pm
You may also need to throw in a "free" variable - to help it along. (declare @dumm with same type as @ord.). I ran into the same issue the other day - and for some reason - @dummy did the trick (pun fully intended). It seems to "force" is to recalc each row.
Update T
Set @dummy=@Ord = (Case When @dt<>Dt Then 0 Else @Ord End) +1,
@dt = Dt,
Ord = @Ord
From #TVen As T
WITH (INDEX(#TVen),tablock)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2007 at 4:04 pm
Thanks for your answer.
I already try that way, but it doesn't works!
You can notice that, in my example, there is only one index, the clustered one!
Remember that in my small example, everything works! The trouble is when I use bigger tables. In my real case, I get a SQL code where if I take out a single Inner Join, the result turns OK ???!!!
SQL Server uses threads, except if I turn off in the server side, but then why exists a MAXDOP option (max degree of parallelism) in the Update Statement?
October 8, 2007 at 4:26 pm
Hi Matt,
Thanks again.
In my version (SQLServer 2000 - SP 4), it doesn't works double assignment.
But, in any way, my example works OK.
Unfortunaly my real code doesn't works just for a single "Inner Join"
But now I've got a closed example that doesn't works!!!!
Declare @dt SmallDateTime
Declare @Ord Int
Declare @Conta Int
Declare @ndias Int
Declare @Venda Int
Drop Table #TVen
Create Table #TVen (Dt SmallDateTime, Venda Numeric(10,2), Ord Int)
Create Clustered Index #TVen On #TVen(Dt, Venda)
Set @Conta = 0
While @Conta<1000
begin
Set @ndias = Floor( rand()*100 )
Set @Venda= Floor(rand()*200)
Insert Into #TVen Select Cast('20071001' as SmallDateTime) + @ndias, @Venda, 0
Set @Conta = @Conta + 1
end
Set @Ord = 0
Update T
Set @Ord = (Case When @dt<>Dt Then 0 Else @Ord End) +1,
@dt = Dt,
Ord = @Ord
From #TVen As T
Option (MAXDOP 1)
Select * From #TVen
Order By Ord
October 8, 2007 at 7:58 pm
Works fine for what you have... no primary key.
To help, I need to know what you expect the output of your first post to be...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2007 at 6:35 am
Thanks, Jeff
I would expect that #TVEN would shows ORD numbering
was in the same index order (Dt + Venda).
I don't uses option UNIQUE in CREATE INDEX because it's no impossible that "Venda" Field had 2 equal values ("Venda' is "Sales" in Portuguese)
October 9, 2007 at 7:30 am
Thanks, but doesn't quite tell me what you want... not trying to be difficult here, either but "one picture is worth a thousand words"....;)
You listed this as an "input"...
Insert Into #TVen Select '20071001', 200,0
Insert Into #TVen Select '20071001', 100,0
Insert Into #TVen Select '20071001', 300,0
Insert Into #TVen Select '20071002', 400,0
Insert Into #TVen Select '20071002', 200,0
Insert Into #TVen Select '20071002', 300,0
Please place those in the expected output order and replace the ?? with the correct numbers...
Dt Venda Ord
'20071001' 200 ??
'20071001' 100 ??
'20071001' 300 ??
'20071002' 400 ??
'20071002' 200 ??
'20071002' 300 ??
Thanks for the clarification... it'll be worth it, I promise...:D
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2007 at 8:26 am
Hi, Jeff 😎
In my first post yesterday (Oct,9 22:20 pm), the example output that I want is the output that I get (Ord field respecting data + venda index order). No problem.
The trouble starts when the same table is bigger!
In my other post yesteday (Oct, 9 - 11:26 PM), using RAND, I get a bigger table (1,000 records) with the same structure and intending produces a wrong output.
I've commited a mistake :crying:, That example runs with no problem too. So I alter the code, to make RAND returns always the same sequence and forces to produce a logical error, inserting a inner join in the code.
Set NoCount ON
Declare @dt SmallDateTime
Declare @Count Int
Declare @NDays Int
Declare @Sales Int
Declare @Inic Float
Select @Inic=rand(1000)
Drop Table #Sales
Create Table #Sales (Dt SmallDateTime, Sales Numeric(10,2), Ord Int)
Create Clustered Index #Sales On #Sales(Dt, Sales)
Set @Count = 0
While @Count< 1000
begin
Set @NDays = Floor( rand()*100 )
Set @Sales= Floor(rand()*200)
Insert Into #Sales Select
Cast('20071001' as SmallDateTime) + @NDays, @Sales, 0
Set @Count = @Count + 1
end
Drop Table #Summary
Select Dt,
Sum(Sales) As Sales
Into #Summary
From #Sales
Group By Dt
Create Clustered Index #SumVen On #Summary(Dt)
Declare @Ord Int
Set @Ord = 0
Update T
Set
@Ord = @Ord+1,
Ord = @Ord
From #Sales As T
Inner Join #Summary As S
On S.Dt=T.Dt
Option (MAXDOP 1)
Select * From #Sales
Order By Ord
I've got this following output. (:crazy: )
Date Sales Ord
10/12/2007 13 1
10/12/2007 159 2
10/12/2007 49 3
...
And I would expect the output like
Date Sales Ord
01/10/2007 38 1
01/10/2007 41 2
01/10/2007 74 3
...
But the same records above shows
Date Sales Ord
01/10/2007 38 313
01/10/2007 41 308
01/10/2007 74 315
...
Seems like the SQL Server open many and many different threads ...
I would like very much that would be possible find a solution without to use subqueries neither cursors.
Best Regards,
Paulo
October 9, 2007 at 9:02 am
Sorry... that just doesn't help me... all of your examples show only one date... would you please do as I ask so I can help? Please change the following to the correct order, replace the ?? with the correct numbers, and post it...
Here it is, one more time...
Dt Venda Ord
'20071001' 200 ??
'20071001' 100 ??
'20071001' 300 ??
'20071002' 400 ??
'20071002' 200 ??
'20071002' 300 ??
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2007 at 9:12 am
Jeff,
The output is the following
Dt Venda Ord
'20071001' 200 2
'20071001' 100 1
'20071001' 300 3
'20071002' 400 3
'20071002' 200 1
'20071002' 300 2
The "Ord" Field stays in the same order than "Venda" field
The first example works very well, It's what I've expected.
But the real order is (cause the database is linked to
a clustered index "dt" + "venda")
Dt Venda Ord
'20071001' 100 1
'20071001' 200 2
'20071001' 300 3
'20071002' 200 1
'20071002' 300 2
'20071002' 400 3
October 9, 2007 at 9:20 am
Perfect... I'll be back...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2007 at 9:31 am
The compound cluster index is creating some odd sorting sequence. Apparently by having 2 data types, the key being stored internally is stored in some way I can't quite wrap my head around (anyway - it is different that we'd expect.) Solution is to FORCE the issue, by feeding it two values that will concatenate cleanly and will force the sort order you wish.
Try this on for size (looks to me to return the right amount). Of course - I STILL can't figure out what the summary is doing for the update.
CODE SEGMENT
use test
go
Set NoCount ON
Declare @dt SmallDateTime
Declare @Count Int
Declare @NDays Int
Declare @Sales Int
Declare @Inic Float
Select @Inic=rand(1000)
Drop Table #Sales
Create Table #Sales (Dt char(8), Sales Numeric(10,2),salestext char(10), Ord Int)
Create Clustered Index sales_ix On #Sales(Dt, Salestext)
Set @Count = 0
While @Count< 1000
begin
Set @NDays = Floor( rand()*100 )
Set @Sales= Floor(rand()*200)
Insert Into #Sales
Select
Convert(char,Cast('20071001' as SmallDateTime) + @NDays,112),@Sales, right('0000000000'+cast(@Sales as varchar(10)),10), 0
Set @Count = @Count + 1
end
Drop Table #Summary
Select Dt,
Sum(Sales) As Sales
Into #Summary
From #Sales
Group By Dt
Create Clustered Index #SumVen On #Summary(Dt)
Declare @Ord Int
Set @Ord = 0
Update T
Set
@Ord = @Ord+1,
Ord = @Ord
From #Sales As T with (index(sales_ix),tablock)
, #Summary As S with (index(#sumven),tablock)
where T.Dt=s.dt
Select * From #Sales
Order By dt,sales
select * from #sales with (index(sales_ix))
select * from #sales order by dt
/*
select *
From #Sales As T
Inner Join #Summary As S
On S.Dt=T.Dt
order by t.dt,s.dt
*/
END CODE SEGMENT
Interestingly enough - your original code works flawlessly in 2005, but causes something interesting to happen in 2000. I can't quite figure out what forces the 12/10/2007 values to be first in the clustered index without the conversions, but....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 9:37 am
one sec - (quick back and forths going on - posting based on code a few answers up). there needs to be a case when then 0 else @ord end in there if you intend the group to reset.
use test
go
Set NoCount ON
Declare @dt char(10)
Declare @Count Int
Declare @NDays Int
Declare @Sales Int
Declare @Inic Float
Select @Inic=rand(1000)
Drop Table #Sales
Create Table #Sales (Dt char(8), Sales Numeric(10,2),salestext char(10), Ord Int)
Create Clustered Index sales_ix On #Sales(Dt, Salestext)
Set @Count = 0
While @Count< 1000
begin
Set @NDays = Floor( rand()*100 )
Set @Sales= Floor(rand()*200)
Insert Into #Sales
Select
Convert(char,Cast('20071001' as SmallDateTime) + @NDays,112),@Sales, right('0000000000'+cast(@Sales as varchar(10)),10), 0
Set @Count = @Count + 1
end
Drop Table #Summary
Select Dt,
Sum(Sales) As Sales
Into #Summary
From #Sales
Group By Dt
Create Clustered Index #SumVen On #Summary(Dt)
Declare @Ord Int
Set @Ord = 0
set @dt=''
Update T
Set
@Ord = case when @dt=t.dt then @Ord else 0 end +1,
Ord = @Ord,@dt=t.dt
From #Sales As T with (index(sales_ix),tablock)
, #Summary As S with (index(#sumven),tablock)
where T.Dt=s.dt
Select * From #Sales
Order By dt,sales
select * from #sales with (index(sales_ix))
select * from #sales order by dt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 10:08 am
Thank you very much, Miller.
Wow! It's completely crazy :w00t:
I simplify the example (@Ord= @Ord +1) in order to the make the things clearer.
Select * From #Sales or
select * from #sales with (index(sales_ix))
Don't respect the clustered index and list 'Nov,27 - 07' first (?????)
Select * From #Sales
Order By dt,sales
Almost respect the same order of clustered index but Oct,9 - 07 - Sales 187 has ORD = 74 and 978 (there is other exceptions)
I've noticed the every index key tie let the SQL Server nuts.
Really, when I change 1 line in your code, it's all over.
Create Clustered Index sales_ix On #Sales(Dt, Sales)
________In Place Of
Create Clustered Index sales_ix On #Sales(Dt, Salestext)
38 is considered greater than 41, 90, etc.
My original code works even in Sql Server 2005, even in the tie key index case?
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply