Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: optimize query - how to make it an "Index seek"

    create table t1(a varchar(50) , b varchar(50))

    create index i1 on t1(a)
    create index i2 on t1(b)

    create view v1
    as
    select * from t1 where isnull(a,b) = 'test'

    select * from v1

    The above SQL "select * from v1" is doing a table scan.
    What do I do to make it perform an index seek ????

    TIA

    - ForXLDB

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi!

    This query will make use of index i3:

    Code:
    create index i3 on t1(a,b)
    Carsten

  3. #3
    Join Date
    Dec 2003
    Posts
    61
    Is it possible to have make an "Index seek" ??

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Just found the following behaviour:

    When running:
    select * from dbo.v1 => SQL Server uses an index scan

    select * from dbo.v1 where a='test' => SQL Server uses an index seek

    It's quite strange....

  5. #5
    Join Date
    Dec 2003
    Posts
    61
    I do use the following....
    select * from dbo.v1 where a='test'

    So, it works for me I guess.

    Thanks !!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by CarstenK
    quite strange....
    How so?

    There's no predicate...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    61
    I'm not sure. But, it shows "Index Seek" in the plan !!

  8. #8
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi Brett,

    what confused me is the point, that the DBMS changes the execution plan even if there is basically no difference in the constraint (in this case "a = 'test'").

    The definition of v1 already contains this WHERE-clause. And that's why I expect SQL Server to generate the same execution plan.

    Carsten

    Quote Originally Posted by Brett Kaiser
    How so?

    There's no predicate...

  9. #9
    Join Date
    Dec 2003
    Posts
    61

    how to make perform "index seek" rather than index scan

    create table t1(a datetime , b datetime)

    create view v1
    as
    select a,b, isnull(a,b) as c from t1

    create index i3 on t1(a,b)

    select *
    from v1
    where c = '01/01/2004'

    the above sql is using index scan......can we make it to use index seek ???

    --clean up
    --drop table t1
    --drop view v1
    --drop index t1.i3

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One small thing is that you need to have a bunch of data in the table, before the optimizer thinks about the index. If the data portion of the table consists of a single page, then you will always get a table scan. This is not bad, as it is one read. If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.

    A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    --Change view to:
    alter view v1
    as
    select * from t1 where a = 'test'
    union
    select * from t1 where b = 'test'
    go

    --add 2 indexes and drop existing ones:
    drop index t1.i1
    drop index t1.i2
    create index i3 on t1(a,b)
    create index i4 on t1(b,a)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by MCrowley
    ...If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.
    This is not true when you deal with queries that do not address fields that are not part of an index.

    Quote Originally Posted by MCrowley
    ...A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.
    Really? Try this using DDL changes from my previous post:

    select * from v1 where isnull(a,b)='test'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •