Solved

SQL Server 2005 - slow view performance

Posted on 2006-11-27
16
1,965 Views
Last Modified: 2010-08-05
There seems to be a plethora of problems migrating from SLQ 2000 to 2005. However, I would like to pose this problem to see if anyone has an answer.

I have a view which runs fine in SQL 2000 and takes less than 1 sec to complete. But in 2005 it takes 3 secs. Why is this so? Here is the view:

SELECT       ca.AppID,fy.ProjectID,
      ApplicationNumber,
      PaymentMethod,
      Amount as Total,BankedAmount AS TotalBanked
FROM dbo.ClientApplications ca
INNER JOIN dbo.tblFinancialYear fy ON ca.YearID = fy.FinancialYearID
INNER JOIN dbo.ApplicationsPayments ap ON ap.AppID = ca.AppID
WHERE PaymentPurpose = 'd' and PaymentMethod = 'Cheque'
      and PaymentID NOT IN
      (SELECT PaymentID FROM dbo.vw_SheetApplications WHERE SheetLetter='A')
      and Amount<>0

Now if you remove the NOT IN clause the result is instantaneous. And if you run the NOT IN Select clause by itself the result is instantaneous. But run the whole thing and it takes 3 secs.

The NOT IN bit returns 5000 rcds. The main bit returns about 2000 rcds.

Why does this view behave differently in 2005?
0
Comment
Question by:ravl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 18025480
index all field in join codition
YearID
FinancialYearID
AppID
0
 
LVL 1

Author Comment

by:ravl
ID: 18025926
Thank you HuyBD. I will try that tomorow.

However, my question is also getting at the issue - am I seriously expected to have to review all my views, and presumably stored procs., to somehow make them 2005 friendly!?

Cheers
Rob
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18025931
Add an ORDER BY statement to the main query. I have seen many instances where SQL 2000 was more forgiving in terms of the ordering, but SQL 2005 was not.

Add indexes as HuyBC suggested. Also add to the vw_SheetApplciations (this may not be a proper join, but SQL has to do a hash match internally anyway).

Consider doing a join instead of a 'NOT IN' statement
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 1

Author Comment

by:ravl
ID: 18026150
Thanks. I will try these things. I did try the order by earlier today as I read somewhere that this can be an issue, but it made no difference and in fact made it run even longer.

But I am still looking for some reason for the 2000/2005 difference in performance. I don't really want to have to rewrite all my views.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18026362
Hi ravl,
How did you migrate from sql 2000 to sql 2005
Did you reindex and update statistics after you migrate to sql 2005

try
dbcc dbreindex(ClientApplications,'')
dbcc dbreindex(tblFinancialYear ,'')
dbcc dbreindex(ApplicationsPayments ,'')
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18026484
AND
DBCC DBREINDEX(vw_SheetApplications,'',0)
You can use the following script to reindex all your tables on the database (just be aware that it is best done when there is no-one online!)

--Script to automatically reindex all tables in a database

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',0)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
0
 
LVL 1

Author Comment

by:ravl
ID: 18027027
Thank you people. There are a few things for me to try there.

Migration was done simply by restoring from a 2000 backup. Absolutely no other work was done after restoring the database to 2005. I'm not the DBA so don't know how the server was actually set up. But I believe this database is not running in 2000 compatability mode.

Is there a recommended migration process, employing some of the re-indexing etc. you've mentioned?

By the way DBCC DBREINDEX(vw_SheetApplications,'',0)
returns
Msg 5239, Level 16, State 1, Line 1
Unable to process object ID 973298577 (object 'vw_SheetApplications') because this DBCC command does not support objects of this type.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18027152
this is a view so dont do it.

Yes you need to update statiscs when you restore backup from 2000 to 2005
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18027650
Reindexing on SQL2005 will build a complete 100% accurate set of statistics. This should also be a semi-regular maintenance task (in SQL 2005, there are also options to rebuild certain indexes online with no impact on performance, and there is also the option to defrag indexes)

Also, make sure that your statistics are updated frequently.
0
 
LVL 1

Author Comment

by:ravl
ID: 18032043
OK people. I've tried all the suggestions so far - except I can't see how you can replace NOT IN with a join. everything is re-indexed and statistics updated. No change. Still takes 3 secs.

I tried removing the NOT and the query runs instantantaneously. So, that seems to be the issue for 2005. What can I do about it?
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 18035256
Change it to

SELECT      
      ca.AppID,
      fy.ProjectID,
      ApplicationNumber,
      PaymentMethod,
      Amount as Total,
      BankedAmount AS TotalBanked
FROM dbo.ClientApplications ca
      INNER JOIN dbo.tblFinancialYear fy
      ON ca.YearID = fy.FinancialYearID
      INNER JOIN dbo.ApplicationsPayments ap
      ON ap.AppID = ca.AppID

WHERE ap.PaymentPurpose = 'd' and ap.PaymentMethod = 'Cheque'      and Amount<>0
      and NOT exists
     (SELECT 1 FROM dbo.vw_SheetApplications vs WHERE vs.SheetLetter='A' and ap.PaymentID = vs.PaymentID)
0
 
LVL 1

Author Comment

by:ravl
ID: 18035867
Brilliant. Thank you. You hit the nail on the head Imran.

All you have to do now is explain three things to me.

1) Why does your solution work faster?
2) How does this logic work. It has never occurred to me to reference a column in the main view from within a sub-select. I just sort of assumed it was not logically possible.
3) What is it about 2005 that causes my version to run slowly when it runs fine in 2000. What are the 'things to look out for' when migrating from 2000 to 2005.

Cheers
Rob

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18036356
1) use of   NOT exists  it returns a flag only.
2) should look at ap.PaymentID = vs.PaymentID in the sub-select
3) Not exists is always faster then not in bot in 2000 and 2005
0
 
LVL 1

Author Comment

by:ravl
ID: 18040952
Thanks again Imran and I'll happily give you the points for making the view perform faster.

However, no one has yet answered the real thrust of my question relating to degradation in performance in 2005. What is it about 2005 that causes my version to run slowly when it runs fine in 2000. What are the 'things to look out for' when migrating from 2000 to 2005.

I am concerned that I am going to encounter more such issues and that moving to 2005 at this time is going to be very costly in terms of re-work to fix these types of issues. Does anyone have any further comment about this?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18059147
Hi,
What i found out that the adhoc quries which is ok in sql 2000 runs slow in sql 2005. You have to make sure that all your quries are according to best practice.
0
 
LVL 1

Author Comment

by:ravl
ID: 18059323
OK thanks Imran.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question