|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
index and query optimization plzzzI 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??
Show quote
"Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message Post you actual table DDL.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?? 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%' Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:%23Ptp6Fq8FHA.476@TK2MSFTNGP15.phx.gbl... > > "Islamegy®" <NULL_Islamegy_N***@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 > 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 -- Show quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com "Islamegy®" <NULL_Islamegy_N***@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 m***@hotmail.com> wrote in > message news:%23Ptp6Fq8FHA.476@TK2MSFTNGP15.phx.gbl... >> >> "Islamegy®" <NULL_Islamegy_N***@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 >> > > "Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message <snip>news:uHMFKgq8FHA.2492@TK2MSFTNGP10.phx.gbl... > [Ma7kama_ID] [PKInt] NOT NULL , <snip>> [Tasneef_ID] [PKInt] NULL , BOL and Google don't seem to mention PKInt as a data type in SQLServer. What am I missing here? Sincerely, Chris O. There is no such datatype as PKInt. It is probably a user defined type.
Show quote "Chris2" <rainofsteel.NOTVA***@GETRIDOF.luminousrain.com> wrote in message news:OaOdnfGosarMMxXeRVn-hA@comcast.com... > > "Islamegy®" <NULL_Islamegy_N***@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. > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message Kalen Delaney,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 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?? Show quote "Chris2" <rainofsteel.NOTVA***@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... >> 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. > > 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_N***@yahoo.com> wrote in message Yes.. PKInt is a user defined datatype..news:eY1jNHz8FHA.2036@TK2MSFTNGP14.phx.gbl... 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?? Show quote "Chris2" <rainofsteel.NOTVA***@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... >> 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. > > Sorry if my question look strange but
What is index hints?! what index hints i have to try?? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:uvXjRB18FHA.3200@TK2MSFTNGP11.phx.gbl... > 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_N***@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.NOTVA***@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... >>> 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. >> >> > > "Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message Islamegy,news:uAhljo28FHA.476@TK2MSFTNGP15.phx.gbl... > Sorry if my question look strange but > What is index hints?! > what index hints i have to try?? > Look up, "Table Hints", in Books Online. The first table hint is "INDEX (index_val [ ,... n ] )". Using hints of any kind is a last resort. SQL Server's query optimizer is pretty smart when it comes to figuring out which indexes to use. Using them successfully depends on understanding how the query optimizer functions in normal circumstances. As I pointed out in my other post, the query optimizer may decide that an index scan is so costly (one million rows) that a table scan is a good idea (based on the other parts of the originally posted query). If you throw an index hint onto your table that forces an index scan on the column (t2.[Text]) the "%%" is being used on, you may speed up the search for values in that column, and then slow things down afterward when SQL Server has to go back and find all corresponding values in the associated column (t2.[Rule_No]). The table scan may have been selected by the query optimizer because it calculated that it was cheaper _overall_ You'll have to do some testing to find out what, if any, benefits come from using a table hint in this case. Sincerely, Chris O. "Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message Islamegy,news:eY1jNHz8FHA.2036@TK2MSFTNGP14.phx.gbl... > Yes.. PKInt is a user defined datatype.. > The clustered indexs that Tom suggest is exist in my tables .. But the DDL that was posted did not show a clustered index in the tables. Sincerely, Chris O. Islamegy® (NULL_Islamegy_N***@yahoo.com) writes:
> and my query is: The query looks strange. You have a RIGHT JOIN to AH_SubMaster, but> 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%' then then WHERE clause includes a condition on this table. The net result is that the query returns the same result as an inner join. Is this what you want? As for the performance issue, try running UPDATE STATISTICS tbl WITH FULLSCAN on both tables, and see what the effect might be. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx "Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message Table1 AS t1news: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 > on (t1.ID = t2.[Table1_ID]) Where t1.Court_ID =1 and t2.[Text] 1,000,000like '%some > text%'. > > I don't talk here about the (like '%%') performance.. > When i look at execution plan i found it's estimated row count > record of table2 which i'm sure waste of time, I tried inner join Isalamegy,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?? > > 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.  |
|||||||||||||||||||||||
Other interesting topics