There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

  • JKSQL (4/14/2009)


    I am also looking forward to part due. I really try not to use cursors, but sometimes get in traps where I can not think of a better solution. anyway I am always looking for geeky stuff like this.

    Thanks, hopefully, this series will help.

    [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]

  • RBarryYoung (4/14/2009)


    Manie Verster (4/14/2009)


    ...

    RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that...

    Check out Notification Services. I am no expert in it, but I have heard that it is designed to send out millions of customized emails at the same time.

    Only problem with using Notification Services is that it goes away in SQL Server 2008.

  • I too am looking forward to follow-up articles. I'm hoping new techiques are presented in addition to a consolidation of techiques we've already seen in previous SSC articles.

    I've been convinced in the past that my looping code was the only solution to a problem I was working on only to be shown a far more efficient set based solution using a tally table or some other technique!

    I now border on being an anti-cursor zealot, but I still concede that there are cases where a loop (cursor or otherwise) might make more sense, especially if the anticipated loop counts are low and the cost of loop overhead is insignificant compared to overall job cost.

    Which leads to my question for the purists who suggest looping code can always be optimized using a set based approach: What is a more efficient set based approach for the case where a "canned" stored procedure needs to be called multiple times with varying arguments? (assume rewriting the sp is not an option - eg. it's a SQL Server system sp or vendor-provided sp)

    I think that's the trickiest part of RJ's sp_send_dbmail question, not so much the "how do you send multiple e-mails from SQL Server?" (Although that can be tricky, too!)

    So, for argument's sake, let's take RJ's question a step further and assume we have a set based query that pulls e-mail address, subject line, and message body columns for sending content specific e-mail reports to a dynamically changing set of sales managers. Let's say we send the top 10% in sales each month a message saying something like "Congratulations, Patty O'Furniture. Your region's sales total of $123,456.00 made the top 10 percent this month."

    The obvious looping solution is to build a cursor loop on the query and call sp_send_dbmail inside the loop. Stick a fork in it, it's done.

    A dynamic SQL technique was posted that builds a string with the requisite set of "sp_send_dbmail" calls and then executes it. (please forgive my ignorance, but is this technique called aggregated execution?)

    This technique has drawbacks, however:

    1) If the message body or subject line might contain apostrophes, we would have to call the "REPLACE" function which adds overhead.

    2) Depending on how the procedure is called, there might be ownership chain permission issues.

    3) It's arguable that the code is less readable/maintainable

    I'm convinced that in this case a loop is the best solution but hopefully I'll be pleasantly surprised by subsequent posts to the contrary.

    Just so it's clear I'm not asking about e-mail, notification services, etc. my question is: Is it possible to avoid loops (efficiently ;-)) when you need to call sp_cant_touch_this(@someargument as varchar (25)) multiple times with a set of changing arguments?

  • Andy DBA (4/14/2009)


    Just so it's clear I'm not asking about e-mail, notification services, etc. my question is: Is it possible to avoid loops (efficiently ;-)) when you need to call sp_cant_touch_this(@someargument as varchar (25)) multiple times with a set of changing arguments?

    One might argue that either

    a) sp_cant_touch_this really should be touched - i.e. the design of the system needs looking at

    or

    b) sp_cant_touch_this is something that would better be handled by an application rather than some T-SQL.

    I personally think b) applies in the case of the email question. I'm sure i'll be hung, drawn & quartered for that opinion however!

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • I'll just jump in and risk making a fool of myself.

    In one of my data import apps (Oracle to SQL Server), I use temp tables with varchar(500) fields to ensure all data can be imported successfully to the local server (via DTS). The data from the temp tables is then merged into the final tables using T-SQL (delete / insert) statements.

    Since the source data can sometimes contain "invalid data" (e.g. primary key violations or field size too big), the import sometimes fails and this is where my cursor solution comes into play. It imports all rows from the temp table into the final table one by one. Each row that fails is imported into the corresponding error table. This allows correction or just validation of the invalid data lateron and ensures that correct data is available to all clients that were not affected. (Note: the first try is always set based - only in case of an error, the cursor based approach starts)

    Wondering if someone has a hint on a better way to do this.

    If you need more rationale for this, let me know.

    Best Regards,

    Chris Büttner

  • Lynn Pettis (4/14/2009)


    RBarryYoung (4/14/2009)


    Manie Verster (4/14/2009)


    ...

    RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that...

    Check out Notification Services. I am no expert in it, but I have heard that it is designed to send out millions of customized emails at the same time.

    Only problem with using Notification Services is that it goes away in SQL Server 2008.

    My understanding was that it's functionality would get rolled into Reporting Services?

    [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]

  • Christian Buettner (4/14/2009)


    I'll just jump in and risk making a fool of myself.

    In one of my data import apps (Oracle to SQL Server), I use temp tables with varchar(500) fields to ensure all data can be imported successfully to the local server (via DTS). The data from the temp tables is then merged into the final tables using T-SQL (delete / insert) statements.

    Since the source data can sometimes contain "invalid data" (e.g. primary key violations or field size too big), the import sometimes fails and this is where my cursor solution comes into play. It imports all rows from the temp table into the final table one by one. Each row that fails is imported into the corresponding error table. This allows correction or just validation of the invalid data lateron and ensures that correct data is available to all clients that were not affected. (Note: the first try is always set based - only in case of an error, the cursor based approach starts)

    Wondering if someone has a hint on a better way to do this.

    If you need more rationale for this, let me know.

    I use BULK INSERT, BCP or Integration Services(SSIS) for stuff like this. They have this capability built-in.

    [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]

  • Thanks for the series Barry. I'm looking forward to learning this.

  • bruce.trimpop (4/14/2009)


    Matt Whitfield (4/14/2009)


    bruce.trimpop (4/14/2009)


    Unfortunately, the "readily consumable format" is a problem. Packaging something up that would cover all or most of the critical functional variations would be a bit time consuming. Thanks though.

    My point is just that I agree that using a cursor just because it's the easy solution is a bad approach, that doesn't mean that cursors should never be used. I've found that many "older" developers that came from a row based procedural style of data processing (xBase for example...gosh did I just date myself :-D) found that cursors fit their logic model more comfortably that set based logic. As such I've run into quite a bit of SQL code that used cursors when set based solutions are obviously the better choice. Those fall under the "cursor bad!" category. I've also run into situations where using a cursor is a proper fit, just one more tool in the tool box as it were.

    That's a shame - sounded like it could have been an excellent demonstration because of the complexities involved. Using things like windowed functions would probably have given a really good example of how SS2K5 has helped in the eradication of cursors.

    If you do feel inclined to post a bit of DDL/DML to set up a test, would be *sooo* appropriate right here 🙂

    I'll see if I can come up with something stripped down, but meaningful. I'd love to see some alternate (non-cursor) solutions for this 🙂 I admit I have not revisited this in SS2K5 since it still needs to work in SS2K and it's not a high priority. It may take a day or two to come up with something if folks can wait that long!

    Well here it is as best I can manage, I've tried to strip it down to the bare minimum and still keep the complexity!

    I apologize for any typo's or messed up data, I worked this up pretty quickly!

    :crazy:

    I'll be very interested in seeing any non-cursor based solutions.

    Thanks all! 🙂

    CREATE TABLE [dbo].[client](

    [clientid] [int] not null,

    [income] [numeric] (12,2) )

    CREATE TABLE [dbo].[sometable](

    [clientid] [int] not null,

    [dependents] [int] )

    CREATE TABLE [dbo].[clientfee](

    [uniqueid] [int] NOT NULL,

    [clientid] [int] not null,

    [psfeetablemstrid_c] [char](18) NULL,

    [clientfeetype_c] [char](1) NOT NULL CONSTRAINT [df_clientfee1] DEFAULT ('M'),

    [feetype_c] [char](1) COLLATE NULL,

    [feebasis_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_clientfee2] DEFAULT (0),

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    [visitsmax_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee3] DEFAULT (0),

    [visitsused_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee4] DEFAULT (0),

    [dollarsmax_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee5] DEFAULT (0),

    [dollarsused_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee6] DEFAULT (0),

    CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED

    (

    [uniqueid] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    This table (clientfee) can contain multiple rows for a client

    clientid is the client id

    psfeetablemasterid_c is the unique id of the fee table used if the clientfeetype_c is "T"able based

    clientfeetype_c determines if the fee is "M"anual (user entered value) or a "T"able (calculated from user entered criteria)

    feetype_c identifies the fee as a "F"lat dollar amount or "P"ercentage of charge

    feebasis_n is used when clientfeetype_c is "M"anual and contains a "F"lat dollar amount or a "P"ercent value based on feetype_c

    startdate_d and enddate_d is the date range that the fee is valid for.

    visitsmax_n and dollarsmax_n are the capitations available for this fee (0 means no cap)

    visitsused_n and dollarsused_n track how much of this fee has been used.

    =============================================================================

    CREATE TABLE [dbo].[psfeetablemstr](

    [uniqueid] [int] NOT NULL,

    [table1_vc] [varchar](20) NULL,

    [column1_vc] [varchar](20) NULL,

    [table2_vc] [varchar](20) NULL,

    [column2_vc] [varchar](20) NULL,

    [feetype_c] [char](1) NULL,

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED

    (

    [uniqueid] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    This table (psfeetablemstr) is contains the master (parent) fee table rows

    table1_vc - contains name of user table with column used in range determination

    column1_vc - contains name of column in table entered in table1_vc used in range determination

    table2_vc - contains name of user table with column used in range determination

    column2_vc - contains name of column in table entered in table2_vc used in range determination

    feetype_c identifies the fee as a "F"lat dollar amount or "P"ercentage of charge

    startdate_d and enddate_d is the date range that the fee table is valid for.

    ========================================================

    CREATE TABLE [dbo].[psfeetable](

    [uniqueid] [int] NOT NULL,

    [value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),

    [value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),

    [value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),

    [value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),

    [value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),

    [psfeetablemstrid] [int] NOT NULL,

    CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED

    (

    [uniqueid] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    This table (psfeetable) contains the child fee table rows that are linked to psfeetablemstr

    value1min_n minimum value from the column stored in column 1 stored in psfeetablemastr to compare against

    value1max_n maximum value from the column stored in column 1 stored in psfeetablemastr to compare against

    value2min_n minimum value from the column stored in column2 stored in psfeetablemastr to compare against

    value2max_n maximum value from the column stored in column2 stored in psfeetablemastr to compare against

    value3_n contains the fee value for the this range

    psfeetablemstrid links the row to the appropriate fee table in psfeetablemstr

    ========================================================

    CREATE TABLE [dbo].[service](

    [uniqueid] [int] NOT NULL,

    [servicedate_d] [datetime] NOT NULL,

    [component1_c] [char](5) NULL,

    [component2_c] [char](5) NULL,

    [component3_c] [char](5) NULL,

    [component4_c] [char](5) NULL,

    [component5_c] [char](5) NULL,

    [component6_c] [char](20) NULL,

    [component7_c] [char](20) NULL,

    [component8_c] [char](20) NULL,

    [component9_c] [char](20) NULL,

    [component0_c] [char](20) NULL,

    [clientid] [int] NULL,

    [charge] [numeric] (12,2) NULL,

    [calculatedfee] [numeric] (12,2) null

    CONSTRAINT [pk_activwork] PRIMARY KEY NONCLUSTERED

    (

    [uniqueid] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    This table contains the client services

    servicedate_c is the date of service that will be compared to the start/end dates in the fee tables.

    component0_c through component9_c are the service components that determine what the service is at least one component must have a value.

    clientid is the id of client who recieved service used to link the clientfee table

    =====================================================

    CREATE TABLE [dbo].[limitcodes](

    [uniqueid] [int] NOT NULL,

    [codetype_c] [char](10) NOT NULL,

    [validcode_vc] [varchar](20) NOT NULL,

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    [source_c] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [linkid_c] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    CONSTRAINT [pk_limitcodes] PRIMARY KEY NONCLUSTERED

    (

    [uniqueid_c] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    Contains service codes that various things are valid for

    codetype_c contains which component column the code is entered in.

    validcode_vc contains the valid service code

    start/end dates are the date range the code is valid for

    source_c is the table that has the service validation criteria entered against

    linkid_c is the uniqueid of the row from the table in source_c that has the service validation criteria entered against

    Sample data

    dbo.client

    1,25000

    dbo.sometable

    1,2

    dbo.clientfee

    1,1,1,"T",NULL,NULL,4/1/2009,NULL,30,29,50,400

    2,1,1,"T",NULL,NULL,1/1/2009,3/31/2009,20,20,300,300

    3,1,0,"M","P",25,1/1/2009,NULL,50,45,0,0

    4,1,0,"M","F",10,1/1/2009,NULL,0,0,100,0

    5,1,2,"T",NULL,NULL,1/1/2009,0,0,0,0

    dbo.psfeetablemstr

    1,"dbo.client","income","dbo.sometable","dependents","P",NULL,NULL

    2,"dbo.sometable","dependents","F",NULL,NULL

    dbo.psfeetable

    1,0,5000,1,1,10,1

    2,5001,10000,1,1,20,1

    3,10001,20000,1,1,30,1

    4,20001,50000,1,1,40,1

    5,50001,9999999,1,1,50,1

    6,0,5000,2,3,7,1

    7,5001,10000,2,3,17,1

    8,10001,20000,2,3,27,1

    9,20001,50000,2,3,37,1

    10,50001,9999999,2,3,47,1

    11,0,5000,4,999,5,1

    12,5001,10000,4,999,15,1

    13,10001,20000,4,999,25,1

    14,20001,50000,4,999,35,1

    15,50001,9999999,4,999,45,1

    16,1,1,0,0,10,2

    17,2,3,0,0,20,2

    18,4,999,0,0,30,2

    dbo.service

    1,3/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

    2,4/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

    3,5/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

    4,3/1/2009,"A","C",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,200.00,NULL

    5,3/1/2009,NULL,"1",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,200.00,NULL

    6,4/1/2009,NULL,"2",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,150.00,NULL

    7,5/1/2009,NULL,"3",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,150.00,NULL

    dbo.limitcodes

    1,"component1_c","A",NULL,NULL,'psfeetablemstr',1

    2,"component1_c","B",NULL,NULL,'psfeetablemstr',1

    3,"component1_c","C",NULL,NULL,'psfeetablemstr',1

    4,"component4_c","B",NULL,NULL,'psfeetablemstr',1

    5,"component4_c","D",NULL,NULL,'psfeetablemstr',1

    6,"component2_c","1",NULL,NULL,'psfeetablemstr',2

    7,"component2_c","2",NULL,NULL,'psfeetablemstr',2

    8,"component5_c","D",NULL,NULL,'psfeetablemstr',2

    9,"component5_c","D",NULL,NULL,'clientfee',4

    Note - limitcodes explained - this means that the psfeetablemstr (fee table) with id 1 is only valid for services

    where component1 is "A","B", or "C" AND component4 is "B" or "D". psfeetablemstr (fee table) with id 2 is only valid for services where

    component2 is "1" or "2" AND component5 is "D". clientfee row with id 4 is only valid for services where component5 is "D"

    So given this data the services would end up with the following calculatedfee values for the services based on the charge values stored with the service

    dbo.service

    1. $25 (clientfee row 3 manual 25 percent used)

    2. $37 (clientfee row 1, psfeetablemstr row 1, psfeetable row 9 because client.income and sometable.dependents values fall in this range)

    3. $25 (clientfee row 3 because previous service used remaining visits cap of fee table 1

    4. $50 (clientfee row 3 only fee that covers component2 = "C" because it has not service limitations)

    5. $20 (clientfee row 5 psfeetablemstr row 2 psfeetable row 17 flat $20 because sometable.dependents value falls in this range)

    6. $20 (clientfee row 5 psfeetablemstr row 2 psfeetable row 17 flat $20 because sometable.dependents value falls in this range)

    7. $10 (clientfee row 4 only fee that covers component5 = "D" regardless of other component values)

  • The author makes the claim several times that there is no reason to ever use a cursor again with SQL Server 2005, because everything can be done with set-based logic. When the very realistic question is posed about calling sp_send_dbmail, we see no answer that supports the claim. Notification Services?, SQL CLR?, RS? These are the solutions to avoid a simple looping process to send emails?

    I am interested in seeing this problem addressed without a looping process:

    I ship a packaged DB product to our customers. I do not use NS or SQL CLR. I am not on site to administer their systems.

    I have a set of 50 pre-defined status messages in a message table. Each row in the message table defines a message, with all the execution parameters for a call to sp_send_dbmail. A message execution schedule is also included, as well as the last execution date/time, and results of the last sp_send_dbmail procedure execution for each message.

    The stored procedure is kicked-off by an hourly scheduled job, but only those messages that are due to be sent are sent. The job executes in less than 5 seconds.

    Sure, I can avoid a cursor by creating a temp table, loading all the rows that are due to be sent, WHILE-LOOPING through each row to send a message and capture the proc result, then updating my message table with the results. This is a lot more code than using a basic fast_forward cursor, and there is no performance issue with such a small amount of work to do.

  • Great example, RJ. Of course, A specific answer will be dependent on the specifics details of your requirements. For instance is "msdb.dbo.sp_send_dbmail" really a business requirement or is it just assumed to be the means that you will have to use?

    General speaking I can see 4 possible ways to approach this that do not use Cursors: distribution lists, NS (Notification Services), SQL CLR (or external activation), and aggregated execution. Distribution lists are usually the preferred solution.

    Well! After a long day I got a chance to read all the posts. WOW!!!

    I think you all misplaced my requirement. The job (which is a stored procedure) will identify users who can continue to be an valid user in my application. If for some reason the user no longer qualify to be an active user in the application then the user would be deleted from the application.

    Once this happens the user & his/her manager would be notified that the user has been removed from the system. when the job runs there could be multiple users removed from the system & each user might have one or more managers associated i.e.>

    user1 -> manager1

    user2 -> manager2, manager3

    user3 -> manager1, manager4

    & so on....

    The schema structure is below:

    create table user (id int, username varchar(100), email varchar(100), status int)

    create table usermanager (id int, deptid int)

    create table userdept (id int, deptid int)

    alter table usermanager add constraint user_usermanager_fk

    foreign key (id) references user(id)

    alter table usermanager add constraint dept_usermanager_fk

    foreign key (deptid) references dept(id)

    alter table userdept add constraint user_userdept_fk

    foreign key (id) references user(id)

    alter table userdept add constraint dept_userdept_fk

    foreign key (deptid) references dept(id)

    insert into user (id,username,email,status)

    select 1,'user1','user1@user.com',0

    union all

    select 2,'user2','user2@user.com',0

    union all

    select 3,'user3','user3@user.com',0

    union all

    select 4,'user4','user4@user.com',0

    insert into usermanager (id,deptid)

    select 2,1

    insert into userdept (id,deptid)

    select 1,1

    union all

    select 3,1

    union all

    select 4,1

    In the above code user2 is the manager for users 1,3 &4. If any users status changes from 0 to 1 then they should be deleted from the system & email to both user & manager should be sent.

    I can only use stored procedure as far as my knowledge & cannot use any other approach like NS (can't use it as we would be moving to SQL Server 2008 shortly), distribution lists (wow what is distribution lists?), SQL CLR (I got to do some research myself), and aggregated execution (well I dont understand this part either)

    Thanks

    RJ

  • [font="Verdana"]An interesting start, and I'll look forward to reading more.

    BTW, I would challenge the statement that cursors perform better in Oracle. From my own experience (from Oracle 7.0 through to Oracle 10g), cursors perform just as poorly in Oracle as they do in SQL Server. Technology like Oracle Forms and Oracle Reports inherently create cursors, so they are prevalent: that doesn't mean they perform well.

    In terms of the example of e-mailing out using a cursor, my own thoughts are:

    1. Yes, I've done the same. Admittedly I used a while loop without a cursor. But same thing.

    2. I believe that Notification Services (part of Reporting Services in 2008) is designed to do this sort of thing. Might be a reason to upgrade from SQL Server 2000! However, I haven't played around with it myself. Worth investigating though if your system relies on this.

    3. Is sending e-mail from the database the right approach? I've done it for emailed alerts to DBAs or the like, but I'm thinking from an architectural and security point of view for an application, it would be better to handle this outside the database.

    4. Converting it to one huge piece of dynamic SQL won't work. SQL Server 2000 doesn't have varchar(max).

    I believe that there are times when cursors are appropriate, but as SQL Server and Oracle develop, I have found myself using them less and less.

    [/font]

  • Joe Celko????

    Is it possible??

  • bruce.trimpop (4/14/2009)


    Bruce

    Here is the SQL i used on 2000 to re-create the data and tables - some mods were required, so hope you can check.

    Is it possible that you could post the cursor you use? I am having trouble understanding how some of the data interacts, and seeing the source would really help.

    My initial reaction would be that the table layout would benefit from a bit of a redesign in terms of being somewhere close to 3NF, which would probably have made the set based approach a lot easier in the first place. I presume that you cannot change table structures however, and will respect that in my solution!

    The code...

    CREATE TABLE [dbo].[client](

    [clientid] [int] not null,

    [income] [numeric] (12,2)

    )

    CREATE TABLE [dbo].[sometable](

    [clientid] [int] not null,

    [dependents] [int]

    )

    CREATE TABLE [dbo].[clientfee](

    [uniqueid] [int] NOT NULL,

    [clientid] [int] not null,

    [psfeetablemstrid_c] [char](18) NULL,

    [clientfeetype_c] [char](1) NOT NULL CONSTRAINT [df_clientfee1] DEFAULT ('M'),

    [feetype_c] [char](1) NULL,

    [feebasis_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_clientfee2] DEFAULT (0),

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    [visitsmax_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee3] DEFAULT (0),

    [visitsused_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee4] DEFAULT (0),

    [dollarsmax_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee5] DEFAULT (0),

    [dollarsused_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee6] DEFAULT (0),

    CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED

    ( [uniqueid] ASC )

    )

    CREATE TABLE [dbo].[psfeetablemstr](

    [uniqueid] [int] NOT NULL,

    [table1_vc] [varchar](20) NULL,

    [column1_vc] [varchar](20) NULL,

    [table2_vc] [varchar](20) NULL,

    [column2_vc] [varchar](20) NULL,

    [feetype_c] [char](1) NULL,

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED

    ( [uniqueid] ASC )

    )

    CREATE TABLE [dbo].[psfeetable](

    [uniqueid] [int] NOT NULL,

    [value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),

    [value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),

    [value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),

    [value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),

    [value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),

    [psfeetablemstrid] [int] NOT NULL,

    CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED

    ( [uniqueid] ASC )

    )

    CREATE TABLE [dbo].[service](

    [uniqueid] [int] NOT NULL,

    [servicedate_d] [datetime] NOT NULL,

    [component1_c] [char](5) NULL,

    [component2_c] [char](5) NULL,

    [component3_c] [char](5) NULL,

    [component4_c] [char](5) NULL,

    [component5_c] [char](5) NULL,

    [component6_c] [char](20) NULL,

    [component7_c] [char](20) NULL,

    [component8_c] [char](20) NULL,

    [component9_c] [char](20) NULL,

    [component0_c] [char](20) NULL,

    [clientid] [int] NULL,

    [charge] [numeric] (12,2) NULL,

    [calculatedfee] [numeric] (12,2) null

    CONSTRAINT [pk_activwork] PRIMARY KEY NONCLUSTERED

    ( [uniqueid] ASC )

    )

    CREATE TABLE [dbo].[limitcodes](

    [uniqueid] [int] NOT NULL,

    [codetype_c] [char](20) NOT NULL,

    [validcode_vc] [varchar](20) NOT NULL,

    [startdate_d] [datetime] NULL,

    [enddate_d] [datetime] NULL,

    [source_c] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [linkid_c] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    CONSTRAINT [pk_limitcodes] PRIMARY KEY NONCLUSTERED

    ( [uniqueid] ASC )

    )

    --Sample data

    --dbo.client

    INSERT INTO [dbo].[client] ([clientid], [income])

    VALUES (1,25000)

    --dbo.sometable

    INSERT INTO [dbo].[sometable] ([clientid], [dependents])

    VALUES (1,2)

    --dbo.clientfee

    INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

    VALUES (1,1,1,'T',NULL,0,'20090401',NULL,30,29,50,400)

    INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

    VALUES (2,1,1,'T',NULL,0,'20090101','20090331',20,20,300,300)

    INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

    VALUES (3,1,0,'M','P',25,'20090101',NULL,50,45,0,0)

    INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

    VALUES (4,1,0,'M','F',10,'20090101',NULL,0,0,100,0)

    INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

    VALUES (5,1,2,'T',NULL,0,'20090101',NULL,0,0,0,0)

    --dbo.psfeetablemstr

    INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc], [feetype_c], [startdate_d], [enddate_d])

    VALUES (1,'dbo.client','income','dbo.sometable','dependents','P',NULL,NULL)

    INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc], [feetype_c], [startdate_d], [enddate_d])

    VALUES (2,'dbo.sometable','dependents',NULL,NULL,'F',NULL,NULL)

    --dbo.psfeetable

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (1,0,5000,1,1,10,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (2,5001,10000,1,1,20,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (3,10001,20000,1,1,30,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (4,20001,50000,1,1,40,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (5,50001,9999999,1,1,50,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (6,0,5000,2,3,7,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (7,5001,10000,2,3,17,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (8,10001,20000,2,3,27,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (9,20001,50000,2,3,37,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (10,50001,9999999,2,3,47,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (11,0,5000,4,999,5,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (12,5001,10000,4,999,15,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (13,10001,20000,4,999,25,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (14,20001,50000,4,999,35,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (15,50001,9999999,4,999,45,1)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (16,1,1,0,0,10,2)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (17,2,3,0,0,20,2)

    INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])

    VALUES (18,4,999,0,0,30,2)

    --dbo.service

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (1,'20090301','A',NULL,NULL,'B',NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (2,'20090401','A',NULL,NULL,'B',NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (3,'20090501','A',NULL,NULL,'B',NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (4,'20090301','A','C',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,200.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (5,'20090301',NULL,'1',NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL,1,200.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (6,'20090401',NULL,'2',NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL,1,150.00,NULL)

    INSERT INTO [dbo].[service] ([uniqueid], [servicedate_d], [component1_c], [component2_c], [component3_c], [component4_c], [component5_c], [component6_c], [component7_c], [component8_c], [component9_c], [component0_c], [clientid], [charge], [calculatedfee])

    VALUES (7,'20090501',NULL,'3',NULL,NULL,'D',NULL,NULL,NULL,NULL,NULL,1,150.00,NULL)

    --

    --

    --

    --dbo.limitcodes

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (1,'component1_c','A',NULL,NULL,'psfeetablemstr',1)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (2,'component1_c','B',NULL,NULL,'psfeetablemstr',1)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (3,'component1_c','C',NULL,NULL,'psfeetablemstr',1)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (4,'component4_c','B',NULL,NULL,'psfeetablemstr',1)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (5,'component4_c','D',NULL,NULL,'psfeetablemstr',1)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (6,'component2_c','1',NULL,NULL,'psfeetablemstr',2)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (7,'component2_c','2',NULL,NULL,'psfeetablemstr',2)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (8,'component5_c','D',NULL,NULL,'psfeetablemstr',2)

    INSERT INTO [dbo].[limitcodes] ([uniqueid], [codetype_c], [validcode_vc], [startdate_d], [enddate_d], [source_c], [linkid_c])

    VALUES (9,'component5_c','D',NULL,NULL,'clientfee',4)

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • Wedge78 (4/14/2009)


    Hi All

    I agree that you can run into problems when using cursors without thought

    However I have an instance where I have to gather information from a 3.2 billion row partioned table. The table is partioned on website_id

    and table houses keyword data by page.

    I have to pull information out of this table for potentially thousands of websites and if I do it set-based it takes a large amount of time

    I changed to use a cursor as the speed of the retrieval of this information was cut by 50 times when using a cursor which was a massive saving and allowed people to use the report it was intended for

    So I gues what I am saying is that its all good and fine to bash cursors but would it not be fair to conceed that there is a place for cursors?

    And if you are adament that cursors should never be used I will be happy to use my example as a proper production test case to be proved wrong

    thanks

    Chris

    Chris... You've got my curiosity up. What did you do in the cursor that made such a change in performance?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 106 through 120 (of 380 total)

You must be logged in to reply to this topic. Login to reply