query performance

Question

Post reply

query performance

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

kk1173 SSC Eights! Points: 940 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1003918 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kk1173 SSC Eights! Points: 940 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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 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

This is a problem. Will look at more later.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003918 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003918 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kk1173 SSC Eights! Points: 940 More actions · Answer 10

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

Sean Lange SSC Guru Points: 286589 More actions · Answer 11

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/

kk1173 SSC Eights! Points: 940 More actions · Answer 12

I had posted the ddl /indexes yesterday at 10:35AM

Sean Lange SSC Guru Points: 286589 More actions · Answer 13

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/

kk1173 SSC Eights! Points: 940 More actions · Answer 14

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.

Sean Lange SSC Guru Points: 286589 More actions · Answer 15

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/