Jeff Moden

Forum Replies Created

Viewing 15 posts - 39,556 through 39,570 (of 59,098 total)

RE: CLR, to be or not to be, that is the question!

Roust_m (3/2/2010)
Another issue we have with this function is that it fails after few days without SQL instance restart. When we restart it it works until SQL Server uses...
--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)

March 3, 2010 at 8:37 pm

#1128217
RE: CLR, to be or not to be, that is the question!

Ah... one final note. If you can live within the bounds of VARBINARY(8000) and change the input parameter datatype from VARCHAR(MAX) to VARCHAR(8000), then you'll get roughly 3 times...
--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)

March 3, 2010 at 8:34 pm

#1128216
RE: CLR, to be or not to be, that is the question!

Relatively short test (15k conversions of NEWID()) on my box came in at about 5 seconds on an 8 year old p4 1.8GHz... should do a lot better on 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)

March 3, 2010 at 8:30 pm

#1128215
RE: CLR, to be or not to be, that is the question!

I forgot to mention that if you don't know what a Tally table is (CTE in this case) or how it works to replace certain While Loops, please read 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)

March 3, 2010 at 8:19 pm

#1128213
RE: CLR, to be or not to be, that is the question!

Never mind... decided to make a "direct" replacement so you don't necessarily have to change code. Read the comment in the code header below...
CREATE FUNCTION dbo.fn_varbintohexstr
/*****************************************************************************************
Purpose:
...
--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)

March 3, 2010 at 8:17 pm

#1128211
RE: CLR, to be or not to be, that is the question!

Roust_m (3/1/2010)
Hi,
A developer approached me in regards for sys.fn_varbintohexstr function they are using at the moment saying it is slow in production: 60 seconds for 15k records (though fast...
--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)

March 3, 2010 at 7:33 pm

#1128204
RE: Avoid dynamic SQL

Paul White (3/2/2010)
Performance results for that (huge) thread were summarized very nicely by Florian Reischl on his blog:
http://florianreischl.blogspot.com/2009_09_01_archive.html
It covers all the main methods, including WHILE loops, Tally tables, XML, and...
--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)

March 3, 2010 at 6:58 am

#1127738
RE: Split one field into multiple using a delimiter

Heh... you really really don't won't to use even well written While Loops for this though.
--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)

March 3, 2010 at 6:28 am

#1127711
RE: which query's exec. costs are better/more efficient

lmu92 (3/1/2010)
Jeff Moden (3/1/2010)
...
I know you know this, Lutz... just saying this in case anyone thinks otherwise....
Don't rely on the execution plan for differenences in performance either. Sure,...
--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)

March 3, 2010 at 6:12 am

#1127697
RE: Best code for generating sequence numbers

mstjean (3/2/2010)
...uhoh...
Jeff Moden (3/1/2010)
...It would be diffcult to reserve a range of ID's with because IDENTITIES are not guaranteed to be sequential if other people are also inserting into 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)

March 3, 2010 at 5:56 am

#1127681
RE: Split one field into multiple using a delimiter

Mohamed Asane (3/3/2010)
it may one of solution:
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h',@delimiter = ','
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
SELECT...
--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)

March 3, 2010 at 5:50 am

#1127675
RE: number the Group on data from two columns duplicated

Jeff Moden (1/15/2010)
shaini.binu (1/15/2010)
Hi,
I have solved the puzzle with the help of cursurs, Thanks for all the help,
Thanks
Sh.
Two way street here, shaini... please post...
--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)

March 2, 2010 at 6:28 pm

#1127483
RE: Calculating interest query

The way I believe they do it in the states is with the interest rate out to "X" places as you say but the principle is rounded to the nearest...
--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)

March 2, 2010 at 6:26 pm

#1127482
RE: Passing a dateTime2 to a CLR Stored Procedure

What is the CLR for? I mean, what is it supposed 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)

March 2, 2010 at 2:37 pm

#1127434
RE: Avoid dynamic SQL

Rob Schripsema (3/2/2010)
I do know that SSRS passes field(parameter?) lists as a string of comma-separated values. That doesn't require dynamic SQL or even a cursor/while loop to split it...
--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)

March 2, 2010 at 2:29 pm

#1127429

Viewing 15 posts - 39,556 through 39,570 (of 59,098 total)