April 1, 2008 at 12:50 am
The below is the query:
select acc.*
FROM
JV_ACCOUNT_HISTORY ACC, JV_SACCODE_CSS SAC
WHERE
LTRIM(RTRIM(ACC.ACC_NO))= LTRIM(RTRIM(SAC.ACCOUNT)) AND
ACC.SOURCE_SYS_ID = 'CSS' AND
LTRIM(RTRIM(ACC.CUSTOMER_ID)) = LTRIM(RTRIM(SAC.SAC_CD)) AND
ISNULL(ACC.ENTITY_ID,'') = '' AND
ISNULL(ACC.ENTITY_NAME,'') = '' and
ISNULL(ACC.ENTITY_ID,'')+ISNULL(ACC.ENTITY_NAME,'')<>ISNULL(SAC.ENTITY_ID,'')+ISNULL(SAC.ENTITY_NM,'')
when i executed in 2000 it is taking 33 secs, but in 2005 it is 4 mins....All the table structure,data and index etc., are identical in 2005 and 2000.
Did anybody come accross this issue??? Any solution is greately appreciated!!!
April 1, 2008 at 1:16 am
Did you migrate a existing database to SQL Server 2005.
In this case you should ensure to update the database statistics.
You can do this with the stored Procedure [p][font="Courier New"]sp_updatestats[/font]
[/p]
April 1, 2008 at 6:45 am
I have updated all the stats all ..., but eventually it is showing the same performance issue...is there anything i need to update??????
April 1, 2008 at 8:00 am
You can look at the execution plan. Here you can see if the SQL Server processes the Statement as same as sql 2000 does. With SQL Server 2000 i had sometimes problems with missing statistics.
On the other hand you should think about improve the sql Statement. There is an interesting article
"The Cost of Function Use In A Where Clause[/url]".
You should also have a look at the comments in the Forum for this article.
April 1, 2008 at 8:01 am
Start by having a look at the query execution plan to see where all the processing is taking place.
You may have to rebuild or recreate your indexes.
April 1, 2008 at 8:30 am
The fact that you are joining your two tables by fields wrapped in functions:
LTRIM(RTRIM(ACC.ACC_NO))= LTRIM(RTRIM(SAC.ACCOUNT))
forces a table scan when your query runs.
The engine must evaluate the function on every row before it can determine if they match.
In SQL 2000, your two data sets would always be returned in the order of the clustered index - they never guaranteed it, but that is what would happen. In SQL 2005, the parallel processing engine returns records in a random order that can be different every time a query runs. Because of this, the unordered hash match in SQL 2005 is actually going to take longer because the data sets need to be re-ordered (probably in temp tables in this case).
You may get the SQL 2000 performance by setting the MAX Degrees of Parallelism to 1 as an option on this query. It is likely to then act much the same way it would have in SQL 2000.
In the end, you need to fix your tables so you do not have to trim fields before you join them together - this is just a design issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply