January 2, 2009 at 1:51 pm
SQLBOT (1/2/2009)
RBarryYoung (1/2/2009)
SQLBOT (1/2/2009)
Can you loop through dm_db_index_physical_stats and reindex all the databases that require reindexing without an iterative structure? It's beyond my meager skills if it is possible.It probably wouldn't be advisable to do it as a set-based op. because they'd all get reindexed at the same time causing upheaval and turmoil in the DB.
So, you're saying that you actually want it to run slower?
LOL...Nope,
Just to see if it's possible. I imagine dozens of set-based advocates furiously coding this very minute to come up with a good way :hehe:
I make no assumptions that it will run slower because I have no real Idea of how it would execute.
Can Someone write a psuedo-iterative set-based query using cross-applied table valued functions that would reindex... maybe. It might even act iterative too and be faster than a cursor. It would be the logical absurdity of !(RBAR), but I'd like to see it.
Oh, it can be done, and technically non-iterative at that (though "iterative" is a bit of a weasel word as it could be applied to lots of things other than explicit cursors and loops that are considered an acceptable halfway-point by most folks). And it's not that hard, (you'll just say "oh, yeah" when you see it), and I've posted this type of solution here many times (as have others), but it does pile it on relentlessly when it runs. And it would be likely to make your production environment miserable while it was running.
But if you want to avoid doing "everything at once" and "chunk" through things, doing a certain amount, then waiting a while, then doing some more, until all of them are done, ...? Then yes, I think that is an appropiate use for an explicit procedural loop(cursors or "While"): to actually slow a process down so that it does not cause performance problems in other processes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 1:54 pm
wagner crivelini (1/2/2009)
In fact, concatenating strings will not do on this example.This works when you have one scalar value to return, instead of a whole table.
But there are workarounds, of course.
I don't intend here to suggest you a "best-practice" approach, but simply something that can help you.
For instance, try declaring a table variable (let's call it @tmpResult) with fields codName and PhoneNumbers.
Then create a loop (using WHILE stement) over each record on dbo.tblUser.
Within the loop, you run this SELECT statement you used to concatenate PhoneNumbers for each codUser.
Finally you should populate (using INSERT statement) @tmpResult with each record (@vchName, @AllPhones)
At the end, you show the records within the table variable (with a SELECT statement).
I hope it can help.
No... no loops... they're just as bad a cursors. See what I just wrote above.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 1:56 pm
Linson.Daniel (1/2/2009)
...nevertheless worth a look and definitely an alternate solution to cursors.....
No! It's NOT! It still uses a declared loop and it's still just as bad as a "firehose" cursor. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 1:56 pm
Jeff: I think that the URL link at the end of your last post is messed-up.
edit: OK, you're too fast for me. Three posts back...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 2:01 pm
robert.letts (1/2/2009)
Excellent article on cursors, Wagner.Here is a weird problem that made me choose a cursor. The incoming data was in a header / detail format. The header had 5 columns: ICN Code, Officer, Department, Function, and Message. Essentially, it is a Money Transfer by Wire application running on a Tandem system. The data provisioning was in pure TAB Delimited Text, so I used OPENROWSET and a format file to load the daily files of 50,000 rows.
But the rows were really weird. The header had data in 4 out of five columns, and the detail had data in one out of five columns. The rows between headers were the details for the prior header. Where the last column (Message) was NULL on the header marked the end of the last header - the Message being NULL was a sign that the it was a header row. Where the Message was not NULL there were all kinds of "field codes", where each field code has very odd and specific flags and options. Each row of the message had space for 2000 VARCHARS. Row counts varied between headers too - not all the headers had the same number of Message rows.
The problem was to pick up the Currency, Amount, Customer ID, and Originating institution from each of the Message Rows for each of the headers. I used a cursor, much to my dismay. It is slow, but it works and gives me fine-grained control.
I would like to try a set based approach too. If you pick up this comment, let me know, and I can send you more details.
Bob
Provided there's nothing in the file that would violate the privacy of individuals, attach the file and let's have a go at it! If there is private information in the file, send me a nondisclosure agreement, I'll sign it, and then send me the file via email. If I can do it set based in about less than a 10th of the time it currently takes you, you simply agree to credit my PayPal account for a pre-agreed upon amount. If I can't, you owe me nothing. How'z that for a deal? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:11 pm
wagner crivelini (1/2/2009)
My point in this article is "Whenever you suspect your code is not running as expected, no matter it uses cursors or not, you should check your code's execution plan. This is the ultimate information you have to assess your code's performance."
Then you picked a most unfortunate title! 😛
And, the execution is just one small tool and it's frequently wrong. It is by far and [font="Arial Black"]NOT [/font]the "ultimate information you have to assess your code's performance".
For example... according to either the estimated or the actual execution plan, which of the two following sections of code is the best and the fastest?
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
SET @DateVal = '2008-01-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(yy, 5, @DateVal)
)
select @Bitbucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2008-01-01'
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,5,@StartDate)))
@Bitbucket = @StartDate-1+t.N
FROM Tally t
ORDER BY N
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
And, if you still don't have a Tally table, here's where to get one and how it can be used to replace certain cursors and while loops...
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:14 pm
LSAdvantage (1/2/2009)
In response to sudhanvag, who stated:But i want a result like this
---------------------------------
codName (no column name)
abc 1 281 444 5555 , 55 11 4582 2752 , *********** ,
def 1 XXX XXX XXXXX ,
-----------------------------------
Let me offer the following solution:
select u.codName,
stuff((select ', ' + PhoneNumber
from tblPhone p1
where p1.codUser = p2.codUser
for xml path ('')), 1, 2, '')
from tblPhone p2
inner join tblUser u on p2.codUser = u.codUser
group by u.codName, p2.codUser
I'm open to any other suggestions on this. I was going to pose the very same question but sudhanvag beat me to it.
Lisa
P.S. Please see sudhanvag's post on page 2 where tblUser was added.
No... that's the way to do it if you're going to do it at all... I still think that this type of thing should use the clock cycles on the client machine to do formatting by concatenation instead on the database server.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:20 pm
SQLBOT (1/2/2009)
RBarryYoung (1/2/2009)
SQLBOT (1/2/2009)
Can you loop through dm_db_index_physical_stats and reindex all the databases that require reindexing without an iterative structure? It's beyond my meager skills if it is possible.It probably wouldn't be advisable to do it as a set-based op. because they'd all get reindexed at the same time causing upheaval and turmoil in the DB.
So, you're saying that you actually want it to run slower?
LOL...Nope,
Just to see if it's possible. I imagine dozens of set-based advocates furiously coding this very minute to come up with a good way :hehe:
I make no assumptions that it will run slower because I have no real Idea of how it would execute.
Can Someone write a psuedo-iterative set-based query using cross-applied table valued functions that would reindex... maybe. It might even act iterative too and be faster than a cursor. It would be the logical absurdity of !(RBAR), but I'd like to see it.
~BOT
This is one of the few places where a cursor can be used without much difference on performance because it's not the cursor that provides a performance problem.
However, if you post your looping code that does it, I'll be happy to show you how to do it without a declared loop of any kind.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:22 pm
RBarryYoung (1/2/2009)
Jeff: I think that the URL link at the end of your last post is messed-up.edit: OK, you're too fast for me. Three posts back...
Thanks, Barry.... it was actually the URL in the quote of that post that messed me up. I've repaired it. Thanks for the heads up.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:26 pm
TheSQLGuru (1/1/2009)
I wonder if poor Mr. Wagner Crivelini is aware of the recent firestorm of posts engendered by an article writer who had the audacity to take on cursors on this website! :w00t:
BWAAA-HAAA!!!! I'm thinking he wasn't! I suppose that this thread, too, qualifies as a "firestorm". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:28 pm
Grant Fritchey (1/2/2009)
Let me reiterate before I whup on you some more 😉
If that's not a fair "Pork Chops are immenent" warning, then there's no such thing. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:31 pm
RBarryYoung (1/2/2009)
But when replacing cursors, we are letting go some important features that only cursors can provide to your code.
Assuming that we are talking about SQL Server 2005/2008, I know of no feature that "only cursors can provide", other than intentionally slowing down your code (which believe it or not, can be desirable). I am aware of some capabilities that only procedure-based loops (of which cursors are the least desirable) can provide, but they are vanishingly obscure and obtuse in real application.
All of the usual excuses for using cursors are based on misunderstandings of T-SQL capabilities and how SQL code should be designed and managed.
Damn, Barry... I'm keeping THAT one! Very well stated!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2009 at 2:46 pm
Heh. I thought that you would like that one. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 5:46 pm
Jeff Moden (1/2/2009)
SQLBOT (1/2/2009)
RBarryYoung (1/2/2009)
SQLBOT (1/2/2009)
Can you loop through dm_db_index_physical_stats and reindex all the databases that require reindexing without an iterative structure? It's beyond my meager skills if it is possible.It probably wouldn't be advisable to do it as a set-based op. because they'd all get reindexed at the same time causing upheaval and turmoil in the DB.
So, you're saying that you actually want it to run slower?
LOL...Nope,
Just to see if it's possible. I imagine dozens of set-based advocates furiously coding this very minute to come up with a good way :hehe:
I make no assumptions that it will run slower because I have no real Idea of how it would execute.
Can Someone write a psuedo-iterative set-based query using cross-applied table valued functions that would reindex... maybe. It might even act iterative too and be faster than a cursor. It would be the logical absurdity of !(RBAR), but I'd like to see it.
~BOT
This is one of the few places where a cursor can be used without much difference on performance because it's not the cursor that provides a performance problem.
However, if you post your looping code that does it, I'll be happy to show you how to do it without a declared loop of any kind.
Awesome!
I can't show you mine without breaching agreements with my employer, but by and large they are all the same:
http://technet.microsoft.com/en-us/library/bb838727.aspx
Mine just adds some more checks for LOB data types for online reindexing and free space checking.
not much different for our purposes.
It checks the current row, builds dynamic SQL and executes it. That sums up all maintenance routines, really.
You could just post a psuedocode example too. I'm not interested in cutting and pasting your code, but rather learning how to use row-based results to run commands or execute specific stored procedures.
I'm guessing a cross applied UDF is how to do it, but I just want to see how it's done!
Craig Outcalt
January 2, 2009 at 7:14 pm
OK, here's my conversion of the MS example:
-- ensure a USE statement has been executed first.
SET NOCOUNT ON;
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
AND avg_fragmentation_in_percent > 10.0;-- Set this to what you Need
DECLARE @command varchar(8000);
DECLARE @sql varchar(MAX);
Select @command = 'ALTER INDEX [' + i.name + '] ON ' + s.name + '.[' + o.name + '] '
+ Case When w.frag < 30.0 Then 'REORGANIZE' Else 'REBUILD' End
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
+ Case When (Select count(*)
FROM sys.partitions p
WHERE p.object_id = w.objectid AND p.index_id = w.indexid) > 1
Then ' PARTITION=' + CONVERT (CHAR, w.partitionnum)
Else '' End
, @sql = Coalesce(@sql,'') + @command + ';
PRINT ''Executed ' + @command + ''';
'
--select *
From work_to_do w
Join sys.objects As o On o.object_id = w.objectid
Join sys.schemas as s ON s.schema_id = o.schema_id
Join sys.indexes As i On i.object_id = w.objectid And i.index_id = w.indexid
EXEC(@sql);
GO
Notice: no iteration or even order dependency.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply