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_PREVIEWER12 40 KB8 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_PREVIEWERdbouser table2004-11-12 17:31:05.130
ID intno4
NAME varcharno50
URL varcharno1024
PARTNERNAMEvarcharno50
CACHEPERIODintno4
BEINGUPDATEDbitno1
ADDITIONALURLvarcharno512
UPDATEBEGINDATEdatetimeno8
OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
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
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_PREVIEWER12 40 KB8 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_PREVIEWERdbouser table2004-11-12 17:31:05.130
> ID intno4
> NAME varcharno50
> URL varcharno1024
> PARTNERNAMEvarcharno50
> CACHEPERIODintno4
> BEINGUPDATEDbitno1
> ADDITIONALURLvarcharno512
> UPDATEBEGINDATEdatetimeno8
> OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
> 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:
[vbcol=seagreen]
> 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: