Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2004
  • Last Modified:

SQL Server 2005 - slow view performance

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
ravl
Asked:
ravl
  • 7
  • 5
  • 3
  • +1
1 Solution
 
HuyBDCommented:
index all field in join codition
YearID
FinancialYearID
AppID
0
 
ravlAuthor Commented:
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
 
NightmanCTOCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
ravlAuthor Commented:
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
 
imran_fastCommented:
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
 
NightmanCTOCommented:
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
 
ravlAuthor Commented:
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
 
imran_fastCommented:
this is a view so dont do it.

Yes you need to update statiscs when you restore backup from 2000 to 2005
0
 
NightmanCTOCommented:
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
 
ravlAuthor Commented:
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
 
imran_fastCommented:
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
 
ravlAuthor Commented:
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
 
imran_fastCommented:
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
 
ravlAuthor Commented:
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
 
imran_fastCommented:
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
 
ravlAuthor Commented:
OK thanks Imran.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now