Query suddenly runs slowly
-
I am
having a performance problem with a step within a stored procedure that has been running reliably for over two years but has recently slowed to a crawl. This bit of code ran in a few seconds up until about two weeks ago. It will now run in about ten minutes, with some occasions where it runs for over an hour, at which point we kill the process and start over, and then it runs in the ten minute zone. I have checked the obvious stuff, like covering indexes. The databases are used for reporting, not OLTP, and there is not much going on elsewhere on the DB server. The table TBL_MM_MAR is in the Comp database, while the Invoice and Transac tables are in the SMFIDXDM database, and the stored proc is run while connected to Comp. Here is the offending code:
INSERT
into TBL_MM_MAR
SELECT
4 AS GROUP_ZZ, GL.POST_PD,
mp
.Medical_Group,
DN102
.NAME AS DIVISION,
mp
.DN3Key,
mp
.PROV_GL_NUM,
mp
.ProviderName,
mp
.Care_Type,
mp
.Comp_Type,
mp
.Rpt_Lvl, 'MC' AS TYPE,
SUM(GL.GL_ENTRY) AS Value,
' ' AS Rep_Cat_1,
' ' AS Rep_Cat_2, '3864' AS Record_type, 'Rev' AS Analysis,
'3. CoPay Revenue' AS Reporting_Category,
'II. COMPENSATION REVENUE CREDIT' AS MajorSort,
MinorSort
= Case When mp.Care_Type = 'PCP' Then 'A. Managed Care Pool Contribution' Else 'A. Managed Care Credit' End,
PROGRAM
= NULL
FROM
(SELECT i.MEDICAL_GROUP
, t.POST_PD
, i.PROV
, SUM(t.PAY_AMT) AS GL_ENTRY
FROM SMFIDXDM.dbo.INVOICE AS i INNER JOIN
SMFIDXDM
.dbo.TRANSAC AS t ON i.GROUP_ZZ = t.GROUP_ZZ
AND i.PATIENT = t.PATIENT
AND i.INVOICE = t.INVOICE
WHERE -- ((t.PAY_CODE_NUM IN ('600', '17')) AND (t.POST_PD >= 10701) AND (i.MEDICAL_GROUP IN (1, 27))) --removed 5/2
((t.PAY_CODE_NUM IN ('600', '17')) AND (t.POST_PD >= 10701) AND (i.MEDICAL_GROUP IN (27)))
or
((t.PAY_CODE_NUM IN ('600', '17')) AND (t.POST_PD >= 10601) AND (i.MEDICAL_GROUP IN (1)) AND (NOT (i.LOC IN (125, 169, 113, 172, 222, 220, 453, 475))) AND (i.DIV IN (22, 7, 10, 16, 18, 9, 12, 17, 65, 35, 6)))
GROUP BY i.MEDICAL_GROUP, i.PROV, t.POST_PD
HAVING (SUM(t.PAY_AMT) <> 0)) AS GL
JOIN TBL_MM_MAR_PROFILE AS mp ON GL.Prov = mp.DN3Key AND GL.Medical_Group = mp.Medical_Group
JOIN SMFIDXDM.dbo.DN102 DN102 ON mp.Div_Key = DN102.Record_Number
WHERE
(GL.POST_PD BETWEEN mp.RPT_EFF_PD AND mp.RPT_TERM_PD) AND
mp
.Medical_Group IN (1, 27) -- only for SMG or SNSMG
GROUP
BY GL.POST_PD,
mp
.Medical_Group,
mp
.DN3Key,
mp
.Rpt_Lvl, DN102.NAME,
mp
.PROV_GL_NUM,
mp
.ProviderName,
mp
.Care_Type,
mp
.Comp_Type
---------------------------------------------------------------------
Here are the tables involved
:
USE
[SMFIDXDM]
GO
CREATE
TABLE [dbo].[INVOICE]( -- 13,780,033 rows
[GROUP_ZZ] [int]
NOT NULL,
[PATIENT] [numeric]
(12, 0) NOT NULL,
[INVOICE] [numeric]
(12, 0) NOT NULL,
[ADM_DT] [datetime]
NULL,
[BILLING_AREA] [int]
NULL,
[CASE_NUM] [varchar]
(15) NULL,
[CASE_PACKAGE] [int]
NULL,
[CASE_TYPE] [int]
NULL,
[CLM_NUM] [numeric]
(10, 0) NULL,
[CLM_RUN_DT] [datetime]
NULL,
[CLM_RUN_NUM] [int]
NULL,
[COMM_LAB] [int]
NULL,
[CORR_INV_NUM] [int]
NULL,
[DISCH_DT] [datetime]
NULL,
[int]
NULL,
[DT_OF_INJ] [datetime]
NULL,
[DX_EIGHT] [int]
NULL,
[DX_FIVE] [int]
NULL,
[DX_FOUR] [int]
NULL,
[DX_NINE] [int]
NULL,
[DX_ONE] [int]
NULL,
[DX_SEVEN] [int]
NULL,
[DX_SIX] [int]
NULL,
[DX_TEN] [int]
NULL,
[DX_THREE] [int]
NULL,
[DX_TWO] [int]
NULL,
[FIRST_SPLIT_INV] [int]
NULL,
[FSC] [int]
NULL,
[HOS] [int]
NULL,
[INV_BAL] [money]
NULL,
[INV_CRE_DT] [datetime]
NULL,
[INV_CRE_PD] [int]
NULL,
[INV_NUM] [int]
NULL,
[LAST_ACT_PCE_1] [int]
NULL,
[LAST_ACT_PCE_2] [int]
NULL,
[LAST_WORKED_DT] [datetime]
NULL,
[LOC] [int]
NULL,
[MCA_CONTRACT_NUM] [numeric]
(16, 0) NULL,
[MCA_CUSTOM_SUPER_GROUP] [int]
NULL,
[MCA_REF_NUM] [varchar]
(15) NULL,
[NEXT_TXN] [int]
NULL,
[ORIG_INV_FROM_SPLIT] [int]
NULL,
[ORIG_INV_NUM] [int]
NULL,
[OTH_INV_NUM] [int]
NULL,
[PRIOR_AUTH_NUM] [varchar]
(40) NULL,
[PROV] [int]
NULL,
[REFERRING_PHYSICIAN] [int]
NULL,
[REFERRING_PHYSICIAN_FTXT] [varchar]
(80) NULL,
[REJ_1] [varchar]
(75) NULL,
[REJ_2] [varchar]
(75) NULL,
[REJ_3] [varchar]
(75) NULL,
[REJ_4] [varchar]
(75) NULL,
[REJ_5] [varchar]
(75) NULL,
[REJ_6] [varchar]
(75) NULL,
[REJ_7] [varchar]
(75) NULL,
[REJ_8] [varchar]
(75) NULL,
[REJ_9] [varchar]
(75) NULL,
[REJ_MESS] [varchar]
(80) NULL,
[REJ_OTH_DT] [datetime]
NULL,
[SECONDARY_PROV] [int]
NULL,
[SECOND_SPLIT_INV] [int]
NULL,
[SER_DT] [datetime]
NULL,
[TOT_CHG] [money]
NULL,
[TYPE_OF_INJ] [varchar]
(13) NULL,
[TYPE_OF_SER] [int]
NULL,
[U_DW_ENR_LOC] [int]
NULL,
[U_DW_PCP] [int]
NULL,
[U_DW_PLAN] [varchar]
(10) NULL,
[INV_SCHED_LOC] [int]
NULL,
[INV_SCHED_PROV] [int]
NULL,
[U_MCA_VNDR] [int]
NULL,
[MEDICAL_GROUP] [int]
NULL,
[ORDER_NUM] [varchar]
(15) NULL,
[PLACE_OF_SERV] [int]
NULL,
[U_PRIOR_AUTH_NUM] [varchar]
(25) NULL,
[PROGRAM] [int]
NULL,
[U_REJ_DE_1] [int]
NULL,
[RESIDENT_PHY] [int]
NULL,
[ORIG_VIS_NUM] [int]
NULL,
[VIS_NUM] [int]
NULL,
[WORK_COMP_CASE_NO] [varchar]
(31) NULL,
[ZGW_FSC_LIST] [varchar]
(125) NULL,
[ZGW_ORIG_FSC] [int]
NULL,
[DATESTAMP] [datetime]
NULL CONSTRAINT [DF__INVOICE_1__DATES__0DEC9111] DEFAULT (getdate()),
CONSTRAINT [pk_INVOICE] PRIMARY KEY NONCLUSTERED
(
[GROUP_ZZ]
ASC,
[PATIENT]
ASC,
[INVOICE]
ASC
)
ON [PRIMARY]
)
ON [PRIMARY]
-------------------------------------------------------------------------------------
USE
[SMFIDXDM]
GO
CREATE
TABLE [dbo].[TRANSAC]( -- 75,271,300
[GROUP_ZZ] [int]
NOT NULL,
[PATIENT] [numeric]
(12, 0) NOT NULL,
[INVOICE] [numeric]
(12, 0) NOT NULL,
[TRANSACTION_ZZ] [numeric]
(12, 0) NOT NULL,
[ADJ_AMT] [money]
NULL,
[ALLOCATION_AMT] [money]
NULL,
[ALLOWED_AMT] [money]
NULL,
[BANK_DEP_DT] [datetime]
NULL,
[BATCH_NUM] [int]
NULL,
[BATCH_TX_NUM] [int]
NULL,
[COMMENT] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CR_AMT] [money]
NULL,
[DEBIT_AMT] [money]
NULL,
[DX_NUM] [varchar]
(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FEE_SCHED] [int]
NULL,
[FRONT_DESK_PAYCODE] [int]
NULL,
[FR_END_ADJ_AMT] [money]
NULL,
[FR_END_ADJ_PAY_CODE] [varchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FSC] [int]
NULL,
[HMO_ALLOWED_AMT_ADJ] [money]
NULL,
[HMO_ALLOW_AMT_ADJ_PAYCODE] [int]
NULL,
[HMO_COV_CAT] [int]
NULL,
[HMO_FUND] [int]
NULL,
[HMO_NON_COV_AMT] [money]
NULL,
[INS_COMP] [int]
NULL,
[INS_COMP_NM] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MCA_AMT] [money]
NULL,
[MCA_APPROVED_AMT] [money]
NULL,
[MCA_COPAY_AMT] [money]
NULL,
[MCA_DAYS] [numeric]
(10, 3) NULL,
[MCA_DED_AMT] [money]
NULL,
[MCA_DISPOSITION_CODE] [int]
NULL,
[MCA_FUND_CODE] [int]
NULL,
[MCA_PENDING_AMT] [money]
NULL,
[MCA_REJECTED_AMT] [money]
NULL,
[MCA_UNITS] [numeric]
(10, 3) NULL,
[MCD_PROFILE_FEE] [money]
NULL,
[MC_PROFILE_FEE] [money]
NULL,
[NATIONAL_DRUG_CODE] [varchar]
(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OVR_FLAG] [int]
NULL,
[PAY_AMT] [money]
NULL,
[PAY_CODE] [int]
NULL,
[PAY_CODE_NUM] [int]
NULL,
[PIECE_12] [varchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MOD] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[POST_DT_1] [datetime]
NULL,
[POST_FIELD] [datetime]
NULL,
[POST_PD] [int]
NULL,
[PREV_FSC] [int]
NULL,
[PROC_ZZ] [int]
NULL,
[PVENDOR] [int]
NULL,
[PX_CODE] [int]
NULL,
[PX_DESC_OVR] [varchar]
(225) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REJ_1] [int]
NULL,
[REJ_2] [int]
NULL,
[REJ_3] [int]
NULL,
[REJ_4] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REJ_DE_1] [int]
NULL,
[RVU] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RVU_EXPENSE_COMP] [numeric]
(10, 3) NULL,
[RVU_MALPRATICE_COMP] [numeric]
(10, 3) NULL,
[RVU_WORK_COMP] [numeric]
(10, 3) NULL,
[SER_DT] [datetime]
NULL,
[STATISTICAL] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STMT_RUN_NUM] [int]
NULL,
[UNITS] [numeric]
(10, 3) NULL,
[UNITS_ADDITIONAL] [numeric]
(10, 3) NULL,
[UNITS_BASE] [numeric]
(10, 3) NULL,
[UNITS_DURATION] [numeric]
(10, 3) NULL,
[UNITS_TIME] [numeric]
(10, 3) NULL,
[UNITS_TOT] [numeric]
(10, 3) NULL,
[U_INS_SRV_CAT] [int]
NULL,
[U_MOD_1] [varchar]
(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[U_MOD_2] [varchar]
(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[U_MOD_3] [varchar]
(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VENDOR_FROM_DICT] [int]
NULL,
[CHARGE_AMOUNT] [money]
NULL,
[ZGW_REJ_1] [int]
NULL,
[ZGW_REJ_2] [int]
NULL,
[ZGW_REJ_3] [int]
NULL,
[ZGW_REJ_4] [int]
NULL,
[ZGW_TES_BATCH_NUM] [numeric]
(12, 0) NULL,
[ZGW_TES_ENC_NUM] [numeric]
(12, 0) NULL,
[ZGW_TES_TXN_CREAT_DT] [datetime]
NULL,
[ZGW_TES_TXN_CREAT_INI] [int]
NULL,
[ZGW_TES_TXN_NUM] [numeric]
(12, 0) NULL,
[DATESTAMP] [datetime]
NULL DEFAULT (getdate()),
CONSTRAINT [pk_TRANSAC] PRIMARY KEY NONCLUSTERED
(
[GROUP_ZZ]
ASC,
[PATIENT]
ASC,
[INVOICE]
ASC,
[TRANSACTION_ZZ]
ASC
)
ON [PRIMARY]
)
ON [PRIMARY]
GO
--------------------------------------------------------------
Any
ideas? I'm fresh out. Thanks.
There is no "i" in team, but idiot has two. -
Could you also provide the full code for the stored procedure? One thing most will jump on is the possibility of parameter sniffing (search this site for more info on that).
-
I might be missing something, but it seems to be that the outer GROUP BY and aggregate function isn't needed. Your subquery already seems to be summarizing things to return just one value per Med. Group and Provider, which is essentially what the outer one if grouping on as well (the other fields look to be only in there because you don't want SQl2K to squawk about them). It looks to me that all of the groups in the outer group by are made up of one element only.
If that's true - then the outer select has got to run quite a bit faster without it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Lynn, thanks, but there are no parameters involved in this proc. The proc runs and joins tables, that's all.
There is no "i" in team, but idiot has two. -
A few possibilities:
1) The amount of data in the tables has grown such that different query plans become 'least costly'. This can result in DRASTICALLY different query plans and performance times. But SQL Server doesn't optimize run times, it optimizes cost alone.
2) The distribution of values used for various filterings of rows (having clause and where clause(s)) have become such that the optimizer decided to use a different query plan which now leads to poor performance.
3) Parameter sniffing (which IS often a problem) isn't at fault here since there are no parameters.
Do the following:
A) Recheck the query and tables to see if you can improve the index strategy.
B) Evaluate data and index fragmentation as well as hard drive file fragmentation.
C) Check for blocking.
D) Check for waits during query execution. I/O would be the most likely waits. Consider using FileGroups to spread the data/index I/O around if necessary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I took off the group by on the join between invoice and transac tables and it still took over 9 minutes on a quiet server. My index strategy I think is OK. The invoice and transac tables are joined on their primary key.
I have a job that runs every ten minutes on that server looking for blocked processes. We have six filegroups on D for data, then use two on E for indexes and one on F for tempdb. Both invoice and transac are on the same filegroup.
Drive fragmentation could be a problem as the server has never been defragged, and the disk defrag tool tells me I should. I am a little nervous about starting it because I have no idea how long it will take to run. But given production schedules, we can't run disk defrag (Windows Server 2003) until after Labor Day. When I defrag the disks, do I need to close off connections to the database and disable SQL Agent jobs? Or can I just stop SQL Server?
There is no "i" in team, but idiot has two. -
How frequently are statistics updated? Have you forced a recompile of the procedure since early May (date of last documented change in this fragment - which year was it changed)?
Stylstic notes - you use 'INNER JOIN' in the main query, but only 'JOIN' in the subquery. You also use 'IN (1)' in a Where clause, rather than '= 1'. I don't believe that the style will have a material affect on outcome. But it suggests more hands in working this code than yours.
-
Prior to disk defrag, you should shut down all sql server services. Just for giggles, did you size all the files initially to some appropriately large value with an appropriately large growth increment, or did you leave the defaults in place?
BTW, I have a client that was experiencing SEVERE performance and outage issues. First thing I had them do was defrag their hard drive (IIRC it was 300000+ file fragments for database files). 18% throughput improvement just like that. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
File sized appropriately? The short answer is no, with growth set to 10% increment.
The defragmenter report shows under 1000 fragments for the most fragmented files (33 files) on this drive, with 19 and 12 fragments for the two files where this database lives. Is that good or bad?
Steve, I haven't fooled with statistics yet, mostly because I am unfamiliar with the concepts. And yes, there have been a few of us working on this procedure over time.
There is no "i" in team, but idiot has two. -
I advise my clients that when they create a new production database they size it to hold the estimated total data volume (including indexes) for 12-18 months of data plus any historical data to be loaded into it. This empty space is VERY important in that 1) it allows index defrag operations to lay data down in sequential order which gains the benefit of sequential I/O on any scan operations and 2) it avoids automatic file growths which fragment the physical database file.
19 and 12 fragments isn't too bad for large files. I do note however that you mention 2 FILES and not multiple files in fileGROUPS. Assuming you have a 10s or 100s of GB database, single files for data isn't optimal.
I also don't remember your stating if you have observed physical disk parameters in perf mon yet or checked for I/O waits during query execution. Also, how many spindles are in your various file groups. I apologize if I have missed this information in previous posts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
This database actually has 8 filegroups. The filegroup where these tables resides is Primary, which has two files. Primary1 has 91GB, Primary2 has 49GB. They are set for 10% incremental growth. The other filegroups each have one file. This database is "adopted", so I don't know the origin of the eight filegroups or which table lives where. Their natural father is long gone, the bum.
I haven't run perfmon or profiler, and don't know about the disk array setup other than it's RAID-n.
There is no "i" in team, but idiot has two. -
Unfortunately all of this stuff about drives and files only gets to the heart of improving overall performance. Your specific issue of having a poor query performance all of a sudden isn't caused by those things.
Assuming indexing is optimal and stats are up to date and you don't have blockage, I still think it is either data quantity and/or data distribution that is leading to the new, slow, query. And there isn't much you can do about that other than data archival to remove older, unneeded data from the table. You have no filters so even data partitioning won't help because the query still needs to read ALL of the data. Hmm, I suppose you could partition on your where clause - i.e. have one view with all data that does NOT meet your where/join clause and another that does. Then the optimizer should only reference the data that is required. That is a lot of overhead and hastle just to make one query run faster tho!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Have you considered dumping the subquery results to a separate table, preferrably indexed (or dump to it first, then index), and run the outer query based on the new table?
It seems like more work, but you are running joins against an unindexed derived table.
Just a thought.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt, I did what you suggest, using an indexed temp table, and it didn't seem to make a difference in the run time. I could try it with a base table, but I don't anticipate a difference.
It seems as if the data has reached a tipping point in terms of rows or disk space. It was working OK, and now it's not, with no recent changes in the code, no service packs installed, and no changes in the hardware. One thing I see but don't know if there is any significance to is that the invoice and transac tables are in the same filegroup, but the filegroup is spread across two files. Is that a good or a bad thing? Is it better to have two big tables like this in separate or the same file?
There is no "i" in team, but idiot has two. -
Using a base table versus a # temp table will help if your tempdb is either being hammered on, or is badly fragmented. If you're using a lot of #temp tables in lots of other reports - you could simply be waiting for your turn to use the disk/write to the filegroup in use in tempDB. I've had some occasions where it helps a LOT, and some others where it does zip.
As to your file question - if you have spare raid controllers and hard drives to spare - sure it could help some, but it would be along the lines of what SQLGuru was getting at - "overall" performance improvement, not specific improvement to this one query that doesn't run well. If the files are on the same disks/controllers - I'm not convinced you'd see anything worth writing home about.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply