reporting service performance issue

Hi,
I construct a sql statement as a dataset. But when I click Run or Refresh,
the VS.NET runs so slow and no response sometimes. Do you know why, and how
to
deal with this issue?
below is the sql statement I used for:
DECLARE
@.sql NVARCHAR(4000)
select @.resp=ltrim(rtrim(@.resp)),
@.country =ltrim(rtrim(@.country )),
@.entity=ltrim(rtrim(@.entity)),
@.loc=ltrim(rtrim(@.loc)),
--@.office=ltrim(rtrim(@.office)),
@.local_oc=ltrim(rtrim(@.local_oc)),
@.for_agent=ltrim(rtrim(@.for_agent))
select @.sql =
'SELECT Country_Application.country_code, Country_Application.type,
Country_Application.appl_no,
Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
Action_Due.item, Action_Due.action_due_resp_atty,
Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
Action_Due.due_date,
Action_Due.remark, Action_Due.resp_admin
from Country_Application INNER JOIN Action_Due on
Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
= Action_Due.sub_case
inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
''ANNUITY''' + --hard code
' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
''''
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
if @.country not in ('ALL', '')
begin
if @.country = '<> US'
begin
set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
end
else
begin
set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
+')'
end
end
if @.entity not in ('ALL', '')
begin
set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
Invention_Data.loc = '''+@.loc+''''
end
if @.local_oc <> ''
begin
set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
end
if @.for_agent <> ''
begin
set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
end
exec sp_executesql @.sqlThe best way to deal with this is to pull the query out and run it in SQL
SErver Management Studio - get the query plan and find out what is
happening...
You are using some <>, and functions in where clauses which prevents the
optimizer from using index statistics to choose indexes..so you might not the
best plan...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"David Zhu" wrote:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||Hi Snyder,
Thanks a lot.
I also did some investigation. Unfortunately, the sql statement run fast
enough in
Sql query analyser. And I found the report run fast on the RS2000 without
SP2 machine, but slow on RS2000 with SP2.
Could you please give me some suggestion?
"Wayne Snyder" wrote:
> The best way to deal with this is to pull the query out and run it in SQL
> SErver Management Studio - get the query plan and find out what is
> happening...
> You are using some <>, and functions in where clauses which prevents the
> optimizer from using index statistics to choose indexes..so you might not the
> best plan...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "David Zhu" wrote:
> > Hi,
> >
> > I construct a sql statement as a dataset. But when I click Run or Refresh,
> > the VS.NET runs so slow and no response sometimes. Do you know why, and how
> > to
> > deal with this issue?
> >
> > below is the sql statement I used for:
> >
> > DECLARE
> >
> > @.sql NVARCHAR(4000)
> >
> > select @.resp=ltrim(rtrim(@.resp)),
> > @.country =ltrim(rtrim(@.country )),
> > @.entity=ltrim(rtrim(@.entity)),
> > @.loc=ltrim(rtrim(@.loc)),
> > --@.office=ltrim(rtrim(@.office)),
> > @.local_oc=ltrim(rtrim(@.local_oc)),
> > @.for_agent=ltrim(rtrim(@.for_agent))
> >
> > select @.sql => >
> > 'SELECT Country_Application.country_code, Country_Application.type,
> > Country_Application.appl_no,
> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> > Action_Due.item, Action_Due.action_due_resp_atty,
> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> > Action_Due.due_date,
> > Action_Due.remark, Action_Due.resp_admin
> >
> > from Country_Application INNER JOIN Action_Due on
> > Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> > = Action_Due.sub_case
> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
> >
> >
> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> > ''ANNUITY''' + --hard code
> >
> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> > ''''
> >
> > if @.resp not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> > end
> >
> > if @.country not in ('ALL', '')
> > begin
> > if @.country = '<> US'
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> > end
> > else
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> > +')'
> > end
> > end
> >
> > if @.entity not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> > Invention_Data.loc = '''+@.loc+''''
> > end
> >
> > if @.local_oc <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> > end
> >
> > if @.for_agent <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> > end
> >
> > exec sp_executesql @.sql
> >
> >
> >
> >
> >
> >
> >|||My suggestion is to put this in a stored procedure and call that instead and
see what that does for performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:C32A3B2C-5392-4C4B-B909-13D6B1B26F9B@.microsoft.com...
> Hi Snyder,
> Thanks a lot.
> I also did some investigation. Unfortunately, the sql statement run fast
> enough in
> Sql query analyser. And I found the report run fast on the RS2000 without
> SP2 machine, but slow on RS2000 with SP2.
> Could you please give me some suggestion?
> "Wayne Snyder" wrote:
>> The best way to deal with this is to pull the query out and run it in SQL
>> SErver Management Studio - get the query plan and find out what is
>> happening...
>> You are using some <>, and functions in where clauses which prevents the
>> optimizer from using index statistics to choose indexes..so you might not
>> the
>> best plan...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "David Zhu" wrote:
>> > Hi,
>> >
>> > I construct a sql statement as a dataset. But when I click Run or
>> > Refresh,
>> > the VS.NET runs so slow and no response sometimes. Do you know why, and
>> > how
>> > to
>> > deal with this issue?
>> >
>> > below is the sql statement I used for:
>> >
>> > DECLARE
>> >
>> > @.sql NVARCHAR(4000)
>> >
>> > select @.resp=ltrim(rtrim(@.resp)),
>> > @.country =ltrim(rtrim(@.country )),
>> > @.entity=ltrim(rtrim(@.entity)),
>> > @.loc=ltrim(rtrim(@.loc)),
>> > --@.office=ltrim(rtrim(@.office)),
>> > @.local_oc=ltrim(rtrim(@.local_oc)),
>> > @.for_agent=ltrim(rtrim(@.for_agent))
>> >
>> > select @.sql =>> >
>> > 'SELECT Country_Application.country_code, Country_Application.type,
>> > Country_Application.appl_no,
>> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
>> > Action_Due.item, Action_Due.action_due_resp_atty,
>> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
>> > Action_Due.due_date,
>> > Action_Due.remark, Action_Due.resp_admin
>> >
>> > from Country_Application INNER JOIN Action_Due on
>> > Country_Application.pdno = Action_Due.pdno and
>> > Country_Application.sub_case
>> > = Action_Due.sub_case
>> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>> >
>> >
>> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type)
>> > <>
>> > ''ANNUITY''' + --hard code
>> >
>> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' +
>> > @.DateEnd +
>> > ''''
>> >
>> > if @.resp not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
>> > end
>> >
>> > if @.country not in ('ALL', '')
>> > begin
>> > if @.country = '<> US'
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
>> > end
>> > else
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code in ('+
>> > @.country
>> > +')'
>> > end
>> > end
>> >
>> > if @.entity not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
>> > Invention_Data.loc = '''+@.loc+''''
>> > end
>> >
>> > if @.local_oc <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
>> > end
>> >
>> > if @.for_agent <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
>> > end
>> >
>> > exec sp_executesql @.sql
>> >
>> >
>> >
>> >
>> >
>> >
>> >|||I have the same problem..
Unfortunately, things did improve when I wrote it into a stored procedure.
The bad part about this is that we are planning to allow our customers
create reports using our views. They will not like extremely slow reports. I
have tracked this down in the log files to the fact that the report is being
add to a job list and then not being run from 1 to 3 minutes.
I haven't figured out how to make the report run without going to this list.
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:44673263-E25F-44C1-8C33-E5D9DF419422@.microsoft.com...
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and
> how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and
> Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd
> +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END
David Zhu escreveu:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END