Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
Try this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =
ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
pintable[vbcol=seagreen]
take
>
>.
>
|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?
|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
(Please reply only to the newsgroup)
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Friday, March 23, 2012
Query Tuning problem.
Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =
ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
pintable[vbcol=seagreen]
take[vbcol=seagreen]
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
(Please reply only to the newsgroup)
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =
ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
pintable[vbcol=seagreen]
take[vbcol=seagreen]
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
(Please reply only to the newsgroup)
Query Tuning problem.
Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
>> Guys,
>> I'm stumped. While its not pertinent to the
>> matter, we are running a Vignette content management
>> system on Win2k with Sql 2000 Enterprise on a cluster.
>> The server has 2 Gig of RAM , 2 CPU's and the database
>> size is 1.5G.
>> The query below is fired at login. The indexes
>> seem fine based on the query plan. When I look through
>> profiler, the query below takes a very high # of CPU
>> cycles and reads. It consistently takes more than 1.5
>> seconds to execute the query below. I did a dbcc
pintable
>> for ALL the tables in the query and that did not help
>> either. It seemed to make it worse (3 seconds and above)
>> Any idea what could be the issue here? The server
>> is not really heavily taxed.
>> The tables are small. They have very few rows.
>> VGNCCB_ROLE 939
>> VGNCCB_ROLE_JT 62389
>> VGNCCB_GROUP_USER_JT 1364
>>
>> The problem Query:
>> select
>> ROLE_ID,
>> NAME,
>> DESCRIPTION,
>> CREATE_DATE,
>> MODIFIED_DATE
>> FROM
>> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
>> WHERE
>> ROLE_ID in
>> (select ROLE_ID
>> FROM
>> vign.VGNCCB_ROLE_JT -- Non clustered indexes
>> on USER_NAME AND non clustered on GROUP_ID
>> WHERE
>> USER_NAME = 'testRole' or GROUP_ID in (select
>> GROUP_ID
>> FROM
>> vign.VGNCCB_GROUP_USER_JT -- Non clustered
>> index on USER_NAME
>> WHERE
>> USER_NAME = 'testRole'))
>> I'd appreciate it if someone could follow me in this
>> thread to completion. Such a simple query should not
take
>> this long.
>>
>> TIA,
>> Jack
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
--
(Please reply only to the newsgroup)
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
>> Guys,
>> I'm stumped. While its not pertinent to the
>> matter, we are running a Vignette content management
>> system on Win2k with Sql 2000 Enterprise on a cluster.
>> The server has 2 Gig of RAM , 2 CPU's and the database
>> size is 1.5G.
>> The query below is fired at login. The indexes
>> seem fine based on the query plan. When I look through
>> profiler, the query below takes a very high # of CPU
>> cycles and reads. It consistently takes more than 1.5
>> seconds to execute the query below. I did a dbcc
pintable
>> for ALL the tables in the query and that did not help
>> either. It seemed to make it worse (3 seconds and above)
>> Any idea what could be the issue here? The server
>> is not really heavily taxed.
>> The tables are small. They have very few rows.
>> VGNCCB_ROLE 939
>> VGNCCB_ROLE_JT 62389
>> VGNCCB_GROUP_USER_JT 1364
>>
>> The problem Query:
>> select
>> ROLE_ID,
>> NAME,
>> DESCRIPTION,
>> CREATE_DATE,
>> MODIFIED_DATE
>> FROM
>> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
>> WHERE
>> ROLE_ID in
>> (select ROLE_ID
>> FROM
>> vign.VGNCCB_ROLE_JT -- Non clustered indexes
>> on USER_NAME AND non clustered on GROUP_ID
>> WHERE
>> USER_NAME = 'testRole' or GROUP_ID in (select
>> GROUP_ID
>> FROM
>> vign.VGNCCB_GROUP_USER_JT -- Non clustered
>> index on USER_NAME
>> WHERE
>> USER_NAME = 'testRole'))
>> I'd appreciate it if someone could follow me in this
>> thread to completion. Such a simple query should not
take
>> this long.
>>
>> TIA,
>> Jack
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
--
(Please reply only to the newsgroup)
Tuesday, March 20, 2012
query to populate child tables
hi all, can anyone help me?
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightThis is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If I understand you correctly, you want to retreive the Hazard =Information and include the Consequences and Controls for each Hazard, =and you want to limit this by Department. But you also indicated that =there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID =3D cq.HazardID
JOIN Conrols cn
ON h.HazardID =3D cn.HazardID
WHERE h.Department =3D <Department>
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<dwightsmail@.gmail.com> wrote in message =news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> > hi all, can anyone help me?
> > I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
> > To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
> > The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
> > What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
> > If anyone can give me any advice to get me started i will be =incredibly
> grateful
> > thank you
> > > Table information is below
> > Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
> > Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
> > Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
> > > > dwight
>
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I understand you correctly, you want =to retreive the Hazard Information and include the Consequences and Controls for =each Hazard, and you want to limit this by Department. But you also indicated =that there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID , h.Hazard , cq.Consequence , =cn.ControlFROM Hazards h JOIN Consequences =cq ON h.HazardID =3D cq.HazardID JOIN Conrols cn ON h.HazardID =3D =cn.HazardIDWHERE h.Department =3D
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com=...> => hi all, can anyone help me?> > I am a relative newbie to sql =server and I am more familiar with> Enterprise Manager than QA. I have made =many many access databases> though. I am making an asp.net application =where by there are a set> number of users, about 80, each one logs in and =manages information> within their department.> > To get =them started a manager has written 10 different hazards that> will =apply to all of the departments, and he has written consequences> and =controls for the hazards. Each department must have this> information as each =will manage and deal with them differently> > The hazard =information is stored in a main 'hazards' table, and the> consequences and =controls are stored in related tables linked by the> 'hazardID' from the main =table to a foreign key 'hazardID' in the> related tables> > =What i want to know is if there is a relatively simple way of using a> =query to populate the 10 hazards to each department, and to also> include =the related table links, i dont mind renaming the departments> names =to match each hazard, but i do not want to have to relink the> related =tables manually> > If anyone can give me any advice to get me =started i will be incredibly> grateful> > thank you> => > Table information is below> > Hazards> --> HazardID - identity key field> Hazard - varchar> Department - varchar> > =Consequences> --> ConsequenceID - identity key =field> HazardID - FK> Consequence - varchar> > =Controls> --> ControlID - identity key field> HazardID - FK> Control - varchar> > > > dwight>
--=_NextPart_000_00FF_01C6AABD.53A28AE0--|||Hi Arnie
Thank you for the response
I find it hard to explain things like this :)
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and include the Consequences and Controls for each Hazard, and you want to limit this by Department. But you also indicated that there were 10 Hazards that were common to all Departments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> >
> > hi all, can anyone help me?
> >
> > I am a relative newbie to sql server and I am more familiar with
> > Enterprise Manager than QA. I have made many many access databases
> > though. I am making an asp.net application where by there are a set
> > number of users, about 80, each one logs in and manages information
> > within their department.
> >
> > To get them started a manager has written 10 different hazards that
> > will apply to all of the departments, and he has written consequences
> > and controls for the hazards. Each department must have this
> > information as each will manage and deal with them differently
> >
> > The hazard information is stored in a main 'hazards' table, and the
> > consequences and controls are stored in related tables linked by the
> > 'hazardID' from the main table to a foreign key 'hazardID' in the
> > related tables
> >
> > What i want to know is if there is a relatively simple way of using a
> > query to populate the 10 hazards to each department, and to also
> > include the related table links, i dont mind renaming the departments
> > names to match each hazard, but i do not want to have to relink the
> > related tables manually
> >
> > If anyone can give me any advice to get me started i will be incredibly
> > grateful
> >
> > thank you
> >
> >
> > Table information is below
> >
> > Hazards
> > --
> > HazardID - identity key field
> > Hazard - varchar
> > Department - varchar
> >
> > Consequences
> > --
> > ConsequenceID - identity key field
> > HazardID - FK
> > Consequence - varchar
> >
> > Controls
> > --
> > ControlID - identity key field
> > HazardID - FK
> > Control - varchar
> >
> >
> >
> > dwight
> >
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> If I understand you correctly, you want to retreive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated that
> there were 10 Hazards that were common to all Departments. If you want a list of
> Hazards by Department, this may work:
>
> SELECT
> h.HazardID ,
> h.Hazard , cq.Consequence , cn.ControlFROM
> Hazards h JOIN Consequences cq
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = cn.HazardIDWHERE
> h.Department =
> -- Arnie RowlandMost good judgment
> comes from experience. Most experience comes from bad judgment. -
> Anonymous
>
>
>< href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com> wrote in
> message href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com face=Arial size=2>...> > hi all,
> can anyone help me?> > I am a relative newbie to sql server and I
> am more familiar with> Enterprise Manager than QA. I have made many many
> access databases> though. I am making an asp.net application where by
> there are a set> number of users, about 80, each one logs in and manages
> information> within their department.> > To get them
> started a manager has written 10 different hazards that> will apply to
> all of the departments, and he has written consequences> and controls for
> the hazards. Each department must have this> information as each will
> manage and deal with them differently> > The hazard information is
> stored in a main 'hazards' table, and the> consequences and controls are
> stored in related tables linked by the> 'hazardID' from the main table to
> a foreign key 'hazardID' in the> related tables> > What i
> want to know is if there is a relatively simple way of using a> query to
> populate the 10 hazards to each department, and to also> include the
> related table links, i dont mind renaming the departments> names to match
> each hazard, but i do not want to have to relink the> related tables
> manually> > If anyone can give me any advice to get me started i
> will be incredibly> grateful> > thank you> >
> > Table information is below> > Hazards>
> --> HazardID - identity key field> Hazard -
> varchar> Department - varchar> > Consequences>
> --> ConsequenceID - identity key field>
> HazardID - FK> Consequence - varchar> > Controls>
> --> ControlID - identity key field> HazardID -
> FK> Control - varchar> > > >
> dwight>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightThis is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If I understand you correctly, you want to retreive the Hazard =Information and include the Consequences and Controls for each Hazard, =and you want to limit this by Department. But you also indicated that =there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID =3D cq.HazardID
JOIN Conrols cn
ON h.HazardID =3D cn.HazardID
WHERE h.Department =3D <Department>
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<dwightsmail@.gmail.com> wrote in message =news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> > hi all, can anyone help me?
> > I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
> > To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
> > The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
> > What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
> > If anyone can give me any advice to get me started i will be =incredibly
> grateful
> > thank you
> > > Table information is below
> > Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
> > Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
> > Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
> > > > dwight
>
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I understand you correctly, you want =to retreive the Hazard Information and include the Consequences and Controls for =each Hazard, and you want to limit this by Department. But you also indicated =that there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID , h.Hazard , cq.Consequence , =cn.ControlFROM Hazards h JOIN Consequences =cq ON h.HazardID =3D cq.HazardID JOIN Conrols cn ON h.HazardID =3D =cn.HazardIDWHERE h.Department =3D
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
--=_NextPart_000_00FF_01C6AABD.53A28AE0--|||Hi Arnie
Thank you for the response
I find it hard to explain things like this :)
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and include the Consequences and Controls for each Hazard, and you want to limit this by Department. But you also indicated that there were 10 Hazards that were common to all Departments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> >
> > hi all, can anyone help me?
> >
> > I am a relative newbie to sql server and I am more familiar with
> > Enterprise Manager than QA. I have made many many access databases
> > though. I am making an asp.net application where by there are a set
> > number of users, about 80, each one logs in and manages information
> > within their department.
> >
> > To get them started a manager has written 10 different hazards that
> > will apply to all of the departments, and he has written consequences
> > and controls for the hazards. Each department must have this
> > information as each will manage and deal with them differently
> >
> > The hazard information is stored in a main 'hazards' table, and the
> > consequences and controls are stored in related tables linked by the
> > 'hazardID' from the main table to a foreign key 'hazardID' in the
> > related tables
> >
> > What i want to know is if there is a relatively simple way of using a
> > query to populate the 10 hazards to each department, and to also
> > include the related table links, i dont mind renaming the departments
> > names to match each hazard, but i do not want to have to relink the
> > related tables manually
> >
> > If anyone can give me any advice to get me started i will be incredibly
> > grateful
> >
> > thank you
> >
> >
> > Table information is below
> >
> > Hazards
> > --
> > HazardID - identity key field
> > Hazard - varchar
> > Department - varchar
> >
> > Consequences
> > --
> > ConsequenceID - identity key field
> > HazardID - FK
> > Consequence - varchar
> >
> > Controls
> > --
> > ControlID - identity key field
> > HazardID - FK
> > Control - varchar
> >
> >
> >
> > dwight
> >
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> If I understand you correctly, you want to retreive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated that
> there were 10 Hazards that were common to all Departments. If you want a list of
> Hazards by Department, this may work:
>
> SELECT
> h.HazardID ,
> h.Hazard , cq.Consequence , cn.ControlFROM
> Hazards h JOIN Consequences cq
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = cn.HazardIDWHERE
> h.Department =
> -- Arnie RowlandMost good judgment
> comes from experience. Most experience comes from bad judgment. -
> Anonymous
>
>
>< href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com> wrote in
> message href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com face=Arial size=2>...> > hi all,
> can anyone help me?> > I am a relative newbie to sql server and I
> am more familiar with> Enterprise Manager than QA. I have made many many
> access databases> though. I am making an asp.net application where by
> there are a set> number of users, about 80, each one logs in and manages
> information> within their department.> > To get them
> started a manager has written 10 different hazards that> will apply to
> all of the departments, and he has written consequences> and controls for
> the hazards. Each department must have this> information as each will
> manage and deal with them differently> > The hazard information is
> stored in a main 'hazards' table, and the> consequences and controls are
> stored in related tables linked by the> 'hazardID' from the main table to
> a foreign key 'hazardID' in the> related tables> > What i
> want to know is if there is a relatively simple way of using a> query to
> populate the 10 hazards to each department, and to also> include the
> related table links, i dont mind renaming the departments> names to match
> each hazard, but i do not want to have to relink the> related tables
> manually> > If anyone can give me any advice to get me started i
> will be incredibly> grateful> > thank you> >
> > Table information is below> > Hazards>
> --> HazardID - identity key field> Hazard -
> varchar> Department - varchar> > Consequences>
> --> ConsequenceID - identity key field>
> HazardID - FK> Consequence - varchar> > Controls>
> --> ControlID - identity key field> HazardID -
> FK> Control - varchar> > > >
> dwight>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
Wednesday, March 7, 2012
Query Timing out in Enterprise Manager
I am running Enterprise Manager in SQL Server 2000 and attempting
to do some table maintenance. When I run a delete query that should
delete approx. 600,000 rows, the query timesout and gives the following
message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
when I run the same query using "SQL Query Analyzer" the query runs
for 4-5 minutes and finishes with no issues.
What is the difference between running the query in Query Analyzer and
Enterprise Manager?
Here is the query:
Delete from tablename
where (fieldname like '155%')
Enterprise Manager was designed as more of an admin tool,
not a client data tool. It's not meant to be used for long
running queries. Microsoft has a KB article regarding
timeouts with Enterprise Manager. The workaround is to use
Query Analyzer for long running queries:
http://support.microsoft.com/?id=247070
-Sue
On Thu, 22 Mar 2007 07:15:31 -0700, keith c
<keithc@.discussions.microsoft.com> wrote:
>I am running Enterprise Manager in SQL Server 2000 and attempting
>to do some table maintenance. When I run a delete query that should
>delete approx. 600,000 rows, the query timesout and gives the following
>message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
>when I run the same query using "SQL Query Analyzer" the query runs
>for 4-5 minutes and finishes with no issues.
>What is the difference between running the query in Query Analyzer and
>Enterprise Manager?
>Here is the query:
> Delete from tablename
> where (fieldname like '155%')
|||rofl
WOW... I had no idea that they had a write up for that
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:msf703tleevcrihl3pfvsnljvhi5u5ac1t@.4ax.com...
> Enterprise Manager was designed as more of an admin tool,
> not a client data tool. It's not meant to be used for long
> running queries. Microsoft has a KB article regarding
> timeouts with Enterprise Manager. The workaround is to use
> Query Analyzer for long running queries:
> http://support.microsoft.com/?id=247070
> -Sue
> On Thu, 22 Mar 2007 07:15:31 -0700, keith c
> <keithc@.discussions.microsoft.com> wrote:
>
to do some table maintenance. When I run a delete query that should
delete approx. 600,000 rows, the query timesout and gives the following
message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
when I run the same query using "SQL Query Analyzer" the query runs
for 4-5 minutes and finishes with no issues.
What is the difference between running the query in Query Analyzer and
Enterprise Manager?
Here is the query:
Delete from tablename
where (fieldname like '155%')
Enterprise Manager was designed as more of an admin tool,
not a client data tool. It's not meant to be used for long
running queries. Microsoft has a KB article regarding
timeouts with Enterprise Manager. The workaround is to use
Query Analyzer for long running queries:
http://support.microsoft.com/?id=247070
-Sue
On Thu, 22 Mar 2007 07:15:31 -0700, keith c
<keithc@.discussions.microsoft.com> wrote:
>I am running Enterprise Manager in SQL Server 2000 and attempting
>to do some table maintenance. When I run a delete query that should
>delete approx. 600,000 rows, the query timesout and gives the following
>message "[Microsoft][ODBC SQL Server Driver] Timeout expired.", but
>when I run the same query using "SQL Query Analyzer" the query runs
>for 4-5 minutes and finishes with no issues.
>What is the difference between running the query in Query Analyzer and
>Enterprise Manager?
>Here is the query:
> Delete from tablename
> where (fieldname like '155%')
|||rofl
WOW... I had no idea that they had a write up for that
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:msf703tleevcrihl3pfvsnljvhi5u5ac1t@.4ax.com...
> Enterprise Manager was designed as more of an admin tool,
> not a client data tool. It's not meant to be used for long
> running queries. Microsoft has a KB article regarding
> timeouts with Enterprise Manager. The workaround is to use
> Query Analyzer for long running queries:
> http://support.microsoft.com/?id=247070
> -Sue
> On Thu, 22 Mar 2007 07:15:31 -0700, keith c
> <keithc@.discussions.microsoft.com> wrote:
>
Subscribe to:
Posts (Atom)