October 10, 2007 at 9:10 am
I don't think that is the kind of 'order' we're looking for.
FORCEORDER is when you want to tell the optimizer to join in the order tables are mentioned in the query. ie - t1 join t2 join t3 - forcing t1 to join t2 first, then join that to t3
Normally, the optimizer is free to shuffle around the 'ordering of joins' when it's looking for the best plan.
Here's a link where the engine team tells us what options there is when we need *guaranteed* ordering. (it's not that many) But maybe this problem can be worked around using some of the methods that are 'supported'.
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
/Kenneth
October 10, 2007 at 9:43 am
Kenneth
Thanks for your guidelines.
In this case, I don't talk about logics. There is no logic, I've got to admit.
So far, nobody can explains to me that my original query with INNER JOIN doen't works and without INNER JOIN, works very nice.
Works for me means trust in a predictable order in Update Statement (clustered index means to change a physical table order)
But in the real life, I need INNER JOIN !
Nobody can explains why using INNER JOIN in the UPDATE statement works using just string fields in the clustered index with no duplicates!
However, I can't feel happy using INNER JOIN to keep UPDATE order, with so many weird rules, so I search for another solution.
Finally, I run my code with and without FORCE ORDER and INNER LOOP JOIN. One works very well with no additional conditions and other crashes!
What can I do? :crazy:
October 10, 2007 at 5:21 pm
So far, nobody can explains to me that my original query with INNER JOIN doen't works and without INNER JOIN, works very nice.
... and you haven't posted the real life situation in your code examples... you keep messing with what I gave you by removing the index hint... and I still see absolutely no justification for joining to the summary table on the update, YOUR NOT USING ANYTHING FROM IT in the code you posted. According to what you've posted, you don't need the inner join.
I'm glad you've found your solution but all the mumbo-jumbo with Force Order and the Clustered Key just isn't necessary... and, if you do need to do an update FROM the summary table, you certainly don't need to do it while the update for the counts is working. Divide and conquer.
--Jeff Moden
October 11, 2007 at 3:13 am
Well, I do think that the differences you're experiencing has been explained.
What you want: You want a SQL DML statement to process in a predictable order.
Herein lies the contradiction. There is no order in relational theory. SQL Server is a relational tool.
It does it's stuff 'the relational way', and 'order' is in that domain not a concept that exists at all.
There is exactly one way you can specify a predictable order with this tool (SQL Server), and that is to use the ORDER BY clause.
(Unfortunately it's a bit awkward that the syntax is allowed in places where it isn't guaranteed to be followed at all times..) That's what the link above was for: to show where ORDER BY *is* guaranteed, and can be relied on. If you do it in some other way, what you get is something that may look the same, but it's not guaranteed, hence it's pure luck or a coincidence with some behaviour of the physical aspects of how the database objects are implemented. This will in time break, if you rely on it.
But I agree with Jeff, it would be nice to see a few rows of example data, what the desired output should look like, and the idea around the goal of it all. (ie the 'business rules')
We mere mortals likes to think in terms if ordering, iterations etc... It's how our minds naturally work.
The problem is that SQL doesn't work that way, so it often gets very difficult when we want to force our sequential thinking on a tool that has no notion of it, nor is designed for it.
OTOH, there are stuff that have no pure relational solution, you must depend on, say an ordered list to be able to calculate a median value for example.
Question is what this problem is? I don't know for sure yet where the core of the problem lies, only that so far it seems to be about how to force a certain method to work in an 'unsupported' way.
Perhaps it's not the right method for the solution?
/Kenneth
October 11, 2007 at 8:03 am
Jeff, Kenneth, Matt
Thanks for everybody in this forum that has written in my post. I don't would have got in a solution, without the inspiration that the forum has given to me
Now, I've found a link for a papel that describes the same solution
http://www.sql-server-performance.com/articles/dev/cursor_friendly_problem_p3.aspx
"Handling Cursor-Friendly Problems in T-SQL: Running Totals ExampleHandling Cursor-Friendly Problems in T-SQL: Running Totals Example"
I can summarize one of my sub problems, using pseudo code and simplifying a little bit (18 lines). My real problem it's too much complicated to be posted here in SQL Code. Nobody would have patience to reveal this messy code!
Table S: (Sales, Inventory and Distribution Store by Store)
Product, Date, Store, Distribution_Amout, Sales_Amout,
Category_Sales, Alternative_Sales
Table T (Total Sales for All Stores)
Total_Category_Sales, Total_Sales_Amount
Table P (Products)
Product, Minimal_Amount
The final target is to make product sales forecast based on historical data.
Sometimes the historical data is damaged by spurious sales. The situation below is one of this cases.
The stores receives products from a Central Warehouse (Distribution) and
sales to final consumer. One bad possibility is that part of inventory in the store is fake (phisical <> logical inventory), damaged or lost.
I need to pick X (X should be greater than 2) days running, same store and product, with inventory greater than a minimal, 0 Distribution_Amount and 0 Sales_Amount, so that the cummulative probability (Using Poisson Distribution, based on a expected sales) for this fact gets less than a probability threshold. (Because it can exists a fake, lost or damaged inventory that makes sales impossible and deteriorate the historical data and forecast quality)
Category is a group of similar products
@Count = 0
@ProbAcum = 1
@makeit = False
For Each Running Day same product and same store
-- Notice that the P e T are Inner Loop Joins
IF S.Inventory 0
OR S.Sales_Amout>0 THEN
@DtFim = Data-1
Exit
ENDIF
@Count = @Count+1
IF @Count = 1 THEN @DtInic = S.Date
@Expected Sales ~ T.Total_Sales_Amount *
( S.Category_ Sales / T.Total_Category_Sales )
@Prob = Poisson(0, @Expected Sales,False) -- Like Excel
@ProbAcum = @ProbAcum * @Prob
IF @ProbAcum 1
THEN @makeit = TRUE
End For
IF @makeit THEN
Uptate between @DtInic and @DtFim same product and store
S.Alternative_Sales =
T.Total_Sales_Amount *
( S.Category_Sales / T.Total_Category_Sales )
ENDIF
--/--
This so much complicated problem just can be solved using 4 different
methods:
a) Transfering the logical for the programming language. It's possible, but the networks gets cluttered. It's very data intensive. I neither even have tried!
b) Programming with cursors. Everybody knows that SQL Server has no good performance with cursors. I neither start to try!
c) Programming with subqueries. I've tried this, but's is very complicated (too much twisted logic) and slow (Even if faster than cursor),
d) Programming using update order. Fast! Since I've found, in other
situation, update order troubles, I have researched hard in Internet, seaching by solutions. I've found nothing and I start to post in this forum that I've discoverd by choice in my research. I just have added (FORCE ORDER, LOOP JOIN and MAXDOP 1) and I have runned again. Cheers! It works smoothly!
Please, forgive my bad English. I'm from Brazil.
Best regards,
Paulo
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply