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)?

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:
> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> > And this is on a dual Xeon with 4 Gig of memory.
> > Here is the script that's taking so long.
> > Would someone be so nice and try it on their SQL Server 2005 and tell me if
> > they have the same issue? Thx.
> >
> > create table #t (tid int, t2 int)
> >
> > select tid, count(*)
> > from #t
> > group by tid with rollup
> >
> > drop table #t
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #t
Speed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t
|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me i
f
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
--
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
>> faster hardware, same proc takes about 2 minutes to run.
>> Can anyone suggest things to look at?
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.
> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
>> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
>> syntax again to get the query plan as text, with stats? I am trying :
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>> The proc does *lots* of things, so eyeballing a compare between plans is
>> next to impossible.
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>