SQL Server Performance Forum – Threads Archive
Views, unions, table scans and seeks
I’m having difficulty making a set of non-performant(and hence useless) queries into a set of performing (and gleaming) queries. My problem is that the difference between those queries which perform and those which don’t, on face of it, seem small.I have a query such as: select *
from ctcvw_corr_summary_archive inner join complainant_reviews
on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
where complainant_reviews.case_ref=’77513′ Returns (15) rows instantly (hooray). However a query such as: select *
from ctcvw_corr_summary_archive inner join complainant_reviews
on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
where complainant_reviews.comp_key=29 Returns the same data but takes a disasterous 20 secs (booooo). The execution plan of the first query shows that an index seek takes place on each of the underlying tables of the view (ctcvw_corr_summary_archive) before the results are merged. However, the execution plan of the latter query shows that each underlying table of the view (ctcvw_corr_summary_archive) is scanned then merged before a bookmark lookup applied. I don’t really understand why there is such a great difference or what to check for next. I have tried index hints such as: select *
from
ctcvw_corr_summary_archive(index(case_ref)) inner join complainant_reviews
on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
where complainant_reviews.case_ref=’77513′ However the query analyser simply states:’Warning: Index hints supplied for view ‘ctcvw_corr_summary_archive’ will be ignored.’ any help will be greatly appreciated
For the second query your WHERE clause works with the ‘comp_key’ column. -Is there an index on this column in the underlying table?
-What is the selectivity of this column ?
NHO
Are you using the index hint in the view as well? Doing so will result this warnning.
Generally speaking, I would try to avoid using index hints as much as possible. Though in few cases it’s inavitable. It would help if you post the table and view structures and indexes. Bambola.
I agree with vbKenya, the second query uses ‘comp_key’ column. If there is no index on this column, the query analyzer will go for a rable scan. Does index case_ref have comp_key as one of the columns in the index? If not, the query optimizer will not use this index and will flash this error. Other thing is that even if it has the column but it is not the intial column in the index, the optimizer will ignore this hint as it cann’t use the indes in this case as well. HTH. Gaurav
I have reduced the problem described into a more simplifed form. The table scanning affect occurs only when a significant amount of data exists. I have two identical tables in separate databases (called correspondence). I create view: create view testview as
select corr_key, policy_no from database1.dbo.correspondence
union all
select corr_key, policy_no from database2.dbo.correspondence The query:
select * from testview where corr_key = 333 returns instantly. The execution plan shows that the query uses an index seek on corr_key on each of the underlying tables before before concatenating the results. The query:
select * from testview where policy_no = ‘xxxx’ completes in 5 seconds, returning less data. The execution plan shows an index seek on corr_key for one of the underlying tables in the view. The other underlying table requires a table scan, a ‘compute scalar’ on the field policy_no, then a filter. A non-clustered index exists on both tables on both fields
Is the index same on both the tables? What about the data? If there are very less no. of rows in the table, optimizer may ignore the index on the table. Conversely if there is large amount of data and the query is not that selective, then also the index may be ignored. See the difference in no. of rows affected by query in both tables. If this is same, try updating the statistics for these indexes. HTH. Gaurav
For testing I used DTS to transfer the object correspondence from database1 to database2. Therefore the object should be identical. I have also run ‘update statistics’ on both tables. There are 578407 rows in each table. There are 578407 unique corr_key values and 53014 unique policy_no values.
I am not very sure if DTS cretes the indexes. Can you please confirm that the indexes are similar? How about the index? What is the index structure? Gaurav
I see what you mean. I created to similar tables on 2 databases with the same 2 indexed (one on int the other on varchar) and a view on them. Searching by the int column does a seek while the other does a scan. The only way I could have an index seek was using the index hint. It gave the warning, but it also did the seek.
I did the same in the same database, and it was doing seek in both cases. So I guess it has something to do with the fact you are using 2 databases. Bambola.
I can confirm though that (for testing purposes) the data and indexes in the two tables are identical. My issue with the query optimiser is that it is choosing a different query plan when the command is issued as a view rather than as a select. The following queries all perform an index seek on the index ‘policy_no’ and are performant:
Q1 – select * from database1.dbo.correspondence where policy_no = ‘xxxx’ Q2 – select * from database2.dbo.correspondence where policy_no = ‘xxxx’ However when I create a query to select data in union a table scan occurs.
create view testview as
select corr_key, policy_no from database1.dbo.correspondence
union all
select corr_key, policy_no from database2.dbo.correspondence select * from testview where policy_no = ‘xxxx’
/* results in a table scan and poor performance */ Expanding the view to encompass further databases and tables reduces the performance further; only the first table is ‘seeked’ further tables are all ‘scanned’ create view testview as
select corr_key, policy_no from database1.dbo.correspondence /*seeked*/
union all
select corr_key, policy_no from database2.dbo.correspondence /*scanned*/
union all
select corr_key, policy_no from database3.dbo.correspondence /*scanned*/
I wish to know how (or if it is possible) to tell the optimiser (as part of the view?) to use a particular search method.
Bambola, Gaurav, Thank you for taking the taking the time to read and try. Any suggestions you are able to make will be greatly appreciated.
I just tried it in the office and it is doing index seek in both cases. The difference is that at home I have Personal edition and here Developer. What edition are you using? try SELECT @@VERSION in QA. Bambola.
select @@version reveals: Microsoft SQL Server 2000 – 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
🙁
I suggest you update your sql server to sp3 <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Bambola.
]]>