Query with multiple arguments?

Hi, I'm a bit stumped as to how to do this.
I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.

E.g. Say I have two results in my string[], fred and bob.
How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";

IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.

Any help is greatly appreciated.

schuminator:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

try out as below

 String strUsers = String.Empty;for (int a = 0; a < userArray.Length; a++) strUsers = strUsers + @."'" + userArray[a] + @."',"; strUsers = strUsers.Substring(0, strUsers.Length - 1); conn.Open(); SqlCommand command =new SqlCommand("SELECT * FROM tblUsers WHERE UserName in (" + strUsers +")", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close();

Good Luck./.

|||

Sorry, I tried to delete the thread but it was too late.
I got it sorted...it was so simple, I feel like an idiot!

"SELECT * FROM tblUsers WHERE UserName='fred' OR UserName='bob'" etc

I also wrote a little for loop to add another OR... to the end of the string when necessary.

|||

you can use above method also...

so instead of UserName='fred' OR UserName='bob'"......

if will formulate query using IN keywork as..

UserName in ('fred','bob')

|||

ahh ok awesome, thanks :)

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)