July 23, 2009 at 1:11 am
Hi All,
The below query takes only 0 seconds in mssql2000 but it takes 15 seconds in mssql 2005 which we restored the same database in mssql 2005.
Query:
SELECT COUNT(*) FROM ACCOUNT_SEGMENT
WHERE
(ACCT_NBR = 'HQ254047434' AND SEG_ID IN (-1, 2, 3, 4))
OR
(EXISTS(
SELECT A.ACCT_NBR FROM ACCOUNT A
WHERE
A.CREATED_BY = 'sysadmin' AND A.ACCT_NBR = 'HQ254047434'
AND
NOT EXISTS (SELECT V.ACCT_NBR FROM ACCOUNT_SEGMENT V WHERE V.ACCT_NBR = 'HQ254047434')
))
OR
(NOT EXISTS (SELECT seg_id from settings3_segment where setting_id = 100474))
In the above scenario
ACCOUNT_SEGMENT have an index on ACCT_NBR & SEG_ID
ACCOUNT have an primary key on ACCT_NBR.
Any help will be greatly helpful to me.
Regards,
Sunil Kumar
July 23, 2009 at 2:02 am
Hi,
Please post table script and sample data and query execution plans so that we can look into this further.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 23, 2009 at 2:20 am
Have you updated statistics since upgrading to SQL 2005? If not, do so and test the query out again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2009 at 2:37 am
In sql2005 we can right click and save the execution plan but how we can do the same in sql2000.
Please can u suggest me.
Regards,
Sunil
July 23, 2009 at 3:06 am
Even after gathering the statistics the query is taking the same time.Can u please help in tuning the query i.e. mainly the second OR part which is taking time.
Query used to gather the statistics is
exec sp_updatestats
Query to be tuned is
EXISTS(
SELECT A.ACCT_NBR FROM ACCOUNT A
WHERE
A.CREATED_BY = 'sysadmin' AND A.ACCT_NBR = 'HQ254047434'
AND
NOT EXISTS (SELECT V.ACCT_NBR FROM ACCOUNT_SEGMENT V WHERE V.ACCT_NBR = 'HQ254047434')
Can we rewrite the above query to simple way which is in complex to understand.
Regard,
Sunil Kumar K
July 23, 2009 at 4:39 am
try breaking down the query into 3 separate queries and then summing the three counts.
OR statements should never be used unless completely necessary.
July 23, 2009 at 5:06 am
Don't use sp_updatestats. That only updates stats that the engine thinks are outdated. Use UPDATE STATISTICS to be sure that the stats do actually get updated.
As for exec plan on SQL 2000, read this: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply