Hi,
We recently launched a site on OpenBD, and have done some testing on the
database activity related to the client variable datastore.
One of the things we noticed is that some sql statements are causing high CPU
usage on our database server. In those sql statements, some of the parameters
are declared as nvarchar(4000)in the queries, even though the data type in the
actual database table is defined as varchar().
When the declared data type in the query is nvarchar, SQL server will
implicitly convert the data from varchar() to nvarchar() first, and then search
for any data that matches the input value, thereby significantly increasing I/O
and CPU usage.
We found that when the declared datatype is varchar instead, the logical reads
reduce dramatically, as well as I/O and CPU cost.
e.g. parameter: @P0 nvarchar(4000), column: CFID varchar(48)
Can these declared data types get changed from nvarchar(4000) to varchar(4000)
in the source code, or there is a reason the parameters need to be declared as
nvarchar(4000) in the query?
Thanks ...
Below are queries and test result for reference.
-- SQL Statements with high CPU usage.
1. (@P0 nvarchar(4000))SELECT CFDATA FROM BDGLOBAL WHERE CFID = @P0
2. (@P0 nvarchar(4000),@P1 nvarchar(4000))SELECT CFDATA FROM BDDATA WHERE CFID
= @P0 AND APP = @P1
3. (@P0 varchar(8000),@P1 datetime,@P2 nvarchar(4000))INSERT INTO BDGLOBAL
(CFDATA,LVISIT,CFID) VALUES (@P0,@P1,@P2)
4. (@P0 varchar(8000),@P1 datetime,@P2 nvarchar(4000))UPDATE BDGLOBAL SET
cfda...@p0, lvis...@p1 WHERE CFID = @P2
5. (@P0 varchar(8000),@P1 nvarchar(4000),@P2 nvarchar(4000))UPDATE BDDATA SET
cfda...@p0 WHERE CFID = @P1 AND APP = @P2
6. (@P0 varchar(8000),@P1 nvarchar(4000),@P2 nvarchar(4000))INSERT INTO BDDATA
(CFDATA,CFID,APP) VALUES (@P0,@P1,@P2)
-- Test result:
--- declare parameter @p0 / @p1 / @p2 as data type nvarchar(4000)
Table 'BDDATA'. Scan count 1, logical reads 3764, physical reads 0, read-ahead
reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
Table 'BDGLOBAL'. Scan count 1, logical reads 3496, physical reads 0,
read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead
reads 0.
2.76 IO COST
0.38 CPU COST
--- declare parameter @p0 / @p1 / @p2 as data type varchar(4000)
Table 'BDDATA'. Scan count 1, logical reads 3, physical reads 0, read-ahead
reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
Table 'BDGLOBAL'. Scan count 0, logical reads 3, physical reads 0, read-ahead
reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
0.003125 IO COST
0.0001581 CPU COST
--~--~---------~--~----~------------~-------~--~----~
Open BlueDragon Public Mailing List
http://groups.google.com/group/openbd?hl=en
official site @ http://www.openbluedragon.org/
!! save a network - trim replies before posting !!
-~----------~----~----~----~------~----~------~--~---
