Steffi,
FYI: unicode data takes up twice as much space as non-unicode, a
uni-code character uses 2 bytes, to allow for a greater character set.
BUT char uses a fixed amount of space.
So in very simplistic terms; "abc" in unicode (nvarchar) would take up
6 bytes, in non-unicode (varchar) it will take 3 bytes, but if you use
char(4000), it will take 4000 bytes.
But the slowdown is probably caused by it having to convert data to
unicode.
I wasn't aware that RS did use unicode by default, but I maybe wrong!
Chris
[quoted text, click to view] Steffi wrote:
> Hi,
> Is there a difference between
> exec sp_executesql N'SELECT a.pt_orgn,a.pt_ogdesc, a.pt_grop,
> a.pt_dept, a.pt_pros,b.cs_orgn, b.cs_orgn_x, b.cs_ssym,
> b.cs_dept, b.cs_pros FROM vRsPros a LEFT OUTER JOIN vRs22130101A b
> ON a.pt_orgn = b.cs_orgn AND a.pt_dept = b.cs_dept AND a.pt_pros
> = b.cs_pros AND
> b.cs_orgn = @orgn AND (b.cs_ssym BETWEEN @ssymfrom AND
> @ssymto) WHERE a.pt_orgn = @orgn AND a.pt_type = ''IV'' ORDER BY
> a.pt_sort', N'@orgn nvarchar(4000),@ssymfrom nvarchar(4000),@ssymto
> nvarchar(4000)', @orgn = N'2213', @ssymfrom = N'9401', @ssymto =
> N'9401' and
> exec sp_executesql N'SELECT a.pt_orgn,a.pt_ogdesc, a.pt_grop,
> a.pt_dept, a.pt_pros,b.cs_orgn, b.cs_orgn_x, b.cs_ssym,
> b.cs_dept, b.cs_pros FROM vRsPros a LEFT OUTER JOIN vRs22130101A b
> ON a.pt_orgn = b.cs_orgn AND a.pt_dept = b.cs_dept AND a.pt_pros
> = b.cs_pros AND
> b.cs_orgn = @orgn AND (b.cs_ssym BETWEEN @ssymfrom AND
> @ssymto) WHERE a.pt_orgn = @orgn AND a.pt_type = ''IV'' ORDER BY
> a.pt_sort', N'@orgn char(4000),@ssymfrom char(4),@ssymto char(4000)',
> @orgn = N'2213', @ssymfrom = N'9401', @ssymto = N'9401'
>
> I'm running sql profiler to monitor RS query performance and capture
> RS is using first statement and
> have no idea why the first statement is 10 times slower than the
> secnod one ?
>
> Can I enforce RS not to use nvarchar data type ?
>
> TIA
Hi,
Is there a difference between
exec sp_executesql N'SELECT a.pt_orgn,a.pt_ogdesc, a.pt_grop, a.pt_dept,
a.pt_pros,b.cs_orgn, b.cs_orgn_x, b.cs_ssym, b.cs_dept, b.cs_pros
FROM vRsPros a LEFT OUTER JOIN vRs22130101A b
ON a.pt_orgn = b.cs_orgn AND a.pt_dept = b.cs_dept AND a.pt_pros =
b.cs_pros AND
b.cs_orgn = @orgn AND (b.cs_ssym BETWEEN @ssymfrom AND @ssymto)
WHERE a.pt_orgn = @orgn AND a.pt_type = ''IV'' ORDER BY a.pt_sort',
N'@orgn nvarchar(4000),@ssymfrom nvarchar(4000),@ssymto nvarchar(4000)',
@orgn = N'2213', @ssymfrom = N'9401', @ssymto = N'9401'
and
exec sp_executesql N'SELECT a.pt_orgn,a.pt_ogdesc, a.pt_grop, a.pt_dept,
a.pt_pros,b.cs_orgn, b.cs_orgn_x, b.cs_ssym, b.cs_dept, b.cs_pros
FROM vRsPros a LEFT OUTER JOIN vRs22130101A b
ON a.pt_orgn = b.cs_orgn AND a.pt_dept = b.cs_dept AND a.pt_pros =
b.cs_pros AND
b.cs_orgn = @orgn AND (b.cs_ssym BETWEEN @ssymfrom AND @ssymto)
WHERE a.pt_orgn = @orgn AND a.pt_type = ''IV'' ORDER BY a.pt_sort',
N'@orgn char(4000),@ssymfrom char(4),@ssymto char(4000)', @orgn = N'2213',
@ssymfrom = N'9401', @ssymto = N'9401'
I'm running sql profiler to monitor RS query performance and capture RS is
using first statement and
have no idea why the first statement is 10 times slower than the secnod one
?
Can I enforce RS not to use nvarchar data type ?
TIA
