Skip to content

Anna Hadnagy

  • About
25FebFebruary 25, 2015

The thing with cursors

So our aggregated databases have experienced some loading errors lately, and until that gets fixed, I have to collect the data straight from the field databases for each report.
That is, go in the database, pull the data, load into temp.
Go in the next database, pull the data, load into temp.
Go in next, pull, process. Go in next, pull, process.

Sounds familiar? That’s a classic cursor right there.

Sample:

CREATE TABLE pureHatred (
[DBName] varchar(50),
[text] varchar(max)
)
DECLARE @name VARCHAR(50)
DECLARE @sql VARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
While @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ['+@name+']
INSERT INTO pureHatred(DBName, [text])
SELECT DB_NAME(), v.name
from sys.[syscomments] sc
JOIN sys.views v ON v.object_id = sc.id
WHERE sc.text like ''%fill my sproc with hatred%'''
exec (@SQL)
FETCH NEXT FROM db_cursor INTO @name
END
Close db_cursor
DEALLOCATE db_cursor
select * from pureHatred
drop table pureHatred

Now you’ve heard a million times that cursors are a bad approach, and I won’t even deny that fact. They are. They lock rows and load everything into the memory. Cursors also include multiple steps, that is selecting the data, then processing the data, and the lock remains there for the duration of all steps. Also, the server has to execute the plan for each row, each database. (case in point: pull 10K orders each from 500 databases. Oh look! Large numbers.)

I have the luxury to do so in our environment, because we are loaded with memory already. My box still completes the 5 million pull-process cursors in less than 10 seconds, but imagine the peak in memory processes as it does so!* However, we could actually use that memory for other processes, so my solution is only for ad-hoc reports, and only until I fix the data imports. I slap my wrist every time I write up a cursor and hope that the lord of database administrators doesn’t find out about my sin.

But! I don’t know of any other way to jump databases. Feel free to suggest a solution, if you. If you have to repeat the same process in 500-1000-5000 databases, you need a cursor to repeat said process.

So again, should someone toss in the question on your next interview: cursors are bad for using up resources and locking rows.
There are times you can’t avoid them (jumping databases), but if you want to, read up on set-based approaches. I’ll write another article on the matter soon.

Conclusion: If you have to jump databases, you will use UNION ALL 5000 times, or you will load the data in a temp table with a cursor. In this one case I approve of using a cursor, else I you should avoid it.

*You know what? Let me run a query on sys.dm_exec_query_memory_grants next time I set up a mildly complicated cursor, and I tell you just how much memory it costs indeed to use it.

09FebFebruary 25, 2015

Cast datetime as varchar

I stumbled upon this great Q+A on Stack Overflow when I was looking for options to cast datetime for a report. (The customer didn’t like our standard mm/dd/yyyy format.)

I actually like the third answer the most, because it gives you a chart on all the styles. A pretty helpful shortcut, if you ask me.

union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
output, style
Apr 28 2014 9:31AM, 0
04/28/14, 1
14.04.28, 2
28/04/14, 3
28.04.14, 4
28-04-14, 5

Helpful, if you ask me, if you want to depart from your usual convert(101) format. I don’t even know why the answer is not marked correct. Check out the article here, and bookmark it for future use.

09FebFebruary 9, 2015

When a blocked procedure alert comes in

And I realize I was the one who launched those procedures

02DecFebruary 9, 2015

When the Senior DBA sits by me

And I pretend I totally understand the whole query plan

08Sep

Fun fact – SQL server truncates

Tl;dr – SQL server 2008R2’s results to grid output truncates string values above 43,680.

Conclusion: if half of your script is missing, it might still be there.

24JulFebruary 25, 2015

The morning after SQL server failed three times in one night

08Jul

Today was the day

When the server ran out of both memory and disk space. On both drives.

27JunJune 26, 2020

When I talk with my ex supervisor about my current supervisor

@annahadnagy It really is the only way to “supervise” you. Give you a hammer and some gaff tape and leave you alone for a few hours.

— Jason Noble (@lastashikabi) June 27, 2014

Linke
25JunJune 26, 2020

Linke

My brother asked me to come up with some pictures to show her girlfriend the many beautiful places of this world.

Being the dedicated overachiever I am, in return I handed over a website.

Where should Linke go? is a 3D ‘slideshow’ built on Hakim El’s reveal library. Of course it’s optimized to mobile and desktop, and of course I created a unique SoundCloud playlist to complement the experience.

Linke Cover
Linke Cover
Linke - Malta
Linke – Malta
Linke - Thailand
Linke – Thailand
Linke - Colorado
Linke – Colorado

Feast your eyes on more beautiful pictures from all over the world on Where should Linke go? and let me know if there is another country you’d like to see in there.

18JunFebruary 25, 2015

When the leadership team works my hopes up

With a promise of another 32 GB of memory for the analytics server just to cancel the project in the last second:

Posts navigation

Previous 1 2 3 4 Next

Categories

  • cybersecurity
  • Cybersecurity Path
  • Other
  • SQL server
© 2021 Anna Hadnagy
WordPress Theme: AccessPress Parallax