network drives

i have several sql servers 2000 sp3.
all have several network drives mapped.
on some i can't browse to those drives when trying to backup or restore
databases.
basically i will only see local drives.
how do i make sql server see mapped drives?
thanks
Backup to a UNC share that the account that SQL Server is running under has
access to.
BACKUP DATABASE master TO DISK = '\\SomeServer\SomeShare\master.bak' WITH
INIT
RESTORE DATABASE foo FROM DISK = '\\SomeServer\SomeShare\foo.bak' ...
Keith
"milosmaj" <milosmaj@.discussions.microsoft.com> wrote in message
news:B9EBA86D-EB0F-4844-BB89-534D3C9BA8A9@.microsoft.com...
> i have several sql servers 2000 sp3.
> all have several network drives mapped.
> on some i can't browse to those drives when trying to backup or restore
> databases.
> basically i will only see local drives.
> how do i make sql server see mapped drives?
> thanks
|||I agree with Keith, use UNC names, but the reason is that drive mappings
occur when someone logs on to the server... So what if someone else (with
different drive mappings) , logs on to the server, and now your backups
either fail, or go to Hoboken?...
UNC names prevent those kinds of problems from occurring..
Hope this helps...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"milosmaj" <milosmaj@.discussions.microsoft.com> wrote in message
news:B9EBA86D-EB0F-4844-BB89-534D3C9BA8A9@.microsoft.com...
> i have several sql servers 2000 sp3.
> all have several network drives mapped.
> on some i can't browse to those drives when trying to backup or restore
> databases.
> basically i will only see local drives.
> how do i make sql server see mapped drives?
> thanks
sql

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
-Scott
When you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =
Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =
Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:

> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

NEEDED: SQL Server Express 2005 Setup Walkthrough

Someone please help me, Im trying to set up SQLServer on my website(IIS v4.0) and I cant seem to get any web apps that use SQL to intall correctly, I get errors like, SQL does not recieve remote connections by default, or just 'Server Not Found'.

This is what I need to know

- How to set SQL to accept incomming remote connections

- Where to find the Name of the SQL instance

- Is it a good idea to have a password on the db, and how do I add one

- A walkthrough on how to set up SQL Express 2005(what to do after the istall is complete)

As you can see Im very new to SQL. So any information you could give to help would be greatly appreciated.

Thanks

Admin @. Something-to-do.com

1. Here is a KB Article that goes through setting up remote connections. http://support.microsoft.com/kb/914277

2. With SQL Server 2005 Express the server install defaults to ".\SQLEXPRESS" for the instance. To find the name though you can check the windows service list in the Services mmc inside the administration folder under the control panel. The name will be inside brackets beside the SQL Server Service.

3. You should try and stay with Windows Logons for the security.. if you do need to use sql logons you should try a strong password on the database accounts.

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
>************************************************* **********
>********
>