Solved

SQL Server 2005 - slow view performance

Posted on 2006-11-27
16
1,941 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now