Complex Query Speed Help - 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 2008 Forums

Transact-SQL (2008)

Complex Query Speed Help

Author

Topic

AlbertZeroK
Starting Member

2 Posts

Posted - 2014-01-23 : 07:15:44

Okay, so TSQL is not my strength, this is the query I'm running and it's taking for ever. It's a 360,000 row database with multiple sub tables. I'm okay with moving stuff to temp tables, etc. Just would love some direction on making this one faster.

select parcelid, b.cardnumber, v2.currentsf as sqft, nbc as nbhd, b.Grade, b.YearBlt, b.effyearblt,
isnull(JurisdictionalFactor,'') as RAWJurisdict,
case when JurisdictionalFactor is null then ''
when len(JurisdictionalFactor) > 0 then 'Jurisdict: ' + cast(JurisdictionalFactor as varchar(10))
else '' end as Jurisdict,
case when Lumpsum is null then ''
when isnumeric(lumpsum) = 1 then 'Lump Sum: ' + replace(cast(lumpsum as varchar(20)),'.0000','')
else '' end as LumpSum,
isnull(lumpsum,0) as RAWLumpSum,
rtrim(ltrim(isnull(streetnumber,'') + ' ' + tl.description)) as StreetAddress,
isnull(streetnumber,''), tl.description as StreetName,
isnull(dpag.[AgricultCredit],0) + v2.currenttotal as LiveValue,
v2.currentbuildingvalue, v.currentlandvalue, v2.currentyarditems,
case when lumpsum is null then ''
when lumpsum = 0 then ''
else cast(cast(isnull(LumpSum,0) as integer) as varchar(10)) end as lumpsum2,
cast(cast(totaldepr as integer) as varchar(10)) + '%' as totaldep, legaldescription, giskey,
isnull(functionaldeppercent,0) as RAWFunDep,
case when functionaldeppercent is NULL then ''
when isnumeric(functionaldeppercent) = 0 then ''
when functionaldeppercent > 0 then functionaldepcode + ' ' + replace(cast(functionaldeppercent as varchar(10)),'.00','') + '%'
else '' end as fundep,
isnull(economicdeppercent,0) as RAWEcoDep,
case when economicdeppercent is NULL then ''
when isnumeric(economicdeppercent) = 0 then ''
when economicdeppercent > 0 then economicdepcode + ' ' + replace(cast(economicdeppercent as varchar(10)),'.00','') + '%'
else '' end as ecodep,
isnull(specialdeppercent,0) as RAWSpecialDep,
case when specialdeppercent is NULL then ''
when isnumeric(specialdeppercent) = 0 then ''
when specialdeppercent > 0 then specialdepcode + ' ' + replace(cast(specialdeppercent as varchar(10)),'.00','') + '%'
else '' end as specialdep,
isnull(overridedeppercent,0) as RAWOverrideDep,
case when overridedeppercent is NULL then ''
when isnumeric(overridedeppercent) = 0 then ''
when overridedeppercent > 0 then overridedepcode + ' ' + replace(cast(overridedeppercent as varchar(10)),'.00','') + '%'
else '' end as overridedep,
isnull(physicaldeppercent,0) as RAWPhyDep,
case when isnull(dpag.[AgricultCredit],0) + v2.currenttotal > 0 then v2.currentsf / (isnull(dpag.[AgricultCredit],0) + v2.currenttotal)
else 0 end as PricePerSqFt, isnull(hasinf,'') as hasinfappeal, isnull(hasber,'') as hasberappeal, isnull(hasptc,'') as hasptcappeal,
isnull(convert(varchar, sales0910.saledate, 111),'') as SaleDate0910,
isnull(sales0910.saleprice,0) as SalePrice0910, isnull(sales0910.nalcode,'') as SaleCode0910,
case when sales0910.saleprice = 0 or sales0910.saleprice is null then 0
when isnull(dpag.[AgricultCredit],0) + v2.currenttotal > 0 and sales0910.saleprice > 0 then (isnull(dpag.[AgricultCredit],0) + v2.currenttotal) / sales0910.saleprice
else 0 end as SalesRatio0910,
isnull(convert(varchar, sales1114.saledate, 111),'') as SaleDate1114,
isnull(sales1114.saleprice,0) as SalePrice1114, isnull(sales1114.nalcode,'') as SaleCode1114,
case when sales1114.saleprice = 0 or sales1114.saleprice is null then 0
when isnull(dpag.[AgricultCredit],0) + v2.currenttotal > 0 and sales1114.saleprice > 0 then (isnull(dpag.[AgricultCredit],0) + v2.currenttotal) / sales1114.saleprice
else 0 end as SalesRatio1114

FROM prd_apro2014.dbo.DataProperty as p
LEFT JOIN [prd_apro2014].[dbo].[DataGISLookup] as g on g.accountnumber=p.accountnumber
LEFT JOIN prd_apro2014.dbo.TableLocations AS tl ON p.LocationLookup = tl.Code
LEFT JOIN (select '*' as hasinf, max(accountnumber) as infaccount from prd_apro2014.dbo.datapreassesgeneral where fiscalyear=2014 and category='ap11') as InfAccounts on p.accountnumber = infaccount
LEFT JOIN (select '*' as hasber, max(accountnumber) as beraccount from prd_apro2014.dbo.datapreassesgeneral where fiscalyear=2014 and category='ap13') as BERAccounts on p.accountnumber = beraccount
LEFT JOIN (select '*' as hasptc, max(accountnumber) as ptcaccount from prd_apro2014.dbo.datapreassesgeneral where fiscalyear=2014 and category='ap15') as PTCAccounts on p.accountnumber = ptcaccount
LEFT JOIN (

select * from prd_apro2014.dbo.datasales as sales,
(select ds.accountnumber as anum, max(seqnumber) as maxseq, maxsaledate from prd_apro2014.dbo.datasales as ds,
(SELECT accountnumber, max(saledate) as maxsaledate FROM prd_apro2014.dbo.datasales as d
where saledate > '12/31/2008' and saledate < '1/1/2010' group by accountnumber) as dsmaxsaledate
where dsmaxsaledate.maxsaledate=ds.saledate and dsmaxsaledate.accountnumber=ds.accountnumber group by ds.accountnumber,maxsaledate) as dsales
where sales.accountnumber=dsales.anum and sales.saledate=dsales.maxsaledate and sales.seqnumber=dsales.maxseq

) as sales0910 on sales0910.accountnumber=p.accountnumber

LEFT JOIN (

select * from prd_apro2014.dbo.datasales as sales,
(select ds.accountnumber as anum, max(seqnumber) as maxseq, maxsaledate from prd_apro2014.dbo.datasales as ds,
(SELECT accountnumber, max(saledate) as maxsaledate FROM prd_apro2014.dbo.datasales as d
where saledate > '12/31/2010' group by accountnumber) as dsmaxsaledate
where dsmaxsaledate.maxsaledate=ds.saledate and dsmaxsaledate.accountnumber=ds.accountnumber group by ds.accountnumber,maxsaledate) as dsales
where sales.accountnumber=dsales.anum and sales.saledate=dsales.maxsaledate and sales.seqnumber=dsales.maxseq

) as sales1114 on sales1114.accountnumber=p.accountnumber,

prd_apro2014.dbo.DataBuilding as b, prd_apro2014.dbo.datatotalvalues as v,
prd_apro2014.dbo.datatotalvalues as v2,
[prd_apro2014].[dbo].[DataDepreciation] as d, [prd_apro2014].[dbo].datalegaldescription as ld,
prd_apro2014.dbo.DataPreAssesGeneral as dpag,
(select accountnumber, max(seqnumber) as maxseqnum from prd_apro2014.dbo.DataPreAssesGeneral where fiscalyear=2014 group by accountnumber) as maxdpaglist

where b.AccountNumber=p.AccountNumber and b.CardNumber=p.cardnumber
and d.AccountNumber = p.AccountNumber and d.cardnumber=p.cardnumber
and ld.accountnumber=p.accountnumber
and v.accountnumber=p.accountnumber and v.cardnumber=0
and v2.accountnumber=p.accountnumber and v2.cardnumber=p.cardnumber
and dpag.accountnumber=p.accountnumber and dpag.fiscalyear=2014 and dpag.cardnumber=p.cardnumber
and maxdpaglist.accountnumber=p.accountnumber and maxdpaglist.maxseqnum=dpag.seqnumber
and (Closed is null or Closed=0) and p.CardNumber=1
and b.CardNumber=1 and dpag.category = 'RPTV'

and left(nbc,3) = 'cr0'

AlbertZeroK
Starting Member

2 Posts

Posted - 2014-01-23 : 07:19:19

Oh, and you don't need to re-write the query for me, just tell me stuff like, hey stupid, try this or try that :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:28:10

Sorry i dont think anybody would have the time (or patience) to go through above query and suggest anything
a better approach would be to explain what you're trtying to achieve with some sample data and desired output so that we may suggest an alternative.
See how to post data in below link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-01-23 : 08:58:05

quote:
Originally posted by AlbertZeroK

Oh, and you don't need to re-write the query for me, just tell me stuff like, hey stupid, try this or try that :)

What does the query plan show? Tons of scans? Have you tried various indexing schemes?

Kristen
Test

22859 Posts

Posted - 2014-01-25 : 15:09:59

[code]
LEFT JOIN (select '*' as hasinf, max(accountnumber) as infaccount from prd_apro2014.dbo.datapreassesgeneral
where fiscalyear=2014 and category='ap11') as InfAccounts on p.accountnumber = infaccount
LEFT JOIN (select '*' as hasber, max(accountnumber) as beraccount from prd_apro2014.dbo.datapreassesgeneral
where fiscalyear=2014 and category='ap13') as BERAccounts on p.accountnumber = beraccount
LEFT JOIN (select '*' as hasptc, max(accountnumber) as ptcaccount from prd_apro2014.dbo.datapreassesgeneral
where fiscalyear=2014 and category='ap15') as PTCAccounts on p.accountnumber = ptcaccount
[/code]
Do that in one pass, upfront for all 3 categories, and then use the TEMP table instead of making 3 complete passes of prd_apro2014.dbo.datapreassesgeneral

[code]
LEFT JOIN (
select * from prd_apro2014.dbo.datasales as sales
[/code]
Don't ever use "SELECT *", specify only the columns you actually need in your query (even if it is all of them - someone might add a massive IMAGE / BINARY datatype column in future and spoil your day)
[code]
select * from prd_apro2014.dbo.datasales as sales,
(select ds.accountnumber as anum, max(seqnumber) as maxseq, maxsaledate from prd_apro2014.dbo.datasales as ds,
(SELECT accountnumber, max(saledate) as maxsaledate FROM prd_apro2014.dbo.datasales as d
[/code]
should be possible to do that sub-query without the sub-sub-queries. I reckon coding it the way it is now will be horribly inefficient
[code]
where saledate > '12/31/2010'
[/code]
just by-the-by ambiguous dates are a potential problem downstream. If someone connects using British English language, for example, then the default date format is likely to be interpreted differently. Ditto if server settings change, and all sorts of other things that SQL uses to decide what the default date format might be.

Use '20101231' - SQL will treat an 8-digit date in that format as unambiguous. Anything with punctuation in it is ambiguous, and may be parsed differently to how you expect.
[code]
where b.AccountNumber=p.AccountNumber and b.CardNumber=p.cardnumber
and d.AccountNumber = p.AccountNumber and d.cardnumber=p.cardnumber
and ld.accountnumber=p.accountnumber
and v.accountnumber=p.accountnumber and v.cardnumber=0
and v2.accountnumber=p.accountnumber and v2.cardnumber=p.cardnumber
and dpag.accountnumber=p.accountnumber and dpag.fiscalyear=2014 and dpag.cardnumber=p.cardnumber
and maxdpaglist.accountnumber=p.accountnumber and maxdpaglist.maxseqnum=dpag.seqnumber
[/code]
Use newer style JOINs. Much easier to see what connects to what, and from that point to check for what indexes might be missing.
[code]
and (Closed is null or Closed=0)
[/code]
Yuck. Fix the data so that it is NOT NULL. the OR is painful for the optimiser and better you can program it out.
[code]
replace(cast(specialdeppercent as varchar(10)),'.00','')
[/code]
Do the formatting in the presentation layer. SQL is rubbish at doing it. Must be possible to do that without using REPLACE? REPLACE will be trying to replace all.multiple occurrences, whereas you just want to chop the last 3 characters off it they are ".00" I expect. Anyway, SQL is the wrong place to do that. (I know, I know, there are caveats, but you'd have a hard job getting that past me when I did the code review)
[code]
convert(varchar,
[/code]
Don't EVER miss a size on the definition. SQL's defaults will catch you out sooner or later
[code]
when isnull(dpag.[AgricultCredit],0) + v2.currenttotal > 0 and sales1114.saleprice > 0
then (isnull(dpag.[AgricultCredit],0) + v2.currenttotal) / sales1114.saleprice
[/code]
I didn't look closely, but you just trying to prevent Divide by Zero? if so
[code]
(dpag.[AgricultCredit] + v2.currenttotal) / NullIf(sales1114.saleprice, 0)
[/code]
will do that more efficiently (if dpag.[AgricultCredit] is NULL then the whole thing is NULL anyone, so no need to check for/ISNULL() that (but currently that formula is not used if the SUM is less than zero - so maybe conditional code is needed for that

Is the data partitioned? If so make sure that all conditions for the partitions are in place, otherwise all the irrelevant data will be included in the query, and then discarded.

But first and foremost check that the necessary indexes are in place.

The code is ghastly, and unmaintainable. Needs tidying up extensively if the next person coming along is going to have a faint hope of making maintenance changes without yards of testing or user downtime / company financial loss.

Formatting it, before you post it here, would mean more people would read it, and people would more easily spot issues. If you can't be arsed then don't expect people here to be ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources