April 16, 2010 at 3:21 am
I am not good at SQL. My developers has come and asking me to decrease the db compat level to 80 in SQL 2005 env for one of there requirement i cant do this because other queries has started taking more time. They want the output with null values ie the option 2 .
Can you guys help in rewriting the ANSI query in a way it should return 39299904 rows
1.Query with ANSI SQL SYNTAX:
select Count(*)
from dbo.et_Summary_Income_Statement inc
left outer join dbo.it_1_Revenue__Costs rev on rev.itemiid=inc.dimension_1_revenu
left outer join dbo.it_2_elist e on e.itemiid=inc.elist
left outer join dbo.it_2_LOB l on l.itemiid=inc.dimension_2_lob
left outer join dbo.it_2_RegionCurrency rg on rg.itemiid=inc.dimension_2_region
left outer join dbo.it_4_Months_QTRS mnt on mnt.itemiid=inc.dimension_4_months
--Total rows 1125210
2.Query with ANSI SQL SYNTAX:
select Count(*)
from dbo.it_1_Revenue__Costs rev,dbo.it_2_elist e,dbo.it_2_LOB l,
dbo.it_2_RegionCurrency rg,dbo.it_4_Months_QTRS mnt,dbo.et_Summary_Income_Statement inc
where rev.itemiid*=inc.dimension_1_revenu
and l.itemiid*=inc.dimension_2_lob
and e.itemiid*=inc.elist
and rg.itemiid*=inc.dimension_2_region
and mnt.itemiid*=inc.dimension_4_months
-- Total rows 39299904
April 16, 2010 at 6:16 am
I don't know the number of rows it will return, but your non-ansi query translates to this:
select Count(*)
from dbo.it_1_Revenue__Costs rev
left outer join dbo.et_Summary_Income_Statement inc
on rev.itemiid = inc.dimension_1_revenu
left outer join dbo.it_2_elist e
on e.itemiid = inc.elist
left outer join dbo.it_2_LOB l
on l.itemiid = inc.dimension_2_lob
left outer join dbo.it_2_RegionCurrency rg
on rg.itemiid = inc.dimension_2_region
left outer join dbo.it_4_Months_QTRS mnt
on mnt.itemiid = inc.dimension_4_months
-- Gianluca Sartori
April 16, 2010 at 6:21 am
Is it giving the right results?
April 16, 2010 at 6:35 am
Thanks.
I tried but still its giving only 1125213 rows
April 16, 2010 at 6:53 am
This should work, the trick is to follow the WHERE clause:
SELECT COUNT(*)
FROM dbo.et_Summary_Income_Statement inc
RIGHT JOIN dbo.it_1_Revenue__Costs rev
ON rev.itemiid = inc.dimension_1_revenu
RIGHT JOIN dbo.it_2_LOB l
ON l.itemiid = inc.dimension_2_lob
RIGHT JOIN dbo.it_2_elist e
ON e.itemiid = inc.elist
RIGHT JOIN dbo.it_2_RegionCurrency rg
ON rg.itemiid = inc.dimension_2_region
RIGHT JOIN dbo.it_4_Months_QTRS mnt
ON mnt.itemiid = inc.dimension_4_months
April 16, 2010 at 7:57 am
You may also want to compare the actual execution plans between the two queries.
If possible, could you post those here if you still have problems?
April 17, 2010 at 3:27 am
If the queries are starting to take more time, it could be that your statistics are getting out of date. Do you have a maintenance plan for maintaining them? It's also possible, if you're running sp_updatestats and the sampled statistics aren't adequate for your system and you might need to update the statistics with a full scan.
Also, are you keeping your indexes defragmented? Index fragmentation will lead to slow performance over time.
None of these sound like good reasons to change the compatibility level of the database.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 17, 2010 at 4:19 am
evertyhing is fine. all indexes are rebuilt no fragmentaion. My requirement is , just for one query i cannot change the db compat level which is not recomended .
My intention is to rewrite the query in such a way it should return same count as as the non-ansi query returns .
April 17, 2010 at 4:25 am
unfourtunately this is again returning 1125210 rows
April 19, 2010 at 4:17 am
As this query is quite straightforward to translate, prove that these two statements produce different results.
select Count(*)
from dbo.it_1_Revenue__Costs rev,dbo.it_2_elist e,dbo.it_2_LOB l,
dbo.it_2_RegionCurrency rg,dbo.it_4_Months_QTRS mnt,dbo.et_Summary_Income_Statement inc
where rev.itemiid*=inc.dimension_1_revenu
and l.itemiid*=inc.dimension_2_lob
and e.itemiid*=inc.elist
and rg.itemiid*=inc.dimension_2_region
and mnt.itemiid*=inc.dimension_4_months
SELECT COUNT(*)
FROM dbo.et_Summary_Income_Statement inc
RIGHT JOIN dbo.it_1_Revenue__Costs rev
ON rev.itemiid = inc.dimension_1_revenu
RIGHT JOIN dbo.it_2_LOB l
ON l.itemiid = inc.dimension_2_lob
RIGHT JOIN dbo.it_2_elist e
ON e.itemiid = inc.elist
RIGHT JOIN dbo.it_2_RegionCurrency rg
ON rg.itemiid = inc.dimension_2_region
RIGHT JOIN dbo.it_4_Months_QTRS mnt
ON mnt.itemiid = inc.dimension_4_months
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply