March 20, 2012 at 3:31 am
I would like to simplify the query.
Please find the attachment for the same.
Is there any way to make it more simple?
March 20, 2012 at 4:32 am
For those that want the code but don't want to download a .doc, here is the original query.
DECLARE @keydoc INT
SET @keydoc = 539428
SELECT DISTINCT a.keydoc, a.keyfreesiteaccess, a.keyfreesiteaccesspublishable
FROM objectviews..aisarticle a
LEFT JOIN (
SELECT CASE WHEN x0_0.keydoc = @KeyDoc THEN x0_0.keyfreesiteaccessoverride END AS keyfreesiteaccessoverride, x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
WHERE x0_0.keyfreesiteaccessoverride IS NOT NULL
) b ON a.keydoc = b.keydoc
LEFT JOIN (
SELECT CASE WHEN x0_0.keydoc = @KeyDoc THEN 0 END AS keyfreesiteaccesspublishableexclusivefeature, x0_0.keydoc
FROM objectviews..aisarticle x0_0
INNER JOIN objectviews..aisarticletype x1_0 ON x0_0.keyarticletype = x1_0.keyarticletype
WHERE x1_0.keyfreesiteaccess = 0 AND x0_0.keyarticlemarketingbadge = 0
) c ON a.keydoc = c.keydoc
LEFT JOIN (
SELECT CASE WHEN x0_0.keydoc = @KeyDoc THEN 0 END AS keyfreesiteaccesspublishabledepartments, x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
INNER JOIN objectviews.dbo.aisarticleindex x1_0 ON x0_0.keydoc = x1_0.keydoc
LEFT JOIN objectviews.dbo.aisarticletype x2_0 ON x0_0.keyarticletype = x2_0.keyarticletype
WHERE x0_0.KeyDoc = @KeyDoc AND x0_0.keyfreesiteaccessoverride IS NULL AND (
(x1_0.keyarticlesubject = 12 AND x1_0.primarysubject = 1 AND x0_0.articleamendmentcount IS NULL
) OR (x1_0.keyarticlesubject IN (502, 503) AND x1_0.primarysubject = 1
)
) AND (x0_0.keyarticlemarketingbadge IS NULL OR x0_0.keyarticlemarketingbadge = 9)
AND (x2_0.keyarticletype IS NULL OR x2_0.keyfreesiteaccess = 0)
) d ON a.keydoc = d.keydoc
LEFT JOIN (
SELECT CASE WHEN x0_0.keydoc = @KeyDoc THEN 0 END AS keyfreesiteaccesspublishableinstnfiling, x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
LEFT JOIN objectviews.dbo.aisarticletype x1_0 ON x0_0.keyarticletype = x1_0.keyarticletype
INNER JOIN objectviews.dbo.aisdocsreference x2_0 ON x0_0.keydoc = x2_0.keydoc
INNER JOIN objectviews.dbo.instnfiling x3_0 ON x2_0.keydocreferenced = x3_0.keydoc
INNER JOIN objectviews.dbo.instnfilingtype x4_0 ON x3_0.keyinstnfilingtype = x4_0.keyinstnfilingtype
INNER JOIN objectviews.dbo.docsfilingalias x5_0 ON x4_0.filingalias = x5_0.alias
INNER JOIN objectviews.dbo.documentfile g ON x3_0.keydoc = g.keydoc
WHERE x0_0.KeyDoc = @KeyDoc AND x0_0.keyfreesiteaccessoverride IS NULL AND x5_0.
keyinteractiveservicelevel = 0 AND (
(x4_0.keyinstnfilingtype IN ('PR', 'MR', 'ER') OR g.keyfilesource = 1
) AND (x0_0.keyarticlemarketingbadge IS NULL OR x0_0.keyarticlemarketingbadge = 9
) AND (x1_0.keyarticletype IS NULL OR x1_0.keyfreesiteaccess = 0)
)
) e ON a.keydoc = e.keydoc
WHERE a.keydoc = @KeyDoc
For the OP, please read this article about how best to present your questions[/url]. If you include DDL and sample data in your question, then the unpaid volunteers of this site will find it much easier to provide you with tested, working code.
March 20, 2012 at 4:37 am
You could try to put some of that logic in views or iTVFs.
I recently wrote a blog post on how to deal with huge statements. You can find some tips there.
http://spaghettidba.com/2012/03/15/how-to-eat-a-sql-elephant/
-- Gianluca Sartori
March 20, 2012 at 4:59 am
In a query you've posted, all three columns you select are from the same (first) table left joined to all other sub-queries. Therefore it can be simply written as:
SELECT DISTINCT a.keydoc, a.keyfreesiteaccess, a.keyfreesiteaccesspublishable
FROM objectviews..aisarticle a
WHERE a.keydoc = @KeyDoc
You may want to post complete list of columns in SELECT...
March 20, 2012 at 5:09 am
My original query was consisted using 5 different temp tables.
@KeyDoc int
select distinct x0_0.KeyDoc,x0_0.KeyFreeSiteAccess,x0_0.KeyFreeSiteAccessOverride,x0_0.KeyFreeSiteAccessPublishable
INTO #FreeSiteAccess
from ObjectViews..AISArticle x0_0
where x0_0.KeyDoc=@KeyDoc
select case when x0_0.KeyDoc=@KeyDoc then x0_0.KeyFreeSiteAccessOverride end as KeyFreeSiteAccessOverride,x0_0.KeyDoc
INTO #FreeSiteAccessOverride
from ObjectViews.dbo.AISArticle x0_0
where x0_0.KeyDoc=@KeyDoc
and x0_0.KeyFreeSiteAccessOverride is not null
select case when x0_0.KeyDoc=@KeyDoc then 0 end as KeyFreeSiteAccessPublishableExclusiveFeature,x0_0.KeyDoc
INTO #FreeSiteAccessExclusiveFeature
from ObjectViews..AISArticle x0_0
INNER JOIN ObjectViews..AISArticleType x1_0 on x0_0.KeyArticleType=x1_0.KeyArticleType
where x1_0.KeyFreeSiteAccess=0 and x0_0.KeyArticleMarketingBadge=0
select case when x0_0.KeyDoc=@KeyDoc then 0 end as KeyFreeSiteAccessPublishableDepartments,x0_0.KeyDoc
INTO #FreeSiteAccessDepartments
from ObjectViews.dbo.AISArticle x0_0
inner join ObjectViews.dbo.AISArticleIndex x1_0 on x0_0.KeyDoc=x1_0.KeyDoc
left join ObjectViews.dbo.AISArticleType x2_0 on x0_0.KeyArticleType=x2_0.KeyArticleType
where x0_0.KeyDoc=@KeyDoc and x0_0.KeyFreeSiteAccessOverride is null
and ((x1_0.KeyArticleSubject = 12 and x1_0.PrimarySubject=1 and x0_0.ArticleAmendmentCount is null) OR (x1_0.KeyArticleSubject in (502,503) and x1_0.PrimarySubject=1))
and (x0_0.KeyArticleMarketingBadge is NULL OR x0_0.KeyArticleMarketingBadge=9)
and (x2_0.KeyArticleType is null or x2_0.KeyFreeSiteAccess=0)
select case when x0_0.KeyDoc=@KeyDoc then 0 end as KeyFreeSiteAccessPublishableInstnFiling,x0_0.KeyDoc
INTO #FreeSiteAccessInstnFilings
from ObjectViews.dbo.AISArticle x0_0
left join ObjectViews.dbo.AISArticleType x1_0 on x0_0.KeyArticleType=x1_0.KeyArticleType
inner join ObjectViews.dbo.AISDocsReference x2_0 on x0_0.KeyDoc=x2_0.KeyDoc
inner join ObjectViews.dbo.InstnFiling x3_0 on x2_0.KeyDocReferenced=x3_0.KeyDoc
inner join ObjectViews.dbo.InstnFilingType x4_0 on x3_0.KeyInstnFilingType=x4_0.KeyInstnFilingType
inner join ObjectViews.dbo.DocsFilingAlias x5_0 on x4_0.FilingAlias=x5_0.Alias
inner join ObjectViews.dbo.DocumentFile g on x3_0.KeyDoc=g.KeyDoc
where x0_0.KeyDoc=@KeyDoc and x0_0.KeyFreeSiteAccessOverride is null
and x5_0.KeyInteractiveServiceLevel=0 and ((x4_0.KeyInstnFilingType in ('PR','MR','ER') or g.KeyFileSource=1)
and (x0_0.KeyArticleMarketingBadge is NULL OR x0_0.KeyArticleMarketingBadge=9)
and (x1_0.KeyArticleType is null or x1_0.KeyFreeSiteAccess=0))
select x0.KeyDoc,
case when x3.KeyFreeSiteAccessOverride is not null then x3.KeyFreeSiteAccessOverride
when x4.KeyFreeSiteAccessPublishableExclusiveFeature=0 then 0
when x1.KeyFreeSiteAccessPublishableDepartments=0 or x2.KeyFreeSiteAccessPublishableInstnFiling=0 then 0 else x0.KeyFreeSiteAccessPublishable
end as KeyFreeSiteAccessPublishable,
x0.KeyFreeSiteAccess
from #FreeSiteAccess x0
left join #FreeSiteAccessDepartments x1 on x0.KeyDoc=x1.KeyDoc
left join #FreeSiteAccessInstnFilings x2 on x0.KeyDoc=x2.KeyDoc
left join #FreeSiteAccessOverride x3 on x0.KeyDoc=x3.KeyDoc
left join #FreeSiteAccessExclusiveFeature x4 on x0.KeyDoc=x4.KeyDoc
where x0.KeyDoc=@KeyDoc
drop table #FreeSiteAccess
drop table #FreeSiteAccessOverride
drop table #FreeSiteAccessExclusiveFeature
drop table #FreeSiteAccessDepartments
drop table #FreeSiteAccessInstnFilings
So i have modified as above. But still it requires to simplify.
Please let me know if more details are required.
March 20, 2012 at 5:16 am
Sorry to say that, bu I would have left it with the temporary tables, unless performance was not acceptable.
Is there a particular reason why are you merging everything in a single statement?
-- Gianluca Sartori
March 20, 2012 at 5:44 am
Users want to simplify the query for their need.
March 20, 2012 at 6:15 am
But joining all temps into one is not simplification of the query! It is quite opposite!
Also, as I've said in my previous post, your select statement only gets columns from one table, so it's impossible to see the need of LEFT JOINS used.
I cannot download the document you've attached, can you please confirm the list of columns you really want to select?
March 20, 2012 at 6:31 am
Here are the list of columns:
SELECT DISTINCT a.keydoc,
a.keyfreesiteaccess,
a.keyfreesiteaccesspublishable
March 20, 2012 at 7:04 am
Then you don't need any of the left joins! They make no sense in the query as nothing is taken from them, and select is not restricted by them in anyway.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply