July 19, 2011 at 6:42 am
HI,
I am struck in the following query that is being accessed using views and joining in further Database processing and want to know how to improve its performance for 2 mn records in this table.
Tables with prefix dim are dimension tables.
SELECT [1].areabuilding,
[10].yearvalue,
[2].bldgscheme as bldgscheme_dvt,[2].bldgclass as bldgclass_dvt,[2].bldgclasstier1 as bldgclass_dvttier1,[2].bldgdesc as bldgdescdvt,
[3].bldgscheme,[3].bldgclass,[3].bldgclasstier1,[3].bldgdesc,
[4].cflag,[4].cflagcode,[4].bldgclassflag,[4].numstoriesflag,[4].yearvalueflag,[4].occtypeflag
,[4].areabuildingflag,[4].bldgclasstier1flag,[4].occgrpflag,[4].isEligibleAcrossSource,[4].isEligibleInSource,
[5].flagname as flagFilter,
[6].flagname as insflag,
[7].flagname as Singleoccupancyflag,
[8].numstories,
[9].occscheme2,[9].occtype2,[9].occdesc,[9].occintmd,[9].occgrp,[9].occscheme1,[9].occtype1,[9].occgroup,
A.factid,A.basefactid,A.linkid,A.grpaddressid,A.inputid
FROM migration_db.DBO.fact_process7_10 a
INNER JOIN migration_db.dbo.dimareabuilding [1]
ON isnull(a.areabuildingid,1)=[1].areabuildingID
INNER JOIN
migration_db.dbo.dimbldgclass [2]
ON isnull(a.bldgclassdvtid,1)=[2].bldgclassID
INNER JOIN migration_db.dbo.dimbldgclass [3]
ON isnull(a.bldgclassid,1)=[3].bldgclassID
INNER JOIN migration_db.dbo.dimcflag [4]
ON isnull(a.cflagid,1)=[4].cflagID
INNER JOIN migration_db.dbo.dimflag [5]
ON isnull(a.flagid,1)=[5].flagID
INNER JOIN migration_db.dbo.dimflag [6]
ON isnull(a.Insflagid,1)=[6].flagID
INNER JOIN migration_db.dbo.dimflag [7]
ON isnull(a.Singleoccupancyid,1)=[7].flagID
INNER JOIN migration_db.dbo.dimnumstories [8]
ON isnull(a.numstoriesid,1)=[8].numstoriesID
INNER JOIN migration_db.dbo.dimoccupancy [9]
ON isnull(a.occupancyid,1)=[9].occupancyID
INNER JOIN migration_db.dbo.dimyear [10]
ON isnull(a.yearid,1)=[10].yearID
The following are null value counts :
Insflagid --- 1532677
Singleoccupancyid --732556
cflagid --1223643
flagid --1933134
Please suggest. I am attaching Execution Plan , Statistics Profile and Table creation scripts with the post.
Kindly let me know if there are any other pameters that I need to provide.
Regards
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 19, 2011 at 7:01 am
The only possible suggestion I can provide is to either use left join or flush the isnull. But I'm also certain that this would change the results.
Moreover the optimizer timesout trying to optimize this. You could try breaking this into smaller steps but I'm not sure how big of a job it would be to refarctor to whole app after that.
July 19, 2011 at 7:56 am
does this mean there is no other option to enhance this query ?
can you elaborate on what does flushing of the isnull mean?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 19, 2011 at 8:00 am
Get rid of it. You're preventing index usage by using the function there, most likely reducing the join performance as well.
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 19, 2011 at 2:25 pm
What is migration_db? Is this a one-time migration? You have a design or a data issue or both if your regularly matching a nullable column to another and always treating NULL as 1. Can you run an update statement to set migration_db.dbo.dimareabuilding.areabuildingid to 1 where it is NULL before running this query? And do that same thing for all the other columns?
Another option, if migration_db is a permanent DB is to add a computed column to each of the tables with an expression...something along the lines of:
USE migration_db
GO
ALTER TABLE dbo.dimareabuilding ADD areabuildingid_not_null AS (ISNULL(areabuildingid,1)) ;
GO
and then add a proper nonclustered index to areabuildingid_not_null to support your query.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 10:43 pm
Hi SS
Thanks for the suggestion.migration_db is a staging database and in the existing framework the script with isnull has been wrapped in a view, and further processed as a join in other queries which takes 2 hrs for every execution for 10k results.
Hi SSCrazy,
Thanks a lot for the work around it really would solve the perfomance issue.I wanted to know if there is anything that can be done on index front.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 20, 2011 at 9:19 am
I'll assume you were talking to me with the SSCrazy response since I am the only one on this thread with that designation. My nick is opc.three BTW, SSCrazy is just my level on the site 🙂
SQL_By_Chance (7/19/2011)
Hi SSCrazy,Thanks a lot for the work around it really would solve the perfomance issue.I wanted to know if there is anything that can be done on index front.
You're welcome. There are some restrictions, but computed columns can be used in an index as if they were a regular column, even when they are not persisted. If you are simply using ISNULL in the manner I have shown it above you should be fine. Here is the whole story:
http://technet.microsoft.com/en-us/library/ms189292(SQL.90).aspx
EDIT: swap 2008 link for 2005 link
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 21, 2011 at 1:01 am
Thanks a lot opc.three for your help and the link.
Cheers,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 21, 2011 at 9:19 am
You simply must make improvements to your schema/data to allow you to remove the ISNULL(A, B) = something. Not only is it a CPU burn, it also voids index seeks as well as prevents the optimizer from getting good estimates about rowcounts and thus can lead to horribly inefficient query plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2011 at 9:22 am
TheSQLGuru (7/21/2011)
You simply must make improvements to your schema/data to allow you to remove the ISNULL(A, B) = something. Not only is it a CPU burn, it also voids index seeks as well as prevents the optimizer from getting good estimates about rowcounts and thus can lead to horribly inefficient query plans.
So in essence, dump the ** ****** and start from scratch. 😉
July 21, 2011 at 9:27 am
Ninja's_RGR'us (7/21/2011)
TheSQLGuru (7/21/2011)
You simply must make improvements to your schema/data to allow you to remove the ISNULL(A, B) = something. Not only is it a CPU burn, it also voids index seeks as well as prevents the optimizer from getting good estimates about rowcounts and thus can lead to horribly inefficient query plans.So in essence, dump the ** ****** and start from scratch. 😉
If the state of affairs permitted such a bold and radical action that would be my first choice as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 21, 2011 at 9:34 am
I thought of another thing while table creation
Ex.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 21, 2011 at 9:38 am
Hi All,
Thanks a lot for your thought. As, I can't change the schema. I think the most suitable suggestion that worked for me is the Update and then indexing the Table.
I was able to reduce execution time to 42 min in place of 4-5 hours, as was done before.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
July 21, 2011 at 9:40 am
😛
Yeah, if you can start storing 1 instead of NULL, so that when you want to use the column you don't have to convert NULL to 1 on the fly, then yeah, do that!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 21, 2011 at 3:38 pm
SQL_By_Chance (7/21/2011)
Hi All,Thanks a lot for your thought. As, I can't change the schema. I think the most suitable suggestion that worked for me is the Update and then indexing the Table.
I was able to reduce execution time to 42 min in place of 4-5 hours, as was done before.
This SQL makes my head hurt, but I'll take a stab at it. It's really just the exact same problem repeated a dozen times, so I'm going to simpify before explaining my suggested change. It appears that a NULL value in a.areabuildingid is really supposed to mean an actual value of 1, so as suggested by others above, the column(s) should default to 1 and perhaps be declared as NOT NULL as well.
SELECT [1].areabuilding,
...
FROM migration_db.DBO.fact_process7_10 a
...
INNER JOIN migration_db.dbo.dimareabuilding [1]
ON isnull(a.areabuildingid,1) = [1].areabuildingID
...
Assuming that changing the data is not an viable option, then I suggest you try modifying the SQL like below, doing a left outer join(s), and then put the ISNULL transform around the column(s) in the SELECT clause.
SELECT isnull([1].areabuilding,'<some default value>') areabuilding,
...
FROM migration_db.DBO.fact_process7_10 a
...
LEFT JOIN migration_db.dbo.dimareabuilding [1]
ON [1].areabuildingID = a.areabuildingid,
...
Also, if it were me, I'd supply better names for the table aliases than what the original SQL coder used. Those sequential numbers are not helpful when trying to read this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply