Performance Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Performance Optimization

Author

Topic

wolfenstein4u
Starting Member

6 Posts

Posted - 2007-08-06 : 16:17:08

I've a stored procedure that includes so many queries that are summed up with the UNION ALL operator on the same table. I want a solution to get optimized performance as well as reduce query text repetition.

Please have a look for detail;

select 80 sortorder, 'TOTAL A VALUES', count(*) data
from A
where type not in ('A','', 'I') and type is not null
and entrydate between '01/01/2006' and '12/01/2007'

UNION ALL

select 90 sortorder, 'TOTAL B VALUES', count(*) data
from A
where type not in ('A','', 'I') and type is not null
and month(entrydate) = 07 and year(entrydate) = 2007 and entrydate< '12/01/2007'

Now basically i need the Total Count and its respective text, according to the entry date. For that purpose I've to write two queries on the same table and then get the results by UNION ALL.

Kindly let me know if it can be resolved using a single query with the effective performance.

Regards,
Mohsin Shahzad

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 17:09:13

select sortorder, header, sum(data) as data
from (
SELECT CASE
WHEN entrydate between '01/01/2006' and '12/01/2007' then 'Total A values'
WHEN month(entrydate) = 07 and year(entrydate) = 2007 and entrydate< '12/01/2007' then 'Total B values'
end as header,
CASE
WHEN entrydate between '01/01/2006' and '12/01/2007' then 80
WHEN month(entrydate) = 07 and year(entrydate) = 2007 and entrydate< '12/01/2007' then 90
end as sortorder,
1 as data
from A
) as d
where sortorder is not null
group by sortorder, header

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-06 : 19:15:32

If you want good performance, simply calculated your totals on your report or at the presentation layer, as is standard practice. You not only get shorter, simpler code, but it will also run twice as fast.

(sorry, misread your situation

, see below)

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-07 : 08:29:22

I don't think so in this case... doing it your way mean passing the detail rows to the presentation layer to be aggragated. If there are thousands of detail rows that make up each sum, that's going to take a lot longer than just calculating the totals at the server and passing back just the totals.

--Jeff Moden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-07 : 08:33:01

Jeff -- you're right, I misread the problem. I saw the total and the union, and I thought it was a typical "return detail rows and also the total all in SQL" situation, but I read too quickly. (that's what I get for browsing + posting on a PDA now and then!!!)

Peso's got the right approach.

Thanks, Jeff, for setting me straight!

- Jeff

- Jeff
http://weblogs.sqlteam.com/JeffS

wolfenstein4u
Starting Member

6 Posts

Posted - 2007-08-07 : 10:23:26

Yup, Thanx alot to all of you guys.
Jeff, i was also worried for taking the logic at presentation layer but resolved on the right time :)
Peso's solution is working fine :)

Regards,
Mohsin Shahzad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources