July 17, 2003 at 7:57 am
Hi,
We recently upgraded our database from sql7 to sql2000 sp3. Upgrade completed without error and I both rebuild the indexes and ran sp_updatestats on all databases.
Everything seems to work fine except for queries involving views.
Most of our tables have a projectID column together with a key for that table. These two together make up the PK on these tables.
We have a view which does joins over 6 tables, and the joins uses the ProjectID and the TableKeys. The ProjectID is among the columns in the select-list.
A common query would be like "select colA,colB,colC from MyView where projectID=123 and colC like 'abc%'"
One of the tables has around 2 Million records, but grouped by ProjectID it is fairly constant at around 9000 records per project.
Now to my problem:
When I run a query using the view, it will perform a Index Scan of those 2 Million records instead of using the ProjectID in the seek.
Whereas if I run the select-definition of the view together with ProjectID=123 added to the WHERE clause, then it does filter out all tables values by ProjectID to generate its result.
As it is now, the query using the view will make the tempdb grow and eventually fill up the entire disk.
Anyone experienced similar behavior?
Note that we did not have this problem while still using sql7 sp4.
Regards,
Minh Hoac
July 17, 2003 at 8:04 am
quote:
Now to my problem:When I run a query using the view, it will perform a Index Scan of those 2 Million records instead of using the ProjectID in the seek.
Whereas if I run the select-definition of the view together with ProjectID=123 added to the WHERE clause, then it does filter out all tables values by ProjectID to generate its result.
Can you try to refresh the view with sp_refreshview command? If you still get same slow performance, I would suggest to drop the view and recreate it.
July 17, 2003 at 8:07 am
Thanks for your response Allen.
Yes, after the upgrade I re-created all the views. As well as all the procedures and triggers.
/Minh
July 17, 2003 at 9:00 am
Tried adding an index hint?
Andy
July 17, 2003 at 9:22 am
Do you mean adding an index on the view? From the manual it says that indexed views are not well suited when you have frequent updates. Although the view is used mainly to generate reports, the underlying tables have quite frequently inserts.
The execution plan are different between the view and select-statement, so it might not be a useful comparison with respect to the way the indexes are used. But it is strange though that with the view, the Optimizer does a Cluster Index Scan on all 2 million rows on the problem table in question. Whereas in the select, it does Clustered Index Seek using the ProjectID as argument, resulting in 9000 rows for that branch.
Why doesn't it use ProjectID as argument in Index Scan with the view also?
/Minh
July 17, 2003 at 9:31 am
Can you post your view and the query against the view here?
July 17, 2003 at 9:53 am
Thanks guys for your help.
Here it comes. I have stripped off some of the columns for readability, but the structure is the same.
I tried to include the execution plans but in Preview window it seemed to truncated. I will post the execution plan in next posting if you guys want.
/*View*/
select v.phcode, v.epscode
from v_phys_site v
where v.projectID=123
and v.phcode like '%abc%'
/*Select*/
SELECT
p.wbsrecno,p.projectID,p.phcode,
e.epscode,e.epsdescr
FROM
physite p,
typeeps te,
epscodes e,
epsattrib ea,
products pr,
typhy tp
WHERE tp.phrecno = p.phrecnoAND tp.projectID = p.projectID
AND te.tyrecno = tp.tyrecno AND te.projectID = tp.projectID
AND e.epsrecno = te.epsrecno AND e.projectID = te.projectID
AND ea.epsrecno = e.epsrecno AND ea.projectID = e.projectID
AND pr.prorecno = e.prorecno AND pr.projectID = e.projectID
and pr.projectID=123
and p.phcode like '%abc%'
GROUP BY
p.wbsrecno,p.projectID,p.phcode,e.epscode,e.epsdescr
July 17, 2003 at 11:33 am
I meant adding a hint to the query to force it to use the index.
Andy
July 17, 2003 at 11:53 am
quote:
/*View*/select v.phcode, v.epscode
from v_phys_site v
where v.projectID=123
and v.phcode like '%abc%'
This is the query runing against the view (v_phys_site) but I like to see the view v_phys_site itself.
July 17, 2003 at 1:51 pm
The view definition is the same as the select-statement, except that I have added 'ProjectID=123 and phcode like '%abc%' in the select statement.
That's what surprises me. That running a query via a view yields different plan than issuing the select statement directly to the base tables.
/Minh
July 17, 2003 at 2:08 pm
I guess you need post the execution plan for both of queries.
July 18, 2003 at 2:25 am
I solved my problem by re-writing the view definition.
I experimented yesterday with different versions of the same select. The one that seems to make the difference is the ProjectID column in the select-list. It does matter from what table I choose to fetch it from. The original select fetches from physite.projectID. The one that proved to yield the best performance was to products.projectID.
The execution time when from external (until tempdb fills) to 8 seconds.
I am glad I solved this particular problem but I am still convinced that it was the upgrade which triggered this problem. Despite all MS says about performance gain, I haven't noticed any dramatic difference. Although I must admit that I haven't performed any benchmarking to prove.
BUT the performance degradation in my case does shadow all other possible performance gains, if any.
/Minh
July 18, 2003 at 8:16 am
We had about 1% of our queries run slower when we upgraded to 2000. Setting the multi-processing to 1 by using the maxdop option fixed some and reworking the sql fixed some others. There was also a rare change/bug in the optimizer that caused 1 complex query a problem by changing the query plan when we applied the slammer patch to sp2. We sent the code to MS and they confirmed the problem. MS suggested upgrading to sp3, which we have not done yet since we found other work arounds.
You may also want to check the capatibility mode of the database and make sure the memory is configured properly.
In almost all cases, the queries in 2000 ran the same or faster than in 7.0.
July 18, 2003 at 10:01 am
One of My upgrade from sql7 to sql2000 slow a sp from 30 minutes to 1 hr, which contain cursor operation. I have to rewrite the sp and take out all the cursor and get it fixed. Still I am not sure which sql statement screw up in sql2000. It's really upset when there is no document officially from Microsoft showing us what sqlserver engine change make the difference, but It's good that Microsoft SQLServer have more users so we can discuss here, My sybase upgrade( from sybase12 to sybase12.5) slow a query from 6 second to 1 minute, and I got nobody to talk, except Sybase technical support. But I was really frustrated when this happen and delay the whole project.
quote:
We had about 1% of our queries run slower when we upgraded to 2000. Setting the multi-processing to 1 by using the maxdop option fixed some and reworking the sql fixed some others. There was also a rare change/bug in the optimizer that caused 1 complex query a problem by changing the query plan when we applied the slammer patch to sp2. We sent the code to MS and they confirmed the problem. MS suggested upgrading to sp3, which we have not done yet since we found other work arounds.You may also want to check the capatibility mode of the database and make sure the memory is configured properly.
In almost all cases, the queries in 2000 ran the same or faster than in 7.0.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply