Max Query: contains

Showing posts with label contains. Show all posts

Wednesday, March 28, 2012

Help with SQL Contains clause

why does this
Select * from cv_Volunteer_Section where
CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR "accounting
clerk" ')
return this error?
Server: Msg 7619, Level 16, State 1, Line 2
The query contained only ignored words.
Is there away to prevent the error by editing the clause?
Thanks in advance
SanjaySanjay,
What is the version of SQL Server and on what OS platform do you have it
installed? Could you post the full output of -- SELECT @.@.version -- as this
is most helpful info in troubleshooting SQL FTS issues.
The OS platform version is key to understanding this results of your
CONTAINS query as Win2K vs. WinXP or Win2003 ship with different OS-supplied
wordbreakers. see http://groups.google.com/groups?q=langwrbk+infosoft for
more details. Also, the language-specific noise word files, under
\FTDATA\SQLServer\Config\ (noise.enu = US_English) can have an affect on the
query as well. Specifically, are the single letters A, P and R in the
noise.enu file (assuming you're using US English as the default language)?
If so, you may want to remove the single letters from this file and then run
a Full Population and re-test your query.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv#AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
"accounting
> clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Courtesy of John Kane:
http://sqljunkies.com/WebLog/jt_kan.../09/19/217.aspx
-oj
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Speaking of the de-vil. ;-)
-oj
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OktcjHEBFHA.4004@.tk2msftngp13.phx.gbl...
> Sanjay,
> What is the version of SQL Server and on what OS platform do you have it
> installed? Could you post the full output of -- SELECT @.@.version -- as
> this
> is most helpful info in troubleshooting SQL FTS issues.
> The OS platform version is key to understanding this results of your
> CONTAINS query as Win2K vs. WinXP or Win2003 ship with different
> OS-supplied
> wordbreakers. see http://groups.google.com/groups?q=langwrbk+infosoft for
> more details. Also, the language-specific noise word files, under
> \FTDATA\SQLServer\Config\ (noise.enu = US_English) can have an affect on
> the
> query as well. Specifically, are the single letters A, P and R in the
> noise.enu file (assuming you're using US English as the default language)?
> If so, you may want to remove the single letters from this file and then
> run
> a Full Population and re-test your query.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv#AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> "accounting
>|||Thank you, OJ!
It's good to be noticed! I'm now actively blogging on "SQL Full Text Search
Blog" at http://spaces.msn.com/members/jtkane/ and I've cross-posted the
below entry to "SQL Server 2000 FTS on Windows 2000 vs. Windows Server
2003..." at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158.e
ntry
Enjoy.
John
"oj" <nospam_ojngo@.home.com> wrote in message
news:#mWGuIEBFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Courtesy of John Kane:
> http://sqljunkies.com/WebLog/jt_kan.../09/19/217.aspx
>
> --
> -oj
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
>|||Kewl. I'll check it out.
Cheers,
--
-oj
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OpJX6TEBFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Thank you, OJ!
> It's good to be noticed! I'm now actively blogging on "SQL Full Text
> Search
> Blog" at http://spaces.msn.com/members/jtkane/ and I've cross-posted the
> below entry to "SQL Server 2000 FTS on Windows 2000 vs. Windows Server
> 2003..." at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158
.entry
> Enjoy.
> John
>|||Sql Server 7
What was interesting was that it seems like the / seems to be impacting the
searchresults however,
this worked like a charm. (notice i changed a/r or a/p to a/ra/p)
Select * from cv_Volunteer_Section where
CONTAINS(listingdescription, ' "a/pa/r" OR "bookkeeper" OR "accounting
clerk" ')
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks John/oj
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Sanjay,
Yes, the "/" or forward slash does impact the search results, but it is
highly dependent upon what OS platform you are using with SQL Server 7.0,
hence the request for the SELECT @.@.version output. What you have done is to
use double quotes around your single letter queries and turn these search
words into a "phrase word - pa" query and under this condition the single
letter noise words are truly ignored, but the "pa" is considered a "word".
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eRIXv8HBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Sql Server 7
> What was interesting was that it seems like the / seems to be impacting
the
> searchresults however,
> this worked like a charm. (notice i changed a/r or a/p to a/ra/p)
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/pa/r" OR "bookkeeper" OR "accounting
> clerk" ')
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
>

Monday, March 26, 2012

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the p
rimary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I sol
ved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered inde
x scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple
I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know abo
ut
| your data or needs, but in my general experience I've found that indexes o
n
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used f
or
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical que
ries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Wort
h
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip

>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
50,000 records.
clustered, non-unique index.
index scan against the
>primary key of the table, with a WHERE clause for the
far more complex. I
>solved it by altering the joins and rearranging predicates
use the clustered
>index scan at 100% cost and the execution time on that
query
preceding query is so
>simple I don't know what to do.
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
>
> I don't know about
> found that indexes on
> me this one row
> hand, are used for
> identified by this FK).
> FKBatchID would allow
> increasing your
> that it includes
> query shouldn't
> it needs... Worth
> 50,000 records.
> clustered, non-unique index.
> index scan against the
> far more complex. I
> use the clustered
> query
> preceding query is so|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||Thanks for the clarification, Ray; most of my experience is with large
datawarehouse type applications so light on the insert (during production
hours) and very heavy querying is the direction I'm coming from.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:es0OpkwAEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Try putting a nonclustered index on processcount and fkbatchID if this is
> something you do often, if fkbatchid is your clustered index you only have
> to put a nonclustered index on processcount as the clustered key is
included
> in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
> propertied column) if you are heavy inserts this is a good idea as it
> creates hot spots on the disk as your inserts will fall to bottom of the
> leaf level (reducing page splits and affording you not to have to mess
with
> fill factor) If you are query intensive and light inserts, test out Adam's
> solution.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Gallucci" <chris@.gallucci.com> wrote in message
> news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
index.
the
> primary key of the table, with a WHERE clause for the
> solved it by altering the joins and rearranging predicates
> index scan at 100% cost and the execution time on that query
> simple I don't know what to do.
>

Help With Slow SQL Query

This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the primary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I solved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered index scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know about
| your data or needs, but in my general experience I've found that indexes on
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used for
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical queries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Worth
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
>> This query takes 1 minute to execute...
>> SELECT SUM([ProcessCount])
>> FROM [ProcessTable]
>> WHERE [FKBatchID] = 1
>> The table contains about 5,000,000 records.
>> The total record count that matches the WHERE clause is
50,000 records.
>> The table has a clustered index on the primary key.
>> The foreign key in the WHERE clause has a non-
clustered, non-unique index.
>> The execution plan shows a 100% cost on a clustered
index scan against the
>primary key of the table, with a WHERE clause for the
>> [FKBatchID] column.
>> I had a similar problem with another query which was
far more complex. I
>solved it by altering the joins and rearranging predicates
>> in the WHERE clause. This cause the plan to no longer
use the clustered
>index scan at 100% cost and the execution time on that
query
>> went from 2 minutes to 5 seconds. However, the
preceding query is so
>simple I don't know what to do.
>> Please help.
>> ChrisG
>>
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
> >--Original Message--
> >Two questions/suggestions:
> >
> >First of all, why are you clustering on the primary key?
> I don't know about
> >your data or needs, but in my general experience I've
> found that indexes on
> >PKs are used more often for random data retrieval (give
> me this one row
> >identified by this one PK). Indexes on FKs, on the other
> hand, are used for
> >more range-related activity (give me these rows
> identified by this FK).
> >Indeed, for this query, having a clustered index on
> FKBatchID would allow
> >the data to be read contiguously from the disc, greatly
> increasing your
> >performance. So you might consider switching that.
> >
> >Second, you could try covering the non-clustered index so
> that it includes
> >ProcessCount (something like, CREATE INDEX MyIndex ON
> >ProcessTable(FKBatchID, ProcessCount)) ... This way, the
> query shouldn't
> >have to go into the leaves of the cluster to get the data
> it needs... Worth
> >a try, at any rate...
> >
> >
> >"Chris Gallucci" <chris@.gallucci.com> wrote in message
> >news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> >> This query takes 1 minute to execute...
> >> SELECT SUM([ProcessCount])
> >> FROM [ProcessTable]
> >> WHERE [FKBatchID] = 1
> >>
> >> The table contains about 5,000,000 records.
> >> The total record count that matches the WHERE clause is
> 50,000 records.
> >> The table has a clustered index on the primary key.
> >> The foreign key in the WHERE clause has a non-
> clustered, non-unique index.
> >> The execution plan shows a 100% cost on a clustered
> index scan against the
> >primary key of the table, with a WHERE clause for the
> >> [FKBatchID] column.
> >>
> >> I had a similar problem with another query which was
> far more complex. I
> >solved it by altering the joins and rearranging predicates
> >> in the WHERE clause. This cause the plan to no longer
> use the clustered
> >index scan at 100% cost and the execution time on that
> query
> >> went from 2 minutes to 5 seconds. However, the
> preceding query is so
> >simple I don't know what to do.
> >>
> >> Please help.
> >>
> >> ChrisG
> >>
> >>
> >
> >
> >.
> >|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>

Friday, March 23, 2012

Help with SELECT query

My SELECT query returns a data set, one column of which contains a set
of values corresponding to the same date. I.e. for each date in column
"Date", I have a set of numbers in column "Numbers". However, I am
only interested in getting the largest value in column "Numbers"
corresponding to each value in column "Date". How do I do that?
Thanks,

Marcomdi00@.hotmail.com (Marco) wrote in news:e5f1d809.0411190312.c9f8b07
@.posting.google.com:

> My SELECT query returns a data set, one column of which contains a set
> of values corresponding to the same date. I.e. for each date in column
> "Date", I have a set of numbers in column "Numbers". However, I am
> only interested in getting the largest value in column "Numbers"
> corresponding to each value in column "Date". How do I do that?
> Thanks,
> Marco

SELECT "Date", MAX("Numbers")AS "Largest number"
FROM sometable
GROUP BY "Date"|||Marco wrote:

Look up MAX and GROUP BY.

Help with Select - into

Hi everybody,

I have two tables 'tab1' and 'tab2'. 'tab2' contains the same columns
as 'tab1'. 'tab2' does NOT contain any of the constraints of 'tab1',
just the fields. When I create this 'tab2' table using CREATE TABLE,
it gets created fine. Then I use a stored procedure which has a SELECT
INTO statement to copy all data from 'tab1' into 'tab2'. Now If I want
to append more data to 'tab2', I find that it tells me IDENTITY INSERT
on 'tab2' SHOULD BE SET TO ON. I have not defined any identity columns
in the CREATE TABLE, but after executing the SELECT INTO, I found that
it made one column the IDENTITY. Why is this so and How do I just copy
the data from 'tab1' without the frills ? All i want is a dump of one
table into another. How do I do this with an SQL query ?

Thanks in advance.

Best Regards.If you do SELECT INTO it will copy all the columns, including the IDENTITY
property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
INTO will fail if the target table already exists.

To re-create the table without the identity column:

CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)

INSERT INTO Tab2 (X,Y,...)
SELECT X,Y,...
FROM Tab1

--
David Portas
----
Please reply only to the newsgroup
--|||You could try to confuse SQL Server by adding a bogus expression to the
identity column's select to break the connection.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<rdadnS_XYP16OyGi4p2dnA@.giganews.com>...
> If you do SELECT INTO it will copy all the columns, including the IDENTITY
> property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
> INTO will fail if the target table already exists.
> To re-create the table without the identity column:
> CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)
> INSERT INTO Tab2 (X,Y,...)
> SELECT X,Y,...
> FROM Tab1

Thank you for your help. It worked.

Best Regards.

Wednesday, March 21, 2012

Help with returning too much data

I am running the below query and getting back results that have the word
"mode" in it. Isn't the keyword CONTAINS supposed to treat my search
expression as one word? Can someone show me what is wrong with this query so
that it returns only records that have the exact search expression "mode-4"
in it? Thank you.
SELECT MyFields
FROM MyTable M
LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>I am running the below query and getting back results that have the word
> "mode" in it. Isn't the keyword CONTAINS supposed to treat my search
> expression as one word? Can someone show me what is wrong with this query
> so
> that it returns only records that have the exact search expression
> "mode-4"
> in it? Thank you.
> SELECT MyFields
> FROM MyTable M
> LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
> LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
> LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
|||Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question

"Hilary Cotter" wrote:

> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>
>
|||Perhaps if you could post the schema. For the record mode-4 is indexed and
queried two separate words. If 4 is not in your noise word list this should
work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...[vbcol=seagreen]
> Not sure what you mean by "are they integer values", but the table that
> contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
> full-text indexed? Course, I'm thinking yes since you asked the question
>

> "Hilary Cotter" wrote:
|||Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.
I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.
"Hilary Cotter" wrote:

> Perhaps if you could post the schema. For the record mode-4 is indexed and
> queried two separate words. If 4 is not in your noise word list this should
> work.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...
>
>
|||OK, let me guess your schema then from what you have posted.
Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))
This is kind of critical as I think your join condition is all wrong.
But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...[vbcol=seagreen]
> Sorry, but our company specifically prohibits posting any schema details
> in
> newsgroups, but should the order be:
> 1. Remove words from the noise list.
> 2. Create the full-text index.
> I'm wondering that since I created the index before removing the 4 from
> the
> noise list, that it may be the reason my search is not working.
> "Hilary Cotter" wrote:
|||The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.
Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.
"Hilary Cotter" wrote:

> OK, let me guess your schema then from what you have posted.
> Create MyFields(PK int not null identity primary key, Fields1 char(20),
> Field2 char(20), Field3 char(20), Field4 char(20))
> Create T1 (pk int not null references MyFields(PK), Field1 char(20))
> Create T2 (pk int not null references MyFields(PK), Field2 char(20))
> Create T3 (pk int not null references MyFields(PK), Field3 char(20))
> Create T4 (pk int not null references MyFields(PK), Field4 char(20))
> This is kind of critical as I think your join condition is all wrong.
> But you are correct with a search on mode-4 and you have removed 4 from your
> noise word list after building your index you will not get correct results.
> In fact you should get fewer results which makes me wonder about your join
> condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...
>
>
|||MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.
The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.
On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.
Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.
Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.
Hope this helps...
On 14 Feb, 19:35, Mike Collins <MikeColl...@.discussions.microsoft.com>
wrote:
> The join condition is there because there are many other items that we are
> building a where clause on. I simplified the query to what I thought was most
> necessary and only included the joins to be true to my actual query, but with
> what you have said about creating the full-text after removing the number 4
> from the noise file, now I do not think they matter. If I could show you the
> actual schema, I think you would agree with me.

Monday, March 19, 2012

Help with query optimization

"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

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

Wednesday, March 28, 2012

Help with SQL Contains clause

Monday, March 26, 2012

Help With Slow SQL Query

Help With Slow SQL Query

Friday, March 23, 2012

Help with SELECT query

Help with Select - into

Wednesday, March 21, 2012

Help with returning too much data

Monday, March 19, 2012

Help with query optimization

Help with query optimization

Help with query optimization

Max Query

Blog Archive

About Me