REPLACE Multiple Spaces with One
-
Not 100% convinced I should jump back into this thread now but I have been watching since the start and its certainly made an interesting read.
I just wanted to mention that the ||*9*9|| string that I suggested was just an example that any string could be used and that the length of it would add to the chance of it not occurring in the string being parsed. I guess it has served a purpose as an example for comparison against the 'bell' etc but it isnt precious or special in any way. I also got told that it broke Jeff's requirement on lengthening the string being worked on and accept that it was therefore not a relevant suggestion.
I will now return to lurking on the edges of the thread and continue being amazed at the efforts exhibited by C # Screw and everyone else.
Jonathan
PS
For what its worth I also have the automatic response that most people on SQL forums are somewhere to the West of the Atlantic and that I am the only UK based memeber here!! apologies to all members who are situated to the East of Greenwich.
-
Paul White (11/20/2009)
Other side of the Pacific, if you don't mind!
Paul
Depends whether you travel west or east. 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
How about using a C# version of the SQL REPLACE function?
If anyone has C# 'REPLACE' function it would be interesting to see if it is faster than the SQL Server Native REPLACE function.
I am sure the SQL REPLACE function is very good - it would just be interesting to compare to CLR.
Have a nice weekend : wish I was in NZ!:-)
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Inline results : oh I see insert a url to an image that works?
Edit: by the way the worst performer in the table below is the Recursive function : its just off the visible area at result No.1
Out of interest : note the slick CLR's are faster when there are allot of double spaces: I think this might be because not writing so much to the return string - and mainly looping quickly through the large chunks of spaces in the test data.:cool:
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Screw (11/20/2009)
Firstly : sorry Jeff : I spotted an error in earlier SQL, script was actually calling Recusive solution but displaying your name, that explains why you were in front of looping/recursive when no double spaces in the data.Heh... that's why I've been setting up my own tests (haven't had the time to complete them yet)... it looked like something was wrong because the method in the article should pretty much beat up on the While Loop and they were just too close to each other.
I haven't looked at all of your test results since you've made the quoted statement above to see if you've made a correction but it sure would be handy if a new set of tests were run using the correct code to see what really happens.
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)
Intro to Tally Tables and Functions -
Jeff Moden (11/20/2009)
C# Screw (11/20/2009)
Firstly : sorry Jeff : I spotted an error in earlier SQL, script was actually calling Recusive solution but displaying your name, that explains why you were in front of looping/recursive when no double spaces in the data.Heh... that's why I've been setting up my own tests (haven't had the time to complete them yet)... it looked like something was wrong because the method in the article should pretty much beat up on the While Loop and they were just too close to each other.
I haven't looked at all of your test results since you've made the quoted statement above to see if you've made a correction but it sure would be handy if a new set of tests were run using the correct code to see what really happens.
Thanks.
Hello Jeff
The results shown above are after I spotted the error, the error was only introduced after I created the recursive example.
When there are lots of double spaces in data then you are 1982ms, but SQL looping is 2663ms, so you are quite a bit faster!:cool:
When there are no double spaces in data then of course the story changes a bit - as results above.
[edit: Note: The reason of course sql looping is faster when data has no double spaces is because it will do just one CHARINDEX call then drop out of the function, whereas other solutions will execute nested REPLACE at least three times. So of course performance depends on whether you have very many double spaces in your data].
Here is the T-SQL so you can check all is ok, hope that helps..
DBCC FREEPROCCACHEgo
--PREPARE
SET NOCOUNT ON
go
CREATE FUNCTION dbo.fn_CleanUp_Recursion_Brigzy(@Data varchar(max))
RETURNS VarChar(Max)
AS
BEGIN
IF CHARINDEX(' ',@Data) > 0
BEGIN
SET @Data = REPLACE(@Data,' ',' ')
IF CHARINDEX(' ',@Data) > 0
SELECT @Data = dbo.fn_CleanUp_Recursion_Brigzy(@Data)
END
RETURN @Data
END
GO
go
CREATE FUNCTION dbo.fn_CleanUp_Brigzy(@S VARCHAR(max))
RETURNS VARCHAR(max)
BEGIN
WHILE CHARINDEX(' ',@S) > 0
SELECT @S = REPLACE(@S,' ',' ')
RETURN @S
END
go
CREATE FUNCTION dbo.fn_CleanUp_JeffOriginal(@S VARCHAR(max))
RETURNS VARCHAR(max)
BEGIN
RETURN REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(@S)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') --Changes the remaining X's to nothing
END
go
CREATE FUNCTION dbo.fn_CleanUp_MultiChars(@S VARCHAR(max))
RETURNS VARCHAR(max)
BEGIN
RETURN LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(@S,' ',' ||*9*9||'),'||*9*9|| ',''),'||*9*9||','')
))
END
GO
CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
END
go
CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKIII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
END
go
CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKIV(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(REPLACE(@s,CHAR(9),' '))),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ')
END
go
CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_Original(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
--declare @S varchar(8000)
--set @S = '*' + replicate(' ',7998) + '*'
--select len(@s)
set @S = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')
--select len(@s)
RETURN @S
END
CREATE TABLE #TEMP1 (COL1 VARCHAR(900))
CREATE TABLE #TEMP2 (COL2 VARCHAR(900), COL3 VARCHAR(900), COL4 VARCHAR(900),COL5 VARCHAR(900))
go
--INSERT 200k ROWS WITH RANDOM SPACES ON EACH TABLE, SEPARATE TABLES ARE USED TO AVOID CACHING, THIS MAY TAKE QUITE AWHILE
--DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT
--SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)
--INSERT INTO #TEMP1 (COL1)
--OUTPUT inserted.COL1 INTO #TEMP2 (COL2)
--SELECT ' '+REPLICATE('X',@SPACECOUNT1)+' '+REPLICATE('X',@SPACECOUNT2)+' '
--GO 10000
DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT
SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)
INSERT INTO #TEMP1 (COL1)
OUTPUT inserted.COL1 INTO #TEMP2 (COL2)
SELECT 'TEST1'+SPACE(@SPACECOUNT1)+'TEST2'+SPACE(@SPACECOUNT2)+'TEST3'
GO 10000
-- Test result table
CREATE TABLE #tResults(Tag VARCHAR(100),Result int)
go
-- Test 1 : Jeff technique : multi chars for replace char
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_MultiChars(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('SQL function: Replace Technique : but using replacement chars ||*9*9||',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_Recursion_Brigzy(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('SQL function: C#Screw : Recursive',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_JeffOriginal(Col2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Jeff Original : single bell char',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_Brigzy(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function :Brigzy (C#Screw) Looping',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_Original(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth Original',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_MKIII(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth III - extra replace',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_Original(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth : with hard coded spaces',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SELECT @StartTime= GETDATE()
UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_MKIV(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth : with hard coded spaces and TAB support',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
-- Edit 20/11 15:59 : removed _X functions as they not included in results above
-- they will be used to test C# alternative to REPLACE function
--DECLARE @StartTime DATETIME
--SET @StartTime=GETDATE()
--UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_Looping_CLR(COL2)
--FROM #TEMP2
--INSERT INTO #tResults(Tag,Result)VALUES('CLR: Brigzy (C#Screw) Looping',DATEDIFF(ms,@StartTime,GETDATE()))
--go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_NotLooping_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('CLR: Not looping using ||*9*9|| technique in C#',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_SingleChar_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: using Jeff''s single char technique in C#',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_JCB_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('CLR :using JCBnew space replacer C#',DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_PW_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES('CLR: using Paul White space replacer C#' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_FLO_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: using Flo s space replacer C#' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_MMSharpScrew_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Michael Meierruth - C#Screw conversion: hard coded spaces' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_MMSharpScrew_ExtraReplace_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Michael Meierruth - C#Screw conversion: with extra Replace' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_SharpScrewII_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: C#Screw II - Spaces and TABS' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_FLO_SpaceAndTab_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Flo - Spaces and TABS - v.compact' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_and_TABS_SharpScrew_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: C#Screw : Slightly reduced Paul White''s function and Added Support TABS' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_PWSpaceReplacerUnsafe_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Paul White''s function with Pointers by Mark-101232' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_and_TABS_SharpScrew_WithPointers_CLR(COL2)
FROM #TEMP2
INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Paul White''s function, reduced by C#Screw with Pointers ideas from Mark-101232' ,DATEDIFF(ms,@StartTime,GETDATE()))
go 10
SELECT Tag + CHAR(13) + CAST(FLOOR(AVG(Result)) AS VARCHAR(20)) FROM #tResults GROUP BY
Tag ORDER BY SUM(Result) DESC
--CLEANUP
DROP FUNCTION dbo.fn_CleanUp_Brigzy
DROP FUNCTION fn_CleanUp_JeffOriginal
DROP FUNCTION fn_CleanUp_MultiChars
DROP FUNCTION fn_CleanUp_MichaelMeierruth_Original
DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKII
DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKIII
DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKIV
DROP FUNCTION fn_CleanUp_Recursion_Brigzy
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #tResults
GO
--SET STATISTICS TIME OFF
SELECT --query = QT.[text],
SUBSTRING(QT.[text],CHARINDEX('VALUES',text)+8,(CHARINDEX('DATEDIFF(',text)-CHARINDEX('VALUES',text))-8),
--execution_plan = QP.query_plan,
run_count = QS.execution_count,
--total_cpu_time_ms = QS.total_worker_time/1000,
--total_logical_reads = QS.total_logical_reads,
--total_elapsed_time_ms = QS.total_elapsed_time/1000,
avg_cpu_time_ms = (QS.total_worker_time / QS.execution_count)/1000,
avg_logical_reads = QS.total_logical_reads / QS.execution_count,
avg_elapsed_time_ms = (QS.total_elapsed_time / QS.execution_count)/1000
FROM sys.dm_exec_query_stats QS
CROSS
APPLY sys.dm_exec_sql_text (QS.[sql_handle]) QT
CROSS
APPLY sys.dm_exec_query_plan (QS.[plan_handle]) QP
WHERE QT.[text] LIKE '%INSERT INTO #tResults%'
AND QT.[text] NOT LIKE '%dm_exec_query_stats%'
ORDER BY
--QS.last_execution_time ASC;
QS.total_elapsed_time / QS.execution_count DESC
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Screw (11/20/2009)
I think DBA folk are interested in CPU time to see if the server is being strained? but that could be seen by looking at CPU Percentage utilisation too maybe.
Depends on each DBA's situation. CPU Percentage utilization on a server in use by many users and processes doesn't tell you which process did what.
For servers that are CPU constrained (i.e. CPU percentage is very high much of the time, while disk, memory, and network measures are not), CPU time is very important.
For servers that are not CPU constrained (quite often the disk or network or memory measures are very high, while CPU is not), CPU time may be more or less irrelevant unless it's obscene, while Reads and Writes (IO measures) would be absolutely critical.
For either, Duration may be important for something that happens with realtime users... or it might be largely irrelevant unless truly excessive for nightly reporting jobs, large bulk imports/exports (gigabyte or more text files), and so on.
The new DMV based statistics output is nice; can number of writes be pulled from there as well? If so, I'll have to start using it after some databases are upgraded from 2000.
-
DMV
sql_handle varbinary(64)
statement_start_offset int
statement_end_offset int
plan_generation_num bigint
plan_handle varbinary(64)
creation_time datetime
last_execution_time datetime
execution_count bigint
total_worker_time bigint
last_worker_time bigint
min_worker_time bigint
max_worker_time bigint
total_physical_reads bigint
last_physical_reads bigint
min_physical_reads bigint
max_physical_reads bigint
total_logical_writes bigint
last_logical_writes bigint
min_logical_writes bigint
max_logical_writes bigint
total_logical_reads bigint
last_logical_reads bigint
min_logical_reads bigint
max_logical_reads bigint
total_clr_time bigint
last_clr_time bigint
min_clr_time bigint
max_clr_time bigint
total_elapsed_time bigint
last_elapsed_time bigint
min_elapsed_time bigint
max_elapsed_time bigint
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Can anyone comfirm if CPU, IO, etc information is accurately tracked and reported via the referenced DMVs when CLR stuff is being executed??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (11/20/2009)
Can anyone comfirm if CPU, IO, etc information is accurately tracked and reported via the referenced DMVs when CLR stuff is being executed??Yes. http://www.docstoc.com/docs/15431250/SQL-Server-2005-Under-the-Hood_-How-We-Host-the-CLR contains the architectural details. See http://blogs.msdn.com/sqlprogrammability/archive/2006/06/03/615743.aspx for details of how some of the DMVs were extended to provide extra CLR info. sys.dm_exec_query_stats in particular has 'total_clr_time', 'last_clr_time', 'min_clr_time' and 'max_clr_time'. To be clear, clr_time is a component of worker_time.
Separately, in answer to someone else's question of why I'm so keen to measure worker time (scheduled CPU time):
Mostly it's because it's a better general performance metric than, say, logical IO. See http://sqlblog.com/blogs/joe_chang/archive/2008/09/10/why-logical-io-is-a-poor-performance-metric.aspx. Also http://www.qdpma.com/SQLServerCostBasedOptimizer.html. The second one is very deep, but I fully recommend it to anyone who is serious about understanding this stuff!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
My thanks to those who have voted for the Connect item about the collation performance thing. I'll post the response here as soon as I hear anything </end-plug>
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Nadrek (11/20/2009)
C# Screw (11/20/2009)
I think DBA folk are interested in CPU time to see if the server is being strained? but that could be seen by looking at CPU Percentage utilisation too maybe.
Depends on each DBA's situation. CPU Percentage utilization on a server in use by many users and processes doesn't tell you which process did what.
For servers that are CPU constrained (i.e. CPU percentage is very high much of the time, while disk, memory, and network measures are not), CPU time is very important.
For servers that are not CPU constrained (quite often the disk or network or memory measures are very high, while CPU is not), CPU time may be more or less irrelevant unless it's obscene, while Reads and Writes (IO measures) would be absolutely critical.
For either, Duration may be important for something that happens with realtime users... or it might be largely irrelevant unless truly excessive for nightly reporting jobs, large bulk imports/exports (gigabyte or more text files), and so on.
The new DMV based statistics output is nice; can number of writes be pulled from there as well? If so, I'll have to start using it after some databases are upgraded from 2000.
I believe that both disk I/O and time to transport to display (and any wait times the two may have) are also included in duration... as both a DBA and a Developer, I'm always interested in duration as well as CPU and the difference in time between the two. It makes for a very good "health check".
--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)
Intro to Tally Tables and Functions -
Jeff Moden (11/20/2009)
I believe that both disk I/O and time to transport to display (and any wait times the two may have) are also included in duration... as both a DBA and a Developer, I'm always interested in duration as well as CPU and the difference in time between the two. It makes for a very good "health check".I've seen quite a few stored procedure/queries/query sets where different implementations can both dramatically increase the number of reads and decrease the duration, simultaneously, on development systems, test systems, and even the production system under periods of lighter load.
In some cases, what's good for that one query (decreased duration) is very bad for the system as a whole (increased disk I/O on a system that is very seriously disk I/O constrained during periods of load). It's the difference between tuning just one query or stored procedure during happy times, and tuning the entire system for the best case within the set of worst case performance possibilities. If your "worst case" shows up commonly, this can be very important. If it doesn't, then keep an eye on things over the years.
-
My point exactly.
--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)
Intro to Tally Tables and Functions -
Morning All,
Earlier in the test we moved all the SQL into functions, in an attempt to make fair comparison.
Doing so we noticed Jeffs SQL speeded up and we briefly commented on this in the thread.
However I want to just step back and ask people to try and explain this change in performance between function and inline SQL.
This is really interesting now that we have the logical reads information (many thanks to Paul)
Please can anyone/everyone comment on this as there is something fundamental here that I don't understand!
Cheers
Screw!
Edit: Added SQL for you to check
DECLARE @StartTime DATETIMESET @StartTime=GETDATE()
UPDATE #TEMP2 SET COL5= REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(col2)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'') --Changes the remaining X's to nothing
FROM #TEMP2
INSERT INTO #tResults(Tag,Result,StartTime,EndTime)VALUES ('SQL: Jeff INLINE SQL (not an SQL function)' ,DATEDIFF(ms,@StartTime,GETDATE()),@StartTime,GETDATE())
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015
Viewing 15 posts - 211 through 225 (of 425 total)
You must be logged in to reply to this topic. Login to reply