Solved

SQL Server 2005 - slow view performance

Posted on 2006-11-27
16
1,950 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 25
Querying data from 3 SQL tables 2 32
How to search for strings inside db views 4 28
performance query 4 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

803 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