Delete rows older than 1 year (365 days)

Question

Delete rows older than 1 year (365 days)

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jpotucek SSCoach Points: 18583 More actions · Answer 1

When logged into the Server - the response time is the same doing a simple Select * from Forms_Holder - 4 mins to return 19000 rows

but the wait type is different - PAIGEIOLATCH_SH

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

And how does SELECT object_id FROM forms_holder perform?

What is the size of the table in MB?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Also, a few posts back you said SELECT * FROM ... returned 137 rows, now it is 19000 ? Please tell us exactly what you are doing otherwise we can't help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 4

mister.magoo (1/26/2011)
Also, a few posts back you said SELECT * FROM ... returned 137 rows, now it is 19000 ? Please tell us exactly what you are doing otherwise we can't help.

Ditto to what Magoo said.

This is why I asked for all that information earlier. Not to annoy, but to give you the best possible chance of getting a good solution. It's what we ask of all people who post performance issues. We want to help you, but we can't if we don't have all the details.

Did you run Magoo's SELECT statement yet? How many rows came back? How long did it take?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jpotucek SSCoach Points: 18583 More actions · Answer 5

I apologize for the different row counts.. I have different versions of the same table (DEV, Stage, Prod) Prod has the 19,000+ rows

the table in PROD is 3,155.422 MB

I ran

SELECT [object_id]

, [form_family]

, [file_name]

, [language]

, [unit]

, [state]

, [rec_date]

FROM forms_holder

and it returned all 19,000+ rows in a matter of seconds!!!!!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Well, there is your problem - it's a 3GB table and you are trying to select * from it.

So, when you try it over the network you see NETWORK I/O waits and locally you will see PAGEIOLATCH as it reads the data.

I don't think anything will prevent the waits here except not selecting the whole table...

Brandie may be able to help more?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 7

The only real difference between your SELECT * statement and Magoo's code is the varbinary column. At a guess, this column is storing some sort of images, yes?

Images are huge. That column is what is taking up the majority of your table space. It's also probably what's taking a long time to deliver across the network to your clients.

Questions we need to know the answer to:

Is the Developer running the same SELECT * statement you were running? (If not, tell him you need his code so you can troubleshoot it).

What exactly is the Developer trying to accomplish with his code? Is he displaying image data up in a report or delivering it through a third party app? Does he even *need* the varbinary column for what he's doing?

If the answer to the last is no, then tell him to stop SELECTing on that column and his performance issues will disappear.

The Developer's code is essential for us so we can recommend performance tuning techniques that may help speed up the query. One of those recommendations may be an alternative CLUSTERED INDEX or a brand new NONCLUSTERED INDEX. But we won't know until we see the queries that are hitting this table and can find common denominators.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

Jpotucek (1/26/2011)
Thank you!! I changed my query to
DELETE FROM forms_holder
WHERE rec_date <= DateAdd(d,-365,GETDate())
related (I think) question. I questioned the developer as to why he wanted the rows deleted and he said that his queries against this table were taking too long..
I did a simple select * from ... against this table before and after my delete and it took 10 mins to return a couple hundred rows before my delete and more than 3 mins to return 37 rows after my delete..
not sure where to start looking as to why it is taking so long to query against this one table.. ????

Ummmm... I have to ask... if you're not the Developer, what are you?

Shifting gears, and I'm not trying to be snarky about this, if neither you or the developer knew how to do this simple task, I'd recommend getting some training on SQL Server. It would also help the developer to write code that can withstand large numbers of rows without destroying older information just to reduce a rowcount to make code work better.

--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)

Jpotucek SSCoach Points: 18583 More actions · Answer 9

not sure what snarky means.. but you are right.. I could do with some training .. time to hit the books!

turns out that they are storing images (PDFs) .. I will take a look at the Developers code and talk to him about options..

Thanks for your help.

Jpotucek SSCoach Points: 18583 More actions · Answer 10

I am not the Developer. I take care of the Server that hosts their SQL Server Database and attempt to act as DBA for them. : )

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 11

Jpotucek (1/26/2011)
I am not the Developer. I take care of the Server that hosts their SQL Server Database and attempt to act as DBA for them. : )

Ah! I kind of suspected that. I've been in that position myself.

Just as a starting point... As with any language, a lot of the "meat" for writing code can be found in the functions. My recommendation would be to lookup all the functions in "Books Online" (the Help system that comes with SQL Server) and try some of the examples they have for some of them. Sometimes half the battle is just knowing that there's a function or an "operator" (usually a math or string operator) that can do a certain thing.

--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)

Jpotucek SSCoach Points: 18583 More actions · Answer 12

Jpotucek

SSCoach

Points: 18583

January 27, 2011 at 4:31 am

#1278990

Thanks for all your help!!!