help for table scan

Question

help for table scan

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
July 26, 2007 at 9:59 am

#197001

I have a problem with a query that performs full table scans on SLQ server 2005 standard/Win 2003 Server, 64b. The same query behaves differently on the same schema on SQL server 2000 – no FTS. This I strange to me because most of the field participating in this query are index (because of their usage throughout that application)
The problem is not when I use these view alone but when I call them from other views/or join in a query.
Indexes on sc.conf_ID1, sc.conf_ID2, main_id, sc.CONF_dt, sc.CONF_id, es.mm_id, sc2.test_id
Here is the base statement:
create view VIS_0DEGREE_RECENT
select sc.conf_ID1, sc.conf_ID2, sc.CONF_id, sc.test_id, sc.CONF_dt
from sep_CONF sc ,main_score es
WHERE sc.conf_ID1 = es.mm_id and
sc.conf_ID2 = sc.conf_ID1 and
sc.conf_ID2 = es.mm_id
and sc.test_id = es.main_id
and sc.CONF_id =
(select max(sc2.CONF_id)
from sep_CONF sc2 , main_score es2
where c2.conf_ID1 = es2.mm_id and
sc2.conf_ID2 = sc2.conf_ID1 and
sc2.test_id = es2.main_id and
sc2.conf_ID2 = es.mm_id )
I also tried 2 views for better performance:
create view VIS_0DEGREE_EXISTS as
select sc.conf_ID1, sc.conf_ID2, sc.CONF_id, sc.test_id, sc.CONF_dt
from sep_CONF sc where exists (select 1 from main_score es
WHERE sc.conf_ID1 = es.mm_id and
sc.conf_ID2 = sc.conf_ID1
and sc.test_id = es.main_id)
create view VIS_0DEGREE_RECENT as
select * from VIS_0DEGREE_EXISTS ve
where ve.CONF_id = (select max(ve2.CONF_id) from VIS_0DEGREE_EXISTS ve2
where ve.conf_ID1 = ve2.conf_ID1 and
ve.conf_ID2 = ve2.conf_ID2)
No problems here – uses indexes:
Select * from VIS_0DEGREE_RECENT where CONF_id = 1000 and mm_id = 1000
Select * from VIS_0DEGREE_RECENT where conf_ID1=1000 and conf_ID2 = 1005
Problem query - FTS on sep_CONF twice because of the case statement...
In SQL server 2000, it uses the index on CONF_id and conf_ID1.
Same setup , same data.
select CONF_ID, CONF_ID1,CONF_ID2,DEG_OF_SEP,CONF_DT,CONF_STAT,
(case
when sc.DEG_OF_SEP = 0 and not exists (select 1 from main_score es
WHERE sc.test_id = es.main_id and es.mm_id = sc.conf_ID1)
then ( select es.main_id from main_score es
where es.main_id = (select ve.test_id
from VIS_0DEGREE_RECENT ve
where ve.conf_ID1 = sc.conf_ID1
and ve.conf_ID2 = sc.conf_ID2)
and es.mm_id = sc.conf_ID1)
else (test_id) END) as TEST_ID
from sep_CONF sc

Viewing 6 posts - 1 through 5 (of 5 total)

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

Thomas-428301 SSC Veteran Points: 279 More actions · Answer 1

Hi

Its funny how the same query does not have similar execution plans in 2000 and 2005. But at the same time I just want to remind that there have been changes done to the engine that might be the cause for the query not to have similar execution plans.

Eg. The bookmark lookup does not exist any more in 2005 , ect.

Similarly there may be a logical explanation why the results differ.

My personal opinion would be to completely scrap the views that are used and replace them with temporary tables(use indexs on them if required) and generate the result set. The reason been , even though the views are precompiled they have to join with the entire table prior to been usable by any query.

Few guide lines

Not to use views
Avoid using derive tables (You can use the new feature provided in 2005)
Always select only the columns required (never user “Select * from ….)

I

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Recommend rewriting the following using a derived table with a proper join instead of the correlated sub-query that you have...

and sc.CONF_id =

(select max(sc2.CONF_id)

from sep_CONF sc2 , main_score es2

where c2.conf_ID1 = es2.mm_id and

sc2.conf_ID2 = sc2.conf_ID1 and

sc2.test_id = es2.main_id and

sc2.conf_ID2 = es.mm_id )

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

the bookmark lookup still exists in sql 2005. Views are rubbish like you describe, you might want to try table valued function.

I've found that joining views or joining to views often doesn't produce the same query as if the view was a physical table, works a bit like linked queries where all the data might be brought back prior to the join.

If you look at the query plan you'll be able to find out the issue and fix it.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

joresm SSC Veteran Points: 221 More actions · Answer 4

based on the amount of data being extracted, I would definitley not use views for this type of extract. my best bet would be on Stored Procs. put your code in the SP and call the SP. 2000 or 2005 SP would be the most efficient code and would return the fastest result. we have a BW extract that was written in views and SPs boosted the perofrmance considerably.

Todd Stu Say Hey Kid Points: 690 More actions · Answer 5

Todd Stu

Say Hey Kid

Points: 690

July 30, 2007 at 1:36 pm

#723402

make sure statistics are up to date.

Cheers,

Todd