3 min 30 seconds for a Stored Proc execution

In my stored proc I was using SELECT count(*) on table for each user. This
stored proc was finishing in 30 seconds. And customer was happy with the
results.
Now there is a change in my stored proc and i have to use SELECT count (
distinct startdate ) on that table where startdate is of type "int". After
making this change my stored proc is finishing in 3 min 30 seconds which is
unacceptable for customer.
Is there a way to speed up the execution of this stored procedure? There is
already an index on this table which includes "startdate" column.
This is the table structure:
=================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[interactions]
GO
CREATE TABLE [dbo].[interactions] (
[companyid] [int] NOT NULL ,
[interactionid] [bigint] NOT NULL ,
[startdate] [int] NOT NULL ,
[resourcetype] [int] NOT NULL ,
[resourceid] [int] NOT NULL ,
[originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[duration] [int] NOT NULL ,
[interactiontype] [int] NOT NULL ,
[status] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
(
[companyid],
[interactionid]
) ON [PRIMARY]
GO
CREATE INDEX [ix_interactions_startdate] ON
[dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
GO
This is my query:
===========
select @.v_NumberOfCalls = count( distinct i.startdate )
from interactions i
inner join historyactions ha ON
( i.interactionid = ha.interactionid ) AND
( i.companyid = ha.companyid ) AND
( ha.companyid = @.a_CompanyId ) AND
( ha.workgroupid = @.a_WorkGroupId ) AND
( ha.actionid in ( 70005 ) ) AND
( ha.userid = @.v_userid ) AND
( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
Thanks
SyedAre there really more than one row with the same startdate for a given
CompanyID? If not then why the distinct? You probably want to have the
clustered index on CompanyId, Startdate instead of the PK. You will
probably get less page splits and faster results.
Andrew J. Kelly SQL MVP
"JY" <jy1970us@.yahoo.com> wrote in message
news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
> In my stored proc I was using SELECT count(*) on table for each user. This
> stored proc was finishing in 30 seconds. And customer was happy with the
> results.
> Now there is a change in my stored proc and i have to use SELECT count (
> distinct startdate ) on that table where startdate is of type "int". After
> making this change my stored proc is finishing in 3 min 30 seconds which
> is unacceptable for customer.
> Is there a way to speed up the execution of this stored procedure? There
> is already an index on this table which includes "startdate" column.
>
> This is the table structure:
> =================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[interactions]
> GO
> CREATE TABLE [dbo].[interactions] (
> [companyid] [int] NOT NULL ,
> [interactionid] [bigint] NOT NULL ,
> [startdate] [int] NOT NULL ,
> [resourcetype] [int] NOT NULL ,
> [resourceid] [int] NOT NULL ,
> [originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [duration] [int] NOT NULL ,
> [interactiontype] [int] NOT NULL ,
> [status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
> CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
> (
> [companyid],
> [interactionid]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ix_interactions_startdate] ON
> [dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
> GO
>
> This is my query:
> ===========
> select @.v_NumberOfCalls = count( distinct i.startdate )
> from interactions i
> inner join historyactions ha ON
> ( i.interactionid = ha.interactionid ) AND
> ( i.companyid = ha.companyid ) AND
> ( ha.companyid = @.a_CompanyId ) AND
> ( ha.workgroupid = @.a_WorkGroupId ) AND
> ( ha.actionid in ( 70005 ) ) AND
> ( ha.userid = @.v_userid ) AND
> ( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
>
> Thanks
> Syed
>|||There are 1624034 rows in "interactions" table.
There are 20764239 rows in "historyactions" table.
I ran Index Tuning Wizard for my query and it told me that query is ok and
it is using the "ix_interactions_startdate" index 100%.
Any input please.
Thanks
"JY" <jy1970us@.yahoo.com> wrote in message
news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
> In my stored proc I was using SELECT count(*) on table for each user. This
> stored proc was finishing in 30 seconds. And customer was happy with the
> results.
> Now there is a change in my stored proc and i have to use SELECT count (
> distinct startdate ) on that table where startdate is of type "int". After
> making this change my stored proc is finishing in 3 min 30 seconds which
> is unacceptable for customer.
> Is there a way to speed up the execution of this stored procedure? There
> is already an index on this table which includes "startdate" column.
>
> This is the table structure:
> =================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[interactions]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[interactions]
> GO
> CREATE TABLE [dbo].[interactions] (
> [companyid] [int] NOT NULL ,
> [interactionid] [bigint] NOT NULL ,
> [startdate] [int] NOT NULL ,
> [resourcetype] [int] NOT NULL ,
> [resourceid] [int] NOT NULL ,
> [originator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [destinator] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [duration] [int] NOT NULL ,
> [interactiontype] [int] NOT NULL ,
> [status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[interactions] WITH NOCHECK ADD
> CONSTRAINT [pk_interactions] PRIMARY KEY CLUSTERED
> (
> [companyid],
> [interactionid]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ix_interactions_startdate] ON
> [dbo].[interactions]([companyid], [startdate]) ON [PRIMARY]
> GO
>
> This is my query:
> ===========
> select @.v_NumberOfCalls = count( distinct i.startdate )
> from interactions i
> inner join historyactions ha ON
> ( i.interactionid = ha.interactionid ) AND
> ( i.companyid = ha.companyid ) AND
> ( ha.companyid = @.a_CompanyId ) AND
> ( ha.workgroupid = @.a_WorkGroupId ) AND
> ( ha.actionid in ( 70005 ) ) AND
> ( ha.userid = @.v_userid ) AND
> ( i.startdate between @.v_StartDateTime and @.v_EndDateTime )
>
> Thanks
> Syed
>|||yes there really are more than one row with the same startdate for a given
CompanyID. We cannot change the primary key since our software is a
reporting software and only use this database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiazxhOMGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Are there really more than one row with the same startdate for a given
> CompanyID? If not then why the distinct? You probably want to have the
> clustered index on CompanyId, Startdate instead of the PK. You will
> probably get less page splits and faster results.
> --
> Andrew J. Kelly SQL MVP
>
> "JY" <jy1970us@.yahoo.com> wrote in message
> news:OB6If.1405$_D5.131478@.news20.bellglobal.com...
>|||I am not asking you to change the PK definition, only to change which index
is clustered. What kind of indexes do you have on the HistoryActions table?
What is the query plan showing?
Andrew J. Kelly SQL MVP
"JY" <jy1970us@.yahoo.com> wrote in message
news:CV7If.1679$_D5.136961@.news20.bellglobal.com...
> yes there really are more than one row with the same startdate for a given
> CompanyID. We cannot change the primary key since our software is a
> reporting software and only use this database.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uiazxhOMGHA.3732@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 13 Feb 2006 16:11:07 -0500, "JY" <jy1970us@.yahoo.com> wrote:
>Is there a way to speed up the execution of this stored procedure? There is
>already an index on this table which includes "startdate" column.
Maybe a little.
It's ugly, but try doing two single-table queries, either as a single
statement or into a #temp or @.temp, then join those.
20m scan is going to take, what, 30 seconds maybe?
And how many records will you get from this query, on average, at max?
If that number is reasonably small, my guess is you can get it under a
minute, maybe better.
J.|||have you tried an index on interactions( startdate, interactionid,
companyid) and an index on historyactions( companyid, companyid,
interactionid, workgroupid, actionid)?