Good Afternoon Everyone,
I hope everyone is doing GREAT today. I've got a database where my
customers First and Last name are in ONE field (ContactName), and we are
upgrading to another SQL application that actually has (2) seperate fields,
FirstName and LastName. Does anyone know how I can run a query to seperate
the First and Last name and put it into two fields?
Right now this is how the new SQL database is:
FieldNames
FirstName LastName
Anthony Smith
I imported the whole contactname field into the FirstName field. So
Lastname is blank. I'd like to take the last name from the 1st field and
put that into the LastName field.
This is what I'd like to acheive:
FieldNames
FirstName LastName
Anthony Smith
Thanks!
Sincerely,
Anthony Smith
In God We Trust!
Are all the names names formatted the same? If so you can use CHARINDEX or
the LEFT & RIGHT fuctions like:
SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
If they are not formatted the same, you have some issues to ponder. What
should happen if there is a middle name or a middle initial? What if either
the firstname or the last name was missing? How would you address a part of
the name that has more than a single space in it? What about double
barrelled names?
Anith
|||Hi Anthony
The following example should point you in the right direction. A couple of
things to watch out for are people that have two first names ie. Mary Jane
Smith and that the formatting of the data is consistent ie. no double spacing
etc.
CREATE TABLE Names
(
FirstName VARCHAR(20),
LastName VARCHAR(20) NULL
)
INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL
UPDATENames
SETFirstName =
CASE
WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
ELSE FirstName
END,
LastName =
CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
END
SELECT * FROM Names
Returns:
FirstName LastName
-- --
Anthony Smith
Peter Ward
John Brown
Prince NULL
Mary Jane Smith
HTH
- Peter Ward
www.wardyit.com
"Anthony Smith" wrote:
> Good Afternoon Everyone,
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
> Right now this is how the new SQL database is:
> FieldNames
> FirstName LastName
> Anthony Smith
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
> This is what I'd like to acheive:
> FieldNames
> FirstName LastName
> Anthony Smith
>
> Thanks!
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
|||> John Steve St.Smith deWaal III
:-))
create table #t ( [Name] varchar(40))
insert into #t ([Name]) values ('Smith,John E')
insert into #t ([Name]) values ('Smith,Bill')
insert into #t ([Name]) values ('Smith,Adam F')
insert into #t ([Name]) values ('St,Smith deWaal III')
--go
select LastName, FirstName, MiddleName
from (
select
Name,
substring(Name,1,Comma-1) LastName,
substring(Name,Comma+1,Spce-Comma-1) FirstName,
nullif(substring(Name,Spce+1,40),'') MiddleName
from (
select
Name,
charindex(',',Name) Comma,
charindex(' ',Name+space(1),charindex(',',Name)) Spce
from #t
) D
) SplitNames
drop table #t
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0UwP5aVHHA.192@.TK2MSFTNGP04.phx.gbl...
> ... and be prepared to manually scrub names such as:
> John Steve St.Smith deWaal III
> I haven't used such, but there are tools out these for this particular
> purpose. Depending on how many names you have and the complexity of the
> names, such a tool might be cheaper in the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:%23mepx5UVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you everyone for the prompts replies. I think most of them are
formatted the same but there may be a few that aren't. If it takes care of
the bulk of the customers that'll be fine, we can manually change the rest.
Have a blessed day everyone!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uj1IsLVVHHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are all the names names formatted the same? If so you can use CHARINDEX or
> the LEFT & RIGHT fuctions like:
> SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
> SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
> If they are not formatted the same, you have some issues to ponder. What
> should happen if there is a middle name or a middle initial? What if
> either the firstname or the last name was missing? How would you address a
> part of the name that has more than a single space in it? What about
> double barrelled names?
> --
> Anith
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
Query to combine several "records/rows" into one "record/row"?
Im new to SQL, Ive been getting by building lists from our student database by building or teacher (etc.). These have been all records that exist on only one row, so its been easy (so far).
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...
Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.
SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.
My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).
Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM
I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"
Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with
Any help, directions to a webpage or book with the correct terms to look up would be very helpful.
Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.
I'll probably be doing two (similar) things:
1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)
2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)
I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/
If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...
Thanks,
Gary
Heres what Im trying to do and I dont even know what to call it, so I'm not even sure what to search for...
Ive got a MS SQL 6.5 database with the following:
ACTIVE students: each student has ID_NUM[8 digits], NAME, GRADE, SCHOOL with one rowof 4 data items per student.
SCHEDULE of courses with (student) ID_NUM[8 digits], SEMESTER[S1 or S2], HOUR[1-7], COURSE_NAME, ROOM_NUM with 14 records (rows) with these 5 items, in this SCHEDULE database for each student.
My mission is to combine then in to one row using the student ID_NUM as the key. (This is to help me with several things, spreadsheets/database for others to easily use, export to simple databases for teacher handhelds.).
Id like one row of the 75 items combined, resulting in 32 items (ACTIVE 4 items + 14 * 2 SCHEDULE items [COURSE_NAME, ROOM_NUM]) since I want stuff plugged into the correct field, for each student. I'd refer to this as COMBINEDRECORD and Id turn the field names into the following:
ID_NUM[8 digits], NAME, GRADE, SCHOOL, S1HOUR1_NAME, S1HOUR1_ROOM_NUM, S1HOUR2_NAME, S1HOUR2_ROOM_NUM, S1HOUR3_NAME, S1HOUR3_ROOM_NUM, S1HOUR4_NAME, S1HOUR4_ROOM_NUM, S1HOUR5_NAME, S1HOUR5_ROOM_NUM, S1HOUR6_NAME, S1HOUR6_ROOM_NUM, S1HOUR7_NAME, S1HOUR7_ROOM_NUM, S2HOUR1_NAME, S2HOUR1_ROOM_NUM, S2HOUR2_NAME, S2HOUR2_ROOM_NUM, S2HOUR3_NAME, S2HOUR3_ROOM_NUM, S2HOUR4_NAME, S2HOUR4_ROOM_NUM, S2HOUR5_NAME, S2HOUR5_ROOM_NUM, S2HOUR6_NAME, S2HOUR6_ROOM_NUM, S2HOUR7_NAME, S2HOUR7_ROOM_NUM
I dont care if there are any blanks I just want to get the data if
SEMESTER='S2', HOUR='5' & COURSE_NAME='Basketweaving' & ROOM_NUM='Pool'
to end up being in the right spot (S2 and Hour 5) in the new COMBINEDRECORD row with
S2HOUR5_NAME='Basketweaving' & S2HOUR5_ROOM_NUM='Pool' for the correct student ID_NUM. Of course with the correct ACTIVE student info into the same "row"
Does that make sense? It might not be the best way, but itll make the data more accessible to everyone and some programs we already use with our old student system. Obviously theres more data than that but I think this is enough to explain my issue and give me enough to work with
Any help, directions to a webpage or book with the correct terms to look up would be very helpful.
Thank you for any help or direction you can give,
GarySounds like a join. Are you trying to creat a new table or just do a report?|||Or a view with a join if you want to leave the existing tables alone.|||Originally posted by barneyrubble318
Or a view with a join if you want to leave the existing tables alone.
I'll probably be doing two (similar) things:
1) An SQL query that just puts my COMBINEDRECORD table into an Excel Spreadsheet. (Why Excel? Everyone here knows how to merge from it, so they can then manipulate it how they want.)
2) An SQL query from Desktop2MobileDB which will convert the COMBINEDRECORD table into a Palm OS (MobileDB) database so principals and teachers can have more data on hand. (They can see where the kid in the hall is really supposed to be...)
I pretty much do the above two things with data now, the problem is the multi-line data from the SCHEDULE/
If I have to create a new table and then access it from there, I guess I can do that. I might not be able to automate it as easily though...
Thanks,
Gary
Wednesday, March 7, 2012
Query timeout
Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris Weston
Check for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegr oups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston
|||Check out query wait option from the sp_configure.
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris Weston
Check for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegr oups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston
|||Check out query wait option from the sp_configure.
Saturday, February 25, 2012
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:
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:
Subscribe to:
Posts (Atom)