December 1, 2009 at 3:28 pm
BaldingLoopMan (12/1/2009)
-----------------------------------------------------------------------------------------------If you were writing a proc that was supposed to run a log backup command on each active database in Full recovery model, which is a situation where you have to step through one at a time one way or another, what would you use in place of a fast_forward cursor?
-----------------------------------------------------------------------------------------------
The most basic example of the non cursor method that iterate through one rec at a time is as follows.
declare @custId int
--load looping mechanism
select distinct custid
, 0 processed
into #loop
from customers
while exists ( select * from #loop where processed = 0 )
begin
select top 1 @custId = custId from #loop where processed = 0
--do whatever...update the cust records....update some other table whatever
--As i was saying before the difference here is if it is warented i can adjust as many records in the #loop table to processed if i know for what ever reason that at this instance in time they dont need to be processed. By doing so the loops iterations can be greatly reduced. That's really the only point i was making which is you cannot manipulate the original set the cursor is running on so that perhaps it will only do a third of the loop iteration. instead u have to do a fast forward after checking if some value is equal to something over and over and over again.
update #loop
set processed = 1
where custId = @custid
end
drop table #loop
The real catch here is that you are artificially limiting yourself to a TSQL-only solution when there is no good reason to do so, and plenty of reason not to. Specifically for this case, you would probably by better of with a Maintenance Plan or, an external backup tool or, at least an Agent Job with one step for each database.
As most everyone knows, I am 100% against Cursors. Not because there is NO use for them, just that there is no GOOD use, there's always something better.
[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]
December 1, 2009 at 3:32 pm
Jack Corbett (11/18/2009)
...Also check out R. Barry Young's series (2 parts so far) on 15 Ways to Lose Your Cursors. Part 1 here[/url]
Hey, thanks for the plug, Jack! đŸ™‚
[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]
December 1, 2009 at 3:34 pm
dmounday (11/18/2009)
Interesting description of a cursor..."the bastard love child..."
Heh. Yeah, that's what happens when I take my meds before I start writing. đŸ™‚
[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]
December 1, 2009 at 3:49 pm
cursors are a necessary evil... some tasks just need cursors and there's no way of replacing them, except by changing the app code...
my problem for instance, previously posted on this thread, to dynamically build a query with the field's information on other tables...
and the example of
select ... into #table from ... where ...
and do the loop...
SELECT .. INTO it's not a very good idea unless the table you're creating has few rows.
If you need to process lots of records the best thing for you to do is to have a permanent "temp" table (tmpAuxTable) with all the needed columns and indexes and just insert into it. It's faster...
you could also try to use the table variables but if you need to call a SP that's going to use the data you need to use a user defined table variable, but can't change the data on the SP, or do some crazy stuff and convert the table into XML and do the reverse inside the SP to use the data...
That's why having a permanent "temp" table it's best (at least for me) but don't forget to add a column to distinguish each call's data...
Pedro
December 1, 2009 at 4:19 pm
PiMané (12/1/2009)
...SELECT .. INTO it's not a very good idea unless the table you're creating has few rows.
...
No, this is incorrect. It's a holdover myth from SQL2000 or version 7 or even earlier, but it was fixed a long time ago. SELECT INTO is very, very fast and one of the best ways of creating new tables with data.
[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]
December 1, 2009 at 6:10 pm
The fact that no one has shown you a non-cursor way to generate the views in your app is not proof that cursors are necessary.
If you changed the form of your views to something less ugly than all those correlated subqueries, such as:
CREATE VIEW [ViewName] AS
SELECT RecordId, [Name1], [Name2], ...
FROM (
SELECTRecordId, [Value], f.NameToDisplay
FROMDataTable1 t
INNER JOIN ViewFields vf ON t.FieldId = vf.FieldId
INNER JOIN Fields f ON vf.FieldId = f.FieldId
WHEREvf.ViewId = <vid> AND f.DataTable = 'DataTable1'
UNION ALL
SELECTRecordId, [Value], f.NameToDisplay
FROMDataTable2 t
INNER JOIN ViewFields vf ON t.FieldId = vf.FieldId
INNER JOIN Fields f ON vf.FieldId = f.FieldId
WHEREvf.ViewId = <vid> AND f.DataTable = 'DataTable2'
UNION ALL
...
) raw
PIVOT (MAX([Value]) FOR NameToDisplay IN ([Name1], [Name2], ... )) pvt
GO
Then a cursor-less way to generate them would be:
;WITH NameList AS (
SELECT SUBSTRING( SELECT ',' + QUOTENAME(NameToDisplay)
FROMFields f
INNER JOINViewFields vf ON f.FieldId = vf.FieldId
WHEREvf.ViewId = @ViewId
ORDER BY vf.FieldOrder
FOR XML PATH('')), 2, 9999) AS FieldNames),
TableList AS (
SELECT DISTINCTDataTable
FROMFields f
INNER JOINViewFields vf ON f.FieldId = vf.FieldId
WHEREvf.ViewId = @ViewId)
SELECT'CREATE VIEW [' + (SELECT ViewName FROM Views WHERE ViewId = @ViewId) + '] AS
SELECT RecordId,' + FieldNames + ' FROM ('
+ SUBSTRING(
(SELECTREPLACE(REPLACE('UNION ALL '
+ 'SELECT RecordId, [Value], f.NameToDisplay FROM [<tbl>] t '
+ 'INNER JOIN ViewFields vf ON t.FieldId = vf.FieldId '
+ 'INNER JOIN Fields f ON vf.FieldId = f.FieldId '
+ 'WHERE vf.ViewId = <vid> AND f.DataTable = ''<tbl>''',
'<tbl>', DataTable), '<vid>', @ViewId)
FROM TableList
FOR XML PATH('')),
11, 9999)
+ ') raw
PIVOT (MAX([Value]) FOR NameToDisplay IN (' + FieldNames + ')) pvt
' + 'GO'
It might help if you have something like SQL Prompt or SQL Refactor to reformat the results, you can't get line breaks out of a FOR XML query. When necessary I add delimiters such as "|" and use a REPLACE() outside of the FOR XML query to turn them into line breaks.
Of course, this doesn't prove that you NEVER need a cursor, but I consider them a last resort. My biggest problem with cursors is I see them misused by developers who can't think in terms of set-based solutions, and most of them (that I have seen) just start typing DECLARE CURSOR and overlook things like FAST_FORWARD and READ_ONLY.
December 2, 2009 at 12:01 am
Yes.
G. Milner
December 2, 2009 at 12:55 am
The problem is that DataTable1 can be one from 100 or 1000 tables... It's a vertical database where each type of data has it's own table, and each business entity has it's own tables and can relate between each other...
It's not the VIEW that's the problem... it's building that view!!!
It's dynamical. And later a user can create a new field, add it the a view, and the view has to be "recalculated"... When I mentioned view I didn't mean SQL VIEWs... I meant screen views, grid views in c#...
The problem, like I said, it's not the data view itself (I use subqueries in a "normal" SQL statement), it's building that statement...
Pedro
December 2, 2009 at 1:02 am
When you have a fully customizable ERP/CRM solution with a vertical database where your clients can create the business entities, fields, relation between fields and business entities, view, reports.. whenever they want you can't have a "fixed" data view, or "fixed" report...
It's all dynamical and has to be "calculated", not on demand cause that would be crazy, but the first time it's used and then store the data view on a cache field and dispose of it whenever another field is added or removed.. that's when you have to use the CURSORS...
I could dispose of the cursors and have the app calculate the data view by simply sending all all the data needed to do it but there's no need for it since it's very very fast to do this on the database...
Pedro
December 2, 2009 at 5:59 am
The query I posted will build the SELECT statement for your views from as many tables as you like, without a cursor. If you don't want the "CREATE VIEW" line then remove it. Whatever modifications you want to make to the structure of the resulting SELECT statement, I guarantee there is a non-cursor way to do it.
Oh, and it is completely dynamical.
December 2, 2009 at 6:15 am
The cursor is used to build the data view, not get the data... after having the data view I don't need cursors...
The vertical structure of the database is more complex than presented here.
We have modules (business entities), each module can have fields created at any time, and reference any other module...
I have to use cursors to build a simple query to list the data...
I can have a field X on module A that's a reference to a field Y on module B and shows the data existing on module's B Z field...
The user can change the Z field to XX field, from module B, at any time... At that time I use cursors to get the module fields' information to build the correct query to get the data...
And a module can have as many fields as they want and a view only have 2 or 3... so no need to have a view with all fields..
Pedro
December 2, 2009 at 8:17 am
The cursor-less query I posted does exactly that, it builds a query from the metadata. It may not be the exact query you need since I didn't have all the details, but it could be the basis for something that does work. Your repeated claims that you need to use a cursor only show that either you don't understand how the FOR XML PATH('') subqueries work, or you haven't applied the imagination necessary to turn it into what you want. If your current solution is sufficient it may not be worthwhile to spend the effort to develop an alternative, but that doesn't mean an alternative does not exist.
I don't care how complex the application is, or whether it cures cancer and brings world peace, your claim that you absolutly need a cursor to concatenate some fields from metadata tables is completely wrong. I'm not claiming that my approach would be faster, more maintainable, or better in any other way, I'm just claiming that there are ways to solve the problem without a cursor.
December 2, 2009 at 9:34 am
-----------------------------------------------------------------------------------------------
--PiMane yesterdy 10:49pm
--cursors are a necessary evil... some tasks just need cursors and there's no way of replacing them
------------------------------------------------------------------------------------------------
The only explanation why I think someone would feel that was is if they don't have any frontend experience or perhaps their in that stage of their development where that is true for them. Theoretically a cursor is just a loop. Nothing more.
Load the set to loop through into something, loop, go to the next iteration. It's a fancy while loop w/ perhaps some performance gain related to specific t-sql scenarios. That's it. So if you think some tasks just need cursors and there's no way of replacing them then you're just dead wrong. Sorry PiMane. I don’t mean to call you out. The comment is for anyone who feels that way. One thing you don't want to do is get totally comfortable w/ any given method. It's hurtful to your skill set and short sighted. I understand some of these other guys arguments about cursors having better performance in most scenarios and I agree but to say there are instances where a cursor is the only way is just wrong because all it is is another looping mechanism. One of many.
I look at cursors the way i look at building a website using the drag and drop methods. Sure their nice and little easy to use things that will basically make the process a little less painful but in my opinion part of the experience is the building process. i want to write the code myself. I dont need sql to create a magic while loop and call it a cursor for me. I'll do it myself. For the record i didnt always feel this way and to be honest havent written a peice of asp. or .net in about 5 years...prob more and i hate myself for that. I get too comfortable w/ what i do everyday.
Either way the cursor discussion is a good one. There is usually no middle ground. You either use them or u don't which makes for a good discussion.
December 2, 2009 at 9:44 am
In my post I say that I could send all the necessary data to c# and let it do the work, but probably it would make tons of unnecessary calls to the database the gather all the needed data, or just have it all on cache... that's one step we're planning to do, put all the "metadata" on cache since it's not changed that much and invalidate the cache when some change is made...
Also, the approach we're making on building the view, and since a field can reference a field from another module and then reference another field (A -> B -> C -> D), it's pretty heavy since it uses cursors and, even worts, recursive store procedures.... that could kill the database if it was called more often than it is.. for each call to the data build query store we have more than 100.000 to data.. it's a very low call store, since we use a pre-calculated query, but even so we're "moving" the calculus to the c# app since it has all the data needed..
Pedro
December 2, 2009 at 9:44 am
Cursors are just a tool. The problem with them is misuse.
Lots of devs seem to want to use a drill press to cut plywood sheets. Can be done, but a table saw is better at it. The most common argument I see used on this one is that table saws are difficult to learn how to use properly, and the dev already knows how to use a drill press. (To push this metaphor beyond all reasonable use.)
Refusing to use a drill press is just as silly as misusing one. Learn how and when to use each tool, or step aside and let a pro carpenter do the job.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply