Forum Replies Created
Viewing 15 posts - 43,441 through 43,455 (of 59,102 total)
-
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
Paul White (6/13/2009)
Jeff Moden (6/12/2009)
It causes the whole thing to act like a cursor with a recomplile for every row if you don't include the target table in the FROM...--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) -
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
Aaron (6/13/2009)
PS: Why go through all of the trouble creating this code and formatting it if it's just going to get hosed. I attached a text file with the formatting...--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) -
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
Aaron,
The post Paul White wrote that starts with "I would usually write it like this:" is more of what I would have done and for the same reasons. That...
--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) -
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
Aaron (6/13/2009)
Is there a way I can increase performance for concurrency by making the database's isolation level to "READ UNCOMMITTED"? Or is there a way to do this just...--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) -
RE: What SQL Server Sites do you use?
Heh... wasn't talking about SO. I was talking about here. And by heavy hitters, I'm not necessarily talking about folks with a lot of points. I'm talking...
--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) -
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
--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) -
RE: Triggers
Like this...
[font="Courier New"]--===== Disable all triggers on user tables in database
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL =''
SELECT @SQL = @SQL +
+ 'DISABLE TRIGGER ALL ON ' + Table_Schema + '.' +Table_Name +';'
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
EXEC (@SQL)
--===== Enable all triggers on user tables in database
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL =''
SELECT @SQL = @SQL +
+ 'ENABLE TRIGGER ALL ON ' + Table_Schema + '.' +Table_Name +';'
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
EXEC (@SQL)[/font]
--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) -
RE: High volume of transactions definition/measure?
Vivien Xing (6/11/2009)
500+ GB database is considered as a VLDB nowadays.Who made that rule? 😉
--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) -
RE: Grouping problem
Tim Hobbs (6/12/2009)
Many thanks for this - I'm now reading up about CTE's!!!Regards
Tim
It's not the CTE that did the magic. It's the way one ROW_NUMBER was compared to another....
--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) -
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
Cross post... please, no answers here. Go to the following instead...
http://www.sqlservercentral.com/Forums/Topic734177-338-1.aspx
--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) -
RE: How to find the default database location
Jeffrey Williams (6/12/2009)
BTW - if you do not specify file locations in your create database statement, the default location defined for that instance will be used.Voice of reason, right there.
--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) -
RE: Append output from SQLCMD in batch file
Leave the first "-o" alone. Change all of the other "-o" to ">>".
Old DOS warrior trick. 😛
--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) -
RE: Locking Perf Problem: Help Update/Insert WITH table_hints
This is a classic error that causes huge performance problems. The target of the update must also be included in the FROM clause if a join is present. ...
--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) -
RE: Placing tables in memory
For the record, both Temp Tables and Table Variables start out in Memory and spool to TempDB if they get too big.
I suspect neither a Local Temp Table nor 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) -
RE: CTE is not just an expression
What helped it was that you got the COALESCE out of the WHERE clause.
--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 - 43,441 through 43,455 (of 59,102 total)