April 23, 2009 at 2:15 am
My statement is as followed:
I have a table with 2 million records and do a query with the SQL
"select * from TBL where fld_1=@value"
(fld_1 is the primary key and defined as char(8)).
1)when @value is N'10000009', it takes about 0.5 second.
2)when @value is '10000009',it takes about 0.1 second.
3)when @value is string which ends with 9 and length is 8 like N'10000019', N'10000129',it takes about 0.5 second.
4)when @value is string which ends with 9 and length is less than 8,such as N'10000008',it takes about 0.1 second.
5)when @value is string which does not end with 9, just like N'10000001', N'10000003', N'100015'.
,it takes 0.1 second.
6)when fld_1 is defined as nchar(8) and @value is N'10000009',it takes about 0.1 second.
7)when all of the indexes(the default primary index also included) are dropped, it takes the same time whatever the @value is.
In my project a complexible SQL is used and the difference described above is expanded about 30 times.
Does anyone know the special '9'?
April 23, 2009 at 2:53 am
Anytime that you use a unicode constant, the query will be slower. That's because of implicit conversion that's required on the column which prevents any index seeks.
None of the values you've mentioned are unicode so why are you making them unicode?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 3:14 am
It was because of the coding fault at the beginning
and now I want to know why this phenomenon happened.
April 24, 2009 at 7:24 am
It sounds like what you're saying is that the somewhat unusual performance differences are dependent on the indexes. If that's the case, we would probably need to know the table structure (CREATE statement) and the CREATE INDEX statements.
BTW, I like your word "complexible".. sort of a concatenation of "complex" and "flexible"..
April 24, 2009 at 7:32 am
johny (4/23/2009)
and now I want to know why this phenomenon happened.
As I said, if the column is varchar and the string literal is NVarchar (as in your slow examples), the column will be implicitly converted to nvarchar. That implicit conversion makes it impossible for SQL to use an index seek to satisfy the query, it has to scan. A scan will (usually) take longer than a seek.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 7:50 am
There can also be a difference depending on the number of rows that fit the criteria you selected, since returning the data is part of the excution time. Might that be part of it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2009 at 8:00 pm
To tung:
The table is created as follows:
CREATE TABLE T_JUMIN_B_TBL (
MANAGEMENT_NO CHAR(8) NOT NULL,
PERSONAL_NO VARCHAR(8) NOT NULL,
FAMILY_NAME NVARCHAR(50) NULL,
FAMILY_NAME_Y NVARCHAR(50) NULL,
...
...
MODDATE DATETIME NULL) ON 'KFS_DATA'
GO
ALTER TABLE T_JUMIN_B_TBL
ADD CONSTRAINT T_JUMIN_B_TBL_PK PRIMARY KEY CLUSTERED (MANAGEMENT_NO)
April 26, 2009 at 8:16 pm
To GilaMonster:
It should take a same time for all the records when using NVARCHAR.
But now the records ending with '9' take much more time than the non-9 records.
This is the point that puzzled me:(
To GSquared:
The number of rows selected is 1 each time.
April 27, 2009 at 1:35 am
johny (4/26/2009)
To GilaMonster:It should take a same time for all the records when using NVARCHAR.
But now the records ending with '9' take much more time than the non-9 records.
This is the point that puzzled me:(
Are the execution plans different? Can you post them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 8:27 pm
I am now in Japan and the SQLServer's language is Japanese and my english is not good enough to translate the execution plans exactly.
You can create a table to do the test using script below and examine the results in person if possible. The attachment is the explain plan in my computer. It is just a reference. Thank you.
===========================================================
CREATE TABLE T_TEST_9 (
ID CHAR(8) NOT NULL,
MODDATE DATETIME NULL)
GO
ALTER TABLE T_TEST_9
ADD CONSTRAINT T_TEST_9_PK PRIMARY KEY CLUSTERED (ID)
Go
DECLARE @col_value AS CHAR(8)
DECLARE @int_value AS INT
SET @int_value = 10000001
WHILE @int_value < 13000000 --the count of records
BEGIN
set @col_value = convert(CHAR(8),@int_value)
INSERT INTO T_TEST_9
(ID)
VALUES
(@col_value)
set @int_value = @int_value + 1
END
GO
============================================================
" select * from T_TEST_9 where ID=N'10000009' "
takes more time than
" select * from T_TEST_9 where ID=N'10000008' "
April 28, 2009 at 12:49 am
In addition,the records ending with 'z' also take more time than other records.
April 28, 2009 at 2:35 am
Any chance you can post the plans as .sqlplan files (zipped)? The xml should be the same across all languages so, if I load it into my management studio, I'll get it in English
I ran the tests that you gave. (SQL 2008 RTM Developer Edition, x64)
select * from T_TEST_9 where ID=N'10000009'
Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 667 ms.
select * from T_TEST_9 where ID=N'10000008'
Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 668 ms.
select * from T_TEST_9 where ID=N'1000000z'
Table 'T_TEST_9'. Scan count 1, logical reads 9333, physical reads 0
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 666 ms.
I don't see any difference in execution time. I ran them multiple times and the execution times are very, very close each time.
Can you confirm that, when run multiple times, the second is slower than the first. If you swap the order of the two, is the one that ends with 9 still slower?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 3:24 am
Yes. The records that end with '9' take more time every time.
And the attachment is the execution plans(.sqlplan).
Thanks.
April 28, 2009 at 7:08 am
johny (4/28/2009)
Yes. The records that end with '9' take more time every time.
Can you post output of statistics IO and Statistics time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply