I don't have a solution but a possible workaround would be to manually create the BDGLOBAL and BDDATA tables defining CFID to be of type NVARCHAR instead of VARCHAR so that it matches the type used in the prepared statements.
Paul -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Ladd Angelius Sent: Friday, November 13, 2009 10:55 AM To: [email protected] Subject: [OpenBD] performance issue with sql datatype for DB client variable store 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 !! -~----------~----~----~----~------~----~------~--~--- -- Open BlueDragon Public Mailing List http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon mailing list - http://groups.google.com/group/openbd?hl=en !! save a network - please trim replies before posting !!
