Network cables

I just absolutely had to share this. The below email just arrived at our
helpdesk from one of the suits upstairs:
<suit>
Just a quick one. When I hooked up my PC I plugged in grey cable that was
about 20 feet long. It works but I'm wondering if I really need a proper
Ethernet cable (usually blue?). Am I totally imagining this or could I be
using the wrong wire?
</suit>
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiMike Labosh wrote:
> I just absolutely had to share this. The below email just arrived at
> our helpdesk from one of the suits upstairs:
> <suit>
> Just a quick one. When I hooked up my PC I plugged in grey cable that
> was about 20 feet long. It works but I'm wondering if I really need a
> proper Ethernet cable (usually blue?). Am I totally imagining this or
> could I be using the wrong wire?
> </suit>
Get him a shorter, blue one. They look better and feel better about
themselves. I've met many grey cables who feel left out of the
color-coded community. Sometimes giving the grey cable a name tag can
make him feel more important. So instead of "grey cable," he'll be
referred to as "Jim".
"Just a quick one. When I hooked up my PC I plugged in Jim (he's about
about 20 feet long). Jim works, but I'm wondering if I need a more
colorful ethernet cable (preferably a blue one named Sasha). Am I
totally wrong about this or could Jim be all I need?"
David Gugick
Imceda Software
www.imceda.com|||Reply back stating that the cable isn't grey but is actually silver, and
silver cables are reserved for senior management.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uuU6XOIXFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Mike Labosh wrote:
> Get him a shorter, blue one. They look better and feel better about
> themselves. I've met many grey cables who feel left out of the
> color-coded community. Sometimes giving the grey cable a name tag can
> make him feel more important. So instead of "grey cable," he'll be
> referred to as "Jim".
> "Just a quick one. When I hooked up my PC I plugged in Jim (he's about
> about 20 feet long). Jim works, but I'm wondering if I need a more
> colorful ethernet cable (preferably a blue one named Sasha). Am I
> totally wrong about this or could Jim be all I need?"
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||> Reply back stating that the cable isn't grey but is actually silver, and
> silver cables are reserved for senior management.
The support tech answering the request told the guy that it's ok because
grey cables are faster than blue ones. This is really turning into a scene
from "Dilbert".
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uta7A0IXFHA.1468@.tk2msftngp13.phx.gbl...
> The support tech answering the request told the guy that it's ok because
> grey cables are faster than blue ones. This is really turning into a
scene
> from "Dilbert".
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
If he is an important suit, you'd better get the Belkin Gold or Monster
Cable one for 4x the price.
This is not a scene from Dilbert. It is BIG business. Just take a look at
the CompUSA cable aisles.|||"User" <user@.aol.com> wrote in message
news:#O5mq0MXFHA.2796@.TK2MSFTNGP09.phx.gbl...
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:uta7A0IXFHA.1468@.tk2msftngp13.phx.gbl...
and
> scene
> If he is an important suit, you'd better get the Belkin Gold or Monster
> Cable one for 4x the price.
> This is not a scene from Dilbert. It is BIG business. Just take a look at
> the CompUSA cable aisles.
>
And yes, the packaging and display imply the cables are faster.

NetSend notifications

I received the error message listed below after stopping and restarting my
SQL Server 2005 database server.
Please help me resolve this error.
Thank You,
Errorlog Error Message:
[364] The Messenger service has not been started - NetSend notifications
will not be sent
Joe K. wrote:
> I received the error message listed below after stopping and restarting my
> SQL Server 2005 database server.
> Please help me resolve this error.
> Thank You,
>
> Errorlog Error Message:
> [364] The Messenger service has not been started - NetSend notifications
> will not be sent
>
Has the Messenger service been started?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Why would you even consider starting the Messenger service on a Server?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F40A5.3040400@.realsqlguy.com...
> Joe K. wrote:
> Has the Messenger service been started?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql

NetSend notifications

I received the error message listed below after stopping and restarting my
SQL Server 2005 database server.
Please help me resolve this error.
Thank You,
Errorlog Error Message:
[364] The Messenger service has not been started - NetSend notifications
will not be sentJoe K. wrote:
> I received the error message listed below after stopping and restarting my
> SQL Server 2005 database server.
> Please help me resolve this error.
> Thank You,
>
> Errorlog Error Message:
> [364] The Messenger service has not been started - NetSend notificatio
ns
> will not be sent
>
Has the Messenger service been started?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Why would you even consider starting the Messenger service on a Server?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F40A5.3040400@.realsqlguy.com...
> Joe K. wrote:
> Has the Messenger service been started?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Arnie Rowland wrote:
> Why would you even consider starting the Messenger service on a Server?
>
Hey, I was just asking the obvious question based on the error
message... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My response was not directed at you Tracy, I expect that you know better...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4A0E.7040100@.realsqlguy.com...
> Arnie Rowland wrote:
> Hey, I was just asking the obvious question based on the error message...
> :-)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Agent has an option to alert an operator through NET SEND, which uses the Me
ssenger service. If the
service isn't started, then Agent will write this entry to the eventlog. The
messenger and Alerter
(used on the client which receives the message) is considered security risky
and are both disabled
with XP sp2. If you don't Alert operators though NET SEND you can ignore thi
s entry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A5124E41-B6AC-4A8F-8C8F-CFB93F4568F8@.microsoft.com...
> I received the error message listed below after stopping and restarting my
> SQL Server 2005 database server.
> Please help me resolve this error.
> Thank You,
>
> Errorlog Error Message:
> [364] The Messenger service has not been started - NetSend notificatio
ns
> will not be sent
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>sql

Nested SQL or IF END LOGIC ?

Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.

episode_key chart_component_key deficiency_type deficiency_status status_date 13789881 173398 408 C 8/4/2007 13789881 173488 409 S 8/4/2007 13789881 173703 409 S 8/6/2007 13789881 174568 1028 S 8/7/2007 13789881 176213 421 S 8/9/2007 13789881 176214 421 S 8/9/2007 13789881 176215 421 S 8/9/2007 13789881 176216 421 S 8/9/2007 13789881 176218 421 S 8/9/2007 13789881 176219 406 D 8/9/2007

Can someone help me here?

Chuck,

You problem and sample data are a bit ambiguous.

It appears that all of this data belongs to one Episode -since there is but one Episode_key value.

Every Chart_Component-key appears to be unique.

So what is the expected result from this set of data?

|||

This should do it for you

Code Snippet

DELETED - read edit

Edit : I re-read the question and realized you said "all deficiency_status is equal to 'C'" not just that one of the records in the episdoe is set to 'C'.

|||

This one makes sure that ALL the definciency code fields in the recordset of one episode are 'C'

Code Snippet

CREATE Table episode_table (

episode_key INT,

chart_component_key INT,

deficiency_type INT,

deficiency_status CHAR,

status_date DATETIME

)

INSERT INTO episode_table VALUES (13789881, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789881, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789881, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789881, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789881, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176219, 406, 'D', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789882, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789882, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789882, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789882, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176219, 406, 'D', '8/12/2007' )

INSERT INTO episode_table VALUES (13789883, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789883, 173488, 409, 'C', '8/4/2007' )

INSERT INTO episode_table VALUES (13789883, 176219, 406, 'C', '8/12/2007' )

SELECT et1.episode_key, MAX(et1.status_date)

FROM episode_table et1 LEFT OUTER JOIN episode_table et2 ON

-- You only have to include the unique primary key fields here, i didn't want to assume

-- that you had one though

(et1.episode_key = et2.episode_key AND et1.chart_component_key = et2.chart_component_key AND

et1.deficiency_type = et2.deficiency_type AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C')

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)

I joined the tables on all fields to be sure, but you only need to include the fields in the primary key if there is one, and the deficiency status clause.|||Each patient episode may involve several physicians involved in their case and making notes in the chart. Each chart_component_key should signify a different physician making notes in the chart. A "completed" chart is defined as when all physicians have completed their charting thus reflecting a "C" in the deficiency_status fields.

I need to return a "Completed Chart Date" which would mean that all chart_component_keys would be "C" and I would then take the MAX(status_date) as the completed chart date.

|||

Thanks Shawn. I was able to modify this and make it work. This piece of code was to pull the final field in a report I am working on. I am trying to plug it into my main SQL code for the big report, but having difficulties. I'll start a new thread for this issue.

Thanks!

|||

You can use the query below:

Code Snippet

select t.episode_key, max(t.status_date) as completed_chart_date
from <your_table> as t
group by t.episode_key
having count(*) = sum(case t.deficiency_status when 'C' then 1 end);

|||

Very nice Umachandar!

I have to remember that approach, very efficient.

Need to speed up query.

OK Guys. I'm fed up of the query below taking too much time. I CANT
change the query since it is generated by a 3rd party product. I can
change indexes and add new indexes though.
The schema of the tables is given below. The most expensive operation
is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
index as a covering index to cover the
query but that has not seemed to help.

Any ideas, suggestions are most welcome ...

select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT
WHERE
USER_NAME = 'XXXX) )

************************************************** ************

VGNCCB_ROLE_JT

Column_nameType
IDint
ROLE_IDint
USER_NAMEnvarchar
GROUP_IDint

PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARYID
speed_up_loginnonclustered located on PRIMARYUSER_NAME, GROUP_ID,
ROLE_ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARYUSER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARYGROUP_ID

************************************************** *************

VGNCCB_GROUP_USER_JT

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
IDint
GROUP_IDint
USER_NAMEnvarchar

PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARYID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARYGROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARYUSER_NAME

************************************************** *****************Jack A (InformixMail@.yahoo.com) writes:
> OK Guys. I'm fed up of the query below taking too much time. I CANT
> change the query since it is generated by a 3rd party product. I can
> change indexes and add new indexes though.
> The schema of the tables is given below. The most expensive operation
> is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
> index as a covering index to cover the
> query but that has not seemed to help.

One idea is to try whether you can make:

> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT
> WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT
> WHERE
> USER_NAME = 'XXXX) )

into an indexed view, and hope that the optimizer picks it up. But there
are plenty of restrictions on indexed views, so if you create a view,
it may not be indexable. And even if you can, there is no guarantee that
the optimizer will use the view.

> PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary key located on
> PRIMARY ID
> speed_up_login nonclustered located on PRIMARY USER_NAME,
> GROUP_ID,
> ROLE_ID
> VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY USER_NAME
> VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY GROUP_ID

It seems meaningless to have the clustered index on ID. I would change
the primary key into non-clustered. Then I would try a clustered
index on ROLE_ID and keep the non-clustered indexes on USER_NAME
and GROUP_ID, hoping that the optimizer will use index intersection
on the two covering indexes. (With ROLE_ID as clustered, it will be
part of the non-clustered indexes too.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951B54A8CD1CYazorman@.127.0.0.1>...
> Jack A (InformixMail@.yahoo.com) writes:
> > OK Guys. I'm fed up of the query below taking too much time. I CANT
> > change the query since it is generated by a 3rd party product. I can
> > change indexes and add new indexes though.
> > The schema of the tables is given below. The most expensive operation
> > is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
> > index as a covering index to cover the
> > query but that has not seemed to help.
> One idea is to try whether you can make:
> > (select ROLE_ID
> > FROM
> > vign.VGNCCB_ROLE_JT
> > WHERE
> > USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> > FROM
> > vign.VGNCCB_GROUP_USER_JT
> > WHERE
> > USER_NAME = 'XXXX) )
> into an indexed view, and hope that the optimizer picks it up. But there
> are plenty of restrictions on indexed views, so if you create a view,
> it may not be indexable. And even if you can, there is no guarantee that
> the optimizer will use the view.
> > PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary key located on
> > PRIMARY ID
> > speed_up_login nonclustered located on PRIMARY USER_NAME,
> > GROUP_ID,
> > ROLE_ID
> > VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY USER_NAME
> > VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY GROUP_ID
> It seems meaningless to have the clustered index on ID. I would change
> the primary key into non-clustered. Then I would try a clustered
> index on ROLE_ID and keep the non-clustered indexes on USER_NAME
> and GROUP_ID, hoping that the optimizer will use index intersection
> on the two covering indexes. (With ROLE_ID as clustered, it will be
> part of the non-clustered indexes too.

I quite agree with Erland about making the ID column NON-clustered,
and then using your clustered index on something more suitable.

Just for reference, though, your covered index doesn't cover the query
(if it did, you wouldn't still be seeing bookmark lookups). You
haven't included any of the columns in the SELECT clause in the index,
so it still needs to go back to the main table to get these (a truly
covered index doesn't need to go to the main table at all). However,
if you do this, your index will be about the same size as your table -
not great. Erland's suggestion of re-arranging the clustered index
onto a better column would seem to be the best.|||Philip Yale (philipyale@.btopenworld.com) writes:
> Just for reference, though, your covered index doesn't cover the query
> (if it did, you wouldn't still be seeing bookmark lookups). You
> haven't included any of the columns in the SELECT clause in the index,

Jack's speed_up_login index on VGNCCB_ROLE_JT is covering. I think you are
mixing up the tables.

However, since the index also includes ID, it is a variation of the
clustered index. And since there are conditions of two of the values,
the best SQL Server could do is to scan that index. And since did
not scan the table prior to adding index, there is on reason why it should
start to scan an index which is equal to the table. So speed_up_login is
probably not used.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need to speed up query

OK Guys. I'm fed up of the query below taking too much
time. I CANT change the query since it is generated by a
3rd party product. I can change indexes and add new
indexes though.
The schema of the tables is given below. The most
expensive operation is a bookmark lookup on
VGNCCB_ROLE_JT. I created the speed_up_login index as a
covering index to cover the
query but that has not seemed to help.
Any ideas, suggestions are most welcome ...
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT
WHERE
USER_NAME = 'XXXX) )
***********************************************************
***
VGNCCB_ROLE_JT
Column_name Type
ID int
ROLE_ID int
USER_NAME nvarchar
GROUP_ID int
PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
key located on PRIMARY ID
speed_up_login nonclustered located on PRIMARY USER_NAME,
GROUP_ID, ROLE_ID
VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
USER_NAME
VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
GROUP_ID
***********************************************************
****
VGNCCB_GROUP_USER_JT
Column_name Type Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
ID int
GROUP_ID int
USER_NAME nvarchar
PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
key located on PRIMARY ID
VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
PRIMARY GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
PRIMARY USER_NAME
***********************************************************
********Jack,
I'll have a go at it...
1. Create a nonclustered index on
VGNCCB_GROUP_USER_JT(USER_NAME,GROUP_ID)
or
Make index VGNCCB_GROUP_USER_JT_INDEX1 clustered and the primary key
nonclustered.
In fact, my guess is that the USER_NAME-GROUP_ID combination should be
unique. In that case, you should not add additional indexes, but simply
add a UNIQUE constraint to the table on (USER_NAME,GROUP_ID). This will
automatically cause SQL-Server to add a unique (nonclustered) index.
2. Change the primary key on table VGNCCB_ROLE_JT to nonclustered and
add a clustered index on (ROLE_ID). Make sure you keep the
VGNCCB_ROLE_JT_INDEX1 and VGNCCB_ROLE_JT_INDEX2 indexes.
3. Create a clustered index on VGNCCB_ROLE (ROLE_ID)
I would be interested in the query plan before and after these
changes...
Hope this helps,
Gert-Jan
Jack A wrote:
> OK Guys. I'm fed up of the query below taking too much
> time. I CANT change the query since it is generated by a
> 3rd party product. I can change indexes and add new
> indexes though.
> The schema of the tables is given below. The most
> expensive operation is a bookmark lookup on
> VGNCCB_ROLE_JT. I created the speed_up_login index as a
> covering index to cover the
> query but that has not seemed to help.
> Any ideas, suggestions are most welcome ...
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE
> WHERE
> ROLE_ID in (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT
> WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT
> WHERE
> USER_NAME = 'XXXX) )
> ***********************************************************
> ***
> VGNCCB_ROLE_JT
>
> Column_name Type
> ID int
> ROLE_ID int
> USER_NAME nvarchar
> GROUP_ID int
> PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
> key located on PRIMARY ID
> speed_up_login nonclustered located on PRIMARY USER_NAME,
> GROUP_ID, ROLE_ID
> VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
> USER_NAME
> VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
> GROUP_ID
> ***********************************************************
> ****
> VGNCCB_GROUP_USER_JT
>
>
> Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> ID int
> GROUP_ID int
> USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
> key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
> PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
> PRIMARY USER_NAME
> ***********************************************************
> ********
--
(Please reply only to the newsgroup)|||Jack,
Any chance you could show the query plans? I think Gert-Jan has made
good suggestions, and I would try them first.
I'm wondering, though, whether SQL Server realizes values of a column
Y retrieved are in order when a predicate is WHERE X = 'this' AND [any
predicate on Y] and it uses an index seek from an index starting with
(X,Y). If it does, it can use a merge instead of nested loops to
evaluate the INs faster. It's also not clear which IN will benefit most
from a merge instead of nested loops, if it turns out to be possible to
coax one from the optimizer. If Gert-Jan's suggestions don't help
enough, and especially if user 'XXXX' accounts for a good percentage of
the rows, it may be worth trying either
covering or clustered indexes on VGNCCB_ROLE_JT and
VGNCCB_GROUP_USER_JT that have GROUP_ID as the first column
or
changing the clustered index on VGNCCB_ROLE to (ROLE_ID, ID) and
making the clustered or covering index on VGNCCB_ROLE_JT begin with ROLE_ID.
It's late, so I might be misreading something and making poor
suggestion, but it's an interesting question, and I hope you'll let us
know how it turns out.
Steve Kass
Drew University
Jack A wrote:
>OK Guys. I'm fed up of the query below taking too much
>time. I CANT change the query since it is generated by a
>3rd party product. I can change indexes and add new
>indexes though.
>The schema of the tables is given below. The most
>expensive operation is a bookmark lookup on
>VGNCCB_ROLE_JT. I created the speed_up_login index as a
>covering index to cover the
>query but that has not seemed to help.
>Any ideas, suggestions are most welcome ...
>
>
>select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM
> vign.VGNCCB_ROLE
>WHERE
> ROLE_ID in (select ROLE_ID
>FROM
> vign.VGNCCB_ROLE_JT
>WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
>FROM
> vign.VGNCCB_GROUP_USER_JT
>WHERE
> USER_NAME = 'XXXX) )
>***********************************************************
>***
>VGNCCB_ROLE_JT
>
>Column_name Type
>ID int
>ROLE_ID int
>USER_NAME nvarchar
>GROUP_ID int
>PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
>key located on PRIMARY ID
>speed_up_login nonclustered located on PRIMARY USER_NAME,
>GROUP_ID, ROLE_ID
>VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
> USER_NAME
>VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
> GROUP_ID
>***********************************************************
>****
>VGNCCB_GROUP_USER_JT
>
>
>Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>ID int
>GROUP_ID int
>USER_NAME nvarchar
>PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
>key located on PRIMARY ID
>VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
>PRIMARY GROUP_ID
>VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
>PRIMARY USER_NAME
>***********************************************************
>********
>

Need to speed up query

OK Guys. I'm fed up of the query below taking too much
time. I CANT change the query since it is generated by a
3rd party product. I can change indexes and add new
indexes though.
The schema of the tables is given below. The most
expensive operation is a bookmark lookup on
VGNCCB_ROLE_JT. I created the speed_up_login index as a
covering index to cover the
query but that has not seemed to help.
Any ideas, suggestions are most welcome ...
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT
WHERE
USER_NAME = 'XXXX) )
****************************************
*******************
***
VGNCCB_ROLE_JT
Column_name Type
ID int
ROLE_ID int
USER_NAME nvarchar
GROUP_ID int
PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
key located on PRIMARY ID
speed_up_login nonclustered located on PRIMARY USER_NAME,
GROUP_ID, ROLE_ID
VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
USER_NAME
VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
GROUP_ID
****************************************
*******************
****
VGNCCB_GROUP_USER_JT
Column_name Type Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
ID int
GROUP_ID int
USER_NAME nvarchar
PK__VGNCCB_GROUP_USE__1DBB5747 clustered
, unique, primary
key located on PRIMARY ID
VGNCCB_GROUP_USER_JT_INDEX1 nonclustered
located on
PRIMARY GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2 nonclustered
located on
PRIMARY USER_NAME
****************************************
*******************
********Jack,
I'll have a go at it...
1. Create a nonclustered index on
VGNCCB_GROUP_USER_JT(USER_NAME,GROUP_ID)
or
Make index VGNCCB_GROUP_USER_JT_INDEX1 clustered and the primary key
nonclustered.
In fact, my guess is that the USER_NAME-GROUP_ID combination should be
unique. In that case, you should not add additional indexes, but simply
add a UNIQUE constraint to the table on (USER_NAME,GROUP_ID). This will
automatically cause SQL-Server to add a unique (nonclustered) index.
2. Change the primary key on table VGNCCB_ROLE_JT to nonclustered and
add a clustered index on (ROLE_ID). Make sure you keep the
VGNCCB_ROLE_JT_INDEX1 and VGNCCB_ROLE_JT_INDEX2 indexes.
3. Create a clustered index on VGNCCB_ROLE (ROLE_ID)
I would be interested in the query plan before and after these
changes...
Hope this helps,
Gert-Jan
Jack A wrote:
> OK Guys. I'm fed up of the query below taking too much
> time. I CANT change the query since it is generated by a
> 3rd party product. I can change indexes and add new
> indexes though.
> The schema of the tables is given below. The most
> expensive operation is a bookmark lookup on
> VGNCCB_ROLE_JT. I created the speed_up_login index as a
> covering index to cover the
> query but that has not seemed to help.
> Any ideas, suggestions are most welcome ...
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE
> WHERE
> ROLE_ID in (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT
> WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT
> WHERE
> USER_NAME = 'XXXX) )
> ****************************************
*******************
> ***
> VGNCCB_ROLE_JT
>
> Column_name Type
> ID int
> ROLE_ID int
> USER_NAME nvarchar
> GROUP_ID int
> PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
> key located on PRIMARY ID
> speed_up_login nonclustered located on PRIMARY USER_NAME,
> GROUP_ID, ROLE_ID
> VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
> USER_NAME
> VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
> GROUP_ID
> ****************************************
*******************
> ****
> VGNCCB_GROUP_USER_JT
>
>
> Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> ID int
> GROUP_ID int
> USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
> key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
> PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
> PRIMARY USER_NAME
> ****************************************
*******************
> ********
(Please reply only to the newsgroup)|||Jack,
Any chance you could show the query plans? I think Gert-Jan has made
good suggestions, and I would try them first.
I'm wondering, though, whether SQL Server realizes values of a column
Y retrieved are in order when a predicate is WHERE X = 'this' AND [any
predicate on Y] and it uses an index seek from an index starting with
(X,Y). If it does, it can use a merge instead of nested loops to
evaluate the INs faster. It's also not clear which IN will benefit most
from a merge instead of nested loops, if it turns out to be possible to
coax one from the optimizer. If Gert-Jan's suggestions don't help
enough, and especially if user 'XXXX' accounts for a good percentage of
the rows, it may be worth trying either
covering or clustered indexes on VGNCCB_ROLE_JT and
VGNCCB_GROUP_USER_JT that have GROUP_ID as the first column
or
changing the clustered index on VGNCCB_ROLE to (ROLE_ID, ID) and
making the clustered or covering index on VGNCCB_ROLE_JT begin with ROLE_ID.
It's late, so I might be misreading something and making poor
suggestion, but it's an interesting question, and I hope you'll let us
know how it turns out.
Steve Kass
Drew University
Jack A wrote:

>OK Guys. I'm fed up of the query below taking too much
>time. I CANT change the query since it is generated by a
>3rd party product. I can change indexes and add new
>indexes though.
>The schema of the tables is given below. The most
>expensive operation is a bookmark lookup on
>VGNCCB_ROLE_JT. I created the speed_up_login index as a
>covering index to cover the
>query but that has not seemed to help.
>Any ideas, suggestions are most welcome ...
>
>
>select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM
> vign.VGNCCB_ROLE
>WHERE
> ROLE_ID in (select ROLE_ID
>FROM
> vign.VGNCCB_ROLE_JT
>WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
>FROM
> vign.VGNCCB_GROUP_USER_JT
>WHERE
> USER_NAME = 'XXXX) )
> ****************************************
*******************
>***
>VGNCCB_ROLE_JT
>
>Column_name Type
>ID int
>ROLE_ID int
>USER_NAME nvarchar
>GROUP_ID int
>PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
>key located on PRIMARY ID
>speed_up_login nonclustered located on PRIMARY USER_NAME,
>GROUP_ID, ROLE_ID
>VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
> USER_NAME
>VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
> GROUP_ID
> ****************************************
*******************
>****
>VGNCCB_GROUP_USER_JT
>
>
>Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>ID int
>GROUP_ID int
>USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered
, unique, primary
>key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered
located on
>PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered
located on
>PRIMARY USER_NAME
> ****************************************
*******************
>********
>

Need to speed up query

OK Guys. I'm fed up of the query below taking too much
time. I CANT change the query since it is generated by a
3rd party product. I can change indexes and add new
indexes though.
The schema of the tables is given below. The most
expensive operation is a bookmark lookup on
VGNCCB_ROLE_JT. I created the speed_up_login index as a
covering index to cover the
query but that has not seemed to help.
Any ideas, suggestions are most welcome ...
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT
WHERE
USER_NAME = 'XXXX) )
************************************************** *********
***
VGNCCB_ROLE_JT
Column_nameType
IDint
ROLE_IDint
USER_NAMEnvarchar
GROUP_IDint
PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary
key located on PRIMARYID
speed_up_loginnonclustered located on PRIMARYUSER_NAME,
GROUP_ID, ROLE_ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY
USER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY
GROUP_ID
************************************************** *********
****
VGNCCB_GROUP_USER_JT
Column_nameTypeComputedLengthPrec
ScaleNullableTrimTrailingBlanks
FixedLenNullInSourceCollation
IDint
GROUP_IDint
USER_NAMEnvarchar
PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary
key located on PRIMARYID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on
PRIMARYGROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on
PRIMARYUSER_NAME
************************************************** *********
********
Jack,
I'll have a go at it...
1. Create a nonclustered index on
VGNCCB_GROUP_USER_JT(USER_NAME,GROUP_ID)
or
Make index VGNCCB_GROUP_USER_JT_INDEX1 clustered and the primary key
nonclustered.
In fact, my guess is that the USER_NAME-GROUP_ID combination should be
unique. In that case, you should not add additional indexes, but simply
add a UNIQUE constraint to the table on (USER_NAME,GROUP_ID). This will
automatically cause SQL-Server to add a unique (nonclustered) index.
2. Change the primary key on table VGNCCB_ROLE_JT to nonclustered and
add a clustered index on (ROLE_ID). Make sure you keep the
VGNCCB_ROLE_JT_INDEX1 and VGNCCB_ROLE_JT_INDEX2 indexes.
3. Create a clustered index on VGNCCB_ROLE (ROLE_ID)
I would be interested in the query plan before and after these
changes...
Hope this helps,
Gert-Jan
Jack A wrote:
> OK Guys. I'm fed up of the query below taking too much
> time. I CANT change the query since it is generated by a
> 3rd party product. I can change indexes and add new
> indexes though.
> The schema of the tables is given below. The most
> expensive operation is a bookmark lookup on
> VGNCCB_ROLE_JT. I created the speed_up_login index as a
> covering index to cover the
> query but that has not seemed to help.
> Any ideas, suggestions are most welcome ...
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE
> WHERE
> ROLE_ID in (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT
> WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT
> WHERE
> USER_NAME = 'XXXX) )
> ************************************************** *********
> ***
> VGNCCB_ROLE_JT
>
> Column_name Type
> ID int
> ROLE_ID int
> USER_NAME nvarchar
> GROUP_ID int
> PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary
> key located on PRIMARY ID
> speed_up_login nonclustered located on PRIMARY USER_NAME,
> GROUP_ID, ROLE_ID
> VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY
> USER_NAME
> VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY
> GROUP_ID
> ************************************************** *********
> ****
> VGNCCB_GROUP_USER_JT
>
>
> Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> ID int
> GROUP_ID int
> USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
> key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
> PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
> PRIMARY USER_NAME
> ************************************************** *********
> ********
(Please reply only to the newsgroup)
|||Jack,
Any chance you could show the query plans? I think Gert-Jan has made
good suggestions, and I would try them first.
I'm wondering, though, whether SQL Server realizes values of a column
Y retrieved are in order when a predicate is WHERE X = 'this' AND [any
predicate on Y] and it uses an index seek from an index starting with
(X,Y). If it does, it can use a merge instead of nested loops to
evaluate the INs faster. It's also not clear which IN will benefit most
from a merge instead of nested loops, if it turns out to be possible to
coax one from the optimizer. If Gert-Jan's suggestions don't help
enough, and especially if user 'XXXX' accounts for a good percentage of
the rows, it may be worth trying either
covering or clustered indexes on VGNCCB_ROLE_JT and
VGNCCB_GROUP_USER_JT that have GROUP_ID as the first column
or
changing the clustered index on VGNCCB_ROLE to (ROLE_ID, ID) and
making the clustered or covering index on VGNCCB_ROLE_JT begin with ROLE_ID.
It's late, so I might be misreading something and making poor
suggestion, but it's an interesting question, and I hope you'll let us
know how it turns out.
Steve Kass
Drew University
Jack A wrote:

>OK Guys. I'm fed up of the query below taking too much
>time. I CANT change the query since it is generated by a
>3rd party product. I can change indexes and add new
>indexes though.
>The schema of the tables is given below. The most
>expensive operation is a bookmark lookup on
>VGNCCB_ROLE_JT. I created the speed_up_login index as a
>covering index to cover the
>query but that has not seemed to help.
>Any ideas, suggestions are most welcome ...
>
>
>select
>ROLE_ID,
>NAME,
>DESCRIPTION,
>CREATE_DATE,
>MODIFIED_DATE
>FROM
>vign.VGNCCB_ROLE
>WHERE
>ROLE_ID in (select ROLE_ID
>FROM
>vign.VGNCCB_ROLE_JT
>WHERE
>USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
>FROM
>vign.VGNCCB_GROUP_USER_JT
>WHERE
>USER_NAME = 'XXXX) )
>************************************************* **********
>***
>VGNCCB_ROLE_JT
>
>Column_nameType
>IDint
>ROLE_IDint
>USER_NAMEnvarchar
>GROUP_IDint
>PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary
>key located on PRIMARYID
>speed_up_loginnonclustered located on PRIMARYUSER_NAME,
>GROUP_ID, ROLE_ID
>VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY
>USER_NAME
>VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY
>GROUP_ID
>************************************************* **********
>****
>VGNCCB_GROUP_USER_JT
>
>
>Column_nameTypeComputedLengthPrec
>ScaleNullableTrimTrailingBlanks
>FixedLenNullInSourceCollation
>IDint
>GROUP_IDint
>USER_NAMEnvarchar
>PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary
>key located on PRIMARYID
>VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on
>PRIMARYGROUP_ID
>VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on
>PRIMARYUSER_NAME
>************************************************* **********
>********
>