help with sql query

Dear all,

i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Hi,

Try something like the following:

(
SELECT employee_id AS ALLemployees
FROM employeesIBM
)
UNION
(
SELECT employee_id
FROM employeesSUN
)

Not perfect, but it should get you the result set you're after. Note that if there are duplicate rows between tables and you don't want to filter out the duplicates then I believe you need to use UNION ALL instead of UNION

Hope that helps a bit, but sorry if it doesn't
|||

That does solves my prob.

Thanks,

Kris

sql

Help with sql query

Hi

I have a query which should do the following..

Bring up all records from customer table that

datelastvisited field is not during the last 3 months
or is null
AND has a region matching a user input value

OR

in a customercalls table with a one to many relationship on customerid

if nextcalldate is within the lst 3 months
or is null

i thought i had it working but its not..

below is the query that i thought was working before I tried adding the null criteria..

SELECT customers.*, customers.CustomerLastVisitDate, CustomerCalls.CustomerCallDateNext FROM customers INNER JOIN CustomerCalls ON customers.CustomerID = CustomerCalls.CustomerID WHERE ((([customers.customerregion])='" & Me.cboRegion & "') AND ([customers.CustomerLastVisitDate] Not Between Date() And DateAdd('m',-3,Date()))) AND ((CustomerCalls.CustomerCallDateNext) Between Date() And DateAdd('m',-3,Date())) OR (([customercalls.customercalldatenext])=Date());

can anyone help me?

I seem to have lsot the plot..

thanks
matselect customers.*
from customers
where customerregion = '" & Me.cboRegion & "'
and (
CustomerLastVisitDate is null
or CustomerLastVisitDate
Not Between Date()
and DateAdd('m',-3,Date())
)
union
select customers.*
from customers
inner
join CustomerCalls
on customers.CustomerID
= CustomerCalls.CustomerID
where CustomerCalls.CustomerCallDateNext is null
or CustomerCalls.CustomerCallDateNext
Between Date()
and DateAdd('m',-3,Date())|||Thanks very much..
Ur a star!

mat

Help with SELECT query

Hi
Is there a way to get a limited number of rows results from a select query
?
like select top 50 * from mytable returns only the first 50 records, but
what if a want to get records 50 to 100.
(I woulk like to spread the results over multiple pages.)
I would like to avoid to have all records pulled from the server and do the
job at the client side.
Johan
If you are using SQL Server 2005 you can use a windowing function like
Rownumber() for getting a partial resultsset. See the BOL for more
information. This requires having SQL Server 2005 as this
functionality is not present in SQL Server 2000.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

help with select

I have 300 records in one table. I want to select that in 3 times, each time
100 rows.
Anyone have some idea how to do that'
So, I would have 3 queries and each query would get 100 records.
If anyone can help...
Thanks!!Here's an example.
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
If you want the resultsets to be guaranteed to be identical, then I'd use a
temporary table first and do the 3 selects from that.
Select top 100 * into #yourtemptable from yourtable order by yourprimarykey
select * from #yourtemptable
select * from #yourtemptable
select * from #yourtemptable
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||(or use REPEATABLEREAD if you need consistency).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps you want to retrieve the 300 rows, by getting 100 rows at a =time.
If so,
-- First 100
SELECT TOP 100 {ColumnList}
FROM MyTable
ORDER BY {SortValue}
-- Second 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 100 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 200 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BJ" <bernard@.hi.hinet.hr> wrote in message =news:ed460v$cuh$1@.magcargo.vodatel.hr...
>I have 300 records in one table. I want to select that in 3 times, each =time
> 100 rows.
> Anyone have some idea how to do that'
> So, I would have 3 queries and each query would get 100 records.
> > If anyone can help...
> > Thanks!! > >
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Perhaps you want to retrieve the 300 =rows, by getting 100 rows at a time.
If so,
-- First 100
SELECT TOP 100
{ColumnList}
FROM MyTable
ORDER BY ={SortValue}
-- Second 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =100 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =200 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"BJ" =wrote in message news:ed460v$cuh$1@.magcargo.vodatel.hr...>I have 300 =records in one table. I want to select that in 3 times, each time> 100 =rows.> Anyone have some idea how to do that'> So, I would have 3 =queries and each query would get 100 records.> > If anyone can help...> > Thanks!! > =>

---

--=_NextPart_000_0A32_01C6CC1D.EA4583E0--

help with select

I have 300 records in one table. I want to select that in 3 times, each time
100 rows.
Anyone have some idea how to do that'
So, I would have 3 queries and each query would get 100 records.
If anyone can help...
Thanks!!Here's an example.
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
If you want the resultsets to be guaranteed to be identical, then I'd use a
temporary table first and do the 3 selects from that.
Select top 100 * into #yourtemptable from yourtable order by yourprimarykey
select * from #yourtemptable
select * from #yourtemptable
select * from #yourtemptable
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||(or use REPEATABLEREAD if you need consistency).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Perhaps you want to retrieve the 300 rows, by getting 100 rows at a time.
If so,
-- First 100
SELECT TOP 100
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
-- Second 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN
( SELECT TOP 100 PKeyValue
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN
( SELECT TOP 200 PKeyValue
{ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BJ" <bernard@.hi.hinet.hr> wrote in message news:ed460v$cuh$1@.magcargo.vodatel.hr...eagreen">
>I have 300 records in one table. I want to select that in 3 times, each tim
e
> 100 rows.
> Anyone have some idea how to do that'
> So, I would have 3 queries and each query would get 100 records.
>
> If anyone can help...
>
> Thanks!!
>
>

Help with returning a certain # of records from a view.

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view

How can I set up my query to only return a certain # of records, say
the first 300?

Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
MikeOn 24 Jun 2004 08:43:30 -0700, Mike wrote:

>I have a view that will return say 5000 records when I do a simple
>select query on that view like.
>select *
>from vw_test_view
>
>How can I set up my query to only return a certain # of records, say
>the first 300?
>
>Here is what is going on, we have a large amount of data that returns
>in a view and we need to work with all of it eventually, However we
>want to do it in chunks. So my thoughts were as follows:
>1. To run a query to return X amount of the total data for us to work
>with.
>2. Update these records with a flag in a table that the vw_test_view
>filters out.
>3. The next time I run the query to pull data from the view it will
>skip the records that I have already looked at (because of step 2) and
>pull the next X amount of records.
>Thanks in advance,
>Mike

Hi Mike,

You could use the TOP clause of the SELECT statement:

SELECT TOP 300 Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....

Without the order by, you'll still get maximum 300 rows, but there's no
way predicting which 300 out of the total number of matching rows will be
selected. With the ORDER BY, you'll get the first 300 according to the
specified sort order.

An alternative is to use SET ROWCOUNT:

SET ROWCOUNT 300
SELECT Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....
SET ROWCOUNT 0-- restored default behaviour

The SET ROWCOUNT gives the maximum number of rows to affect for all future
commands from the same connection. Note that this applies to UPDATE and
DELETE as well!! To return to the default behaviour of affecting all rows,
use SET ROWCOUNT 0 or close and re-open the connection.

Note that both methods use proprietary Transact-SQL syntax. An ANSI
standard version can only be done with a specified order (you'll have to
specify by which order you want the 300 "first" rows) and requires a
correlated subquery. It will be much slower.

SELECT Column1, Column2
FROM MyView AS a
WHERE ....-- if necessary
AND (SELECT COUNT(*)
FROM MyView AS b
WHERE ....-- same as in outer join
AND b.OrderingColumn < a.OrderingColumn)
< 300
ORDER BY OrderingColumn-- may be omitted

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I set up my query to only return a certain # of records
[sic], say
the first 300? <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

You will have to get out the RDBMS world and use a cursor of some
kind.

>> Here is what is going on, we have a large amount of data that
returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. <<

1) A mere 5000 rows is not a lot of data.

2) The idea of "doing it in chunks" is dangerous; do you know anything
about transactions, isolation levels and shared data?|||
Sometimes it benefits programmers to get out of in front of their
screens for a while and see how what they do affects end users.
Unfortunately too many of them do not take the time to do this or to try
and understand things from an end users point of view. No 5000 rows is
not a lot of data from a programmers point of view, but from a user who
has to go through this and verify certain information this can seem like
a daunting task, if you can break it down either feed it to them slowly
or split it amongst several people it becomes much more manageable for
them. This by the way is not what I am trying to accomplish, nor is
5000 the # of rows that I have of total data or 300 how many that I want
to pull out at a time. All that this is are made-up scenarios to
illustrate the type of things that I am trying to accomplish.

If you want to crucify me with semantics go ahead. It doesn't matter,
all that does is that people understand my question and through their
generosity point me in the right direction.

Hugo, thanks again for the help this will give me what I need to get the
job done.
And I already have the view using an order by clause on the data and it
returns exactly what I need, so if I add in the top clause it should
give me exactly what I need.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Help with query to fill in missing records

The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Help with query to fill in missing records

The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000
On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Help with query to fill in missing records

The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

Help with query that only returns filds with certain characters

Hi,
I need to write a sql statement that only returns records if
a certain field DOESN'T contain a letter between a and d, a number, a #,
and a asterisk. If a different character is found there, it should
return that record.
For example. If we have these records
1. 43242#
2. %3499
3. $$#
4. ak
5. abd43#
6. 4242#44z
7. abc_
8. 342#ab*
9. *(
My query should return 2, 3, 4, 6, 7 and 9
I tried the following:
select * from Table1
where MyField LIKE '%[^0-9]%'
and MyField like '%[^a-d]%'
and it works fine for the numbers and letters, but I don't how to
include the # and the *
Thanks a lot.You can negate the LIKE clause by putting NOT in front of it.
So
SELECT * FROM Table1
WHERE MyField LIKE '%[^0-9]%'
AND MyField LIKE '%[^a-d]%'
AND MyField NOT LIKE '%#%'
AND MyField NOT LIKE '%*%'
One note though, the criteria you mentioned and the examples you gave do not
seem to match up.

But you said you should return rows (and your sample code that you said
works as intended) that do NOT contain a number. Some of those rows clearly
contain a number. Just not sure where you were going with that.
HTH,
John Scragg
"Star" wrote:
> Hi,
> I need to write a sql statement that only returns records if
> a certain field DOESN'T contain a letter between a and d, a number, a #,
> and a asterisk. If a different character is found there, it should
> return that record.
> For example. If we have these records
> 1. 43242#
> 2. %3499
> 3. $$#
> 4. ak
> 5. abd43#
> 6. 4242#44z
> 7. abc_
> 8. 342#ab*
> 9. *(
>
> My query should return 2, 3, 4, 6, 7 and 9
> I tried the following:
> select * from Table1
> where MyField LIKE '%[^0-9]%'
> and MyField like '%[^a-d]%'
> and it works fine for the numbers and letters, but I don't how to
> include the # and the *
> Thanks a lot.
>|||John,
Yes, I think my explanation was a little bit confusing.
I will try to rephrase it.
The query should return rows if the field
does not contain one of these characters:
- Numbers
- Letters (a-d)
- #
- *
For example, the query should return %3499 because
there is a % symbol there and that symbol is not on that list.
I had already tried what you suggested, but doesn't work for me.
If I run it, I wouldn't get 4242#44z back, and I should because it
contains a 'z'
John Scragg wrote:
> You can negate the LIKE clause by putting NOT in front of it.
> So
> SELECT * FROM Table1
> WHERE MyField LIKE '%[^0-9]%'
> AND MyField LIKE '%[^a-d]%'
> AND MyField NOT LIKE '%#%'
> AND MyField NOT LIKE '%*%'
> One note though, the criteria you mentioned and the examples you gave do n
ot
> seem to match up.
>
>
> But you said you should return rows (and your sample code that you said
> works as intended) that do NOT contain a number. Some of those rows clear
ly
> contain a number. Just not sure where you were going with that.
> HTH,
> John Scragg
>
> "Star" wrote:
>|||
You may also need to consider the escape character
e.g.
select * from (select 'a%b' col1 union select 'cde') x
where col1 LIKE '%\%%' ESCAPE ''
returns only 'a%b'|||Thanks, Steven. I will keep in mind.
However, I still haven't found a solution for this problem...
If I do this
select myfield from mytable
where myfield LIKE '%[^0-9]%'
and myfield like '%[^a-d]%'
and myfield not LIKE '%*%'
and myfield not LIKE '%#%'
and myfield LIKE '%\%%' ESCAPE ''
I only get %3499 back.
I really don't know what else to try...|||> I really don't know what else to try...
I've finally worked out (I think) what it is that you need
All records that contain one (or more) characters that are not in
(1234567890abcd#*)
Would that be a fair assumption ?|||
> I really don't know what else to try...
Is this SQL 2000 or SQL2005 ?
You could use a regex match
OR:
select col1 , patindex('%[^abcd1234567890#*]%',col1)
from
(
select
'43242#' col1
UNION SELECT
'%3499' UNION SELECT
'$$#' UNION SELECT
'ak' UNION SELECT
'abd43#' UNION SELECT
'4242#44z' UNION SELECT
'abc_' UNION SELECT
'342#ab*' UNION SELECT
'*('
) x
where patindex('%[^abcd1234567890#*]%',col1) <>0|||On Wed, 02 Nov 2005 14:05:46 -0500, Star wrote:

>John,
>Yes, I think my explanation was a little bit confusing.
>I will try to rephrase it.
>The query should return rows if the field
>does not contain one of these characters:
>- Numbers
>- Letters (a-d)
>- #
>- *
>For example, the query should return %3499 because
>there is a % symbol there and that symbol is not on that list.
Hi Star,
You write "does not contain one of these characters", but your example
suggests that you mean "contains at least one character not in this
list". For '%3499' does contain a number (even four!), yet you want it
returned.
SELECT MyField, other columns
FROM MyTable1
WHERE MyField LIKE '%[^0-9a-d#*]%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes... I apologize again. I had a really bad day. I should have thought
twice my question.
Sorry about that and thanks for your help.|||Awesome!
That worked. To be honest, I didn't know about that patindex function. I
won't forget next time.
I really really appreciate your help and time.

Help with query optimization

Hi,
Suppose that I have a Master table with almost 40,000 records. The Details
table contains 50,000 records. My query Inner Joins these two table and the
query optimizer chooses Hash Join algorithm to perform the query.
Obviously it will not be a good idea to try to change the plan to a Nested
Loop because the number of rows in tables are large and close to each other.
Therefore Merge Join will (probably) be the best algorithm. I must create an
index on Details table beginning with FK column and including other columns
to cover the query.
The problem is that sometimes the number of required columns are more that
allowable quantity or the length of index exceeds 900 bytes.
Should I convince my boss to be satisfy with Hash Join or there's a
solution?
Any help will be greatly appreciated.
LeilaAnother solution would be to have a clustered index on the foreign table
starting with the foreign key column.
FYI, in SQL Server 2005 you will be able to create indexes with included
non-key columns for covering purposes, and the 900 bytes limitation does not
apply to included non-key columns.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||Thanks Itzik,
I thought about it, but I have several queries like that, I cannot have a
clustered index for each ;-)
Any solution before 2005?!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> Another solution would be to have a clustered index on the foreign table
> starting with the foreign key column.
> FYI, in SQL Server 2005 you will be able to create indexes with included
> non-key columns for covering purposes, and the 900 bytes limitation does
not
> apply to included non-key columns.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that[vbcol=seagreen]
>|||<snip>... and including other columns to cover the query.</snip>
WHy do you need it to be a covering index? That is useful when the number
of columns required by a query is small, but if a query returns a large
number of columns, that is not a good idea, and whwther or not a covering
index exists will not affect the type of join algorithm the optmizer uses...
"Leila" wrote:

> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and th
e
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each othe
r.
> Therefore Merge Join will (probably) be the best algorithm. I must create
an
> index on Details table beginning with FK column and including other column
s
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
>|||Indexed views is another option.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Thanks Itzik,
> I thought about it, but I have several queries like that, I cannot have a
> clustered index for each ;-)
> Any solution before 2005?!
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> not
> Details
> Nested
> create
> that
>|||Are you trying to join the entire table at a time? If so it might be
cheaper overall to just go Hash Join. It is a pretty good algorithm, though
you are right the Merge Join will be good. I wouldn't try to index all rows
necessarily because that will be costly to maintain.
The Nested loops join should be the best algorithm for a simple one to many
with low cardinality (which you should have since your parent table only has
4/5 of the number of rows that the child has) and a reasonable join key. Do
you have an index on the foreign key now? Can you post the table
structures?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||If the index is not covering, then I suppose bookmark lookup will be
required to gather other columns. Can lookup happen in a merge join?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> <snip>... and including other columns to cover the query.</snip>
> WHy do you need it to be a covering index? That is useful when the number
> of columns required by a query is small, but if a query returns a large
> number of columns, that is not a good idea, and whwther or not a covering
> index exists will not affect the type of join algorithm the optmizer
uses...[vbcol=seagreen]
>
> "Leila" wrote:
>
Details[vbcol=seagreen]
the[vbcol=seagreen]
Nested[vbcol=seagreen]
other.[vbcol=seagreen]
create an[vbcol=seagreen]
columns[vbcol=seagreen]
that[vbcol=seagreen]|||Great! Do you mean I create an indexed view on all required columns of
Details table and join the Master with this view or ...?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> Indexed views is another option.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
a[vbcol=seagreen]
table[vbcol=seagreen]
included[vbcol=seagreen]
does[vbcol=seagreen]
and[vbcol=seagreen]
>|||Thanks Louis!

> Are you trying to join the entire table at a time?
Yes, I need to.

> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Nested loop should be good when the Master table is small. I don't think if
40,000 index seeks on Details table can result in a good performance.

> Do you have an index on the foreign key now?
Yes but it doesn't help.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Are you trying to join the entire table at a time? If so it might be
> cheaper overall to just go Hash Join. It is a pretty good algorithm,
though
> you are right the Merge Join will be good. I wouldn't try to index all
rows
> necessarily because that will be costly to maintain.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Do
> you have an index on the foreign key now? Can you post the table
> structures?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that[vbcol=seagreen]
>|||Yes, sure...
"Leila" wrote:

> If the index is not covering, then I suppose bookmark lookup will be
> required to gather other columns. Can lookup happen in a merge join?
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> uses...
> Details
> the
> Nested
> other.
> create an
> columns
> that
>
>

Help with query optimization

Hi,
Suppose that I have a Master table with almost 40,000 records. The Details
table contains 50,000 records. My query Inner Joins these two table and the
query optimizer chooses Hash Join algorithm to perform the query.
Obviously it will not be a good idea to try to change the plan to a Nested
Loop because the number of rows in tables are large and close to each other.
Therefore Merge Join will (probably) be the best algorithm. I must create an
index on Details table beginning with FK column and including other columns
to cover the query.
The problem is that sometimes the number of required columns are more that
allowable quantity or the length of index exceeds 900 bytes.
Should I convince my boss to be satisfy with Hash Join or there's a
solution?
Any help will be greatly appreciated.
Leila
Another solution would be to have a clustered index on the foreign table
starting with the foreign key column.
FYI, in SQL Server 2005 you will be able to create indexes with included
non-key columns for covering purposes, and the 900 bytes limitation does not
apply to included non-key columns.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
|||Thanks Itzik,
I thought about it, but I have several queries like that, I cannot have a
clustered index for each ;-)
Any solution before 2005?!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> Another solution would be to have a clustered index on the foreign table
> starting with the foreign key column.
> FYI, in SQL Server 2005 you will be able to create indexes with included
> non-key columns for covering purposes, and the 900 bytes limitation does
not[vbcol=seagreen]
> apply to included non-key columns.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that
>
|||<snip>... and including other columns to cover the query.</snip>
WHy do you need it to be a covering index? That is useful when the number
of columns required by a query is small, but if a query returns a large
number of columns, that is not a good idea, and whwther or not a covering
index exists will not affect the type of join algorithm the optmizer uses...
"Leila" wrote:

> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each other.
> Therefore Merge Join will (probably) be the best algorithm. I must create an
> index on Details table beginning with FK column and including other columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
>
|||Indexed views is another option.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Thanks Itzik,
> I thought about it, but I have several queries like that, I cannot have a
> clustered index for each ;-)
> Any solution before 2005?!
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> not
> Details
> Nested
> create
> that
>
|||Are you trying to join the entire table at a time? If so it might be
cheaper overall to just go Hash Join. It is a pretty good algorithm, though
you are right the Merge Join will be good. I wouldn't try to index all rows
necessarily because that will be costly to maintain.
The Nested loops join should be the best algorithm for a simple one to many
with low cardinality (which you should have since your parent table only has
4/5 of the number of rows that the child has) and a reasonable join key. Do
you have an index on the foreign key now? Can you post the table
structures?
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
|||If the index is not covering, then I suppose bookmark lookup will be
required to gather other columns. Can lookup happen in a merge join?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> <snip>... and including other columns to cover the query.</snip>
> WHy do you need it to be a covering index? That is useful when the number
> of columns required by a query is small, but if a query returns a large
> number of columns, that is not a good idea, and whwther or not a covering
> index exists will not affect the type of join algorithm the optmizer
uses...[vbcol=seagreen]
>
> "Leila" wrote:
Details[vbcol=seagreen]
the[vbcol=seagreen]
Nested[vbcol=seagreen]
other.[vbcol=seagreen]
create an[vbcol=seagreen]
columns[vbcol=seagreen]
that[vbcol=seagreen]
|||Great! Do you mean I create an indexed view on all required columns of
Details table and join the Master with this view or ...?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Indexed views is another option.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
a[vbcol=seagreen]
table[vbcol=seagreen]
included[vbcol=seagreen]
does[vbcol=seagreen]
and
>
|||Thanks Louis!

> Are you trying to join the entire table at a time?
Yes, I need to.

> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Nested loop should be good when the Master table is small. I don't think if
40,000 index seeks on Details table can result in a good performance.

> Do you have an index on the foreign key now?
Yes but it doesn't help.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Are you trying to join the entire table at a time? If so it might be
> cheaper overall to just go Hash Join. It is a pretty good algorithm,
though
> you are right the Merge Join will be good. I wouldn't try to index all
rows
> necessarily because that will be costly to maintain.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Do
> you have an index on the foreign key now? Can you post the table
> structures?
>
> --
> ----
--[vbcol=seagreen]
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that
>
|||Yes, sure...
"Leila" wrote:

> If the index is not covering, then I suppose bookmark lookup will be
> required to gather other columns. Can lookup happen in a merge join?
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> uses...
> Details
> the
> Nested
> other.
> create an
> columns
> that
>
>

Help with query optimization

Hi,
Suppose that I have a Master table with almost 40,000 records. The Details
table contains 50,000 records. My query Inner Joins these two table and the
query optimizer chooses Hash Join algorithm to perform the query.
Obviously it will not be a good idea to try to change the plan to a Nested
Loop because the number of rows in tables are large and close to each other.
Therefore Merge Join will (probably) be the best algorithm. I must create an
index on Details table beginning with FK column and including other columns
to cover the query.
The problem is that sometimes the number of required columns are more that
allowable quantity or the length of index exceeds 900 bytes.
Should I convince my boss to be satisfy with Hash Join or there's a
solution?
Any help will be greatly appreciated.
LeilaAnother solution would be to have a clustered index on the foreign table
starting with the foreign key column.
FYI, in SQL Server 2005 you will be able to create indexes with included
non-key columns for covering purposes, and the 900 bytes limitation does not
apply to included non-key columns.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||Thanks Itzik,
I thought about it, but I have several queries like that, I cannot have a
clustered index for each ;-)
Any solution before 2005?!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> Another solution would be to have a clustered index on the foreign table
> starting with the foreign key column.
> FYI, in SQL Server 2005 you will be able to create indexes with included
> non-key columns for covering purposes, and the 900 bytes limitation does
not
> apply to included non-key columns.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||<snip>... and including other columns to cover the query.</snip>
WHy do you need it to be a covering index? That is useful when the number
of columns required by a query is small, but if a query returns a large
number of columns, that is not a good idea, and whwther or not a covering
index exists will not affect the type of join algorithm the optmizer uses...
"Leila" wrote:
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each other.
> Therefore Merge Join will (probably) be the best algorithm. I must create an
> index on Details table beginning with FK column and including other columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
>|||Indexed views is another option.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Thanks Itzik,
> I thought about it, but I have several queries like that, I cannot have a
> clustered index for each ;-)
> Any solution before 2005?!
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> Another solution would be to have a clustered index on the foreign table
>> starting with the foreign key column.
>> FYI, in SQL Server 2005 you will be able to create indexes with included
>> non-key columns for covering purposes, and the 900 bytes limitation does
> not
>> apply to included non-key columns.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Are you trying to join the entire table at a time? If so it might be
cheaper overall to just go Hash Join. It is a pretty good algorithm, though
you are right the Merge Join will be good. I wouldn't try to index all rows
necessarily because that will be costly to maintain.
The Nested loops join should be the best algorithm for a simple one to many
with low cardinality (which you should have since your parent table only has
4/5 of the number of rows that the child has) and a reasonable join key. Do
you have an index on the foreign key now? Can you post the table
structures?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||If the index is not covering, then I suppose bookmark lookup will be
required to gather other columns. Can lookup happen in a merge join?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> <snip>... and including other columns to cover the query.</snip>
> WHy do you need it to be a covering index? That is useful when the number
> of columns required by a query is small, but if a query returns a large
> number of columns, that is not a good idea, and whwther or not a covering
> index exists will not affect the type of join algorithm the optmizer
uses...
>
> "Leila" wrote:
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create an
> > index on Details table beginning with FK column and including other
columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
> >|||Thanks Louis!
> Are you trying to join the entire table at a time?
Yes, I need to.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Nested loop should be good when the Master table is small. I don't think if
40,000 index seeks on Details table can result in a good performance.
> Do you have an index on the foreign key now?
Yes but it doesn't help.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Are you trying to join the entire table at a time? If so it might be
> cheaper overall to just go Hash Join. It is a pretty good algorithm,
though
> you are right the Merge Join will be good. I wouldn't try to index all
rows
> necessarily because that will be costly to maintain.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Do
> you have an index on the foreign key now? Can you post the table
> structures?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||Great! Do you mean I create an indexed view on all required columns of
Details table and join the Master with this view or ...?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> Indexed views is another option.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > Thanks Itzik,
> > I thought about it, but I have several queries like that, I cannot have
a
> > clustered index for each ;-)
> > Any solution before 2005?!
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> Another solution would be to have a clustered index on the foreign
table
> >> starting with the foreign key column.
> >>
> >> FYI, in SQL Server 2005 you will be able to create indexes with
included
> >> non-key columns for covering purposes, and the 900 bytes limitation
does
> > not
> >> apply to included non-key columns.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Suppose that I have a Master table with almost 40,000 records. The
> > Details
> >> > table contains 50,000 records. My query Inner Joins these two table
and
> >> > the
> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> > Obviously it will not be a good idea to try to change the plan to a
> > Nested
> >> > Loop because the number of rows in tables are large and close to each
> >> > other.
> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> > create
> >> > an
> >> > index on Details table beginning with FK column and including other
> >> > columns
> >> > to cover the query.
> >> > The problem is that sometimes the number of required columns are more
> > that
> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> > Should I convince my boss to be satisfy with Hash Join or there's a
> >> > solution?
> >> > Any help will be greatly appreciated.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yes, sure...
"Leila" wrote:
> If the index is not covering, then I suppose bookmark lookup will be
> required to gather other columns. Can lookup happen in a merge join?
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> > <snip>... and including other columns to cover the query.</snip>
> >
> > WHy do you need it to be a covering index? That is useful when the number
> > of columns required by a query is small, but if a query returns a large
> > number of columns, that is not a good idea, and whwther or not a covering
> > index exists will not affect the type of join algorithm the optmizer
> uses...
> >
> >
> > "Leila" wrote:
> >
> > > Hi,
> > > Suppose that I have a Master table with almost 40,000 records. The
> Details
> > > table contains 50,000 records. My query Inner Joins these two table and
> the
> > > query optimizer chooses Hash Join algorithm to perform the query.
> > > Obviously it will not be a good idea to try to change the plan to a
> Nested
> > > Loop because the number of rows in tables are large and close to each
> other.
> > > Therefore Merge Join will (probably) be the best algorithm. I must
> create an
> > > index on Details table beginning with FK column and including other
> columns
> > > to cover the query.
> > > The problem is that sometimes the number of required columns are more
> that
> > > allowable quantity or the length of index exceeds 900 bytes.
> > > Should I convince my boss to be satisfy with Hash Join or there's a
> > > solution?
> > > Any help will be greatly appreciated.
> > > Leila
> > >
> > >
> > >
>
>|||That's one option. The other (in case it's an Enterprise edition), is to
continue querying the base tables.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Great! Do you mean I create an indexed view on all required columns of
> Details table and join the Master with this view or ...?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> Indexed views is another option.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Itzik,
>> > I thought about it, but I have several queries like that, I cannot have
> a
>> > clustered index for each ;-)
>> > Any solution before 2005?!
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> Another solution would be to have a clustered index on the foreign
> table
>> >> starting with the foreign key column.
>> >>
>> >> FYI, in SQL Server 2005 you will be able to create indexes with
> included
>> >> non-key columns for covering purposes, and the 900 bytes limitation
> does
>> > not
>> >> apply to included non-key columns.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> > Suppose that I have a Master table with almost 40,000 records. The
>> > Details
>> >> > table contains 50,000 records. My query Inner Joins these two table
> and
>> >> > the
>> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> > Obviously it will not be a good idea to try to change the plan to a
>> > Nested
>> >> > Loop because the number of rows in tables are large and close to
>> >> > each
>> >> > other.
>> >> > Therefore Merge Join will (probably) be the best algorithm. I must
>> > create
>> >> > an
>> >> > index on Details table beginning with FK column and including other
>> >> > columns
>> >> > to cover the query.
>> >> > The problem is that sometimes the number of required columns are
>> >> > more
>> > that
>> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> > Should I convince my boss to be satisfy with Hash Join or there's a
>> >> > solution?
>> >> > Any help will be greatly appreciated.
>> >> > Leila
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Sorry I didn't get it, could please tell me more!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> That's one option. The other (in case it's an Enterprise edition), is to
> continue querying the base tables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> > Great! Do you mean I create an indexed view on all required columns of
> > Details table and join the Master with this view or ...?
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> Indexed views is another option.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> > Thanks Itzik,
> >> > I thought about it, but I have several queries like that, I cannot
have
> > a
> >> > clustered index for each ;-)
> >> > Any solution before 2005?!
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> Another solution would be to have a clustered index on the foreign
> > table
> >> >> starting with the foreign key column.
> >> >>
> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> > included
> >> >> non-key columns for covering purposes, and the 900 bytes limitation
> > does
> >> > not
> >> >> apply to included non-key columns.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> > Suppose that I have a Master table with almost 40,000 records. The
> >> > Details
> >> >> > table contains 50,000 records. My query Inner Joins these two
table
> > and
> >> >> > the
> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> >> > Obviously it will not be a good idea to try to change the plan to
a
> >> > Nested
> >> >> > Loop because the number of rows in tables are large and close to
> >> >> > each
> >> >> > other.
> >> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> >> > create
> >> >> > an
> >> >> > index on Details table beginning with FK column and including
other
> >> >> > columns
> >> >> > to cover the query.
> >> >> > The problem is that sometimes the number of required columns are
> >> >> > more
> >> > that
> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
a
> >> >> > solution?
> >> >> > Any help will be greatly appreciated.
> >> >> > Leila
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||How slow is it now, and how much faster do you want it? That is a tall
order to join so many rows at once. It sounds like you might be stuck where
you are. Can you post the plan?
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:OCSOPHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Thanks Louis!
>> Are you trying to join the entire table at a time?
> Yes, I need to.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Nested loop should be good when the Master table is small. I don't think
> if
> 40,000 index seeks on Details table can result in a good performance.
>> Do you have an index on the foreign key now?
> Yes but it doesn't help.
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Are you trying to join the entire table at a time? If so it might be
>> cheaper overall to just go Hash Join. It is a pretty good algorithm,
> though
>> you are right the Merge Join will be good. I wouldn't try to index all
> rows
>> necessarily because that will be costly to maintain.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Do
>> you have an index on the foreign key now? Can you post the table
>> structures?
>>
>> --
>> ----
> --
>> Louis Davidson - drsql@.hotmail.com
>> SQL Server MVP
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Blog - http://spaces.msn.com/members/drsql/
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services. All other replies may be ignored :)
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Sure.
In Enterprise edition the optimizer can consider using an indexed view even
if you don't query the view directly, rather the base tables.
It doesn't work in all cases, i.e., there are still cases where the
optimizer reverts to the base tables and not the indexed view, but you can
try and hope for the best.
BTW, SQL Server 2005 does a better job at this, and it uses the indexed view
in more cases.
If it's not an Enterprise edition, in order to use the indexed view, you
must:
1. Query the view directly
2. Specify the NOEXPAND hint
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> Sorry I didn't get it, could please tell me more!
>
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> That's one option. The other (in case it's an Enterprise edition), is to
>> continue querying the base tables.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> > Great! Do you mean I create an indexed view on all required columns of
>> > Details table and join the Master with this view or ...?
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> Indexed views is another option.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> > Thanks Itzik,
>> >> > I thought about it, but I have several queries like that, I cannot
> have
>> > a
>> >> > clustered index for each ;-)
>> >> > Any solution before 2005?!
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> Another solution would be to have a clustered index on the foreign
>> > table
>> >> >> starting with the foreign key column.
>> >> >>
>> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> > included
>> >> >> non-key columns for covering purposes, and the 900 bytes limitation
>> > does
>> >> > not
>> >> >> apply to included non-key columns.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Hi,
>> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> > The
>> >> > Details
>> >> >> > table contains 50,000 records. My query Inner Joins these two
> table
>> > and
>> >> >> > the
>> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> >> > Obviously it will not be a good idea to try to change the plan to
> a
>> >> > Nested
>> >> >> > Loop because the number of rows in tables are large and close to
>> >> >> > each
>> >> >> > other.
>> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> > must
>> >> > create
>> >> >> > an
>> >> >> > index on Details table beginning with FK column and including
> other
>> >> >> > columns
>> >> >> > to cover the query.
>> >> >> > The problem is that sometimes the number of required columns are
>> >> >> > more
>> >> > that
>> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
> a
>> >> >> > solution?
>> >> >> > Any help will be greatly appreciated.
>> >> >> > Leila
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Thanks indeed!
What if i create an indexed view from the main INNER JOIN query rather than
creating indexed view from only Details table?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
> Sure.
> In Enterprise edition the optimizer can consider using an indexed view
even
> if you don't query the view directly, rather the base tables.
> It doesn't work in all cases, i.e., there are still cases where the
> optimizer reverts to the base tables and not the indexed view, but you can
> try and hope for the best.
> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
view
> in more cases.
> If it's not an Enterprise edition, in order to use the indexed view, you
> must:
> 1. Query the view directly
> 2. Specify the NOEXPAND hint
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Sorry I didn't get it, could please tell me more!
> >
> >
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> That's one option. The other (in case it's an Enterprise edition), is
to
> >> continue querying the base tables.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> >> > Great! Do you mean I create an indexed view on all required columns
of
> >> > Details table and join the Master with this view or ...?
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> >> Indexed views is another option.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> >> > Thanks Itzik,
> >> >> > I thought about it, but I have several queries like that, I cannot
> > have
> >> > a
> >> >> > clustered index for each ;-)
> >> >> > Any solution before 2005?!
> >> >> >
> >> >> >
> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
in
> >> >> > message
> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> >> Another solution would be to have a clustered index on the
foreign
> >> > table
> >> >> >> starting with the foreign key column.
> >> >> >>
> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> >> > included
> >> >> >> non-key columns for covering purposes, and the 900 bytes
limitation
> >> > does
> >> >> > not
> >> >> >> apply to included non-key columns.
> >> >> >>
> >> >> >> --
> >> >> >> BG, SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> >> > Hi,
> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
> >> >> >> > The
> >> >> > Details
> >> >> >> > table contains 50,000 records. My query Inner Joins these two
> > table
> >> > and
> >> >> >> > the
> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
query.
> >> >> >> > Obviously it will not be a good idea to try to change the plan
to
> > a
> >> >> > Nested
> >> >> >> > Loop because the number of rows in tables are large and close
to
> >> >> >> > each
> >> >> >> > other.
> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
> >> >> >> > must
> >> >> > create
> >> >> >> > an
> >> >> >> > index on Details table beginning with FK column and including
> > other
> >> >> >> > columns
> >> >> >> > to cover the query.
> >> >> >> > The problem is that sometimes the number of required columns
are
> >> >> >> > more
> >> >> > that
> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
there's
> > a
> >> >> >> > solution?
> >> >> >> > Any help will be greatly appreciated.
> >> >> >> > Leila
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Even better. The indexed view can cover the whole join query saving the need
for rejoining every time you query.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23cSBjY4RFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Thanks indeed!
> What if i create an indexed view from the main INNER JOIN query rather
> than
> creating indexed view from only Details table?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
>> Sure.
>> In Enterprise edition the optimizer can consider using an indexed view
> even
>> if you don't query the view directly, rather the base tables.
>> It doesn't work in all cases, i.e., there are still cases where the
>> optimizer reverts to the base tables and not the indexed view, but you
>> can
>> try and hope for the best.
>> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
> view
>> in more cases.
>> If it's not an Enterprise edition, in order to use the indexed view, you
>> must:
>> 1. Query the view directly
>> 2. Specify the NOEXPAND hint
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Sorry I didn't get it, could please tell me more!
>> >
>> >
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> That's one option. The other (in case it's an Enterprise edition), is
> to
>> >> continue querying the base tables.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> >> > Great! Do you mean I create an indexed view on all required columns
> of
>> >> > Details table and join the Master with this view or ...?
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> >> Indexed views is another option.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Thanks Itzik,
>> >> >> > I thought about it, but I have several queries like that, I
>> >> >> > cannot
>> > have
>> >> > a
>> >> >> > clustered index for each ;-)
>> >> >> > Any solution before 2005?!
>> >> >> >
>> >> >> >
>> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
> in
>> >> >> > message
>> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> >> Another solution would be to have a clustered index on the
> foreign
>> >> > table
>> >> >> >> starting with the foreign key column.
>> >> >> >>
>> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> >> > included
>> >> >> >> non-key columns for covering purposes, and the 900 bytes
> limitation
>> >> > does
>> >> >> > not
>> >> >> >> apply to included non-key columns.
>> >> >> >>
>> >> >> >> --
>> >> >> >> BG, SQL Server MVP
>> >> >> >> www.SolidQualityLearning.com
>> >> >> >>
>> >> >> >>
>> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> >> > Hi,
>> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> >> > The
>> >> >> > Details
>> >> >> >> > table contains 50,000 records. My query Inner Joins these two
>> > table
>> >> > and
>> >> >> >> > the
>> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
> query.
>> >> >> >> > Obviously it will not be a good idea to try to change the plan
> to
>> > a
>> >> >> > Nested
>> >> >> >> > Loop because the number of rows in tables are large and close
> to
>> >> >> >> > each
>> >> >> >> > other.
>> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> >> > must
>> >> >> > create
>> >> >> >> > an
>> >> >> >> > index on Details table beginning with FK column and including
>> > other
>> >> >> >> > columns
>> >> >> >> > to cover the query.
>> >> >> >> > The problem is that sometimes the number of required columns
> are
>> >> >> >> > more
>> >> >> > that
>> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
> there's
>> > a
>> >> >> >> > solution?
>> >> >> >> > Any help will be greatly appreciated.
>> >> >> >> > Leila
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>