index and query optimization plzzz

I have Table1, Table2,
Table1 Have ID(primary key),Court_ID (clustered index)..
Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
Table 2 have 1,000,000 record, I use this query:
SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
text%'.
I don't talk here about the (like '%%') performance..
When i look at execution plan i found it's estimated row count 1,000,000
record of table2 which i'm sure waste of time, I tried inner join also but
it's the same..
I want 'like' operator to scan only approx 86,000 record which to Court_ID =
1 not scan the whole table then filter it.
Any help plz to correct my indexes or write better query'"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key), Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count 1,000,000
> record of table2 which i'm sure waste of time, I tried inner join also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to Court_ID
> = 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
Post you actual table DDL.
David|||CREATE TABLE [AH_Master] (
[ID] [PKInt] NOT NULL ,
[Ma7kama_ID] [PKInt] NOT NULL ,
[Case_No] [int] NOT NULL ,
[Case_Year] [smallint] NOT NULL ,
[Case_Date] [datetime] NOT NULL ,
[Office_Year] [smallint] NULL ,
[Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
[Page_No] [smallint] NULL ,
[Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
[IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
DEFAULT (0),
[Part_No] [smallint] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
and my query is:
SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM AH_SubMaster
AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID]) Where
MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Post you actual table DDL.
> David
>|||According to your DDL, there are no clustered indexes on your tables. Try
clustering AH_Master on Ma7kama_ID and clustering AH_SubMaster on MasterID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> CREATE TABLE [AH_Master] (
> [ID] [PKInt] NOT NULL ,
> [Ma7kama_ID] [PKInt] NOT NULL ,
> [Case_No] [int] NOT NULL ,
> [Case_Year] [smallint] NOT NULL ,
> [Case_Date] [datetime] NOT NULL ,
> [Office_Year] [smallint] NULL ,
> [Office_Sufix] [char] (2) COLLATE Arabic_CI_AI_KS_WS NULL ,
> [Page_No] [smallint] NULL ,
> [Master_Text] [varchar] (200) COLLATE Arabic_BIN NULL ,
> [IF_Agree] [smallint] NULL CONSTRAINT [DF__AH_Master__IF_Ag__79A81403]
> DEFAULT (0),
> [Part_No] [smallint] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [PK_AH_MASTER] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 80 ON [PRIMARY] ,
> CONSTRAINT [FK_AH_MASTE_REFERENCE_AH_MA7AK] FOREIGN KEY
> (
> [Ma7kama_ID]
> ) REFERENCES [AH_Ma7akem] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> CREATE TABLE [AH_SubMaster] (
> [Master_ID] [int] NOT NULL ,
> [Fakra_No] [smallint] NOT NULL ,
> [Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
> [Tasneef_ID] [PKInt] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
> (
> [Master_ID],
> [Fakra_No]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> and my query is:
> SELECT Sub.[Fakra_No], Sub.[Fakra_Text], Sub.[Tasneef_ID] FROM
> AH_SubMaster AS Sub right join AH_Master AS MT on (Sub.Master_ID =MT.[ID])
> Where MT.Ma7kama_ID =1 and Sub.[Fakra_Text] like '%sometext%'
>
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23Ptp6Fq8FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:u23gign8FHA.2364@.TK2MSFTNGP12.phx.gbl...
> I have Table1, Table2,
> Table1 Have ID(primary key),Court_ID (clustered index)..
> Table2 Have Table1_ID,Rule_No(both are clustered primary key),
Text (text)
> Table 2 have 1,000,000 record, I use this query:
> SELECT t2.[Rule_No], t2.[Text], FROM Table2 AS t2 right join
Table1 AS t1
> on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text]
like '%some
> text%'.
> I don't talk here about the (like '%%') performance..
> When i look at execution plan i found it's estimated row count
1,000,000
> record of table2 which i'm sure waste of time, I tried inner join
also but
> it's the same..
> I want 'like' operator to scan only approx 86,000 record which to
Court_ID =
> 1 not scan the whole table then filter it.
> Any help plz to correct my indexes or write better query'
>
Isalamegy,
The predicate:
like '%some text%'
Will always cause an index or table scan as far as I know. The
potential exists for it to equal the column value of any row, and so
the column must be scanned.
Even if the column is indexed, depending on a variety of factors,
the query optimizer may decide the cost of scanning the index
reaches the point where the whole table might as well be scanned,
and so it will switch from index scan to table scan.
Sincerely,
Chris O.|||"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
<snip>

> [Ma7kama_ID] [PKInt] NOT NULL ,
<snip>

> [Tasneef_ID] [PKInt] NULL ,
BOL and Google don't seem to mention PKInt as a data type in SQL
Server.
What am I missing here?
Sincerely,
Chris O.|||There is no such datatype as PKInt. It is probably a user defined type.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:OaOdnfGosarMMxXeRVn-hA@.comcast.com...
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:uHMFKgq8FHA.2492@.TK2MSFTNGP10.phx.gbl...
> <snip>
>
> <snip>
>
>
> BOL and Google don't seem to mention PKInt as a data type in SQL
> Server.
> What am I missing here?
>
> Sincerely,
> Chris O.
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> There is no such datatype as PKInt. It is probably a user defined
type.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
Kalen Delaney,
Ah, I didn't think of that. Thank you.
Sincerely,
Chris O.|||Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>|||You may want to try index hints in this case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eY1jNHz8FHA.2036@.TK2MSFTNGP14.phx.gbl...
Yes.. PKInt is a user defined datatype..
The clustered indexs that Tom suggest is exist in my tables ..
so my question. why even i narrow the search using Ma7kama_ID from table
master it still scan the whole AH_SubMaster Text column... then filter it!!
Why it don't search only in fields with the specified Ma7kama_ID'
am i have to add Ma7kama_ID field to table AH_SubMaster, I tried it and it
work as i expected but this way will duplicate ma7kama_ID in many of my
tables with text which i need to scan with the same way..
any ideas'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:soydnYOHY8Ew1BTeRVn-vQ@.comcast.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23pfZlur8FHA.1140@.tk2msftngp13.phx.gbl...
> type.
> Kalen Delaney,
> Ah, I didn't think of that. Thank you.
>
> Sincerely,
> Chris O.
>