Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,186 through 43,200 (of 59,063 total)

RE: Are the posted questions getting worse?

Roy Ernest (6/26/2009)
GilaMonster (6/26/2009)
Grant Fritchey (6/26/2009)
And if things get REALLY bad, we'll just toss Gail into the middle of them. Although, that might be considered a cruel thing to do...
--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)

June 26, 2009 at 8:17 pm

#1016330
RE: Are the posted questions getting worse?

Florian Reischl (6/26/2009)
lmu92 (6/24/2009)
Hi folks,
would someone with execution plan background mind to take a look at this post and verify if I'm on the right track or misguiding the OP?
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)

June 26, 2009 at 7:57 pm

#1016327
RE: How to transpose from Row to Columns with out using Pivot

Heh... I almost forgot... you can get a tiny bit more speed out of it if you move the pre-aggregation from the derived table to a CTE like this...
[font="Courier New"];WITH
ctePreAgg AS
(--==== Pre-aggregate the data.  This will obviously work much better with the correct index
SELECT Acct_Debtor, Occurance, MAX(LandLine_Contact_No) AS Max_LandLine_Contact_No
   FROM dbo.Post_File082_Landline_No
  GROUP BY Acct_Debtor, Occurance
)
SELECT preagg.Acct_Debtor,
        MAX(CASE WHEN preagg.Occurance=1 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber1,
        MAX(CASE WHEN preagg.Occurance=2 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber2,
        MAX(CASE WHEN preagg.Occurance=3 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber3
   FROM ctePreAgg AS preagg
  GROUP BY preagg.Acct_Debtor[/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)

June 26, 2009 at 7:56 pm

#1016326
RE: How to transpose from Row to Columns with out using Pivot

Paul White (6/25/2009)
Cool stuff Chris. Of course it leaves me wondered about the hash aggregate versus stream aggregate thing but hey.
So PIVOT can be slightly more efficient - if...
--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)

June 26, 2009 at 7:43 pm

#1016325
RE: How to transpose from Row to Columns with out using Pivot

lmu92 (6/22/2009)
You don't need the subselect:
SELECT
ACCT_DEBTOR,
MAX(Case WHEN OCCURRENCE=1 THEN LANDLINE_CONTACT_NO ELSE null END) AS LandLineNumber1,
MAX(Case WHEN OCCURRENCE=2 THEN LANDLINE_CONTACT_NO ELSE null END) AS LandLineNumber2,
MAX(Case WHEN OCCURRENCE=3 THEN LANDLINE_CONTACT_NO ELSE...
--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)

June 26, 2009 at 7:34 pm

#1016324
RE: select count(*) in a partitioned table takes 100%cpu

In that case, if the CPU spikes for more than an hundred milliseconds or so, I'd have to say something is wrong.
Can't help without a bit more information:
1. How...
--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)

June 26, 2009 at 6:58 pm

#1016321
RE: 911 - Cursor killing my trigger

Scott Coleman (6/26/2009)
Sorry, I got here through "Active Threads" and didn't notice that it was 2000.
You can use a self-join solution, although depending on your rowcounts and indexes this may...
--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)

June 26, 2009 at 3:29 pm

#1016299
RE: Good SQL Server article

Florian Reischl (6/26/2009)
Sure, it's a task on my list, too. 😉 But it's really neat that he provides a complete test setup.
I agree... it just like some of the good...
--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)

June 26, 2009 at 3:25 pm

#1016297
RE: Good SQL Server article

Florian Reischl (6/26/2009)
Jeff Moden (6/26/2009)
jaclynmcatanzaro (6/26/2009)
Good SQL Server article
http://www.informationflash.com/%5B/url%5D
Which one?
Can't answer your question but the new article by Adam Machanic is really good in my opinion:
The Hidden Costs of INSERT...
--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)

June 26, 2009 at 3:19 pm

#1016295
RE: What has been your experience of agile development

Unless you're very careful, it becomes nothing more than an excuse for releasing bad or slow product with no documentation. Contrary to what many believe, it does take some...
--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)

June 26, 2009 at 3:08 pm

#1016292
RE: How to chage IDENTITY Column seed

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

June 26, 2009 at 3:00 pm

#1016288
RE: Good SQL Server article

jaclynmcatanzaro (6/26/2009)
Good SQL Server article
http://www.informationflash.com/%5B/url%5D
Which 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)

June 26, 2009 at 2:53 pm

#1016285
RE: Reindex/Reorg Space Concerns

You can specify that the sort done during the rebuild will be done in TempDB which should make your life a whole lot easier when it comes to space used.
Of...
--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)

June 26, 2009 at 2:49 pm

#1016282
RE: Reindex/Reorg Space Concerns

karthik_sql (6/24/2009)
Please try the below
1. Put db to Simple recovery
Absolutely NOT! That will make a mess of the log file for purposes of backup.
--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)

June 26, 2009 at 2:46 pm

#1016281
RE: select count(*) in a partitioned table takes 100%cpu

tejas parikh (6/26/2009)
Hi. I've a partitioned table based on 6 months. each partition has 2 files(1fg).
All I'm running is a simple count(*) below. The cpu usage goes to a 100%...
--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)

June 26, 2009 at 2:44 pm

#1016280

Viewing 15 posts - 43,186 through 43,200 (of 59,063 total)