June 14, 2002 at 2:36 pm
I recently upgraded our development database to sql2000
Now Crystal reports takes a long time to load.
I captured the script that crystal was running
SELECT a.name, user_name (a.uid), a.type FROM sysusers c, sysobjects a
WHERE c.uid=user_id() AND a.type IN ('~','U','V','P')
AND (suser_id()=1 OR a.uid=user_id() OR
((SELECT max(sign(uid)*(2-sign(uid-user_id()))*2+protecttype)
FROM sysprotects b WHERE b.id=*a.id AND (b.uid=0 OR b.uid=user_id()
OR b.uid=*c.gid) AND (action=193 OR action=224))&1)=1)
This script under sa takes less than second to run but under any other user it will take more than 3 minutes.
I look at query-Plan and only difference is number of rows retrieved from sysprotects table under sa it is retrieving 3k rows but under another user it will take 10 Million rows.
I tried to update statistics on sysprotects but failed because it is virtual table.
How can I update statistics on this or am I completely on wrong track and should re-evaluate the query again.
June 14, 2002 at 7:36 pm
Wrong track. Consider why sa only needs 3k rows when a user has far more. What specifically happens and what are you expecting from this query. Maybe you need to adjust the query and rewrite for users other than sa.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 8:14 pm
Sorry to complain but it gives me a headache! On my workstation with nothing much in SQL at the moment it shows a table scan across 17k rows, but returns 900 or so. If I have time I'll try to work on this weekend. Definitely seems that tuning the query is the place to start.
Andy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply