Re: Need help with hint syntax in SQL Server 2000

On Dec 9, 11:22 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Dec 6, 11:00 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:





Mark D Powell (Mark.Pow...@xxxxxxx) 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...@xxxxxxxxxxxxx

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 --- Hide quoted text -

- Show quoted text -

Update. I figured out what was wrong and got around the issue but I
think I should have been able to do it differently.

The query was a simple query against a view:
select * from view where col = value

This value was indexed in the primary target table under the view with
5.4M rows. The view is a union all of a two table join to a multi-
table join.

When I coded the index hint the SQL Server plan showed it being used
however a test of the query took 9 minutes instead of the 1 being
taken in the full table scan. A review of the statistics seemed to
indicate that although the index was used to retrieve the data the
index value availalbe in the query was not being applied as all 5.4M
rows were being passed up the plan.

A quick query agains the large table for a count showed only 205 rows
for the value in question and a test query ran immediately to fetch
those rows via the index. I tried coding a view on this table and
using it in a new version of the existing view to get SQL Server to
filter via the index. No luck.

So I took the modified view code I was using and placed the filter
condition into the sql in both parts of the union and the result was
instanct. I gave that to the developer. He was happy enough with the
result.

It seems like SQL Server should have pushed the filter condition down
and performed it against the large table for the query against the
view like it does for the extracted view SQL when submitted as a
query.

I saw the noexpand hint but that seems the opposite of what I think
should have happened. Anyone able to explain why SQL Server was
unable to recognize that it shoul push the where clause condition down
to the table access?

-- Mark D Powell --
.

Relevant Pages

  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Forcing index usage...
    ... I'm in a situation where oracle spatial doesn't use a domain index even if I use the index hint. ... But as soon as I add more predicates or nest the query into another one, ... Could you give us an example of the SQL ... Complete with execution plans. ...
    (comp.databases.oracle.server)