Jeff Moden

Forum Replies Created

Viewing 15 posts - 38,476 through 38,490 (of 59,072 total)

RE: CTE performance

kazim.raza (5/10/2010)
This is what I have done; plugged the tally from the snippet for #a;
;WITH
ctePreNumber AS
( --=== Add a row number to each "a" so we can repivot after the...
--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)

May 10, 2010 at 2:48 pm

#1165243
RE: CTE performance

Lamprey13 (5/10/2010)
Jeff Moden (5/8/2010)
<snip>
Fifth... [font="Arial Black"]do a SELECT/INTO to build the temp table [/font]instead of building it ahead of time. It will be much faster because it won't log...
--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)

May 10, 2010 at 2:42 pm

#1165240
RE: Touchy Subject...

Steve Jones - Editor (5/10/2010)
Some good advice here, but if your boss won't listen, I think you ought to document your care in an email, and probably writing, and send...
--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)

May 10, 2010 at 11:58 am

#1165155
RE: Touchy Subject...

Heh... forget all the legalities. Strap on a recording device and tell your manager "No" because it's against the law.
--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)

May 10, 2010 at 11:06 am

#1165135
RE: DBA Exit Questions

Can you post your list so we don't make duplicate suggestions? Thanks.
--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)

May 10, 2010 at 11:04 am

#1165132
RE: Updating summary row counts in one table from another

You need to do the count (or any aggregation) as a SubQuery and join to it for the update.
--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)

May 10, 2010 at 11:03 am

#1165130
RE: Trimming a digit if it exists

Glenn5709 (5/10/2010)
Our database consists of phone numbers from all over the UK, so all of our phone numbers are inclusive of an area code therefore starting with 01, 02 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)

May 10, 2010 at 8:12 am

#1165009
RE: Microsoft Humor

BWAA-HAA!!! They must be preping us for the next release. 😛
--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)

May 10, 2010 at 8:06 am

#1165001
RE: Quick way to form relationship from several hundred tables to one

Lowell (5/4/2010)
i remember a post from Jeff Moden mentioning that a view could only have ~200 references; it was less than 255 but more than 200; so if you 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)

May 10, 2010 at 8:01 am

#1164998
RE: Using " IN " inside " WHERE " clause

only4mithunc (5/10/2010)
Hi Jeff,
I would like to know how you will approach this if the values are not hard coded, say it is from some table or it is a string...
--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)

May 10, 2010 at 6:49 am

#1164937
RE: A good way to do an update statement in 1000 row chunks

Jeffrey Williams-493691 (5/9/2010)
Jeff Moden (5/9/2010)
Jeffrey Williams-493691 (5/9/2010)
I don't see any reason to worry about which rows you have updated and/or tracking that in the temp table.
Keeps the locking localized to...
--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)

May 9, 2010 at 10:58 pm

#1164813
RE: importing data to a table having a unique key (moving records not inserted to a log table) and not aborting the operation

IIRC, SQL Server 2008 has the "5th" command in it... MERGE. Take a look at Books Online for the MERGE command. It'll do everything you ask in your...
--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)

May 9, 2010 at 10:46 pm

#1164812
RE: Application Is Slow-- Performance?

<I told you so mode ON>
Heh... You just hit one of my pet peeves so I'm not sure I'm one of the folks to answer this one because I tend...
--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)

May 9, 2010 at 10:42 pm

#1164810
RE: CTE performance

kazim.raza (5/9/2010)
It ain't working on #HIE 🙁 either its too much of data or what.. it keeps executing freezing my notebook :S
Are you using a real Tally table like...
--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)

May 9, 2010 at 10:24 pm

#1164808
RE: Cannot successfully perform an Order By on a particular table

Yes... I've seen such a thing just slam a 4 CPU system into the wall on UPDATES. I believe the problem may be that you're using the table name...
--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)

May 9, 2010 at 10:21 pm

#1164806

Viewing 15 posts - 38,476 through 38,490 (of 59,072 total)