Hi,

I've tried whatever I know, if anyone can help me on this it would be
really very helpful.
The application is related to web server hits reporting. The platform
is asp/vb/sql2000/win 2k. The main database consists of the records from
the Web Server Log files and it grows at 0.1 million records per day. There
are currently 1679606 records in the main table t_reportlogspecials, which
has all the data to be reported.
I've given the query & table structure below. The problem is the query
is taking 30 seconds to execute. It takes < 3 seconds when I have only count(*)
but it takes 30 seconds when I have the count(distinct cookieid) & count(distinct
aspsessionid). When I saw the execution plan it showed that it used the
index seek on both cases.
There was also another pblm that I noted when I ran the query in Query
analyzer as a direct SQL stmt it took 23 secs and when I ran it as a Stored
Proc it took 30 secs.

The break up for the execution plan for

1) Query
Total time = 23 sec, index scan cost 19%, index IO cost 5.8, table scan (2
nos) each cost 22%, IO cost 8.342)

2) Stored proc
Total time = 30 sec, index scan cost 37%, index IO cost 3.87, table scan
(2 nos) each cost 21%, IO cost 0.662

I need to know 2 things
1) How to tune or make this aggregation query return the count (distinct..)
values faster. I need to fix this query to return data quickly. This is the
main priority.
2) Why should the query take lesser time to execute than the stored proc
even if it is so, why should the diff be so much.Awaiting replyMohan

==========================

The query is

SELECT day(requestdt) AS timeperiod, COUNT(*) AS requestcount,
COUNT(DISTINCT CookieID) AS ucount,
COUNT(DISTINCT ASPSessionID) AS visitcountFROM t_ReportLogSpecials
WHERE (RequestDT > '10/31/2001') AND (RequestDT < '12/01/2001')
And (FSIAdvertiserID = 246 or ropadvertiserid=246)
And cookieid<>’’ and aspsessionid<>’’GROUP BY day(requestdt)
ORDER BY count(*) desc


The Stored Proc is:

CREATE PROCEDURE [dbo].[GetRetailerMonthlyStats] @intAdvertiserId integer,
@dteDate datetimeAS
SELECT DAY(RequestDT) AS timeperiod, COUNT(*) AS requestcount, COUNT(DISTINCT
ASPSessionID) AS visitcount, COUNT(DISTINCT CookieID)
AS ucountFROM t_ReportLogSpecials
WHERE (RequestDT >= @dteDate) AND (RequestDT < DATEADD(m, 1, @dteDate))
and (FSIAdvertiserID = @intAdvertiserId or ROPAdvertiserID= @intAdvertiserID)
AND (CookieID <> '') AND (ASPSessionID <> '')GROUP BY DAY(RequestDT)
ORDER BY DAY(RequestDT) DESC

This query is used to select the total hits, total unique visitors and
total visit count based on certain conditions on the table t_reportlogspecials
& the results are grouped by day during that period e.g. Monthly.

The Table structure is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_ReportLogSpecials]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_ReportLogSpecials]GO
CREATE TABLE [dbo].[t_ReportLogSpecials] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL , [RequestDT] [datetime] NULL ,
[NewspaperID] [int] NULL , [FSIAdvertiserID] [int] NULL ,
[ROPAdvertiserID] [int] NULL , [FSIID] [int] NULL , [PageID] [int] NULL
,
[AdID] [int] NULL , [LocationID] [int] NULL , [SubregionID] [int] NULL ,
[ManufacturerID] [int] NULL , [CategoryID] [int] NULL ,
[ClientIP] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerName] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CookieID] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserAgent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASPSessionID] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[List] [bit] NULL ,
[Qstring] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]GOALTER TABLE [dbo].[t_ReportLogSpecials] WITH NOCHECK ADD

CONSTRAINT [PK_t_ReportLogSpecials] PRIMARY KEY CLUSTERED ( [ID]
) ON [PRIMARY] GO

CREATE INDEX [IX_FSIID] ON [dbo].[t_ReportLogSpecials]([FSIID]) ON [PRIMARY]GO

CREATE INDEX [IX_RequestDT] ON [dbo].[t_ReportLogSpecials]([RequestDT])
ON [PRIMARY]GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_CookieID] ON [dbo].[t_ReportLogSpecials]
([CookieID]) ')GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_ASPSessionID] ON [dbo].[t_ReportLogSpecials]
([ASPSessionID]) ')GO
CREATE INDEX [IX_Calendar] ON [dbo].[t_ReportLogSpecials]([RequestDT],
[FSIAdvertiserID], [ROPAdvertiserID], [CookieID], [ASPSessionID]) ON [PRIMARY]GO

===============