May 12, 2010 at 9:59 am
1. Quirky Update is very fast, but has some limitations and it is using undocumented feature of the SELECT. If you've read the article I've mentioned, you have seen that it was some discussions about that it might not work correctly some times. I have used this technique (or better call it similar technique) long ago in SQL7.0 and SQL2000 to achieve concatination of fields without using cursors. And I can tell you, that it was sometimes cases, when it didn't work as intended (I should say that in these cases queries were much more complicated, included joins and grouping).
2. CTE (or using temp tables) - will always work. For performance you can optimise the query I've gave you by introducing temp tables and indices.
If you are not sure what to use, try both methods, test it for performance with relevant dataset size, and, then you will be able to make (or should make) the right decision!
Good Luck,
Me
May 12, 2010 at 11:41 am
elutin (5/12/2010)
If you are not sure what to use, try both methods, test it for performance with relevant dataset size, and, then you will be able to make (or should make) the right decision!Good Luck,
Me
You may experience a difference in performance for one situation over another. It is well advised that you test each method and select whichever will be most scalable while performing best performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 9:46 pm
elutin (5/12/2010)
"Quirky Update" wayHere we are:
1. I would not use it if I would be you.
2. If you deside to use it, you must read
"The RULES" section of the
Solving the "Running Total" & "Ordinal Rank" Problems
article By Jeff Moden
http://www.sqlservercentral.com/articles/T-SQL/68467/
3. Add required precausions (eg. MAXDOP)
Now:
In order to get the Quirky Update to work, first you will need a clustered index to enforce update order. In your case I believe it should be that:
CREATE CLUSTERED INDEX indx_tmp_Flows ON #flows (ClientCode, TempletID, AssetCode,FlowDate)
then, here the update itself:
DECLARE @AR MONEY
DECLARE @PrevAmt MONEY
DECLARE @ClientCode INT
DECLARE @TempletID INT
DECLARE @AssetCode VARCHAR(100)
SET @PrevAmt = 0
UPDATE #Flows
SET
@AR = AccountReturn = CASE WHEN (@ClientCode = ClientCode AND @TempletID = TempletID AND @AssetCode = AssetCode) THEN
ROUND(1.0 * Amount/ISNULL(NULLIF(@PrevAmt,0),Amount), 4)
ELSE
1
END
,@PrevAmt = Amount
,@ClientCode = ClientCode
,@TempletID = TempletID
,@AssetCode = AssetCode
FROM #Flows
SELECT * FROM #Flows
Is the above code reliable? You will find the answers (or more questions) in the Jeff Moden article!
Heh... YOU didn't follow the very rules that you cited! You didn't include MAXDOP and, although it doesn't matter on a Temp Table, it's still the best practice to use WITH (TABLOCKX). Also, I take a fair bit of offense to what you said... if you're going to tell people that YOU wouldn't use it, then don't write the code that uses it. Kapeesh? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2010 at 10:04 pm
elutin (5/12/2010)
1. Quirky Update is very fast, but has some limitations and it is using undocumented feature of the SELECT. If you've read the article I've mentioned, you have seen that it was some discussions about that it might not work correctly some times. I have used this technique (or better call it similar technique) long ago in SQL7.0 and SQL2000 to achieve concatination of fields without using cursors. And I can tell you, that it was sometimes cases, when it didn't work as intended (I should say that in these cases queries were much more complicated, included joins and grouping).2. CTE (or using temp tables) - will always work. For performance you can optimise the query I've gave you by introducing temp tables and indices.
If you are not sure what to use, try both methods, test it for performance with relevant dataset size, and, then you will be able to make (or should make) the right decision!
Good Luck,
Me
Yet you post code that uses it and neither YOU nor anyone else has been able to break the code once it's setup properly with all the rules including the ones you forgot in your code post. How do you want your pork chops done?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2010 at 10:06 pm
vijay.s (5/12/2010)
Thankx for your post,I tried Quarily Update from My self and but i gets failed as I am not able to debug the problem.
Still I want to know thwt which approch is better for solving these type of queries.
CTE OR Rank Number OR Quarily Update???
If you have any idea then share it.
Thankx:-)
Post the "Quirky" Update code that failed! Be sure to include any error messages, an explanation of how it failed, and some readily consumable data in accordance with the first link in my signature below so we can test the code you post to figure out what the problem is.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2010 at 10:58 pm
Thankx all to provide me the way to achive some intresting things.
May 13, 2010 at 2:23 am
Jeff Moden (5/12/2010)
elutin (5/12/2010)
1. Quirky Update is very fast, but has some limitations and it is using undocumented feature of the SELECT. If you've read the article I've mentioned...Yet you post code that uses it and neither YOU nor anyone else has been able to break the code once it's setup properly with all the rules including the ones you forgot in your code post. How do you want your pork chops done?
Heh... YOU didn't follow the very rules that you cited! You didn't include MAXDOP and, although it doesn't matter on a Temp Table, it's still the best practice to use WITH (TABLOCKX). Also, I take a fair bit of offense to what you said... if you're going to tell people that YOU wouldn't use it, then don't write the code that uses it. Kapeesh?
Dear Jeff,
I have not included all right things in a code on purpose. I wanted to ensure that Vijay will read your nice article.
I 'm not using it.... Please don't take it as offence, I give you example: let say that I am a salesman in the lady's underware shop: So, I am more than able to advise how to put the bra on, but it doesn't mean that I use this item for myslef 😀
I cannot post the code I have used, as it was back in 2001. As I said, it was not exactly the Quirky Update, actually it was not update at all, however it was build on the same principals. I had no opprtunity to read you article in that old time, so no rules were known to me. The queries I had were complex: many joins and included grouping. They were SELECT queries, where ORDER BY could be used. They worked fine most of the time. However sometime, while developing them, I had very strange behaviour. The absolutely fine query, didn't return result I expected in terms of Order. Do you know what fixed that query? You will be surprised: I have just deleted some line from it reinsereted it back (with no modifications whatever). I had nice talk about this fact with one SQL man from Microsoft. He said me: bla-bla-bla - too smart, shoudln't do it this way, whole thing should not be done in SQL (I've agreed with that, as we were building string messages in SPs), but why it did happen - NO IDEA.
And the last bit:
Post the "Quirky" Update code that failed! Be sure to include any error messages, an explanation of how it failed, and some readily consumable data in accordance with the first link in my signature below so we can test the code you post to figure out what the problem is.
Jeff, I believe that Vijay said that he failed to write the Quirky update that works, not that the Quirky update failed...;-)
Cheers,
Me
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply