0

I have created an SQL script that runs really fast and is doing everything I want. It uses a cursor to loop through the parent record and then does some calculations on each and then outputs the results into a temporary table. I have another cursor in that one to extract all the children records of that parent and again does some work and puts it into a temporary table.

My senior Dev is saying cursors are awful and I should do it another way, but also doesn't tell me what a better way is.

So how do I loop through records and do steps of calculations and create an output for each record without using a cursor?

I'm sorry due to work product and how large the script is I can't post it's code. The format of it is:

  • cursor loops through table that holds parent records
  • For each parent record it takes field values and does conversions from strings to time.
  • Those conversions are then used in between statements to figure out if a time falls between the 2 field times
  • An insert statement with the output is put into a temp table and summed at the end.
  • Another cursor is created in the parent cursor to pull child records of the parent record from another table. The same process as the parent happens.

I'm not actually upset with my script, its working as intended, its running very quickly so far, but I am open to better practices if possible.

Improve this question
6
  • Cursors are awful. However, unnecessary changes to working production code might be even worse. Commented Oct 24, 2019 at 17:55
  • 1
    SQL should be done using set based logic, not cursors. Is there really anything you're doing that requires row by row processing? Commented Oct 24, 2019 at 17:56
  • I agree with others -- cursors are necessary sometimes, but should be avoided whenever possible. SQL is designed and optimized to operate on sets. Nothing in the (very simplified) function you described cannot be done as a set. The answer to your question "how do I loop through records and do steps of calculations and create an output for each record without using a cursor?" is that you don't. You don't loop. You do it all in selects, where clauses, case statements, etc. Commented Oct 24, 2019 at 18:01
  • it comes down to mindset. consider this: what is a join? records from each table are returned if the matching condition is satisfied. you don't have to write a loop but you know that looping or matching has to happen behind the scenes. its easier to think of it as the intersection of 2 sets of data. now consider conditional logic. you can add extra columns into a select which transform the data using case statements. that in effect is looping through the table applying your case logic to each record. Think about how you can replace the calculation you're doing in a loop with a CASE. Commented Oct 24, 2019 at 18:58
  • I originally tried to do all of this work using select statements only. But I found that adding the conversions from string 24h time to time, and attempting complex case statements it resulted in extremely slow run times, was a mess readability wise and offered little room for growth. By breaking it out into Cursor the run time dropped a minute in my case and adding/changing/testing code became a breeze. I don't understand how to do convert string to time, do the between case statements that might have time roll into the next day and then sum up results. Commented Oct 25, 2019 at 11:49

1 Answer 1

Reset to default
1

First of all, I hope you're aware that SQL Server 2008 is out of support even with SP4 for a few months now.

Second, as others already said, your Senior DBA is right about cursors. And if your code is too big to post it here, it probably is too time consuming for him to go through it, understand your code and then change it for you. I would expect a senior to give some hints on what to search for, though.

About your question, I find it very hard to think of an answer because your description only gives me a vague idea what you're trying to accomplish. E.g., what are the "field values" that you convert to time?

As of my experience, SQL Server does a pretty good job interpreting datetime strings. You may also find cast/convert and datepart useful.

As far as I understood your parent/child table, you'd probably want to use a table join here. They are well explained here: https://www.w3schools.com/sql/sql_join.asp

You may aggregate the result set to sum(). But again, my understanding of your endeavour is to vague.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the reply. Yes we are more then aware that 08 is out of support. To elaborate a bit more on why I thought in row by row. The time fields in the system were put in as strings and 24h time. So i'm converting those strings to real time fields. Then if the off time is greater then on time it does a simple between Case statement to figure out what hours fall between the 2 times. EX: If 5am falls betwee on of 3am and off of 6am then 1 else 0. I don't really see how you do a join on this setup or how I would do the conversions, between calculations and finally the case statements.
What you're describing sounds easy enough in a query. But again, without at least some of your code . . .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.