Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
ID int no 4
NAME varchar no 50
URL varchar no 1024
PARTNERNAME varchar no 50
CACHEPERIOD int no 4
BEINGUPDATED bit no 1
ADDITIONALURL varchar no 512
UPDATEBEGINDATE datetime no 8
OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
JorgeHi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Is the only index you have the index created by the primary key? If so, you probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any non-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of a
permanent resolution by understanding what is causing so many locks in such a
small table.
--
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on the
> current table. While this can be a temporary solution, you need to think of a
> permanent resolution by understanding what is causing so many locks in such a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:
> > Hello,
> >
> > I've got a very dificult or strange troubleshoot in my hands. I've one table
> > that is very small in my database.
> >
> > sp_spaceused 'osusr_131_PREVIEWER'
> > Name Rows Reserved Data
> > Index_size Unused
> > osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> >
> > When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> > query execution time to return the final result its 2 minutes. I know that
> > this table its locked a lot of times, but i cant turn arround this problem.
> >
> > I send a description of my table:
> >
> > osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> >
> > ID int no 4
> > NAME varchar no 50
> > URL varchar no 1024
> > PARTNERNAME varchar no 50
> > CACHEPERIOD int no 4
> > BEINGUPDATED bit no 1
> > ADDITIONALURL varchar no 512
> > UPDATEBEGINDATE datetime no 8
> >
> > OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> >
> > PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> >
> > Table is referenced by foreign key.
> > OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> > OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> > OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> > OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> > OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> > OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> >
> > Have you any idea to resolve this situation?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
> >