performance question

[inna]

Hi , long time I didn't ask any questions
And now I am back.
I have a huge query
that looks like that

select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition1 and
condition2 and condition3
union
select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition4 and
condition5 and condition6
union
select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition7 and
condition8 and condition9
(If you need the real one I can include it (It is 1000 lines))
So when I run it on database it always takes a different time to
execute.
A lot of times it was timing out, sometimes it doesn't take long (with
the same parameters)
I reindexed all tables, ran
sp_updatestats
sp_configure 'min memory per query (KB)' ,10240
(with reconfigure of course.) It was the same instability.
I replaced all selects in from : (select sum(field1),empid from table
group by empid )
with functions in select , but it didn't help.
What can be wrong? what should I do in this case? I have a procedures
like that.

[Garry]

The best way to resolve problem - analyze execution plan

[Erland Sommarskog]

inna (me****@hotmail.com) writes:

Hi , long time I didn't ask any questions
And now I am back.
I have a huge query
that looks like that

select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d

Why are you repeating the same derived table all over again? Obviously
your real query does not look like that, but in what you have obscured
that query, I don't know, and I don't want to guess.

With the CREATE TABLE and CREATE INDEX statements for the involved tables,
some information about that data size and distribution in the tables, as
well as the query of the table itself, people in this newsgroup might be
able to help you. Of course, if it is a 1000-line monster, then the sheer
size of the query may be an obstacle for assistance over newsgroups.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp