May 1, 2009 at 8:40 am
My SQL skills are VERY VERY minimal. This query is timing out and I have no idea as to how to speed it up. I'm sure a hugh part of the problem is related to the fact that my Orgs view has 1,623,122 records (and counting).
Here is the view. It combines 3 tables which contain organizational information.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT ORG_REG_NR, ORG_DIS_NR, CONVERT(nvarchar(30), EIN) AS EIN, ORG_NM, NULL AS INCARE_NM, NULL AS ORG_ADDR, CITY, STATE, NULL
AS ZIP_CD, NULL AS GRP_EXEMPT_NR, NULL AS SUBSEC_CD, NULL AS AFFIL_CD, NULL AS CLASS_CD, NULL AS RUL_DT, NULL AS DED_CD, NULL
AS FOUND_CD, NULL AS ACT_CD, NULL AS ORG_CD, NULL AS EXEMP_ORG_STS_CD, NULL AS ADV_RUL_EXP_DT, NULL AS TAX_PER, NULL
AS ASSET_CD, NULL AS INC_CD, NULL AS FIL_REQ_CD, NULL AS ACCT_PER, NULL AS ASSET_AMT, NULL AS INC_AMT, NULL AS NEG_INC, NULL
AS FORM990_REV_AMT, NULL AS NEG_REV, NULL AS NTEE_CD, NULL AS SRT_NM, 'USA' AS CNY_NM
FROM dbo.Organizations
UNION ALL
SELECT 'XX' AS ORG_REG_NR, 'XX' AS ORG_DIS_NR, CONVERT(nvarchar(30), EIN) AS EIN, ORG_NM, INCARE_NM, ORG_ADDR, CITY, STATE, ZIP_CD,
GRP_EXEMPT_NR, SUBSEC_CD, AFFIL_CD, CLASS_CD, RUL_DT, DED_CD, FOUND_CD, ACT_CD, ORG_CD, EXEMP_ORG_STS_CD,
ADV_RUL_EXP_DT, TAX_PER, ASSET_CD, INC_CD, FIL_REQ_CD, ACCT_PER, ASSET_AMT, INC_AMT, NEG_INC, FORM990_REV_AMT, NEG_REV,
NTEE_CD, SRT_NM, CNY_NM
FROM dbo.T501C3_IRS
UNION ALL
SELECT 'XX' AS ORG_REG_NR, 'XX' AS ORG_DIS_NR, CONVERT(nvarchar(30), EIN) AS Expr27, ORG_NM, NULL AS Expr1, NULL AS Expr2, CITY, STATE, NULL
AS Expr3, NULL AS Expr4, NULL AS Expr5, NULL AS Expr6, NULL AS Expr7, NULL AS Expr8, NULL AS Expr9, NULL AS Expr10, NULL AS Expr11, NULL
AS Expr12, NULL AS Expr13, NULL AS Expr14, NULL AS Expr15, NULL AS Expr16, NULL AS Expr17, NULL AS Expr18, NULL AS Expr19, NULL
AS Expr20, NULL AS Expr21, NULL AS Expr22, NULL AS Expr23, NULL AS Expr24, NULL AS Expr25, NULL AS Expr26, CNY_NM
FROM dbo.Canada_Registered_Charities
UNION ALL
SELECT 'XX' AS ORG_REG_NR, 'XX' AS ORG_DIS_NR, CONVERT(nvarchar(30), ORG_ID) AS EIN, ORG_NM, NULL AS INCARE_NM, NULL AS ORG_ADDR, CITY,
STATE, NULL AS ZIP_CD, NULL AS GRP_EXEMPT_NR, NULL AS SUBSEC_CD, NULL AS AFFIL_CD, NULL AS CLASS_CD, NULL AS RUL_DT, NULL
AS DED_CD, NULL AS FOUND_CD, NULL AS ACT_CD, NULL AS ORG_CD, NULL AS EXEMP_ORG_STS_CD, NULL AS ADV_RUL_EXP_DT, NULL
AS TAX_PER, NULL AS ASSET_CD, NULL AS INC_CD, NULL AS FIL_REQ_CD, NULL AS ACCT_PER, NULL AS ASSET_AMT, NULL AS INC_AMT, NULL
AS NEG_INC, NULL AS FORM990_REV_AMT, NULL AS NEG_REV, NULL AS NTEE_CD, NULL AS SRT_NM, NULL AS Expr2
FROM dbo.OTHER_ORGANIZATIONS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the Query.
SELECT ORG.ORG_NM,
(CASE ORG_ADDR
WHEN NULL THEN ' '
ELSE LTRIM(RTRIM(ORG_ADDR))+', '
END) AS ORG_ADDR, CITY,STATE,
(CASE ZIP_CD
WHEN NULL THEN ' '
ELSE ', '+RTRIM(LTRIM(ZIP_CD))
END) AS ZIP_CD,DIS.REG_NR, DIS.DIS_NR,DIS.REG_NA,DIS.DIS_NA,
ISNULL(SUM(HRS.EMP_HRS_QY),0) AS EMP_HRS_QY,
ISNULL(SUM(HRS.RET_EMP_HRS_QY),0) AS RET_EMP_HRS_QY,
ISNULL(SUM(HRS.FAM_FRD_HRS_QY),0) AS FAM_FRD_HRS_QY,
ISNULL(SUM(HRS.EMP_HRS_QY),0)+ISNULL(SUM(RET_EMP_HRS_QY),0)+ ISNULL(SUM(FAM_FRD_HRS_QY),0) AS TOT_HRS_QY,
COUNT(distinct HRS.SYS_EMP_ID_NR) as EMP_COUNT
FROM [dbo].[VolunteerHours] HRS
INNER JOIN [dbo].[org_ALL] ORG
ON (HRS.EIN_NR = ORG.EIN or CAST(HRS.OTHER_ORG_ID as nvarchar(30))=ORG.EIN)
JOIN [dbo].[VDIFF] DIS
ON (HRS.REG_NR = DIS.REG_NR
AND HRS.DIS_NR = DIS.DIS_NR)
WHERE @pFromDt <= CAST(FLOOR(CAST(HRS.ACT_START_DT AS float)) AS datetime)
AND @pToDt >= CAST(FLOOR(CAST( HRS.ACT_END_DT AS float)) AS datetime)
AND HRS.APPROVAL_STS = 'A'
GROUP BY DIS.REG_NR,DIS.REG_NA, DIS.DIS_NR, DIS.DIS_NA, ORG.ORG_NM,ORG_ADDR,CITY,STATE, ZIP_CD
ORDER BY ORG_NM
Thanks in advance!
May 1, 2009 at 8:51 am
First, the execution plan would be key to helping you solve this. Please save it, zip it, and upload the zipped file.
Second, which one of the three objects in the final query is the view?
Third, can you provide table creation scripts for the other two objects?
I think you're going to be better off building a temp table and then selecting from that, instead of joining to a view with a bunch of Union Alls in it, but I can't suggest anything more concrete than that without the above data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2009 at 9:01 am
I attached the execution Plan. The View is 'ORG_ALL'.
Here is the dbo.volunteerhours
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VolunteerHours](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SYS_EMP_ID_NR] [nvarchar](11) NOT NULL,
[REG_NR] [nchar](2) NULL,
[DIS_NR] [nchar](2) NULL,
[OP_TYP_CD] [nchar](3) NULL,
[CTR_NR] [nchar](5) NULL,
[SRT_TYP_CD] [char](1) NULL,
[EIN_NR] [nvarchar](30) NULL,
[OTHER_ORG_ID] [int] NULL,
[PROGRAM_TYP] [smallint] NOT NULL,
[ACT_TYP] [smallint] NOT NULL,
[EMP_HRS_QY] [decimal](10, 2) NULL,
[FAM_FRD_HRS_QY] [decimal](10, 2) NULL,
[RET_EMP_HRS_QY] [decimal](10, 2) NULL,
[FAM_MEMBER_NR] [int] NOT NULL CONSTRAINT [DF_VolunteerHours_FAM_MEMBER_NR] DEFAULT ((0)),
[ACT_START_DT] [datetime] NOT NULL,
[ACT_END_DT] [datetime] NOT NULL,
[APPROVAL_STS] [char](1) NOT NULL,
[COMMENT_TE] [ntext] NULL,
[APPROVAL_DT] [datetime] NULL,
[APPROVED_BY] [int] NULL,
[COORD_COMMENT_TE] [ntext] NULL,
[EventID] [int] NULL,
[STATE_CD] [nchar](2) NULL,
[FT_PT_CD] [nchar](1) NULL,
[MGMT_IR] [nchar](1) NULL,
[EMP_TYP] [nvarchar](50) NULL,
CONSTRAINT [PK_VOL_HRS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is dbo.VDIFF:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE [N2N]
GO
/****** Object: Table [dbo].[VDIFF] Script Date: 05/01/2009 11:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VDIFF](
[REG_NR] [nvarchar](2) NOT NULL,
[DIS_NR] [nvarchar](2) NOT NULL,
[DIS_NA] [nvarchar](30) NULL,
[REG_NA] [nvarchar](30) NULL,
[DIS_ABR_NA] [nvarchar](8) NULL,
[REG_ABR_NA] [nvarchar](8) NULL,
[ACT_IR] [nchar](1) NULL,
[Country_IR] [nvarchar](3) NULL,
CONSTRAINT [PK_VDIFF] PRIMARY KEY CLUSTERED
(
[REG_NR] ASC,
[DIS_NR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks!
May 1, 2009 at 9:53 am
The first thing to do is try this:
SELECT
CONVERT(nvarchar(30), EIN) AS EIN,
ORG_NM,
CAST('' AS VARCHAR(50)) AS ORG_ADDR,
CITY,
STATE,
CAST('' AS VARCHAR(20)) AS ZIP_CD
INTO
#Orgs
FROM
dbo.Organizations
UNION ALL
SELECT
CONVERT(nvarchar(30), EIN) AS EIN,
ORG_NM,
ORG_ADDR,
CITY,
STATE,
ZIP_CD
FROM
dbo.T501C3_IRS
UNION ALL
SELECT
CONVERT(nvarchar(30), EIN) AS EIN,
ORG_NM,
CAST('' AS VARCHAR(50)) AS ORG_ADDR,
CITY,
STATE,
CAST('' AS VARCHAR(20)) AS ZIP_CD
FROM
dbo.Canada_Registered_Charities
UNION ALL
SELECT
CONVERT(nvarchar(30), ORG_ID) AS EIN,
ORG_NM,
CAST('' AS VARCHAR(50)) AS ORG_ADDR,
CITY,
STATE,
CAST('' AS VARCHAR(20)) AS ZIP_CD
FROM
dbo.OTHER_ORGANIZATIONS
create clustered index IDX_Orgs_Main on #Orgs (EIN)
Use that temp table in the final query, instead of the view.
There's more to try, but try that first, let me know what it does, if anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2009 at 9:56 am
Hi,
Suggestions/thoughts:
1)
One of the biggest problems seems to be that VolunteerHours.EIN_NR is a varchar column. Does it really contain any characters?
Reason for asking: Since the view casts all underlying columns for ViewColumn [Ein] to varchar (tables Organizations, T501C3_IRS, Canada_Registered_Charities and OTHER_ORGANIZATIONS), I assume that those columns originally are integer values.
If HRS.EIN_NR contains numbers only, convert this one to integer and get rid of all the varchar converts in the view. Finally, you're still comparing numbers...
This seems to be the root cause for the nested loop, which is 50% of your query cost.
Casting an integer value to a varchar within a join condition is not really a good approach...
2)
Like GSquared already mentioned, it's better to replace the view with a temp table - the SQL Server does it anyway (Table Spool, another 39%). Make sure you index that table before the query.
I'm sure the guru's around will find some more to tune but that's what I'd start with...
May 1, 2009 at 10:08 am
Next thing to try is changing this:
WHERE @pFromDt = CAST(FLOOR(CAST( HRS.ACT_END_DT AS float)) AS datetime)
to:
WHERE HRS.ACT_START_DT >= @pFromDt
AND HRS.ACT_END_DT <= @pToDt + 1
It might also help if you have an index on dbo.VolunteerHours that's something like:
CREATE INDEX IDX_VolunteerHours_DateRange ON dbo.VolunteerHours (APPROVAL_STS, ACT_START_DT, ACT_END_DT, EIN_NR, OTHER_ORG_ID, REG_NR, DIS_NR)
INCLUDE (EMP_HRS_QY, RET_EMP_HRS_QY, FAM_FRD_HRS_QY, SYS_EMP_ID_NR) ;
If you already have an index like that, it's not being used by the query, probably because of the date math in the current query. If you don't, adding the index and fixing that part of the Where clause, will almost certainly speed things up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2009 at 10:13 am
Imu92,
Of the 3 joins in the view, 2 of the EINs actually contain chracters and are of the datatype nvarchar.
Thanks for your suggestions. I will try them along with GSquared's suggestions. I'll let you all know what happens.
My DBAs are taking their sweet time helping me out and I really need my users to produce these reports. Thanks again!
May 1, 2009 at 10:24 am
Thanks so much GSquared! Using the temp table the query produced 5000 records in 24 seconds. I'm going to try the other suggestions as well.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply