SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Help in Optimizing a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phyxe
Starting Member

Philippines
13 Posts

Posted - 10/01/2012 :  22:43:06  Show Profile  Reply with Quote
Hi there! i have this query that we've been using for the past month, its a nice query where it provides me all the details i need for a report though my main problem is that i can't optimize it properly here's my query:
WITH TotalRecord(location1, totalrecords, brgyindex)
as
(
	select  Realproperty.location, COUNT (tdno), brgyIndex
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex
),
TotalPending (location2, totalpending, brgyindex)
as
(
	select distinct temp.location, ISNULL (temp2.approved,0),temp.brgyindex
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp
on RPTLedger.objid=temp.objid
left join
(
select  Realproperty.location[location], COUNT (tdno)[approved]
from RPTLedger
join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
where RPTLedger.state='PENDING'
group by RealProperty.location
)temp2
on temp.location=temp2.location
),
TotalApproved (location3, totalapproved, brgyindex)
as
(
select distinct temp.location, ISNULL (temp2.approved,0),temp.brgyindex
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp
on RPTLedger.objid=temp.objid
left join
(
select  Realproperty.location[location], COUNT (tdno)[approved]
from RPTLedger
join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
where RPTLedger.state='APPROVED'
group by RealProperty.location
)temp2
on temp.location=temp2.location
), 
TotalExcempt (location4, totalexcempt, brgyindex)
as
(select temp3.location, ISNULL(temp4.expt,0)[excempt], temp3.brgyindex 
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp3 on RPTLedger.objid = temp3.objid
left join
(
select location, COUNT (RPTLedger.tdno)[expt]
from RPTLedger
	join TaxDeclaration on (RPTLedger.tdid=TaxDeclaration.objid)
	join RPU on (RPU.objid = TaxDeclaration.rpuid)
where exemptCode is not NULL
and RPTLedger.state ='PENDING'
group by location
)temp4 on temp3.location=temp4.location
),
TotalZeroAss (location5, totalzero, brgyindex)
as
(select temp5.location, ISNULL(temp6.zero,0)[ZeroCount], temp5.brgyindex 
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp5 on RPTLedger.objid = temp5.objid
left join
(
select location, COUNT (RPTLedger.tdno)[zero]
from RPTLedger
	join TaxDeclaration on (RPTLedger.tdid=TaxDeclaration.objid)
	join RPU on (RPU.objid = TaxDeclaration.rpuid)
where exemptCode is NULL
and RPTLedger.state ='PENDING'
and RPTLedger.assessedValue ='0'
group by location
)temp6 on temp6.location=temp5.location
)

select TotalRecord.location1[Barangay], totalrecords,totalapproved,
totalpending, totalexcempt, totalzero
from TotalRecord 
join TotalPending on (TotalRecord.location1 =TotalPending.location2)
join TotalApproved on (TotalRecord.location1=TotalApproved.location3)
join TotalExcempt on (TotalRecord.location1 = TotalExcempt.location4)
join TotalZeroAss on (TotalRecord.location1=TotalZeroAss.location5)
group by TotalRecord.location1, totalrecords, TotalPending.location2,
totalpending, TotalApproved.location3, totalapproved, TotalApproved.brgyindex, totalexcempt, totalzero
order by TotalApproved.brgyindex asc



this query run for more than a minute around 1mins and 45sec top so i really need to make it faster if that is still possible :)

any instructions or tip on how i can optimize this one will be a great help

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/01/2012 :  23:23:58  Show Profile  Reply with Quote
it would really difficult for someone to look into query and understand what its doing

you may be better off posting some sample data and give sample output so that we can suggest an alternate approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

phyxe
Starting Member

Philippines
13 Posts

Posted - 10/02/2012 :  01:23:19  Show Profile  Reply with Quote
i see... well initially i just combined the queries with the "with" function, well my database has some data status such as "PENDING" and "APPROVED" so i basically need a query that will generate a single table result wherein it would search on a table that the "where" clause would include both the "PENDING" and "APPROVED" as a criteria for searching
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1906 Posts

Posted - 10/02/2012 :  01:40:27  Show Profile  Visit jackv's Homepage  Reply with Quote
Could you produce the DDL statements for the objects - with some sample data.
Also , it is a good idea to present the Exceution Plan (text is OK) - http://www.sqlserver-dba.com/2010/09/sql-server-execution-plan-as-text.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/02/2012 :  15:43:18  Show Profile  Reply with Quote
quote:
Originally posted by phyxe

i see... well initially i just combined the queries with the "with" function, well my database has some data status such as "PENDING" and "APPROVED" so i basically need a query that will generate a single table result wherein it would search on a table that the "where" clause would include both the "PENDING" and "APPROVED" as a criteria for searching


thats too vague an explanation
translating this directly to sql will mean just a filter condition like

WHERE StatusField IN ('PENDING','APPROVED')

but I'm sure thats not what you're after

So please post some sample data as requested and explain your output for us

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000