SQLTeam.com | Weblogs | Forums

Alternate methods for sub-queries(Compatability lvl80)

sql2008r2

#1

Hey all!
I was hoping to get some pointers to help me figure out how to re-write this SSRS report. We've had this report in place for over 2 years, it was built by my predecessor, and for some reason about 3 weeks ago, "it stopped returning the full result set like it did before" as per the end user that received this report...
They argue that the report would send an e-mail for every result, though the subscription is set to send on weekdays at 9PM...there was only 1 subscription...
The report is essentially pulling the most recently created company ID from the Company table, and returning the very first order entered with that customer in the 24 hours prior. It returns some other info that's present on the Orderheader table, and the relationship between those 2 tables is based on the CompanyID. It also returns the city name from the City table. The relationship there is set with the orderheader based on the ORigin/Destination cities which are codes linked to the same code in the city table.

set transaction isolation level read uncommitted

select top 1 ord_billto 'Bill To ID', 
c.cmp_name 'Bill To Name',
c.cmp_creditlimit 'Credit Limit',
c.cmp_revtype2 'Sales Office',
c.cmp_subcompany 'Sales Rep',
(select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Ship Date',
(select top 1 o.ord_cmdvalue from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Order Value',
(select top 1 o.ord_hdrnumber from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Order Number',
(select top 1 (select cty_nmstct from city where cty_code=o.ord_origincity) from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Ship City',
(select top 1 (select cty_nmstct from city where cty_code=o.ord_destcity) from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Delivery City',
(select top 1 o.ord_status from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Order Status'
from orderheader
inner join company c on c.cmp_id=orderheader.ord_billto
where ord_billto not in (select billto_id from fls_firsttime_billto_flag)
and ord_status in ('cmp','std','avl','pln')
group by ord_billto, c.cmp_name, c.cmp_revtype2, c.cmp_subcompany, c.cmp_creditlimit 
order by (select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate)
insert into fls_firsttime_billto_flag
select top 1 ord_billto , 
(select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate)
from orderheader
inner join company c on c.cmp_id=orderheader.ord_billto
where ord_billto not in (select billto_id from fls_firsttime_billto_flag)

As you can see, this query isn't written very well and the performance isn't any good either...(It takes over 40 seconds to return a single row). At the end you can see it also does an Insert so that it logs all the results so they don't come up next time it runs.

Here's an alternate method i tried but for some reason i can't get it to return any results. I checked the execution plan and i can see that it's select 7 rows at the start, but then it drops them when it gets to the first Join: IF i run the subquery in the WHERE clause, it's returning exactly what i need to be able to run the report...Basically i need to figure out how to store the result from the WHERE clause such that the other columns in the query use that as the "filter argument"(Not sure what term to use here :S)

select o.ord_billto, c.cmp_name, c.cmp_creditavail,c.cmp_revtype2, o.ord_startdate, (select top 1 o.ord_hdrnumber from orderheader o order by o.ord_startdate desc)'Order#',
 o.ord_status,o.ord_charge, c1.cty_name, c2.cty_name
from orderheader o
inner join company c on o.ord_billto=c.cmp_billto
join city c1 on o.ord_origincity=c1.cty_code
join city c2 on o.ord_destcity=c2.cty_code
where c.cmp_id in (select cmp_id from company where datediff(hh,cmp_createdate,getdate())<=24 and cmp_billto='Y')

I hope i mentioned all the necessary info. Any help or input is appreciated! Btw, we're running level 80 compatability, so i'm pretty sure i won't be able to use any recent new functions to work around this like using WITH...


#2

I didn't look at the original code too much as it makes my head spin, so I focused on your new code and the part of your post that said that subquery in the where clause is getting the right data.

Does this work?
where datediff(hh,cmp_createdate,getdate())<=24 and cmp_billto='Y'

If so, switch it to this for performance reasons (hoping cmp_createdate is indexed):
where cmp_createdate > getdate()-1 and cmp_createdate <= getdate() and cmp_billto='Y'


#3

Regarding the original code though, this needs to be removed: set transaction isolation level read uncommitted

That is equivalent to using NOLOCK which can bring back BAD/INCORRECT data. Perhaps that's what's causing the issue. Even if it's not causing the issue, it should be removed as it sounds like accurate data is needed to be returned by this report. If accuracy matters, never using NOLOCK/READ UNCOMMITTED.


#4

Hey! Thanks so much for the replies!
I'm going to try running it without the SET TRANSACTION ISOLATION LEVEL READ and see if that returns anything difference.I made a few slight changes to my new script and was able to get some valid results that made sense(Simply selecting from Company instead of ORderheader seemed to fix it).

select c.cmp_id, c.cmp_name, c.cmp_creditlimit,c.cmp_revtype2,c.cmp_createdate, o.ord_startdate, o.ord_hdrnumber'Order#',
 o.ord_status, c1.cty_name'Origin', c2.cty_name'Destination',o.ord_cmdvalue'Order Value'
from company c
inner join orderheader o on c.cmp_id=o.ord_billto
join city c1 on o.ord_origincity=c1.cty_code
join city c2 on o.ord_destcity=c2.cty_code
where c.cmp_id in (select cmp_id from company where datediff(dd,cmp_createdate,getdate())<=1 and cmp_billto='Y')

And for the Update part that follows, i re-wrote it's select statement like this:

select c.cmp_id, c.cmp_createdate, o.ord_hdrnumber from company c
join orderheader o on c.cmp_id=o.ord_billto
where datediff(hh,c.cmp_createdate,getdate())<=24
group by c.cmp_id, c.cmp_createdate, o.ord_hdrnumber, o.ord_startdate
HAVING count(o.ord_hdrnumber)>0
order by o.ord_startdate desc

It's a good step forward, but my end user is saying that there's 1 other argument the report should be checking for, but i'm not sure how that would be done considering what our application is able to log...I reached out to the person who built this and am expecting them to get back to me later today. But my new query seems to be taking care of the main part of the report which is to pull any new companies creates the day prior and return the first order entered with their ID. The missing part is for companies that already exist that have been re-enabled...But i'm going to disregard that for now.

I'll update once i get this resolve. Thanks again for your input!


#5

I'm not understanding why it's using a subquery when company is already in the FROM. Plus get rid of the datediff on cmp_createdate for performance reasons.

Both changes I'm recommending:
select c.cmp_id, c.cmp_name, c.cmp_creditlimit,c.cmp_revtype2,c.cmp_createdate, o.ord_startdate, o.ord_hdrnumber'Order#',
o.ord_status, c1.cty_name'Origin', c2.cty_name'Destination',o.ord_cmdvalue'Order Value'
from company c
inner join orderheader o on c.cmp_id=o.ord_billto
join city c1 on o.ord_origincity=c1.cty_code
join city c2 on o.ord_destcity=c2.cty_code
where cmp_createdate > getdate()-1 and cmp_createdate <= getdate() and cmp_billto='Y'