Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,966 through 43,980 (of 59,101 total)

RE: Which country is best for Outsourcing?

Doc (4/30/2009)
A funny side of Outsourcing from Onion news network 😀
http://www.theonion.com/content/video/more_american_workers_outsourcing
THAT's freakin' funny, Doc! Heh... I'll bet there are folks out there that have actually managed to pull...
--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 1, 2009 at 9:05 pm

#987657
RE: Performance issue with tally solution

Actually, it's a "white space" problem.
[font="Courier New"]DECLARE @CrLf NVARCHAR(MAX)
SET @CrLf = 'X '+CHAR(13)+CHAR(10)
SELECT LEN(@CrLf),
LEN(REPLACE(@CrLf,CHAR(13)+CHAR(10),''))[/font]
Any sproc that has spaces just before the last CrLf will have the problem because when the trailing CrLf goes away, those spaces become...
--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 1, 2009 at 8:43 pm

#987652
RE: Performance issue with tally solution

See what I mean? This works...
[font="Courier New"] SELECT [Name],LenCount,LenTally,REPLACE(definition,CHAR(13)+CHAR(10),CHAR(1))
   FROM (
         SELECT TOP(200) o.name, m.definition,
                (DATALENGTH(m.Definition)-DATALENGTH(REPLACE(m.Definition,(CHAR(13)+CHAR(10)),'')))/4 AS LenCount,
                (
                 SELECT COUNT(*) AS DoDah
                   FROM Tally
                  WHERE N <= LEN(definition)
                    AND SUBSTRING(definition,N,2) = CHAR(13)+CHAR(10)
                )AS LenTally
           FROM MASTER.sys.all_objects o
           JOIN MASTER.sys.all_sql_modules m ON o.OBJECT_ID = m.OBJECT_ID
          WHERE TYPE = 'P'
        )d
  WHERE LenCount  LenTally[/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)

May 1, 2009 at 8:04 pm

#987648
RE: Performance issue with tally solution

Phil Factor (5/1/2009)
I've just been running some tests on the original problem and hit a curiosity of the Replace function. (I was unsuccessfully trying to get a winning 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 1, 2009 at 7:58 pm

#987647
RE: Performance issue with tally solution

Paul White (5/1/2009)
Dear oh dear.
I have *just* realized the point of Lynn's solution!
It avoids the I/O on the custom tally table completely.
Not my finest moment.
Luckily it won't be available on...
--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 1, 2009 at 7:02 pm

#987643
RE: Sending Table to CLR stored procedure

I still want to know what the CLR procedure is going 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)

May 1, 2009 at 6:52 pm

#987642
RE: Performance issue with tally solution

Phil Factor (5/1/2009)
Jeff,
...but their developers seem unable to fix things so they work like they used to.
Ouch.
They're doing all they can to put in a better solution ASAP. The problem...
--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 1, 2009 at 12:03 pm

#987480
RE: Performance issue with tally solution

Florian Reischl (5/1/2009)
TheSQLGuru (5/1/2009)
Flo, are you getting your reads from SET STATISTICS IO ON? IIRC UDFs don't report IO correctly in this manner. Try getting them with Profiler.
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)

May 1, 2009 at 11:14 am

#987443
RE: Performance issue with tally solution

I'll take a look, Paul. 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 1, 2009 at 11:07 am

#987440
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

JohnG (5/1/2009)
If you do create a TALLY table, for whatever purpose, in Oracle my recommendations are:
1. Create it as an Indexed Organized Table (IOT) using the ORGANIZATION INDEX 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)

May 1, 2009 at 10:53 am

#987436
RE: Performance issue with tally solution

Almost forgot... the reason why I don't use the code windows anymore is because, although they may work fine with FireFox, they suck when using IE7. The remove blank...
--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 1, 2009 at 6:31 am

#987235
RE: Performance issue with tally solution

Florian Reischl (4/30/2009)
We had a performance issue in conjunction with STUFF? Maybe, I'm just gone blank... :doze:
Maybe it's just me and I'm thinking of the wrong person. It was...
--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 1, 2009 at 6:27 am

#987231
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

JohnG (4/30/2009)
Actually not. Don't worry about large IN lists in Oracle. I've had subqueries returning thousands - 10s of thousands of rows fed into an IN list. ...
--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)

April 30, 2009 at 5:05 pm

#987086
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

That would be the ANSI standard for UPDATE... subqueries for each field(s) being updated. If you do such a thing in SQL Server, the duration of the update increases...
--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)

April 30, 2009 at 2:59 pm

#986988
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Heh... I wonder if the ANSI committee like pork chops. 😉
--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)

April 30, 2009 at 1:20 pm

#986890

Viewing 15 posts - 43,966 through 43,980 (of 59,101 total)