Need help with hint syntax in SQL Server 2000
in [Using MS SQL Server]
|
From: Mark D Powell on 5 Dec 2008 16:48 I have only used hints a few times and it has been a while. Every time I try to play with an SQL statement to force use of index over a non-indexed path so I can compare the two approaches it take me forever because I cannot remember how to code the hints. I just cannot figure it out form the books on-line syntax diagrams. Can someone point to to an article which has numerous examples of how to code the hints into the SQL? It will probably eventually clink since it has in the past, but I would rather not spend a couple hours on whatever stupid mistake I am making. Thanks -- Mark D Powell --
From: Erland Sommarskog on 6 Dec 2008 08:10 Mark D Powell (Mark.Powell(a)eds.com) writes: > I have only used hints a few times and it has been a while. Every > time I try to play with an SQL statement to force use of index over a > non-indexed path so I can compare the two approaches it take me > forever because I cannot remember how to code the hints. I just > cannot figure it out form the books on-line syntax diagrams. > > Can someone point to to an article which has numerous examples of how > to code the hints into the SQL? > > It will probably eventually clink since it has in the past, but I > would rather not spend a couple hours on whatever stupid mistake I am > making. FROM tbl alias WITH (INDEX = yourindex) or FROM tbl WITH (INDEX = yourindex) if you don't use aliases. The WITH keyword is not mandatory in SQL 2000, but it is SQL 2005, so start using it. If you want to force the clustered index, I think it's better to say: FROM tbl WITH (INDEX = 1) as a name easily could to wrong. But for non-clustered indexes, names is better over index ids. There are some older syntax that you have used in the past. Forget about those. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Mark D Powell on 6 Dec 2008 10:39 On Dec 6, 8:10 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Mark D Powell (Mark.Pow...(a)eds.com) writes: > > > I have only used hints a few times and it has been a while. Every > > time I try to play with an SQL statement to force use of index over a > > non-indexed path so I can compare the two approaches it take me > > forever because I cannot remember how to code the hints. I just > > cannot figure it out form the books on-line syntax diagrams. > > > Can someone point to to an article which has numerous examples of how > > to code the hints into the SQL? > > > It will probably eventually clink since it has in the past, but I > > would rather not spend a couple hours on whatever stupid mistake I am > > making. > > FROM tbl alias WITH (INDEX = yourindex) > > or > > FROM tbl WITH (INDEX = yourindex) > > if you don't use aliases. > > The WITH keyword is not mandatory in SQL 2000, but it is SQL 2005, so start > using it. > > If you want to force the clustered index, I think it's better to say: > > FROM tbl WITH (INDEX = 1) > > as a name easily could to wrong. But for non-clustered indexes, names is > better over index ids. > > There are some older syntax that you have used in the past. Forget about > those. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Blast. Based on < table_hint > ::= { INDEX ( index_val [ ,...n ] ) I was doing: with index (index_name) I will have to the correct syntax on Monday and see if I can get the optimizer to try the index. It is a simple query with one condition on a view that is a union of two joins. The view is written using the new with ANSI 92 join syntax. We scan a small 138 row table and then scan a 5.9M row tables. I am trying to index the column the join is done on and a second column that is used to filter the large table for a time test. I haven't worked out how selective the index will be yet as I have to present proof that an index would not fix the problem. I have suggested purging as an alternate if I cannot find an index that will help. I know a FROM (table) hint on a query against a view is propogated to the tables in the view but will an index hint propogate or will I have to place the hint into the view itself? I have already created a copy of the real view to use in my testing but I ran into the syntax error again so I haven't had the opportunity to see if the hint propogates or now. Thank you for the reply. -- Mark D Powell --
From: Erland Sommarskog on 6 Dec 2008 11:00 Mark D Powell (Mark.Powell(a)eds.com) writes: > Blast. Based on < table_hint > ::= > { INDEX ( index_val [ ,...n ] ) > I was doing: with index (index_name) There are just too many ways to specify index hints! What you missed was the extra layer of parenthesis, found higher up in the topic. This is what you should try: WITH (INDEX (myindex)) I see that the syntax I suggested is not in Books Online 2000, but it appears in Books Online 2008, although it seem to suggest that I should say: WITH (INDEX = (myindex)) Nevertheless, I am quite sure that my original proposal works. > I know a FROM (table) hint on a query against a view is propogated to > the tables in the view but will an index hint propogate or will I have > to place the hint into the view itself? I think you will have to stick the hint in the view itself. Which is something I would try to avoid. But try using the query as such with the hint and see if it helps. But run UPDATE STATISTICS WITH FULLSCAN on the tables first. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Mark D Powell on 9 Dec 2008 11:22 On Dec 6, 11:00 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Mark D Powell (Mark.Pow...(a)eds.com) writes: > > > Blast. Based on < table_hint > ::= > > { INDEX ( index_val [ ,...n ] ) > > I was doing: with index (index_name) > > There are just too many ways to specify index hints! > > What you missed was the extra layer of parenthesis, found higher up > in the topic. This is what you should try: > > WITH (INDEX (myindex)) > > I see that the syntax I suggested is not in Books Online 2000, but it > appears in Books Online 2008, although it seem to suggest that I should > say: > > WITH (INDEX = (myindex)) > > Nevertheless, I am quite sure that my original proposal works. > > > I know a FROM (table) hint on a query against a view is propogated to > > the tables in the view but will an index hint propogate or will I have > > to place the hint into the view itself? > > I think you will have to stick the hint in the view itself. Which is > something I would try to avoid. But try using the query as such with > the hint and see if it helps. > > But run UPDATE STATISTICS WITH FULLSCAN on the tables first. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you for the update. I did not get to work on this yesterday but I intend to try to test today. -- Mark D Powell --
|
Next
|
Last
Pages: 1 2 Prev: Having problems using BCP for CSV output Next: MS SQLS Server 1.2 JDBC Driver - problems |