Jeff Moden

Forum Replies Created

Viewing 15 posts - 16,876 through 16,890 (of 59,098 total)

RE: delete millions of rows

The Dixie Flatline (9/13/2016)
Jeff, I've got someone trying to get that information now. But by my calculations, it's doing 444 rows per second. That seems slow.
Curiously,...
--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)

September 13, 2016 at 8:09 am

#1900560
RE: delete millions of rows

ChrisM@Work (9/13/2016)
Eric M Russell (9/12/2016)
4 million rows is actually not a lot, but it depends on the speed of your storage system and whether the table will need to remain...
--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)

September 13, 2016 at 7:53 am

#1900552
RE: Best way for dynamic partitioning of table sql server

GilaMonster (9/13/2016)
Jeff Moden (9/12/2016)
And, the OP responded :-).
Yes, back in 2014.
Perhaps we can find out how he used partitioning to improve performance. :w00t:
The person who said they...
--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)

September 13, 2016 at 7:47 am

#1900550
RE: Best way for dynamic partitioning of table sql server

sjimmo (9/13/2016)
was able to improve performance. But it was a lot more than just partitioning the table.
No problem. What I'm interested in is above. At a 10,000...
--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)

September 13, 2016 at 7:44 am

#1900546
RE: delete millions of rows

coool_sweet (9/9/2016)
i have table which has 5 millions record and i need to delete 4 millions in production.
what is the best approach to do it ?
A little feedback would be...
--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)

September 13, 2016 at 7:42 am

#1900543
RE: delete millions of rows

The Dixie Flatline (9/13/2016)
you might be able to use the false positives against an EXCEPT for final determination
Jeff, how am I going to know if it's a false positive or...
--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)

September 13, 2016 at 7:27 am

#1900534
RE: Best way for dynamic partitioning of table sql server

GilaMonster (9/12/2016)
Jeff Moden (9/12/2016)
You asked 4 rudimentary questions about partitioning, which seemed to indicate that you didn't actually do your homework on partitioning. Now your telling us that you...
--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)

September 12, 2016 at 4:11 pm

#1900427
RE: Best way for dynamic partitioning of table sql server

BI_NewBie (12/3/2014)
Hello all,
Greetings!
I need suggestion on Dynamic Partition of SQL Table. I have done my homework on this topic but still below questions are still stuck in my mind.
1. ...
--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)

September 12, 2016 at 3:49 pm

#1900421
RE: Best way for dynamic partitioning of table sql server

sjimmo (9/12/2016)
Jeff Moden (9/12/2016)
BI_NewBie (12/3/2014)
Hey Jeff!!
Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when...
--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)

September 12, 2016 at 3:32 pm

#1900417
RE: Best way for dynamic partitioning of table sql server

BI_NewBie (12/3/2014)
Hey Jeff!!
Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any...
--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)

September 12, 2016 at 9:11 am

#1900345
RE: Best way for dynamic partitioning of table sql server

jpandrade (9/12/2016)
sort of newbie to SQL Server partitioning, there are replies in this thread that state partitioning will not be something that would help with query performance. However msdn...
--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)

September 12, 2016 at 8:59 am

#1900342
RE: delete millions of rows

The Dixie Flatline (9/11/2016)
I have not. My widest table is "only" 138 columns wide and I'm pretty ticked off at that especially considering the all the bloody columns that have...
--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)

September 12, 2016 at 7:50 am

#1900316
RE: delete millions of rows

The Dixie Flatline (9/10/2016)
I'll admit the loop is going to run a long time, but you don't have to worry about missing a constraint, nonclustered index, foreign key, etc.
I...
--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)

September 11, 2016 at 10:26 am

#1900241
RE: A Case FOR Cursors...

Charles Kincaid (9/9/2016)
I would like for some body to test what happens if a procedure that uses a cursor is re-coded as a CLR proc where FOR loops (like a...
--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)

September 9, 2016 at 11:40 pm

#1900201
RE: delete millions of rows

As a bit of a sidebar, the use of a table-wide EXCEPT with temporal criteria is VERY quick at resolving differences between two tables for true ups and is one...
--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)

September 9, 2016 at 11:18 pm

#1900200

Viewing 15 posts - 16,876 through 16,890 (of 59,098 total)