June 18, 2010 at 9:02 am
I have a table of Programs (Master table) and viewers (Detail table). The master table has two fields criteria like featured and IsActive, I want results in single SELECT query from both tables. Like Total Programs 150 total total viewed 790.
Select
(Select Count(ProgramTracking.Id)
from ProgramMetaInfo
inner join ProgramTracking with(nolock) on ProgramTracking.ProgramId = ProgramMetaInfo.Id
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
) as TotalViews,
(Select Count(ProgramMetaInfo.Id)
from ProgramMetaInfo
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
) as TotalPrograms
I want not to repeat the two different SELECT One for TotalPrograms and One for TotalViews.
Is there any way to optimize this query for efficient results?
Shamshad Ali
June 18, 2010 at 9:13 am
With nothing to test against the following is a guess:
select
count(distinct pmi.Id) as TotalPrograms,
count(pt.Id) as TotalViews
from
dbo.ProgramMetaInfo pmi
inner join dbo.ProgramTracking pt
on (pmi.Id = pt.ProgramId)
where
pmi.isdeleted = 0
and pmi.ispublished = 1
and (pmi.IsFeatured = 1
or pmi.IsSellable = 1
or pmi.AccessibilityId = 0)
June 18, 2010 at 9:20 am
If there are no related records found, the above query will not count the record from master table. Therefore, LEFT JOIN is more appropriate.
June 18, 2010 at 9:21 am
Well, here the programs are not bound, I mean the total No of program even not viewed should be counted. so there is difference in results of both queries.
Left Join worked with that.
There are two more tables, Like Rating and RatingAvg tabe join with MasterTable. Now its not giving me the results when i tried Left join at all.
the actual query produces following results:
TotalViewsTotaProgramsTotalRatingTotalVotes
=====================================================
1113550763.660457516385
Select
(Select Count(ProgramTracking.Id)
from ProgramMetaInfo
inner join ProgramTracking with(nolock) on ProgramTracking.ProgramId = ProgramMetaInfo.Id
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
) as TotalViews,
(Select Count(ProgramMetaInfo.Id)
from ProgramMetaInfo
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
) as TotalPrograms,
IsNull(
(Select sum(IsNull(AverageRating,0))/count(ProgramMetaInfo.Id)
from ProgramMetaInfo
inner join RatingAverage with(nolock) on RatingAverage.ObjectId = ProgramMetaInfo.Id and RatingAverage.ObjectTypeId=2
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
),0
) as TotalRating,
(Select Count(Rating.ID)
from ProgramMetaInfo
inner join Rating with(nolock) on Rating.RatedObjectId = ProgramMetaInfo.Id and Rating.RatedObjectType=2
where
ProgramMetaInfo.isdeleted = 0 and ProgramMetaInfo.ispublished = 1
and (
ProgramMetaInfo.IsFeatured = 1
Or ProgramMetaInfo.IsSellable = 1
Or ProgramMetaInfo.AccessiblityId = 0
)
) as TotalVotes
Please help, I made following change just added three tables:
select count(distinct m.id), count(t.ProgramId), sum(IsNull(AverageRating,0)) from ProgramMetaInfo m
left join ProgramTracking t on m.id = t.ProgramId
left join RatingAverage r on r.ObjectId = m.Id and r.ObjectTypeId = 2
where m.isdeleted = 0 and m.ispublished = 1
and (m.IsFeatured = 1 Or m.IsSellable = 1 Or m.AccessiblityId = 0)
Shamshad Ali.
June 18, 2010 at 10:30 am
Table DDL, sample data, expected results based on sample data.
Help us help you.
June 18, 2010 at 11:00 am
Without table DDL and data sample setup it is hard to test
, but try:
select
count(distinct pmi.Id) as TotalPrograms
,count(pt.Id) as TotalViews
,sum(IsNull(ra.AverageRating,0)) / count(distinct pmi.Id) as TotalRating
,sum( case when rt.ID is null then 0 else 1 end) as TotalVotes
from
dbo.ProgramMetaInfo pmi
left join dbo.ProgramTracking pt with(nolock)
on (pmi.Id = pt.ProgramId)
left join RatingAverage ra with(nolock)
on ra.ObjectId = pmi.Id and ra.ObjectTypeId=2
left join Rating rt with(nolock) on rt.RatedObjectId = pmi.Id and rt.RatedObjectType=2
where
pmi.isdeleted = 0
and pmi.ispublished = 1
and (pmi.IsFeatured = 1
or pmi.IsSellable = 1
or pmi.AccessibilityId = 0)
Next time please follow http://www.sqlservercentral.com/articles/Best+Practices/61537/ to represent your question.
June 18, 2010 at 11:06 am
I do have to point out I disagree with the use of the WITH (NOLOCK) hints in most of the queries presented on this thread. You all are aware of the potential issues that this brings to the resultant data returned by the queries, right?
You are allowing dirty reads of the data that may affect the results.
June 18, 2010 at 1:20 pm
I am assuming this query is run for reporting purposes, therefore dirty reads might be acceptable.
June 18, 2010 at 1:35 pm
Eugene Elutin (6/18/2010)
I am assuming this query is run for reporting purposes, therefore dirty reads might be acceptable.
It depends on the reporting ... in many cases the fact that it is for reporting doesn't make it any more acceptable to have dirty reads.
June 18, 2010 at 2:04 pm
Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.
I can be wrong, of cause.
June 19, 2010 at 12:27 am
I think there might be a way for the single query to be converted but no idea how to do this. Time is constraint, i will look into it and post it agian in more detail, but i wanted to optimize this query as every user hit site runs this SP. The only problem I see is one table require LEFT JOIN and the other two tables require INNER JOIN with program table. so when doing this it changes the output.
let it continue later on.
Shamshad Ali.
June 19, 2010 at 12:55 am
Eugene Elutin (6/18/2010)
Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.I can be wrong, of cause.
Bad assumption. Many people use it because they think it helps performance but are unaware of the other issues that may arise from its use.
June 19, 2010 at 2:35 am
Lynn Pettis (6/19/2010)
Eugene Elutin (6/18/2010)
Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.I can be wrong, of cause.
Bad assumption. Many people use it because they think it helps performance but are unaware of the other issues that may arise from its use.
Very pessimistic view on other people :-D. You do know "other issues that may arise from its use", but recon that most of others are in dark about it. Contrary, I think most of the time, developers using hints, do know enough about them. So, in my experience, I found that "many people" use itto help performcance when dirty reads are acceptable.
June 19, 2010 at 11:19 am
Eugene Elutin (6/19/2010)
Lynn Pettis (6/19/2010)
Eugene Elutin (6/18/2010)
Ok, I assume that if the person used it, he knows what "WITH (NOLOCK) does, therefore it must be acceptable in their case.I can be wrong, of cause.
Bad assumption. Many people use it because they think it helps performance but are unaware of the other issues that may arise from its use.
Very pessimistic view on other people :-D. You do know "other issues that may arise from its use", but recon that most of others are in dark about it. Contrary, I think most of the time, developers using hints, do know enough about them. So, in my experience, I found that "many people" use itto help performcance when dirty reads are acceptable.
Not pessimistic, pragmatic. I see it too often and when questioned most use it because they've heard it improves processing and avoids blocking, but don't understand what happens behind the scenes when you allow dirty reads.
June 19, 2010 at 12:52 pm
Eugene Elutin (6/19/2010)
Contrary, I think most of the time, developers using hints, do know enough about them. So, in my experience, I found that "many people" use itto help performcance when dirty reads are acceptable.
I asked in a TechEd presentation last year how many people used nolock often (session on query hints and plan guides). About 1/4 of the room put up their hands (20 or so people). I asked how many of them knew that it allowed dirty reads and possibly duplicated or missed data. All but one person put their hands down.
It's a very small percentage of people who research a hint beyond 'it helped in this case'. Nolock is particularly bad because people use it when giving answers on forums without bothering to explain what it does (assuming that they even know themselves) so people get the impression that it's a good thing to use because the 'experts' are using it.
Much the same goes for the majority of join and index hints that I've seen in production code. The developer's using it because they were told to or because it helped in that case or because they had a misconception about what it does rather from actual understanding of what the hints do and under what circumstances they would actually be good to add. I've worked with someone who insisted that every single join got the MERGE hint because 'it's the fastest join type'. No actual understanding of what a Merge join is and under what circumstances it's the appropriate join. I'm not even talking about a junior here. This was a senior developer with 6 or more years of development experience.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply