March 6, 2013 at 2:58 pm
To give you an idea of the issue, if I run this query it takes 39 sec. All the required indexes are in place.
I have also attached the query plan
SELECT MinorMkt ,Sum(admits) as 'Total Admits' ,Sum([days]) as 'Total Days'
, sum(OppAdm) as 'Opp Adm' ,sum(OppTotDays) as 'Opp Tot Days', sum(paid_amt) as 'Paid Amt',sum(bill_amt) as 'Bill Amt'
, sum(ReadmitOpportNet) as 'Readmit Oppor Net' ,sum(readmit_expected) as 'Readmit Expected', sum(Readmit_Factor) as 'Readmit Factor',sum(ER) as 'Total ER'
, ISNULL(sum(ER)/NULLIF(sum(Admits),0),0) as '% ER' ,ISNULL(sum([Days])/NULLIF(sum(Admits),0),0) as 'LOS'
, ISNULL(sum(ExpDays)/NULLIF(sum(Admits),0),0) as 'Expected LOS' ,ISNULL(sum(OppTotDays)/NULLIF(sum(OppAdm),0),0) as 'Opport Admit LOS'
,ISNULL(sum(OppExpDays)/NULLIF(sum(OppAdm),0),0) as 'Opport Admit Expected LOS',ISNULL(sum(Readmit_Los)/NULLIF(sum(Readmit30),0),0) as 'ReadmitLOS'
,ISNULL(sum(Readmit_Allowed)/NULLIF(sum(Readmit30),0),0) as 'ReadmitAllowedPerCase'
,sum(ReadmitOpportNet)*ISNULL(sum(Readmit_Allowed)/NULLIF(sum(Readmit30),0),0) as 'ReadmitOpportNetSavings'
,ISNULL(sum([Days])/NULLIF(sum(ExpDays),0),0) as 'LOS To Expected',ISNULL(sum(Amt_allowed_case)/NULLIF(sum(Admits),0),0) as 'Allowed Per Case'
,ISNULL(sum(Trauma)/NULLIF(sum(Admits),0),0) as '% Trauma',ISNULL(sum(Ped)/NULLIF(sum(Admits),0),0) as '% Ped'
,ISNULL(sum(OneDayStay)/NULLIF(sum(Admits),0),0) as '% One Day Stay',ISNULL(sum(Short_Stay_1)/NULLIF(sum(Admits),0),0) as '% LOS 1-2 Days'
,ISNULL(sum(Short_Stay_2)/NULLIF(sum(Admits),0),0) as '% LOS 3-5 Days',ISNULL(sum(Short_Stay_3)/NULLIF(sum(Admits),0),0) as '% LOS > 5 Days'
,ISNULL(sum(Readmit30)/NULLIF(sum(Admits),0),0) as 'Readmit Rate',ISNULL(sum(Readmit_Expected)/NULLIF(sum(Admits),0),0) as 'Expected Readmit Rate'
,ISNULL(sum(Mcg_Obs_Oneday_Count)/NULLIF(sum(Admits),0),0) as '% MCG Obs 1 Day',ISNULL(sum(Mcg_Obs_Oneday_Count)/NULLIF(sum(Admits),0),0) as '% MCG OP Surg 1 Day'
,ISNULL(sum(Readmit30)/NULLIF(sum(Readmit_Expected),0),0) as 'Readmits to Expected'
,ISNULL(sum(readmit30_unplanned)/NULLIF(sum(Readmit30),0),0)as '% Unplanned Readmits'
,ISNULL(sum(OppDaysPos)/NULLIF(sum(Admits),0),0) as 'Opport Days/Admit'
FROM vw_FactData1 a where [plan] IN('COM','MCR' )
and adm_dt>='01-01-2009' and adm_dt<'01-01-2013'
group by MinorMkt order by a.MinorMkt
March 6, 2013 at 2:59 pm
TheSQLGuru (3/6/2013)
The UDFs are a problem if for no other reason than the optimizer cannot know what is coming out of them, thus it will get inaccurate estimates on the IN clauses and possibly a disastrously bad query plan.
The exception to that rule is when you use an iTVF. Those are included in the execution plan just like a View would be.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2013 at 3:03 pm
Following is the code for the function. For the previous query i had stripped this function and was directly running the IN clause. I did not notice any difference in performance
This function returns a table for a comma seperated values passed.
CREATE FUNCTION [dbo].[uf_AppendString](@RepParam nvarchar(max))
RETURNS @Values TABLE (Param nvarchar(max))
AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(max)
declare @Delim char(1)
set @Delim= ','
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(CAST(@Piece AS nvarchar(max)))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0
BREAK
END
RETURN
END
March 6, 2013 at 3:59 pm
kk1173 (3/6/2013)
Following is the code for the function. For the previous query i had stripped this function and was directly running the IN clause. I did not notice any difference in performanceThis function returns a table for a comma seperated values passed.
CREATE FUNCTION [dbo].[uf_AppendString](@RepParam nvarchar(max))
RETURNS @Values TABLE (Param nvarchar(max))
AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(max)
declare @Delim char(1)
set @Delim= ','
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(CAST(@Piece AS nvarchar(max)))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0
BREAK
END
RETURN
END
This is a problem. Will look at more later.
March 6, 2013 at 7:22 pm
Jeff Moden (3/6/2013)
TheSQLGuru (3/6/2013)
The UDFs are a problem if for no other reason than the optimizer cannot know what is coming out of them, thus it will get inaccurate estimates on the IN clauses and possibly a disastrously bad query plan.The exception to that rule is when you use an iTVF. Those are included in the execution plan just like a View would be.
1) I must have missed the note that this was an iTVF.
2) Even if it were, do you have an example of an iTVF that takes a delimited string, returns that string split into a table and the optimizer gets proper statistics out of the row(s) put into the returned table for use in the rest of the query plan?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2013 at 7:35 pm
TheSQLGuru (3/6/2013)
Jeff Moden (3/6/2013)
TheSQLGuru (3/6/2013)
The UDFs are a problem if for no other reason than the optimizer cannot know what is coming out of them, thus it will get inaccurate estimates on the IN clauses and possibly a disastrously bad query plan.The exception to that rule is when you use an iTVF. Those are included in the execution plan just like a View would be.
1) I must have missed the note that this was an iTVF.
2) Even if it were, do you have an example of an iTVF that takes a delimited string, returns that string split into a table and the optimizer gets proper statistics out of the row(s) put into the returned table for use in the rest of the query plan?
The function used by the OP is not an itvf, he posted the code. And yes, the DelimitedSplit8K is a true itvf.
March 6, 2013 at 8:15 pm
SELECT CASE WHEN number%3 = 0 THEN 0 ELSE number END AS a
INTO #tmp
FROM kgbtools.dbo.BigNumbers
(100000 row(s) affected)
CREATE CLUSTERED INDEX idx ON #tmp (a)
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT item FROM KGBTools.dbo.DelimitedSplit8K('0',','))
estimated ROWS 10000
actual ROWS, 33333
TABLE scan, 89 reads TABLE , 67391 reads worktable
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT item FROM KGBTools.dbo.DelimitedSplit8K('99',','))
estimated ROWS 10000
actual ROWS, 0
TABLE scan, 89 reads
CREATE TABLE #val (a int)
INSERT #val VALUES(0)
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT a FROM #val)
estimated ROWS 33333
actual ROWS, 33333
INDEX seek, 93 reads
TRUNCATE TABLE #val
INSERT #val VALUES(99)
go
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT a FROM #val)
estimated ROWS 33333
actual ROWS, 0
INDEX seek, 3 reads
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 7, 2013 at 6:41 am
TheSQLGuru (3/6/2013)
Jeff Moden (3/6/2013)
TheSQLGuru (3/6/2013)
The UDFs are a problem if for no other reason than the optimizer cannot know what is coming out of them, thus it will get inaccurate estimates on the IN clauses and possibly a disastrously bad query plan.The exception to that rule is when you use an iTVF. Those are included in the execution plan just like a View would be.
1) I must have missed the note that this was an iTVF.
2) Even if it were, do you have an example of an iTVF that takes a delimited string, returns that string split into a table and the optimizer gets proper statistics out of the row(s) put into the returned table for use in the rest of the query plan?
Sorry Kevin. I mistook what was being said. I thought you were referring to an out of context instance.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2013 at 6:46 am
TheSQLGuru (3/6/2013)
SELECT CASE WHEN number%3 = 0 THEN 0 ELSE number END AS a
INTO #tmp
FROM kgbtools.dbo.BigNumbers
(100000 row(s) affected)
CREATE CLUSTERED INDEX idx ON #tmp (a)
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT item FROM KGBTools.dbo.DelimitedSplit8K('0',','))
estimated ROWS 10000
actual ROWS, 33333
TABLE scan, 89 reads TABLE , 67391 reads worktable
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT item FROM KGBTools.dbo.DelimitedSplit8K('99',','))
estimated ROWS 10000
actual ROWS, 0
TABLE scan, 89 reads
CREATE TABLE #val (a int)
INSERT #val VALUES(0)
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT a FROM #val)
estimated ROWS 33333
actual ROWS, 33333
INDEX seek, 93 reads
TRUNCATE TABLE #val
INSERT #val VALUES(99)
go
DBCC freeproccache
go
SELECT *
FROM #tmp t
WHERE t.a IN (SELECT a FROM #val)
estimated ROWS 33333
actual ROWS, 0
INDEX seek, 3 reads
Now THAT I agree with! A little "Divid'n'Conquer' goes a long way! Even an iTVF has a hard time keepig up with that.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2013 at 10:44 am
Guys - If I run the query without any function it still takes a long time to execute. So use of function is not the issue here.
Any other suggestions?
Thanks
KK
March 7, 2013 at 12:22 pm
kk1173 (3/7/2013)
Guys - If I run the query without any function it still takes a long time to execute. So use of function is not the issue here.Any other suggestions?
Thanks
KK
We still haven't seen ddl and indexes for all the base tables. You said "all the proper indexes are in place", but many times we see that the tables in fact need some indexing to make these queries work quickly. Without ddl and at least some sample data about all we can do is guess.
I don't know if anybody else had any luck downloading the query plan you posted yesterday but I was never able to download it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 12:27 pm
I had posted the ddl /indexes yesterday at 10:35AM
March 7, 2013 at 12:35 pm
kk1173 (3/7/2013)
I had posted the ddl /indexes yesterday at 10:35AM
Yes for tblFact. However, the view has a few other tables in it.
HOSP_UID_Xref
aprdrg_lkup
minormkt
And not your fault but the attachment for the query plan won't download for me. I don't if others have the same problem but it appears to be something wrong with the file. Probably something went haywire when you uploaded it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 12:44 pm
Sorry i missed that. Here you go..
CREATE TABLE [dbo].[MinorMkt](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Region] [varchar](20) NULL,
[MinorMkt] [varchar](350) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hosp_UID_Xref](
[UID_v3] [varchar](20) NOT NULL,
[Prov_Key] [varchar](25) NOT NULL,
[HospName] [varchar](100) NULL,
[HospState] [varchar](2) NULL,
[HospCity] [varchar](25) NULL,
[HospStreet] [varchar](50) NULL,
[HospZip] [varchar](5) NULL,
[HospSys] [varchar](100) NULL,
[HospAssignedType] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
[UID_v3] ASC,
[Prov_Key] 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]
CREATE TABLE [dbo].[aprdrg_lkup](
[Apr_Drg] [varchar](15) NOT NULL,
[AprDrg_Desc] [varchar](120) NULL,
[Apr_Cat] [varchar](15) NULL,
[aprdmc] [varchar](3) NULL,
[AprMDC_Desc] [varchar](120) NULL,
PRIMARY KEY CLUSTERED
(
[Apr_Drg] 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]
GO
Attaching the sql plan again.
March 7, 2013 at 1:26 pm
For some reason the attachment is still not working. I am wondering if your stats are out of date but I can't get the attachment.
Here are a couple of observations. Your table MinorMkt is a heap. I would change this so that your ID column is a nonclustered primary key.
Then you should also add a clustered index to that table on MinorMkt.
In my system with no data the lookup on that table is now using an index seek instead of a table scan.
Also, do not discount what the others have said about replacing your string splitter with the MUCH faster DelimitedSplit8K. I can't remember if it was posted already but you can find an article that explains that splitter by following the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply