Interesting VIEW problem with TOP N

  • Hi all

    Just finished working out a problem (well, probably not a problem as such) with views.

    One of our developers created a large query and slapped it into a view, the view included

    the TOP 1 clause. The query had only 1 where clause condition, ie. tc_id = '122345A1', and when run via query analyser would perform extremely quickly. Slap it into a view, strip the where clause and add it via the view, and the speed went from 0.2sec to 52sec.

    After moving the query around a little, I removed the TOP 1 from the query inside of the view and the 0.2sec performance returned. The TOP 1 clause in the view query was causing all rows to be returned and then the users where clause was being applied! producing of course a totally different query plan.

    Is this typical behaviour?? seems very strange to me. Its strange because a very simple test below does not work (as I would expect) using TOP in a view, but my other complex

    view does, but takes 50sec!?

    Here is simple example:

    create view mytest as

    select top 1 * from tc

    select * from mytest

    where tc_id = '713006A1' <---- returns NO rows

    remove the top and all is ok. Assuming sql does a simply symbolic replacement to give:

    select * from (select top 1 * from tc) as A

    where A.tc_id = '713006A1'

    The more complex query that DOES work with TOP in the query, but just takes a long time is:

    CREATE VIEW trs.vRepDoc_302


    SELECT TOP 1 AS apprentice_id, RTRIM(dbo.address.address_line1) AS employer_address_line1, RTRIM(dbo.address.address_line2)

    AS employer_address_line2, RTRIM(dbo.address.address_line3) AS employer_address_line3, RTRIM(dbo.address.address_postcode)

    AS employer_postcode, RTRIM(dbo.address.address_suburb) AS employer_suburb, RTRIM(dbo.address.address_state) AS employer_state,

    RTRIM(dbo.organisation_name.orgname_name) AS employer_name, RTRIM( AS apprentice_trade,

    RTRIM(dbo.person.person_given_names) AS apprentice_firstname, RTRIM(dbo.person.person_surname) AS apprentice_surname


    INNER JOIN dbo.organisation

    ON = dbo.organisation.org_id

    INNER JOIN dbo.address

    ON dbo.organisation.org_id = dbo.address.address_org_id

    and dbo.address.address_addresstype_id = 2

    INNER JOIN dbo.organisation_name

    ON dbo.organisation.org_id = dbo.organisation_name.orgname_org_id

    and dbo.organisation_name.orgname_nametype_id = 1

    INNER JOIN dbo.person

    ON = dbo.person.person_id

    INNER JOIN dbo.training_product

    ON = dbo.training_product.training_product_id

    INNER JOIN dbo.apprenticeship

    ON dbo.training_product.training_product_id = dbo.apprenticeship.appr_apprenticeship_id


    ON dbo.apprenticeship.appr_trade_id =

    ...and ...

    select * from trs.vRepDoc_302

    where apprentice_id = '713006A1' <---- WORKS, but takes full 50sec



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • I'll try to find time to experiment. Definitely it seems like there are times when the query planner gets confused, must default to a "safe" plan. Have you tried adding the fast N hint to see if it helps?


Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply