Query/Table Join

Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

MikeI guess this is what u r trying for.

select memberid from members where memberid not in(select memberid from attendance where meetingid = 47)

Originally posted by mycoolross
Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

Mike|||THANK YOU!!

Query window (sql2005) displays only 1000 rows

Hi Guys,

when I run a query , it only displays 1000 rows in the query window. Any one know how to change this setting?

thanks

Sonny

On the [Tools] menu, then [Option], then [Query Execution].

Check the SET ROWCOUNT value. If it is 1000, change to 0 for all rows.

|||

thanks Arnie, that way quick

Sonny

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_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)

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)

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)

Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

Query time out while inserting data

Hi guys,
when inserting data in a table i am getting the following
error:
Error : -2147217871
Source : Microsoft OLE DB Provider for SQL Server
Descripion : Timeout expired
Help Context : 1000440
The data is added through a stored procedure which is
called under MTS from a dll which in turn is accessed
through a ASP page.
The table has arround 150,000 records. This is not a bluk
insert. Why am i getting this error, is this a locking
problem?
Please reply ASAP
Thanking you.Could be a locking problem. Use EM, Current Activity, Profiler, sp_who, sp_who2 and sp_lock to
troubleshoot.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Saleem Subhi" <saleem_subhi@.hotmail.com> wrote in message
news:39d201c37614$137a6e00$a301280a@.phx.gbl...
> Hi guys,
> when inserting data in a table i am getting the following
> error:
> Error : -2147217871
> Source : Microsoft OLE DB Provider for SQL Server
> Descripion : Timeout expired
> Help Context : 1000440
> The data is added through a stored procedure which is
> called under MTS from a dll which in turn is accessed
> through a ASP page.
> The table has arround 150,000 records. This is not a bluk
> insert. Why am i getting this error, is this a locking
> problem?
> Please reply ASAP
> Thanking you.
>

Query the Full-Text Words List?

Hi Guys. I’m doing searches in a DB and am using the Full-Text Indexing.

CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.

So take this code for example:

Dim mySQLStatement As String = "SELECT TOP 100 Description, Price, Stock FROM Products WHERE "

x = Split(strString, " ")

For i = 0 To x.GetUpperBound(0)

If x(i).Length > 2 Then

mySQLStatement &= " CONTAINS(Description, '*" & x(i) & "*') AND "

ElseIf x(i).Length = 1 Or x(i).Length = 2 Then

mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "

End If

Next

If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 5)

mySQLStatement &= " ORDER BY PRICE DESC"

dolog(mySQLStatement)

mySQLCommand = New SqlCommand(mySQLStatement, mySQLConnection)

mySQLAdapter = New SqlDataAdapter(mySQLCommand)

myDataSet = New DataSet : mySQLAdapter.Fill(myDataSet)

That is code I’m using in a small proof-of-concept application I’m writing – so I’m aware I can use StringBuilders and should be using SPs and all that jazz.

It will process the queries people type in, so if the person were to type in “sql server”, it would perform all of the following queries: (this is by design by the way)

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%sq%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%se%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*ser*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serv*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serve*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*server*') ORDER BY PRICE DESC

I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.

This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

So. My question is. How can I find out what strings CONTAINS() does and does not consider searchable words. Because I’d like to check if the query should be executed via LIKE or CONTAINS and build the SQL Statement accordingly. Having said that, using LIKE on its own is running quickly enough, but I would really like to use CONTAINS() instead.

I may of course be making so big logical error here or not understand something in particular, so any help would be appreciated…

Jamie, I need some more information to help answer your question:

What exactly do you mean when you say only the queries in bold "work"? Do you get an error or no results?
What is your sample data like?
What is your intention behind putting '*' before and after a string?

--
Sara Tahir
Program Manager
Microsoft SQL Server

|||

plenderj wrote:

CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.
...
I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.

This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

My question is. How can I find out what strings CONTAINS() does and does not consider searchable words.

Full-Text Search results for your queries depend on multiple things including the wordbreaker, noise word list and whether the intention is to prefix, etc. Without that information, it’s hard to recommend which approach is better.

Here is some information that may help:

Full-Text is token (word) based search and not a substring search. Therefore it will not find arbitrary 3 char string patterns in middle of strings.

Tokenizing is dependent on the wordbreaker and that depends on the language of the column. Some tokenizers could consider the * as punctuation and strip it out - others might leave it intact. In any case Full-Text will be consistent in query and indexing, if the intent was to search the exact string with * and so will be able to match.

Full-Text does do prefix search - meaning if the user provides the leading part of a token, it can find all the tokens that have that prefix. The syntax for such a query is ' "foo*" ' where foo is the prefix and both the prefix and the * are enclosed in double-quotes. So if the intent below of trailing * was to do prefix match, you would need to put it in double-quotes.

There is no support for equivalent suffix match. So if the intent of leading * was that, then Full-Text does not support it.

Full-Text does not have any restrictions on the size of tokes - except 64 characters is max and by default single character token are considered noise.

The only things Full-Text does not consider searchable are the noise words – you can find that list in the noise word file for the specific language.

|||

Hello, everyone! I started to use full-text search and found out this not nice limitation in length of words and noise words. Firstly, I'm not sure that this limit exist, because I can search for two letters long words. If we look in noise file we can find all letters typed in, so one letter long words are noise words and this is not lenght limitation (must try to remove those letters and try search for them).
Again, if I have noise words in query I get error message "Server: Msg 7619, Level 16, State 1, Line 1" which doesn't tell me anything about noise word, so I can't determine it and do proper search using "like" for noise words and "contains" for all other.
Finaly, question. What is a best practice to go around this problem. Import all noise words into application or remove all lines from noise words file.

|||Strange that contains doesn't allow one letter words (just letter) but in noise word files I can see letters. If I remove them, doesn't help.|||Erik,
Firstly, The above reference limit of 3 characters does not exist (otherwise, what would of been the point in adding FTS to SQL 7.0, 2000 and 2005 in the first place?)

Secondly, what exactly was the noise word file you modified? Was it noise.enu (US English) or some other noise.* file under \FTDATA\SQLServer\Config where you have SQL Server 2000 installed? Assuming that you modified the correct file for the "Language for Word Breaker" for your FT-enabled table, did you run a Full Population? If not, then re-run a Full Population as this is required after making changes to the noise word files.

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||I removed data from all of noise word files, but limit for one letter is stil exist. I also done full population.
It's not realy a problem now, problem is that if I want to install my application I also need different instance of SQL server, right? Also, I can run application on same instance with others but then I need to determine those noise words, but I'm not sure that there is a safe way to do it.|||Hi Erik,
So, you left only one letter? What really needs to be done is to leave a single space character in your language specific noise.* file. If you're using US English, then the noise word file is noise.enu. You can determine this by running the following code in your FT-enabled database:

sp_help_fulltext_columns
-- FULLTEXT_LANGUAGE value of 1033 = US English

There was no need to remove all text from all noise word files. If you leave a single space in the nosie word file (noise.enu) and then run a Full Population it will work for you.

No, you don't need to install a different instance (unless you want to), as you can use the default install of SQL Server 2000. Note, I'm assuming that you're using SQL Server 2000 (let me know if you're using SQL Server 2005), there is one MSSearch service for SQL 2000, but the noise word files are installed into each named instance folder, specificly under:

\MSSQL<$Instance_Name>\FTDATA\SQLServer<$Instance_Name>\Config

for example: for Instance name "SQL80":
\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

On safe way to determine where are the instance noise word files, is to store the above path in a table along with the instance name. The path is also recorded in the registry under an instance specific registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override\SQLServer$SQL2K\English (United States)
Locale value= 1033
NoiseFile value= d:\mssql80\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||Hi Jamie,
Drop me a line re: this an S205 noise lists and how to develop your own/circumvent.
Do remember they are localised.
fb|||I've since resolved/worked-around by using LIKE:

Shared Function GetMatchingDSForProducts(ByVal strString As String, ByRef mySQLStatement As String) As DataSet
Dim mySQLConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConn").ToString)
mySQLStatement = "SELECT TOP 100 Description, Price, Stock, [SAP Code] FROM Products WHERE "
mySQLConnection.Open()
Dim x() As String, i As Integer
x = Split(strString, " ")
For i = 0 To x.GetUpperBound(0)
If Not x(i) = "instock" Then mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "
Next
If Not InStr(strString, "instock") = 0 Then mySQLStatement &= " STOCK > 0 "
If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 4)
mySQLStatement &= " ORDER BY PRICE DESC"
Dim mySQLCommand As New SqlCommand(mySQLStatement, mySQLConnection)
Dim mySQLAdapter As New SqlDataAdapter(mySQLCommand)
mySQLAdapter.SelectCommand = mySQLCommand
Dim myDataSet As New DataSet : mySQLAdapter.Fill(myDataSet)
mySQLConnection.Close()
Return myDataSet
End Function