December 5, 2001 at 8:07 pm
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
AS
SELECT TOP 1 dbo.tc.tc_id 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(dbo.trade.trade_description) AS apprentice_trade,
RTRIM(dbo.person.person_given_names) AS apprentice_firstname, RTRIM(dbo.person.person_surname) AS apprentice_surname
from
dbo.tc
INNER JOIN dbo.organisation
ON dbo.tc.tc_empsite_org_id = 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.tc.tc_person_id = dbo.person.person_id
INNER JOIN dbo.training_product
ON dbo.tc.tc_trade_training_product_id = dbo.training_product.training_product_id
INNER JOIN dbo.apprenticeship
ON dbo.training_product.training_product_id = dbo.apprenticeship.appr_apprenticeship_id
INNER JOIN dbo.trade
ON dbo.apprenticeship.appr_trade_id = dbo.trade.trade_id
...and ...
select * from trs.vRepDoc_302
where apprentice_id = '713006A1' <---- WORKS, but takes full 50sec
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 5, 2001 at 8:38 pm
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?
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply