query works fine outside union, but doesnt work .. .

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??

select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc

i've included the union query here for completeness of the question

begin
declare @.current_date datetime
set @.current_date = GETDATE()

select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @.current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

endTry using UNION ALL.

-PatP

Query very slow when executed using Reporting Services

Hello,
I have a dataset that uses a stored procedure as the data source. In
the query analyzer, it takes just over 2 seconds to execute. However,
in the execute query mode in the Visual Studio reports designer, the
query takes several minutes.
The report, when run from the server, also takes a very long time to
complete.
There are only 4 parameters, two varchar(2) and two datetime fields.
The particular query I am running returns 12 rows. There are no filters
applied to the report, not that would affect the query in the VS
reports designer.
Does anybody have any ideas on how to improve the performance?
Thank you,
Brian TakitaFirst the good news. You really did a good job with the information you
provided here. The bad news, this is very strange. The most common reasons
for slowness is lots of data returned (but you only have 12 rows), second is
having a filter. The issue with a filter isn't a filter per se but the fact
that all the data is brought over prior to the filter being applied. If you
execute the stored procedure in query analyzer and it returns 12 rows there
then something else is going on.
Do you have a large amount of fields being returned?
How is your data source credential setup? I am wondering if something is
going on with regards to your credentials that is causing the problem.
Do you have another report that uses the same data source? If so, how is its
performance. If not, then try some simple report that uses the same data
source so we can eliminate that as a possible problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112301864.710648.22590@.z14g2000cwz.googlegroups.com...
> Hello,
> I have a dataset that uses a stored procedure as the data source. In
> the query analyzer, it takes just over 2 seconds to execute. However,
> in the execute query mode in the Visual Studio reports designer, the
> query takes several minutes.
> The report, when run from the server, also takes a very long time to
> complete.
> There are only 4 parameters, two varchar(2) and two datetime fields.
> The particular query I am running returns 12 rows. There are no filters
> applied to the report, not that would affect the query in the VS
> reports designer.
> Does anybody have any ideas on how to improve the performance?
> Thank you,
> Brian Takita
>|||Thank you for the prompt reply.
>Do you have a large amount of fields being returned?
Yes. There are 40 fields returned.
>How is your data source credential setup? I am wondering if something
is
>going on with regards to your credentials that is causing the problem.
I'm using integrated security.
>Do you have another report that uses the same data source? If so, how
is its
>performance. If not, then try some simple report that uses the same
data
>source so we can eliminate that as a possible problem.
I tried creating a report using the stored procedure and it is also
slow.|||You can try to pin point what's taking so long. Look in the ReportServer
database; the ExecutionLog
table. It shows what report ran, when, the amount of time for data
retrieval, processing time, and rendering time.
It also shows what render format, render status (fail or success), query
row count and byte size of
the rendered file.
Adrian M.
MCP
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
>>Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
>>How is your data source credential setup? I am wondering if something
> is
>>going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
>>Do you have another report that uses the same data source? If so, how
> is its
>>performance. If not, then try some simple report that uses the same
> data
>>source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Here are the result fields:
TimeDataRetrieval: 255840
TimeProcessing: 45
TimeRendering: 34
Status: rsSuccess
ByteCount: 54070
RowCount: 0
What is strange is when the report is executed on the server, the
RowCount is 0, but when executed from the VS report designer, I get the
correct number of rows, but it is also slow.|||When I try pulling a very similar report with the same parameters as a
pdf file I get the following results:
TimeDataRetrieval: 228716
TimeProcessing: 224082
TimeRendering: 45
Status: rsSuccess
ByteCount: 1872
RountCount: 0|||Is it possible to you to do a test where the stored procedure is returning
fewer fields (for the test just have it do 5 fields for instance) and then
create a report off of that. Let's see if the problem is the number of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112303389.941233.54770@.z14g2000cwz.googlegroups.com...
> Thank you for the prompt reply.
> >Do you have a large amount of fields being returned?
> Yes. There are 40 fields returned.
> >How is your data source credential setup? I am wondering if something
> is
> >going on with regards to your credentials that is causing the problem.
> I'm using integrated security.
> >Do you have another report that uses the same data source? If so, how
> is its
> >performance. If not, then try some simple report that uses the same
> data
> >source so we can eliminate that as a possible problem.
> I tried creating a report using the stored procedure and it is also
> slow.
>|||Thank you for your help Bruce. Your heuristics to solve the problem
were invaluable.
I solved the issue but did not find the ultimate source of the problem.
To solve the issue I optimized the stored procedure. I removed an
insert statement that scanned a fairly large table.
My stored procedure is fairly complicated as it uses several table
variables.
It strange that the sp would work fine using the query analyzer but not
using Sql Server RS.
I also tried recreating the sp, making all of the functions called in
the sp inline. There was a test case in the sp that took a long time to
complete.
Maybe the sp caused the server to run into memory constraints?
Thanks again,
Brian Takita|||Problem solved. The ExecutionLog table is something I'll need remember.
Thank you Adrian.|||You might want to turn on SQL profiler and look at the statements that are
generated by Query Analyzer and Reporting Services (through sqlclient).
Sometimes, the acutal execution syntax will be slightly different even when
the results are the same. This is primarily in the way that sqlclient
prepares parameters and executes the stored procedure. This difference may
cause SQL Server not to use a compiled query plan.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Takita" <briantakitaspam@.yahoo.com> wrote in message
news:1112313413.140441.295110@.f14g2000cwb.googlegroups.com...
> Thank you for your help Bruce. Your heuristics to solve the problem
> were invaluable.
> I solved the issue but did not find the ultimate source of the problem.
> To solve the issue I optimized the stored procedure. I removed an
> insert statement that scanned a fairly large table.
> My stored procedure is fairly complicated as it uses several table
> variables.
> It strange that the sp would work fine using the query analyzer but not
> using Sql Server RS.
> I also tried recreating the sp, making all of the functions called in
> the sp inline. There was a test case in the sp that took a long time to
> complete.
> Maybe the sp caused the server to run into memory constraints?
> Thanks again,
> Brian Takita
>

Query times out when executed from 1 server

I have a weird problem.
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.
Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegr oups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>
|||Updated the statistics and problem still there.
|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...

Query times out when executed from 1 server

I have a weird problem.
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegroups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>|||Updated the statistics and problem still there.|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...

Query times out when executed from 1 server

I have a weird problem.
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegroups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>|||Updated the statistics and problem still there.|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...