June 17, 2012 at 1:39 pm
hi
i am getting my result like this
column1
p = 30
q = 50
r = 70
now i need to divided this like this
column1 column2
p 30
q 50
r 70
thanks
June 17, 2012 at 3:35 pm
I have no doubt that the problem is completely understood by yourself, having the table and its contents available to view. But we do not have that knowledge, so in order to help you, you have to help us.
Please post the table definition(s), some sample data and of course your desired results.
To do this quickly and easily, please click on the first link in my signature block. The article that will be displayed contains the T-SQL code, and instructions as to how to utilize it in a quick easy fashion to supply the table definition(s), sample data and desired results.. This will in turn help those who wish to help you.
June 17, 2012 at 6:38 pm
On the other hand, perhaps the solution is as simple as this:
DECLARE @t TABLE (column1 VARCHAR(20))
INSERT INTO @t
SELECT 'p = 30'
UNION ALL SELECT 'q = 50'
UNION ALL SELECT 'r = 70'
SELECT column1=PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 2)
,column2=PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 1)
FROM @t
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 17, 2012 at 6:45 pm
alright,consider this
i have 1 variable
declare @temp1 nvarchar(50)
set @temp1 = 'P=70'
now in 1 column i need p and in another i need 70 ,from @temp1
so i need function who can take p and 70 from @temp1
column1 column2
p 70
i hope you get it
June 18, 2012 at 12:22 am
harri.reddy (6/17/2012)
alright,consider thisi have 1 variable
declare @temp1 nvarchar(50)
set @temp1 = 'P=70'
now in 1 column i need p and in another i need 70 ,from @temp1
so i need function who can take p and 70 from @temp1
column1 column2
p 70
i hope you get it
Consider Dwain's solution. It will do what you want.
I did the same. But, then I saw that Dwain had posted it already. Good job Dwain 🙂
June 18, 2012 at 12:27 am
vinu512 (6/18/2012)
harri.reddy (6/17/2012)
alright,consider thisi have 1 variable
declare @temp1 nvarchar(50)
set @temp1 = 'P=70'
now in 1 column i need p and in another i need 70 ,from @temp1
so i need function who can take p and 70 from @temp1
column1 column2
p 70
i hope you get it
Consider Dwain's solution. It will do what you want.
I did the same. But, then I saw that Dwain had posted it already. Good job Dwain 🙂
Great minds think alike! 🙂
Thanks to Lowell for turning me onto the PARSENAME function. Very useful for cases like this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 12:39 am
dwain.c (6/18/2012)
vinu512 (6/18/2012)
harri.reddy (6/17/2012)
alright,consider thisi have 1 variable
declare @temp1 nvarchar(50)
set @temp1 = 'P=70'
now in 1 column i need p and in another i need 70 ,from @temp1
so i need function who can take p and 70 from @temp1
column1 column2
p 70
i hope you get it
Consider Dwain's solution. It will do what you want.
I did the same. But, then I saw that Dwain had posted it already. Good job Dwain 🙂
Great minds think alike! 🙂
Thanks to Lowell for turning me onto the PARSENAME function. Very useful for cases like this.
Great minds learn alike as well, I guess 😀
It was Lowell's post which taught me the usefulness of PARSENAME as well. Its pretty Hnady.
June 18, 2012 at 6:17 am
dwain.c (6/17/2012)
On the other hand, perhaps the solution is as simple as this:
DECLARE @t TABLE (column1 VARCHAR(20))
INSERT INTO @t
SELECT 'p = 30'
UNION ALL SELECT 'q = 50'
UNION ALL SELECT 'r = 70'
SELECT column1=PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 2)
,column2=PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 1)
FROM @t
Is this actually any better than doing a more traditional SUBSTRING/CHARINDEX operation? I don't think it is, so to check I set up a little performance test.
Here's 1,000,000 rows of sample data: -
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
chr + ' = ' + CAST(nmbr AS VARCHAR(3)) AS column1
INTO #testEnvironment
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS chr
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),
(17),(18),(19),(20),(21),(22),(23),
(24),(25),(26))a(N)
) a
CROSS JOIN (SELECT (ABS(CHECKSUM(NEWID())) % 100) + 1 AS nmbr
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
)b;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
OK, now let's have a look at the performance 😀
DECLARE @HOLDER VARCHAR(20), @HOLDER2 VARCHAR(20);
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = RTRIM(SUBSTRING(column1,1,CHARINDEX('=',column1)-1)),
@HOLDER2 = LTRIM(SUBSTRING(column1,CHARINDEX('=',column1)+1,LEN(column1)))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 2),
@HOLDER2 = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Results?
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== BASELINE ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 16, read-ahead reads 2472, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 586 ms.
================================================================================
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 1, read-ahead reads 2472, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 877 ms.
================================================================================
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== PARSENAME / REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 3, read-ahead reads 2472, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1984 ms, elapsed time = 2093 ms.
================================================================================
So the more traditional answer is considerably better (over 2 times faster).
Anyway, next, we'll try it without clearing the cache inbetween each query just to see how big or small a difference it makes.
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 2),
@HOLDER2 = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = RTRIM(SUBSTRING(column1,1,CHARINDEX('=',column1)-1)),
@HOLDER2 = LTRIM(SUBSTRING(column1,CHARINDEX('=',column1)+1,LEN(column1)))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Results?
========== BASELINE ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 70 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1922 ms, elapsed time = 1926 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 3094, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 655 ms.
================================================================================
So the traditional method is ~3 times faster.
The thing that kills the performance is the nested REPLACE. "Proof!" I hear you cry. Well, let's change the data a little and get rid of one of the REPLACE functions.
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
chr + '=' + CAST(nmbr AS VARCHAR(3)) AS column1
INTO #testEnvironment
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS chr
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),
(17),(18),(19),(20),(21),(22),(23),
(24),(25),(26))a(N)
) a
CROSS JOIN (SELECT (ABS(CHECKSUM(NEWID())) % 100) + 1 AS nmbr
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
)b;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
DECLARE @HOLDER VARCHAR(20), @HOLDER2 VARCHAR(20);
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(REPLACE(column1, '=', '.'), 2),
@HOLDER2 = PARSENAME(REPLACE(column1, '=', '.'), 1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = RTRIM(SUBSTRING(column1,1,CHARINDEX('=',column1)-1)),
@HOLDER2 = LTRIM(SUBSTRING(column1,CHARINDEX('=',column1)+1,LEN(column1)))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Results?
========== BASELINE ==========
Table '#testEnvironment'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 73 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1235 ms, elapsed time = 1223 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 608 ms.
================================================================================
So we've knocked about one third of the time off by getting rid of one of the REPLACE functions. What happens if we change the data again and get rid of the final one?
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
chr + '.' + CAST(nmbr AS VARCHAR(3)) AS column1
INTO #testEnvironment
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS chr
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),
(17),(18),(19),(20),(21),(22),(23),
(24),(25),(26))a(N)
) a
CROSS JOIN (SELECT (ABS(CHECKSUM(NEWID())) % 100) + 1 AS nmbr
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
)b;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
DECLARE @HOLDER VARCHAR(20), @HOLDER2 VARCHAR(20);
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(column1, 2),
@HOLDER2 = PARSENAME(column1, 1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = RTRIM(SUBSTRING(column1,1,CHARINDEX('.',column1)-1)),
@HOLDER2 = LTRIM(SUBSTRING(column1,CHARINDEX('.',column1)+1,LEN(column1)))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Results?
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000011BF'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 72 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000011BF'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 652 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000011BF'. Scan count 1, logical reads 2845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 617 ms.
================================================================================
Finally, the queries are pretty much equivalent.
June 18, 2012 at 6:17 pm
Everybody's a critic! 🙂
Seriously Cadavre, you make a good point. I just learned the PARSENAME technique and never really tried a performance test on it.
However I do recall a post by Paul White reporting a bug in REPLACE that cost seriously in performance. He had a technique to get around it. Now if I can just find that and try it, maybe we'll see if there's a way to improve it somewhat.
In the immortal words of the Terminator - "I'll be back!" 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 8:19 pm
Paul's trick was to change the collation on the field where REPLACE is to be run to a binary collation, specifically:
COLLATE Latin1_General_BIN2
You can read about it here:
http://www.sqlservercentral.com/Forums/Topic1304299-391-2.aspx#bm1304646
Either I'm not doing it right or it didn't help in this case.
I told you I'd be back.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 3:31 am
Here's the results applying the COLLATION fix to the REPLACE in PARSENAME. It still doesn't touch SUBSTRING / CHARINDEX though (just a slight improvement).
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 2),
@HOLDER2 = PARSENAME(REPLACE(REPLACE(column1, ' ', ''), '=', '.'), 1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE (WITH COLLATION) ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(str1, 2),
@HOLDER2 = PARSENAME(str1, 1)
FROM (
SELECT str1=REPLACE(REPLACE(column1 COLLATE Latin1_General_BIN2, ' ', ''), '=', '.')
FROM #TestEnvironment) x;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = RTRIM(SUBSTRING(column1,1,CHARINDEX('=',column1)-1)),
@HOLDER2 = LTRIM(SUBSTRING(column1,CHARINDEX('=',column1)+1,LEN(column1)))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Results:
========== BASELINE ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000000B8'. Scan count 1, logical reads 3095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 66 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000000B8'. Scan count 1, logical reads 3095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2043 ms, elapsed time = 2046 ms.
================================================================================
========== PARSENAME / REPLACE (WITH COLLATION) ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000000B8'. Scan count 1, logical reads 3095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1888 ms, elapsed time = 1916 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment____________________________________________________________________________________________________0000000000B8'. Scan count 1, logical reads 3095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 804 ms.
================================================================================
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply