I would like to increase a field size to allow input of up to 16 characters
versus the current 11, but the message is that I can't do the modification
due to linked tables. I believe the modification must be done at the server
,
but I wouldn't know where to start. It would seem that it should be an easy
fix if someone could point me in the right direction. I hate to call the
tech guys out to open a couple windows and type 16. The database was done i
n
access converted to SQL.
ThanksDear rtucker913,
Please post DDL or any example which clarifies your request.
Thanks in advance,
"rtucker913" wrote:
> I would like to increase a field size to allow input of up to 16 character
s
> versus the current 11, but the message is that I can't do the modification
> due to linked tables. I believe the modification must be done at the serv
er,
> but I wouldn't know where to start. It would seem that it should be an ea
sy
> fix if someone could point me in the right direction. I hate to call the
> tech guys out to open a couple windows and type 16. The database was done
in
> access converted to SQL.
> Thankssql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
Alan
Alan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Alan Z. Scharf a crit :
in
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
Alan
Alan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Alan Z. Scharf a crit :
in
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||look up the table "syscolumns" in books on line.
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a crit :
in[vbcol=seagreen]
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a crit :
in[vbcol=seagreen]
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a écrit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a écrit :
> > Hi,
> >
> > I there a way to increase the length of the same-named varchar column
in
> > all tables at once in SQL2000?
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
> >
> > Thanks.
> >
> > Alan
> >
> >
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a écrit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a écrit :
> > Hi,
> >
> > I there a way to increase the length of the same-named varchar column
in
> > all tables at once in SQL2000?
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
> >
> > Thanks.
> >
> > Alan
> >
> >
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
Monday, March 26, 2012
Incorrect syntax near the keyword in.
Hi
i want to copy tables from sql to access
i gave
SELECT Persons.* INTO Persons IN 'Backup.mdb'FROM PersonsI think we are going to need more information, but I am pretty sure you could use OPENDATASOURCE for this:
The error is Incorrect syntax near the keyword 'in'
any one reply me
INSERT INTO
OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','DataSource="c:\test.mdb"; User ID=Admin;Password=' )...Persons(column1,column2)
SELECT * FROM Persons
(seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9334 for reference)
Friday, March 23, 2012
Incorrect Syntax Near '-'
Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackHi,
Put the server name in sqare brackets [].
[SQL-SERVER2]
Thanks
Hari
SQL Server MVP
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SP that running every 20 min, the SP will update some tables from
> DB on another SQL server.
> ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> server name in SP, but during test SP, i got error Incorrect Syntax Near
> '-', seems i can't use '-' when refering server, is it normal?
> how could i correct this issue other than rename server?
> Appreicate your help.
> Jack
>|||Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a SP that running every 20 min, the SP will update some tables
from
> > DB on another SQL server.
> >
> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> > server name in SP, but during test SP, i got error Incorrect Syntax Near
> > '-', seems i can't use '-' when refering server, is it normal?
> >
> > how could i correct this issue other than rename server?
> >
> > Appreicate your help.
> >
> > Jack
> >
> >
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackHi,
Put the server name in sqare brackets [].
[SQL-SERVER2]
Thanks
Hari
SQL Server MVP
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SP that running every 20 min, the SP will update some tables from
> DB on another SQL server.
> ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> server name in SP, but during test SP, i got error Incorrect Syntax Near
> '-', seems i can't use '-' when refering server, is it normal?
> how could i correct this issue other than rename server?
> Appreicate your help.
> Jack
>|||Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a SP that running every 20 min, the SP will update some tables
from
> > DB on another SQL server.
> >
> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> > server name in SP, but during test SP, i got error Incorrect Syntax Near
> > '-', seems i can't use '-' when refering server, is it normal?
> >
> > how could i correct this issue other than rename server?
> >
> > Appreicate your help.
> >
> > Jack
> >
> >
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>
Incorrect Syntax Near '-'
Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
Jack
Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from
>
|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/artic...nventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
sql
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
Jack
Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from
>
|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/artic...nventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
sql
Incorrect Syntax Near '-'
Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackBrilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from[vbcol=seagreen]
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/arti...onventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackBrilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from[vbcol=seagreen]
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/arti...onventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
Incorrect syntax in user-defined function
In the script below is the DDL to create some tables and a UDF.
What I'm interested in is the UDF at the end. Specifically, these few
lines:
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0
If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
message:
"Incorrect syntax near keyword ELSE"
Being a good little footsoldier, I want to release resources
explicitly, but clearly I'm putting the CLOSE and DEALLOCATE statements
in the wrong place.
Could someone please tell me where I ought to put them so that the
cursor is CLOSEd and DEALLOCATEd correctly.
By the way, I am not after negative comments on the data design, or the
logic (or lack of it) in the function, just why the syntax error
occurs.
Thanks as ever
Edward
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Employee]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[PurchaseOrder]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TimesheetItem]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[WorkOTRate]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[WorkOTRateDefaults]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PurchaseOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[Complete] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
Function to determine the actual cost, in minutes, of a particular
segment of work. This is what it does, or is supposed to do.
1. From the PARAMETER WorkID, determine the conclusion of the work
block associated with the TimesheetID - i.e. StartTime + DurationMins
2. Establish whether there are records in the WorkOTRate table
corresponding to this particular WorkID, weekday and time period
3. If there are, get the amount of minutes by which the work block
coincides.
4. If there are no such records, get the default values from the
WorkOTRateDefaults table
5. If the block doesn't cross any boundaries then it's just regular
work, so just count the minutes.
25/08/2005 EC
*/
CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@.TimesheetID int)
RETURNS float
AS
BEGIN
DECLARE
@.OTRateTimeFrom datetime,
@.OTRateTimeTo as datetime,
@.OTRateMultiplier as float,
@.EndTime datetime,
@.ReturnValue as float,
@.OrderRatePerHour as money,
@.EmployeeRatePerHour as smallmoney,
@.NumRecords as int,
@.WorkID as int,
@.EmployeeID as int,
@.StartTime as smalldatetime,
@.Duration as int,
@.Found as int,
@.Chargeable as bit
-- Get the various bits and bobs needed for the calculation
SET @.ReturnValue = 0
SET @.Found = 0
SELECT
@.WorkID = WorkID,
@.EmployeeID = EmployeeID,
@.StartTime = Start,
@.Duration = DurationMins
FROM
TimesheetItem
WHERE
ItemID = @.TimesheetID
-- If this work is NOT chargeable, return 0
SELECT
@.Chargeable = Chargeable
FROM
[Work]
WHERE
WorkID = @.WorkID
IF @.Chargeable = 1
BEGIN
SET @.EndTime = DATEADD(mi, @.Duration, @.StartTime)
-- Get the rate per hour for this work
SELECT
@.OrderRatePerHour = RatePerHour
FROM
PurchaseOrder
WHERE
WorkID = @.WorkID
-- Get the rate per hour for the employee
SELECT
@.EmployeeRatePerHour = DefaultRatePerHour
FROM
Employee
WHERE
(EmployeeID = @.EmployeeID)
-- Find out if there's an OT Rate set up for this WorkID
SELECT
@.NumRecords = Count(*)
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dd, @.StartTime)))
IF @.NumRecords > 0
BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dw, @.StartTime)))
OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN
-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)
-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)
IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT
NUBMER 2
S E
OT OT
NUMBER 3
S E
OT OT
NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0
BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRateDefaults
WHERE
(WorkDay = DATEPART(dw, @.StartTime))
OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN
-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)
-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)
IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT
NUBMER 2
S E
OT OT
NUMBER 3
S E
OT OT
NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
CLOSE OTRate
DEALLOCATE OTRate
-- If there were no matching OT records, it's just a regular block
of work in normal hours
IF @.Found = 0
SET @.ReturnValue = @.Duration
END
-- Finally we factor in the relation between the Employee's rate and
the Order's stated rate.
RETURN (@.ReturnValue * (@.EmployeeRatePerHour / @.OrderRatePerHour))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOteddysnips@.hotmail.com wrote:
> In the script below is the DDL to create some tables and a UDF.
> What I'm interested in is the UDF at the end. Specifically, these few
> lines:
> --CLOSE OTRate
> --DEALLOCATE OTRate
> ELSE-- @.NumRecords <= 0
I haven't actually read the code through thoroughly, so I don't know if
others are going to give you advice about doing it in a set oriented
fashion, but I believe that your close and deallocate are coming one
END too late. The two ENDs above them (to my reading) are the END of
the while loop and then the end of the if statement. When using ELSE,
the following should be adhered to:
IF <condition>
<statement or block>
ELSE
<statement or block
where statement is either a single statement or:
BEGIN
<statement> [<statement>...]
END
Damien|||Damien wrote:
> teddysnips@.hotmail.com wrote:
> > In the script below is the DDL to create some tables and a UDF.
> > What I'm interested in is the UDF at the end. Specifically, these few
> > lines:
> > --CLOSE OTRate
> > --DEALLOCATE OTRate
> > ELSE-- @.NumRecords <= 0
> I haven't actually read the code through thoroughly, so I don't know if
> others are going to give you advice about doing it in a set oriented
> fashion, but I believe that your close and deallocate are coming one
> END too late.
You're quite right - many thanks! As for doing it using sets - well, I
really don't have time!
Edward|||(teddysnips@.hotmail.com) writes:
> You're quite right - many thanks! As for doing it using sets - well, I
> really don't have time!
But you assume that anyone will have the time to run that code? I hope
that you can find the time to test it on full-size data, before you
devote your important time to something else!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Incorrect Syntax
Hello all,
Newbie here.
SQL 2000, Windows 2000
I'm trying to alter tables in my SQL DB using statements like the following:
/* AD_GROUPS */
alter table AD_GROUPS alter column AD_GROUP_NAME nvarchar(64)not null
go
/* ARTICLES */
alter table ARTICLES add column CONTENTTYPE_REF int null
go
I get error messages like:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'column'
I got the statements straight out of the Microsoft book "Inside Microsoft
SQL Server 2000"
Thanks in advance for helping to train this raw recruit!
JakeJust grop the "COLUMN" keyword from the ADD statement:
ALTER TABLE Articles ADD contenttype_ref INT NULL
Your ALTER COLUMN statement is correct. It's just a peculiarity of the
syntax that the word "COLUMN" isn't required after ADD.
--
David Portas
SQL Server MVP
--|||> Just grop the "COLUMN" keyword from the ADD statement:
> ALTER TABLE Articles ADD contenttype_ref INT NULL
> Your ALTER COLUMN statement is correct. It's just a peculiarity of the
> syntax that the word "COLUMN" isn't required after ADD.
And pardon the pun, but this missing part of the syntax won't be added
anytime soon, either. ;-)
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> /* ARTICLES */
> alter table ARTICLES add column CONTENTTYPE_REF int null
> go
> I got the statements straight out of the Microsoft book "Inside Microsoft
> SQL Server 2000"
What page? I'd be interested to see a line like that, with the incorrect
column keyword where it is in your statement.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I'd be interested to see that also. :-)
Not that there are absolutely no mistakes in the book, but I just did a
search of the electronic version of the book, and did not find this error.
In fact, I found this note, basically warning about the word 'column' not
being used when adding a new column:
NOTE
-----------------------
--
Notice the syntax difference between dropping a column and adding a new
column: the word COLUMN is required when dropping a column, but not when
adding a new column to a table.
My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of the
book, and then changed ALTER COLUMN to ADD COLUMN.
--
HTH
------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQBNC0cbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > /* ARTICLES */
> > alter table ARTICLES add column CONTENTTYPE_REF int null
> > go
> > I got the statements straight out of the Microsoft book "Inside
Microsoft
> > SQL Server 2000"
> What page? I'd be interested to see a line like that, with the incorrect
> column keyword where it is in your statement.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)|||> My guess is that Jake pulled the ALTER TABLE ALTER COLUMN syntax out of
the
> book, and then changed ALTER COLUMN to ADD COLUMN.
That was my guess too, but wanted to prod a bit more; maybe he found
something the rest of us missed. ;-)
A
Monday, March 12, 2012
inconsistent retrieval of data
I have created a sql 2000 db and imported my tables from an access database. My web site is using asp. I am getting inconsistent results when I retrieve data from the database. Even though I can see the data in the database some fields will not retrieve to the web site. When I go to the database and erase the field and then create a new field and then put data in, I will then be able to retrieve the data at the web site. The problem is some pages will be able to retrieve while others won't. Also some fields will return data while others won't. The web site worked fine with access.
Does anyone know what may be causing this?
Thank you in advance.Betcha got spaces in the column names...
or at least unique chars like ? in them
Can you show us some code?|||There are no spaces that I can see or unique chars.
RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn
filePath = RS("filePath")
myVideo = Split(filePath, ",", -1, 1)
RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn
When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.
Originally posted by Brett Kaiser
Betcha got spaces in the column names...
or at least unique chars like ? in them
Can you show us some code?|||Why not create and call stored procedures?
It'll be more effecient as well
Just curious though, do you have sql server client tools?
Can you type that same select in a query analyzer window?
What would it look like?
Also you can look at profiler to see what's being passed to the server..
my guess is the statement is malformed when it gets passed...
do you know how to start a trace?
I still thinks sprocs are the way to go in any case...|||We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.
Thanks for suggestions. Luckily it was an easy fix for us
Originally posted by Brett Kaiser
Why not create and call stored procedures?
It'll be more effecient as well
Just curious though, do you have sql server client tools?
Can you type that same select in a query analyzer window?
What would it look like?
Also you can look at profiler to see what's being passed to the server..
my guess is the statement is malformed when it gets passed...
do you know how to start a trace?
I still thinks sprocs are the way to go in any case...|||Help me out here...what did you do exactly?|||Originally posted by Brett Kaiser
Help me out here...what did you do exactly?
We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic
For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.
Looks like we have found most of the differences from access to SQL but we need to test it for about a week.
Does anyone know what may be causing this?
Thank you in advance.Betcha got spaces in the column names...
or at least unique chars like ? in them
Can you show us some code?|||There are no spaces that I can see or unique chars.
RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn
filePath = RS("filePath")
myVideo = Split(filePath, ",", -1, 1)
RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn
When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.
Originally posted by Brett Kaiser
Betcha got spaces in the column names...
or at least unique chars like ? in them
Can you show us some code?|||Why not create and call stored procedures?
It'll be more effecient as well
Just curious though, do you have sql server client tools?
Can you type that same select in a query analyzer window?
What would it look like?
Also you can look at profiler to see what's being passed to the server..
my guess is the statement is malformed when it gets passed...
do you know how to start a trace?
I still thinks sprocs are the way to go in any case...|||We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.
Thanks for suggestions. Luckily it was an easy fix for us
Originally posted by Brett Kaiser
Why not create and call stored procedures?
It'll be more effecient as well
Just curious though, do you have sql server client tools?
Can you type that same select in a query analyzer window?
What would it look like?
Also you can look at profiler to see what's being passed to the server..
my guess is the statement is malformed when it gets passed...
do you know how to start a trace?
I still thinks sprocs are the way to go in any case...|||Help me out here...what did you do exactly?|||Originally posted by Brett Kaiser
Help me out here...what did you do exactly?
We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic
For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.
Looks like we have found most of the differences from access to SQL but we need to test it for about a week.
Inconsistent Reads and performance problems
I have a fairly complex query which takes about 3-4 seconds. Since it
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes AveragePa
geDensity ScanDensity BestCount ActualCo
unt LogicalFragmentation ExtentFragm
entation MinimumRecordSize MaximumRecord
Size AverageRecordSize ForwardedReco
rds
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 1
32 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177
138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24 0
0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0 0
57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 100
107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 7
2 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes AveragePa
geDensity ScanDensity BestCount ActualCo
unt LogicalFragmentation ExtentFragm
entation MinimumRecordSize MaximumRecord
Size AverageRecordSize ForwardedReco
rds
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 1
32 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177
138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24 0
0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0 0
57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 100
107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 7
2 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert
Inconsistent Reads and performance problems
I have a fairly complex query which takes about 3-4 seconds. Since it
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert
Inconsistent query result
Hi, I have one view and three tables. My query is like this
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It wil return only 6 records.
If I do this
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
They will return 248407 records for each query.
----
And If I do this
select top 100000 * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It will return exactly 100000 records.
----
I really do not have any idea why my first query only returns 6 records?
I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
Server SP4, SQL Server 2000 Enterprise Edition SP3a.
Thank You in Advance...
Regards,
Jono Indrawijaya
PT eBiz Cipta Solusi - Indonesia
Microsoft Certified PartnerJono,
Certainly adding TOP 100000 should not increase the size of
the result set from 6 to 100000. First, can you run
SELECT @.@.version
to identify which build of SQL Server you are using? Also, could you
post the CREATE TABLE and CREATE VIEW statements for these
tables and view?
Steve Kass
Drew University
Jono Indrawijaya wrote:
>Hi, I have one view and three tables. My query is like this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It wil return only 6 records.
>--
>If I do this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>
>They will return 248407 records for each query.
>----
>And If I do this
>select top 100000 * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It will return exactly 100000 records.
>----
>I really do not have any idea why my first query only returns 6 records?
>I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
>Server SP4, SQL Server 2000 Enterprise Edition SP3a.
>Thank You in Advance...
>
>|||Dear Steve,
I found that the Customer_ID field in FACT_CSUTOMER is bigint, but in the
I_FACT_SERVICE is int. Now it is returning correct recordset after I change
those data type. Thx for u're response, it leads me to this finding.. =)
Regards,
Jono
"Steve Kass" wrote:
> Jono,
> Certainly adding TOP 100000 should not increase the size of
> the result set from 6 to 100000. First, can you run
> SELECT @.@.version
> to identify which build of SQL Server you are using? Also, could you
> post the CREATE TABLE and CREATE VIEW statements for these
> tables and view?
> Steve Kass
> Drew University
> Jono Indrawijaya wrote:
>
>
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It wil return only 6 records.
If I do this
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
They will return 248407 records for each query.
----
And If I do this
select top 100000 * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It will return exactly 100000 records.
----
I really do not have any idea why my first query only returns 6 records?
I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
Server SP4, SQL Server 2000 Enterprise Edition SP3a.
Thank You in Advance...
Regards,
Jono Indrawijaya
PT eBiz Cipta Solusi - Indonesia
Microsoft Certified PartnerJono,
Certainly adding TOP 100000 should not increase the size of
the result set from 6 to 100000. First, can you run
SELECT @.@.version
to identify which build of SQL Server you are using? Also, could you
post the CREATE TABLE and CREATE VIEW statements for these
tables and view?
Steve Kass
Drew University
Jono Indrawijaya wrote:
>Hi, I have one view and three tables. My query is like this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It wil return only 6 records.
>--
>If I do this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>
>They will return 248407 records for each query.
>----
>And If I do this
>select top 100000 * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It will return exactly 100000 records.
>----
>I really do not have any idea why my first query only returns 6 records?
>I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
>Server SP4, SQL Server 2000 Enterprise Edition SP3a.
>Thank You in Advance...
>
>|||Dear Steve,
I found that the Customer_ID field in FACT_CSUTOMER is bigint, but in the
I_FACT_SERVICE is int. Now it is returning correct recordset after I change
those data type. Thx for u're response, it leads me to this finding.. =)
Regards,
Jono
"Steve Kass" wrote:
> Jono,
> Certainly adding TOP 100000 should not increase the size of
> the result set from 6 to 100000. First, can you run
> SELECT @.@.version
> to identify which build of SQL Server you are using? Also, could you
> post the CREATE TABLE and CREATE VIEW statements for these
> tables and view?
> Steve Kass
> Drew University
> Jono Indrawijaya wrote:
>
>
Inconsistent Query Performance
Hello!
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When ru
n
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When ru
n
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with stat
istics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes paral
lelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...[vbcol=seagreen]
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the serve
r
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with st
atistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes par
allelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the
same (or cost is very
close), you tend to get the same performance (or a higher on the high-end ma
chine). You could try
posting to the group -relationalserver.performance and see if you get some m
ore attention there. Or
perhaps even time to open a ticket with MS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...[vbcol=seagreen]
> Stats IO showed very small differences in number of logical reads. Physic
al
> reads were 0 all around - well cached I guess. I had tried to set the ser
ver
> to have SQL Server use only 1 processor which should have the same affect
as
> the MAXDOP setting = 1 (there is some looping and several queries involve
d
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is th
e same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end
machine). You could try
> posting to the group -relationalserver.performance and see if you get some
more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, mayb
e
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SA
N
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When ru
n
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When ru
n
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with stat
istics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes paral
lelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...[vbcol=seagreen]
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the serve
r
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with st
atistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes par
allelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the
same (or cost is very
close), you tend to get the same performance (or a higher on the high-end ma
chine). You could try
posting to the group -relationalserver.performance and see if you get some m
ore attention there. Or
perhaps even time to open a ticket with MS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...[vbcol=seagreen]
> Stats IO showed very small differences in number of logical reads. Physic
al
> reads were 0 all around - well cached I guess. I had tried to set the ser
ver
> to have SQL Server use only 1 processor which should have the same affect
as
> the MAXDOP setting = 1 (there is some looping and several queries involve
d
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is th
e same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end
machine). You could try
> posting to the group -relationalserver.performance and see if you get some
more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, mayb
e
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SA
N
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh
Inconsistent Query Performance
Hello!
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> > Hello!
> > We are having a problem with one of the queries that an application runs.
> > This query uses some temp tables (subbed as table variables with no
> > performance change) and heavily uses some user defined functions. When run
> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> > 2000 Developer Editions), it returns within a second. When run against a
> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> > is nothing else going on with the large server. This will be the only
> > activity on the system.. I disabled awe and turned off /PAE switch with no
> > change. The timings are repeatable and the only improvement on the
> > enterprise class server is shortly after a reboot and even then the results
> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> > deteriorates on the server. I have tried this with the database both on SAN
> > or on local disk on the server and there is no measurable difference.
> >
> > Any ideas on what I should check next?
> >
> > Thanks
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>> First step is to check the execution plans. Same?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> > Hello!
>> > We are having a problem with one of the queries that an application runs.
>> > This query uses some temp tables (subbed as table variables with no
>> > performance change) and heavily uses some user defined functions. When run
>> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> > 2000 Developer Editions), it returns within a second. When run against a
>> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> > is nothing else going on with the large server. This will be the only
>> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> > change. The timings are repeatable and the only improvement on the
>> > enterprise class server is shortly after a reboot and even then the results
>> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> > deteriorates on the server. I have tried this with the database both on SAN
>> > or on local disk on the server and there is no measurable difference.
>> >
>> > Any ideas on what I should check next?
>> >
>> > Thanks
>>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the server
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> > Yes, execution plans are very similar. Not exactly identical - but all
> > components are very similar in costs.
> >
> > "Tibor Karaszi" wrote:
> >
> >> First step is to check the execution plans. Same?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> > Hello!
> >> > We are having a problem with one of the queries that an application runs.
> >> > This query uses some temp tables (subbed as table variables with no
> >> > performance change) and heavily uses some user defined functions. When run
> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> > is nothing else going on with the large server. This will be the only
> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> > change. The timings are repeatable and the only improvement on the
> >> > enterprise class server is shortly after a reboot and even then the results
> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> > or on local disk on the server and there is no measurable difference.
> >> >
> >> > Any ideas on what I should check next?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
close), you tend to get the same performance (or a higher on the high-end machine). You could try
posting to the group -relationalserver.performance and see if you get some more attention there. Or
perhaps even time to open a ticket with MS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> Stats IO showed very small differences in number of logical reads. Physical
> reads were 0 all around - well cached I guess. I had tried to set the server
> to have SQL Server use only 1 processor which should have the same affect as
> the MAXDOP setting = 1 (there is some looping and several queries involved
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>> Similar <> same :-)
>> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
>> check
>> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
>> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>> > Yes, execution plans are very similar. Not exactly identical - but all
>> > components are very similar in costs.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> First step is to check the execution plans. Same?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> >> > Hello!
>> >> > We are having a problem with one of the queries that an application runs.
>> >> > This query uses some temp tables (subbed as table variables with no
>> >> > performance change) and heavily uses some user defined functions. When run
>> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> >> > 2000 Developer Editions), it returns within a second. When run against a
>> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> >> > is nothing else going on with the large server. This will be the only
>> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> >> > change. The timings are repeatable and the only improvement on the
>> >> > enterprise class server is shortly after a reboot and even then the results
>> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> >> > deteriorates on the server. I have tried this with the database both on SAN
>> >> > or on local disk on the server and there is no measurable difference.
>> >> >
>> >> > Any ideas on what I should check next?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end machine). You could try
> posting to the group -relationalserver.performance and see if you get some more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> > Stats IO showed very small differences in number of logical reads. Physical
> > reads were 0 all around - well cached I guess. I had tried to set the server
> > to have SQL Server use only 1 processor which should have the same affect as
> > the MAXDOP setting = 1 (there is some looping and several queries involved
> > in this proc). Funny thing is that this behavior is repeatable on other
> > large servers and the quick performance occurs on other workstations.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Similar <> same :-)
> >> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
> >> check
> >> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> >> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> >> > Yes, execution plans are very similar. Not exactly identical - but all
> >> > components are very similar in costs.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> First step is to check the execution plans. Same?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> >> > Hello!
> >> >> > We are having a problem with one of the queries that an application runs.
> >> >> > This query uses some temp tables (subbed as table variables with no
> >> >> > performance change) and heavily uses some user defined functions. When run
> >> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> >> > is nothing else going on with the large server. This will be the only
> >> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> >> > change. The timings are repeatable and the only improvement on the
> >> >> > enterprise class server is shortly after a reboot and even then the results
> >> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> >> > or on local disk on the server and there is no measurable difference.
> >> >> >
> >> >> > Any ideas on what I should check next?
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, maybe
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SAN
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
--
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> > Hello!
> > We are having a problem with one of the queries that an application runs.
> > This query uses some temp tables (subbed as table variables with no
> > performance change) and heavily uses some user defined functions. When run
> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> > 2000 Developer Editions), it returns within a second. When run against a
> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> > is nothing else going on with the large server. This will be the only
> > activity on the system.. I disabled awe and turned off /PAE switch with no
> > change. The timings are repeatable and the only improvement on the
> > enterprise class server is shortly after a reboot and even then the results
> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> > deteriorates on the server. I have tried this with the database both on SAN
> > or on local disk on the server and there is no measurable difference.
> >
> > Any ideas on what I should check next?
> >
> > Thanks
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>> First step is to check the execution plans. Same?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> > Hello!
>> > We are having a problem with one of the queries that an application runs.
>> > This query uses some temp tables (subbed as table variables with no
>> > performance change) and heavily uses some user defined functions. When run
>> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> > 2000 Developer Editions), it returns within a second. When run against a
>> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> > is nothing else going on with the large server. This will be the only
>> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> > change. The timings are repeatable and the only improvement on the
>> > enterprise class server is shortly after a reboot and even then the results
>> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> > deteriorates on the server. I have tried this with the database both on SAN
>> > or on local disk on the server and there is no measurable difference.
>> >
>> > Any ideas on what I should check next?
>> >
>> > Thanks
>>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the server
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> > Yes, execution plans are very similar. Not exactly identical - but all
> > components are very similar in costs.
> >
> > "Tibor Karaszi" wrote:
> >
> >> First step is to check the execution plans. Same?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> > Hello!
> >> > We are having a problem with one of the queries that an application runs.
> >> > This query uses some temp tables (subbed as table variables with no
> >> > performance change) and heavily uses some user defined functions. When run
> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> > is nothing else going on with the large server. This will be the only
> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> > change. The timings are repeatable and the only improvement on the
> >> > enterprise class server is shortly after a reboot and even then the results
> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> > or on local disk on the server and there is no measurable difference.
> >> >
> >> > Any ideas on what I should check next?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
close), you tend to get the same performance (or a higher on the high-end machine). You could try
posting to the group -relationalserver.performance and see if you get some more attention there. Or
perhaps even time to open a ticket with MS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> Stats IO showed very small differences in number of logical reads. Physical
> reads were 0 all around - well cached I guess. I had tried to set the server
> to have SQL Server use only 1 processor which should have the same affect as
> the MAXDOP setting = 1 (there is some looping and several queries involved
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>> Similar <> same :-)
>> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
>> check
>> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
>> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>> > Yes, execution plans are very similar. Not exactly identical - but all
>> > components are very similar in costs.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> First step is to check the execution plans. Same?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> >> > Hello!
>> >> > We are having a problem with one of the queries that an application runs.
>> >> > This query uses some temp tables (subbed as table variables with no
>> >> > performance change) and heavily uses some user defined functions. When run
>> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> >> > 2000 Developer Editions), it returns within a second. When run against a
>> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> >> > is nothing else going on with the large server. This will be the only
>> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> >> > change. The timings are repeatable and the only improvement on the
>> >> > enterprise class server is shortly after a reboot and even then the results
>> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> >> > deteriorates on the server. I have tried this with the database both on SAN
>> >> > or on local disk on the server and there is no measurable difference.
>> >> >
>> >> > Any ideas on what I should check next?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end machine). You could try
> posting to the group -relationalserver.performance and see if you get some more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> > Stats IO showed very small differences in number of logical reads. Physical
> > reads were 0 all around - well cached I guess. I had tried to set the server
> > to have SQL Server use only 1 processor which should have the same affect as
> > the MAXDOP setting = 1 (there is some looping and several queries involved
> > in this proc). Funny thing is that this behavior is repeatable on other
> > large servers and the quick performance occurs on other workstations.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Similar <> same :-)
> >> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
> >> check
> >> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> >> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> >> > Yes, execution plans are very similar. Not exactly identical - but all
> >> > components are very similar in costs.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> First step is to check the execution plans. Same?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> >> > Hello!
> >> >> > We are having a problem with one of the queries that an application runs.
> >> >> > This query uses some temp tables (subbed as table variables with no
> >> >> > performance change) and heavily uses some user defined functions. When run
> >> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> >> > is nothing else going on with the large server. This will be the only
> >> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> >> > change. The timings are repeatable and the only improvement on the
> >> >> > enterprise class server is shortly after a reboot and even then the results
> >> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> >> > or on local disk on the server and there is no measurable difference.
> >> >> >
> >> >> > Any ideas on what I should check next?
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, maybe
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SAN
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
--
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh
Friday, March 9, 2012
Incompatible Data Types question
Hi,
I've got a dimension table with an Int32 primary key. I have a few fact tables that have an Int16 field, which is acting as a foreign key to the dimension table. I can't actually change the data types of these tables in the SQL database, but I'd still like to create a cube that uses those 32 bit dimension tables with the 16 bit field entries. Unfortunately, the cube designer won't let me specify a relationshop between the two tables because the fields are different data-types.
What I did is create Calculated Member fields which cast the 16 bit value to a 32 bit value, and then create the relationship. I was wondering if anyone else had a different solution?
Thanks,
KobiWe had to create temporary tables and cast the fields into matching data types.
Wednesday, March 7, 2012
Including Views in Replication
I have a three server peer-to-peer replication setup that includes articles for tables and views. As I understand the BOL, scheme changes -- which I take to mean changes, amont other things, changes in the design of a table or view -- should automatically replicate to the other servers in the topology. Here are my quesitons:
When including a view as part of the publication, what is it, exactly, that is getting replicated? If all the tables supporting a given view are being replicated, and the view exists on all three boxes, whatelse, besides the view schema (and changes thereto) is being replicated?
Secondly, if in fact schema changes are replicated, why can't I modify a view that is part of a publication? When I try to make a change to such a view, I get a server timeout message, every single time. When I remove the view from the publication, I can make the modification with no trouble. What does replicating schema changes mean if I can't make changes to the schema?
Thanks for any enlightenment on this.
Randy
In general, when views are replicated, the schema definition of the view object is actually what gets replicated. After initial sync, there are no data changes tied to a view.
In non-peer-to-peer transactional replication, view schema changes are propagated from the publisher to the subscribers automatically. i.e. A view schema change could be changing the list of columns included in the view select statement.
Since you have a peer-to-peer topology set up there are more restrictions to consider. Check out the General Considerations under Peer-to-Peer replication in SQL Server 2005 Books Online. One of the restrictions is that any schema changes require the peer-to-peer topology to be quiesced (stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes).
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||the resultset and content of the view can only be replicated if it is "indexed" if the view is not index the only thing that gets replicated is the view definition.
|||
Hi Randy,
I am interested to know more about your second issue (timeout when modify a view). Could you give me more details on your issue? For example, what statement do you use to modify a view, what is the origional view definition and what is the error message?
Thanks,
Peng
Including view primary keys in relationships
I have two separate databases. One db as several tables that have foreign
key fields whose parents reside in the second db. I would like to be able
to define relationships between these entities.
I was thinking I could create a view of the parent table in the child's
database, but I don't see any way of including a view table as a
relationship partner. Nor do I see how to reference a table in a separate
database as a relationship partner.
Any advice would be greatly appreciated.
TIA.Peter J. Hunter wrote:
> I have two separate databases. One db as several tables that have foreign
> key fields whose parents reside in the second db. I would like to be able
> to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
> TIA.
You cannot create foreign keys across databases.
Keep the referencing tables and the parent tables in the same database,
then create views or synonyms that point to them from the other
database.
It's also possible to use triggers as a "constraint" to enforce the
foreign key.
Or you could replicate the data in both databases by some means.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Peter J. Hunter" <pjhunter@.wintersnet.net> wrote in message
news:%23R3zIUbiGHA.3408@.TK2MSFTNGP05.phx.gbl...
>I have two separate databases. One db as several tables that have foreign
>key fields whose parents reside in the second db. I would like to be able
>to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
If you are inclined to define relationships across databases, then you
probably shouldn't be using multiple databases. Perhaps you can use
multiple schemas inside a single database.
Davdi
key fields whose parents reside in the second db. I would like to be able
to define relationships between these entities.
I was thinking I could create a view of the parent table in the child's
database, but I don't see any way of including a view table as a
relationship partner. Nor do I see how to reference a table in a separate
database as a relationship partner.
Any advice would be greatly appreciated.
TIA.Peter J. Hunter wrote:
> I have two separate databases. One db as several tables that have foreign
> key fields whose parents reside in the second db. I would like to be able
> to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
> TIA.
You cannot create foreign keys across databases.
Keep the referencing tables and the parent tables in the same database,
then create views or synonyms that point to them from the other
database.
It's also possible to use triggers as a "constraint" to enforce the
foreign key.
Or you could replicate the data in both databases by some means.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Peter J. Hunter" <pjhunter@.wintersnet.net> wrote in message
news:%23R3zIUbiGHA.3408@.TK2MSFTNGP05.phx.gbl...
>I have two separate databases. One db as several tables that have foreign
>key fields whose parents reside in the second db. I would like to be able
>to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
If you are inclined to define relationships across databases, then you
probably shouldn't be using multiple databases. Perhaps you can use
multiple schemas inside a single database.
Davdi
Subscribe to:
Posts (Atom)