February 1, 2012 at 9:11 am
Hallo,
I need to improve this slow 00:07 Second View but I don't Know how!
ALTER VIEW
Operation.MemberSessionStatistics
(
GroupCategory,
GroupValue,
NumberOfSessions,
AvarageSessionDuration
)
AS
SELECT
'Country' AS GroupCategory,
CountryId AS GroupValue ,
COUNT(LoginTime) AS NumberOfSessions,
AVG( DATEDIFF ( SECOND , LoginTime,EndTime)) AS AvarageSessionDuration
FROM
(
SELECT
CountryId AS CountryId,
Msessions.LoginDateTime AS LoginTime,
Msessions.EndDateTime AS EndTime
FROM
Operation.Members Omembers
LEFT OUTER JOIN
Operation.MemberSessions Msessions
ON
(Omembers.Id = Msessions.MemberId)
WHERE
EndDateTime IS NOT NULL
)
AS CountryGROUP
GROUP BY CountryId
UNION ALL
SELECT
'Gender' AS GroupCategory,
GenderId AS GroupValue,
COUNT(LoginTime) AS NumberOfSessions,
AVG(CAST(( DATEDIFF ( SECOND , LoginTime, EndTime ) ) AS BIGINT)) AS AvarageSessionDuration
FROM
(
SELECT
Omembers.GenderId AS GenderId,
Msessions.LoginDateTime AS LoginTime,
Msessions.EndDateTime AS EndTime
FROM
Operation.Members Omembers
LEFT OUTER JOIN
Operation.MemberSessions Msessions
ON(Omembers.Id = Msessions.MemberId)
WHERE
EndDateTime IS NOT NULL
)
AS GenderGROUP
GROUP BY GenderId
UNION ALL
SELECT
'Mariatl Status'AS GroupCategory,
MaritalIdAS GroupValue ,
COUNT(LoginTime)AS NumberOfSessions,
AVG( DATEDIFF ( SECOND, LoginTime, EndTime ) ) AS AvarageSessionDuration
FROM
(
SELECT
Omembers.MaritalStatusId AS MaritalId,
Msessions.LoginDateTime AS LoginTime,
Msessions.EndDateTime AS EndTime
FROM
Operation.Members Omembers
LEFT OUTER JOIN
Operation.MemberSessions Msessions
ON
(Omembers.Id = Msessions.MemberId)
WHERE
EndDateTime IS NOT NULL
)
AS MariGROUP
GROUP BY MaritalId
GO
February 1, 2012 at 9:27 am
Since you are using UNION and the tables used look to be the same and the WHERE clauses are the same in them, most if not all of them could be consolidated into one query rather than using all of the UNIONs. This will cause you to have more columns instead of additional rows, but if you have to, pivot the results rather than running all of those many queries and your performance should be much better.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
February 1, 2012 at 12:48 pm
Sound's good. but I'm trying to count(logindatetime) the date column's and it doesn't works.
February 6, 2012 at 1:52 am
REATE VIEW
Operation.MemberSessionStatistics
(
GroupCategory ,
GroupValue ,
NumberOfSessions ,
AverageSessionDuration
)
AS
SELECT
GroupCategory=CASE
WHEN GROUPING (Countries.Name) = 0
THEN N'Country'
WHEN GROUPING (Genders.Name) = 0
THEN N'Gender'
WHEN GROUPING (MaritalStatuses.Name) = 0
THEN N'Marital Status'
END ,
GroupValue=CASE
WHEN GROUPING (Countries.Name) = 0
THEN Countries.Name
WHEN GROUPING (Genders.Name) = 0
THEN Genders.Name
WHEN GROUPING (MaritalStatuses.Name) = 0
THEN MaritalStatuses.Name
END ,
NumberOfSessions= COUNT (*) ,
AverageSessionDuration= AVG (CAST (DATEDIFF (SECOND , MemberSessions.LoginDateTime , MemberSessions.EndDateTime) AS BIGINT))
FROM
Operation.MemberSessions AS MemberSessions
INNER JOIN
Operation.Members AS Members
ON
MemberSessions.MemberId = Members.Id
INNER JOIN
Lists.Countries AS Countries
ON
Members.CountryId = Countries.Id
INNER JOIN
Lists.Genders AS Genders
ON
Members.GenderId = Genders.Id
LEFT OUTER JOIN
Lists.MaritalStatuses AS MaritalStatuses
ON
Members.MaritalStatusId = MaritalStatuses.Id
WHERE
MemberSessions.EndDateTime IS NOT NULL
GROUP BY
GROUPING SETS
(
(Countries.Name) ,
(Genders.Name) ,
(MaritalStatuses.Name)
);
GO
Thank You!!! I didn't do it with the pivot but you gave my idea!!
Execution time now is 3 seconds!
February 6, 2012 at 3:25 pm
You're welcome, I'm glad I could help.
Mark
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply