Just following up on this ... Wow, no responses ;-) Is there anything I can do with this one? Am I on the wrong list? Should I report it as a bug?
Thanks On Nov 13, 10:54 am, "Ladd Angelius" <[email protected]> wrote: > 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://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 !!
