April 29, 2009 at 11:12 am
Hi.
Im struggeling with a GROUP BY. In my opinion this isn't the correct/best way to do it (when having 100k+ records)
I have a "ClientActivity" table:
CREATE TABLE [dbo].[ClientActivity](
[ClientActivityID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[ClientID] [int] NOT NULL,
[ClientUrlID] [int] NOT NULL,
[Keyword] [nvarchar](255) COLLATE Danish_Norwegian_CI_AS NULL,
[Duration] [int] NULL,
[StartPoint] [bit] NULL,
[EndPoint] [bit] NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_ClientActivity] PRIMARY KEY CLUSTERED
(
[ClientActivityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Indexes are as following:
1. ClientID
2. ClientID & DateCreated
3. ClientUrlID, EndPoint & DateCreated
4. ClientUrlID, StartPoint, EndPoint & DateCreated
5. Keyword.
I want to summarize URL's per period, and get COUNT of "StartPoint and EndPoint" and COUNT of "EndPoint". The below SP is using a View, which combines "ClientActivity" and "ClientUrl" (which contains the URL info, each row is unique - one unique URL per row)
CREATE PROCEDURE ...............
@LanguageID Int,
@Type Int,
@DateStart DateTime,
@DateEnd DateTime
AS
BEGIN
SET NOCOUNT ON;
SELECT
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl] As Url,
c.[ClientUrlTitle] As Title,
c.[EntityID],
c.[EntityType],
AVG(c.[Duration]) As Duration,
CAST(0 As Bit) As StartPoint,
CAST(0 As Bit) As [EndPoint],
MIN([DateCreated]) As DateCreated,
MAX([DateCreated]) As DateLastVisited,
COUNT(c.[ClientActivityID]) As PageViews,
COUNT(DISTINCT c.[ClientID]) As PageViewsUnique,
(SELECT COUNT(ca.ClientActivityID) FROM dbo.ClientActivity As ca WHERE ca.ClientUrlID = c.ClientUrlID AND ca.[EndPoint] = 1 AND (ca.[DateCreated] BETWEEN ISNULL(@DateStart,0) AND ISNULL(@DateEnd,GETDATE()))) As ExitCount,
(SELECT COUNT(DISTINCT ca.ClientID) FROM dbo.ClientActivity As ca WHERE ca.ClientUrlID = c.ClientUrlID AND ca.[EndPoint] = 1 AND ca.[StartPoint] = 1 AND (ca.[DateCreated] BETWEEN ISNULL(@DateStart,0) AND ISNULL(@DateEnd,GETDATE()))) As BounceCount
FROM
[dbo].[vwClientActivity] As c
WHERE
((@LanguageID IS NULL) OR (c.LanguageID = @LanguageID))
AND
((@Type IS NULL) OR (c.[EntityType] = @Type))
AND
(c.[DateCreated] BETWEEN ISNULL(@DateStart,0) AND ISNULL(@DateEnd,GETDATE()))
GROUP BY
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl],
c.[ClientUrlTitle],
c.[EntityID],
c.[EntityType]
ORDER BY
PageViews DESC
END
Is there any way to get the COUNT of "StartPoint" and "EndPoint", without the use of sub-queries?
Thanks in advance
Carsten Petersen
Denmark
Best regards,
Carsten Petersen, Denmark
April 29, 2009 at 11:25 am
Hard for me to judge the thing without the definition of the second table and the view that you're querying.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 11:30 am
GSquared (4/29/2009)
Hard for me to judge the thing without the definition of the second table and the view that you're querying.
Sorry =)
"ClientUrl" table:
CREATE TABLE [dbo].[ClientUrl](
[ClientUrlID] [int] IDENTITY(1,1) NOT NULL,
[LanguageID] [int] NOT NULL,
[EntityID] [int] NULL,
[EntityType] [int] NULL,
[Url] [nvarchar](400) COLLATE Danish_Norwegian_CI_AS NOT NULL,
[Title] [nvarchar](255) COLLATE Danish_Norwegian_CI_AS NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_ClientUrl] PRIMARY KEY CLUSTERED
(
[ClientUrlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
"vwClientActivity" View:
SELECT ca.ClientActivityID, ca.ParentID, cu.LanguageID, ca.ClientID, ca.ClientUrlID, cu.Url AS ClientUrl, cu.Title AS ClientUrlTitle, ca.Keyword AS ClientUrlKeyword, cu.EntityID,
cu.EntityType, ca.Duration, ca.StartPoint, ca.EndPoint, ca.DateCreated
FROM dbo.ClientActivity AS ca INNER JOIN dbo.ClientUrl AS cu ON ca.ClientUrlID = cu.ClientUrlID
Best regards,
Carsten Petersen, Denmark
April 29, 2009 at 11:52 am
Give this a try:
CREATE PROCEDURE ...............
@LanguageID Int,
@Type Int,
@DateStart DateTime,
@DateEnd DateTime
AS
BEGIN
SET NOCOUNT ON;
with ClientExitCount(
ClientUrlID,
ExitCount
) as (
SELECT
ca.ClientUrlID,
COUNT(ca.ClientActivityID)
FROM
dbo.ClientActivity as ca
WHERE
ca.[EndPoint] = 1
AND ca.[DateCreated] BETWEEN
ISNULL(@DateStart,0) -- if @DateStart is null, '1900-01-01'
AND ISNULL(@DateEnd,GETDATE()) -- if @DateEnd is null, today's date and time
),
ClientBounceCount (
ClientUrlID,
BounceCount
) as (
SELECT
ca.ClientUrlID,
COUNT(DISTINCT ca.ClientID)
FROM
dbo.ClientActivity As ca
WHERE
ca.[EndPoint] = 1
AND ca.[StartPoint] = 1
AND ca.[DateCreated] BETWEEN
ISNULL(@DateStart,0) -- if @DateStart is null, '1900-01-01'
AND ISNULL(@DateEnd,GETDATE()) -- if @DateEnd is null, today's date and time
)
SELECT
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl] As Url,
c.[ClientUrlTitle] As Title,
c.[EntityID],
c.[EntityType],
AVG(c.[Duration]) As Duration,
CAST(0 As Bit) As StartPoint,
CAST(0 As Bit) As [EndPoint],
MIN([DateCreated]) As DateCreated,
MAX([DateCreated]) As DateLastVisited,
COUNT(c.[ClientActivityID]) As PageViews,
COUNT(DISTINCT c.[ClientID]) As PageViewsUnique,
cec.ExitCount,
cbc.BounceCount
FROM
[dbo].[vwClientActivity] As c
inner join ClientExitCount cec
on (c.ClientUrlID = cec.ClientUrlID)
inner join ClientBounceCount cbc
on (c.ClientUrlID = cbc.ClientUrlID)
WHERE
((@LanguageID IS NULL) OR (c.LanguageID = @LanguageID))
AND
((@Type IS NULL) OR (c.[EntityType] = @Type))
AND
(c.[DateCreated] BETWEEN ISNULL(@DateStart,0) AND ISNULL(@DateEnd,GETDATE()))
GROUP BY
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl],
c.[ClientUrlTitle],
c.[EntityID],
c.[EntityType],
cec.ExitCount,
cbc.BounceCount
ORDER BY
PageViews DESC
END
April 29, 2009 at 1:35 pm
Lynn Pettis (4/29/2009)
Give this a try:
CREATE PROCEDURE ...............
@LanguageID Int,
@Type Int,
@DateStart DateTime,
@DateEnd DateTime
AS
BEGIN
SET NOCOUNT ON;
with ClientExitCount(
ClientUrlID,
ExitCount
) as (
SELECT
ca.ClientUrlID,
COUNT(ca.ClientActivityID)
FROM
dbo.ClientActivity as ca
WHERE
ca.[EndPoint] = 1
AND ca.[DateCreated] BETWEEN
ISNULL(@DateStart,0) -- if @DateStart is null, '1900-01-01'
AND ISNULL(@DateEnd,GETDATE()) -- if @DateEnd is null, today's date and time
),
ClientBounceCount (
ClientUrlID,
BounceCount
) as (
SELECT
ca.ClientUrlID,
COUNT(DISTINCT ca.ClientID)
FROM
dbo.ClientActivity As ca
WHERE
ca.[EndPoint] = 1
AND ca.[StartPoint] = 1
AND ca.[DateCreated] BETWEEN
ISNULL(@DateStart,0) -- if @DateStart is null, '1900-01-01'
AND ISNULL(@DateEnd,GETDATE()) -- if @DateEnd is null, today's date and time
)
SELECT
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl] As Url,
c.[ClientUrlTitle] As Title,
c.[EntityID],
c.[EntityType],
AVG(c.[Duration]) As Duration,
CAST(0 As Bit) As StartPoint,
CAST(0 As Bit) As [EndPoint],
MIN([DateCreated]) As DateCreated,
MAX([DateCreated]) As DateLastVisited,
COUNT(c.[ClientActivityID]) As PageViews,
COUNT(DISTINCT c.[ClientID]) As PageViewsUnique,
cec.ExitCount,
cbc.BounceCount
FROM
[dbo].[vwClientActivity] As c
inner join ClientExitCount cec
on (c.ClientUrlID = cec.ClientUrlID)
inner join ClientBounceCount cbc
on (c.ClientUrlID = cbc.ClientUrlID)
WHERE
((@LanguageID IS NULL) OR (c.LanguageID = @LanguageID))
AND
((@Type IS NULL) OR (c.[EntityType] = @Type))
AND
(c.[DateCreated] BETWEEN ISNULL(@DateStart,0) AND ISNULL(@DateEnd,GETDATE()))
GROUP BY
c.[LanguageID],
c.[ClientUrlID],
c.[ClientUrl],
c.[ClientUrlTitle],
c.[EntityID],
c.[EntityType],
cec.ExitCount,
cbc.BounceCount
ORDER BY
PageViews DESC
END
I'll try that. Thx Lynn =)
Best regards,
Carsten Petersen, Denmark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply