October 19, 2011 at 11:40 am
ok, the title is a little misleading. Here's the question though. I have a While loop to remove most ASCII characters that would cause XML in sql to fail (i.e. sub code 32). What I'm trying to do is replace it with a Tally based query. I have a Tally Table with 100,000 entries created for another process.
Question: Why does this query take 163 reads, 31 cpu, and 17 ms but the next query takes only 2 reads and 1 ms? The only difference is the WHERE clause checking for t.Number > 0
Query 1 - poor performance
DECLARE @Text AS VARCHAR(MAX)
SET @Text = 'ASDFKLASDFJLKASJDFLKASDFJLASKDFJ'
SELECT
@Text = REPLACE(@Text, CHAR(t.Number - 1), '')
FROM
dbo.Tally t
WHERE
t.Number < 33 AND
--t.Number > 0 AND
t.Number <> 10 AND
t.Number <> 11 AND
t.Number <> 14
SELECT @Text
Query 2 - good performance
DECLARE @Text AS VARCHAR(MAX)
SET @Text = 'ASDFKLASDFJLKASJDFLKASDFJLASKDFJ'
SELECT
@Text = REPLACE(@Text, CHAR(t.Number - 1), '')
FROM
dbo.Tally t
WHERE
t.Number < 33 AND
t.Number > 0 AND
t.Number <> 10 AND
t.Number <> 11 AND
t.Number <> 14
SELECT @Text
While Loop Code - trying to replace:
DECLARE @Text AS VARCHAR(MAX)
SET @Text = 'ASDFKLASDFJLKASJDFLKASDFJLASKDFJ'
DECLARE @ASCIICode AS INT
SET @ASCIICode = 0
WHILE @ASCIICode < 32 BEGIN
SELECT
@Text = CASE WHEN @ASCIICode = 9 THEN @Text --Handle vbTab
WHEN @ASCIICode = 10 THEN @Text --Handle vbLf
WHEN @ASCIICode = 13 THEN @Text --Handle vbCr
WHEN @ASCIICode > 31 THEN @Text --Ignore > 31
ELSE REPLACE(@Text, CHAR(@ASCIICode), '') END,
@ASCIICode = @ASCIICode + 1
END
SELECT @Text
Tally Table
CREATE TABLE [dbo].[Tally](
[Number] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [IX_Tally_C_Number] PRIMARY KEY CLUSTERED
(
[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
October 19, 2011 at 11:44 am
Hard to tell without the actual execution plan.
My guess is that the server has a better guess at the amount of rows returned and choose a different plan.
October 19, 2011 at 11:47 am
Sometimes that little bit of extra info is enough to tell the optimizer a better plan to choose. That appears to be the case here. Posting the execution plans for both would be helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 19, 2011 at 11:50 am
Here's the Query plan for both... it shows both batches.
October 19, 2011 at 11:57 am
venoym (10/19/2011)
Here's the Query plan for both... it shows both batches.
As I said... different plan.
#1 scans, #2 seeks.
October 19, 2011 at 3:26 pm
Strange enough, both query 1 en query 2 do not terminate on my machine. Looks like it has something to do with replacing CHAR(0) in combination with the collation. If I change 't.Number > 0' to 't.Number > 1' in query 2, the query terminates. The following query does not terminate on my system (server/database collation Latin1_General_CI_AS):
DECLARE @Text AS VARCHAR(MAX)
SET @Text = 'ASDFKLASDFJLKASJDFLKASDFJLASKDFJ'
SELECT
@Text = REPLACE(@Text, CHAR(0), '')
However, the query terminates when using a binary collation:
DECLARE @Text AS VARCHAR(MAX)
SET @Text = 'ASDFKLASDFJLKASJDFLKASDFJLASKDFJ'
SELECT
@Text = REPLACE(@Text, CHAR(0) COLLATE Latin1_General_BIN, '')
Not so long ago, there was a thread in which the same problem was discussed. As far as I remember, there was a reasonable but rather surprising explanation for the problem. Unfortunately, I can't find this thread anymore.
Peter
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply