Rewrite query - 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

General SQL Server Forums

New to SQL Server Programming

Rewrite query

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-11-19 : 04:45:18

Query below causes a lock that lasts for over ten hours, how can I rewrite the vhe view and update to one query producing the same result?

create view [dbo].[view_DeclareBuilding]
as
SELECT
r2.LANKOD
,r2.KOMKOD
,r2.RNPREGBY as IdPrefixById
,r2.RIDREGBY as IdById
,count(r2.RNPREGBY) as declares
FROM BA_REGENH_01 r1
JOIN BA_REGBYG_50A r2 ON r1.FNR = r2.FNR
WHERE
AND NOT EXISTS (SELECT 1 FROM BA_FastighetUndantag fu WHERE fu.Fnr = r1.FNR)
AND NOT EXISTS (SELECT 1 FROM BA_ByggnadUndantag bu WHERE bu.IdPrefixById = r2.RNPREGBY and bu.IdById = r2.RIDREGBY)
group by r2.LANKOD, r2.KOMKOD, r2.RNPREGBY, r2.RIDREGBY

UPDATE DE
SET nr_buildings = (select count(declares) as declares
from [view_DeclareBuilding] DG
where DE.LANKOD = DG.LANKOD
and DE.KOMKOD = DG.KOMKOD
group by DG.LANKOD, DG.KOMKOD)
FROM BA_KOMKOD DE

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 05:08:04

SELECT
r2.LANKOD
,r2.KOMKOD
,count(r2.RNPREGBY) as declares
into #a
FROM BA_REGENH_01 r1
JOIN BA_REGBYG_50A r2 ON r1.FNR = r2.FNR
WHERE
AND NOT EXISTS (SELECT 1 FROM BA_FastighetUndantag fu WHERE fu.Fnr = r1.FNR)
AND NOT EXISTS (SELECT 1 FROM BA_ByggnadUndantag bu WHERE bu.IdPrefixById = r2.RNPREGBY and bu.IdById = r2.RIDREGBY)
group by r2.LANKOD, r2.KOMKOD, r2.RNPREGBY, r2.RIDREGBY

create clustered index ix on #a (LANKOD, KOMKOD)

select LANKOD, KOMKOD, declares = count(declares) -- are you sure about the count here?
into #b
from #a
group by LANKOD, KOMKOD

create unique clustered index ix on #b (LANKOD, KOMKOD)

UPDATE DE
SET nr_buildings = b declares
from BA_KOMKOD DE
join #b
on DE.LANKOD = b.LANKOD
and DE.KOMKOD = b.KOMKOD

Doing it this way minimises the locking for updates and will also show you which part is slow.
If you need to make sure that underlying tables aren't updated during the process then you will have to hold locks but you should be able to optimise easily.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

maevr
Posting Yak Master

169 Posts

Posted - 2010-11-19 : 05:27:31

Thanks for the fast reply!

The output your query produces does not match my former results, it writes 1 in every row.

The view's count is set to 1 if where is true.

The update counts every row in view and groups them by lankod and komkod giving more that 1 in every row.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 05:36:14

Don't immediately see why there is a difference.
try

SELECT
r2.LANKOD
,r2.KOMKOD
,1 as declares
into #a
FROM BA_REGENH_01 r1
JOIN BA_REGBYG_50A r2 ON r1.FNR = r2.FNR
WHERE
AND NOT EXISTS (SELECT 1 FROM BA_FastighetUndantag fu WHERE fu.Fnr = r1.FNR)
AND NOT EXISTS (SELECT 1 FROM BA_ByggnadUndantag bu WHERE bu.IdPrefixById = r2.RNPREGBY and bu.IdById = r2.RIDREGBY)
group by r2.LANKOD, r2.KOMKOD, r2.RNPREGBY, r2.RIDREGBY

create clustered index ix on #a (LANKOD, KOMKOD)

select LANKOD, KOMKOD, declares = sum(declares)
into #b
from #a
group by LANKOD, KOMKOD

create unique clustered index ix on #b (LANKOD, KOMKOD)

UPDATE DE
SET nr_buildings = b.declares
from BA_KOMKOD DE
join #b
on DE.LANKOD = b.LANKOD
and DE.KOMKOD = b.KOMKOD

You can see from #b the updtes that are going to be made.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources