June 6, 2007 at 9:02 am
Hi,
Can any one help me out to improve the performance of the below query,
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS1
UNION
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS2 WHERE INTNL_PF_NUM NOT IN (SELECT INTNL_PF_NUM FROM V_RPT_TRANS1)
UNION
SELECT DISTINCT INTNL_PTNT_NUM,REG_FCLTY_CD,FCLTY_CD_DESC,MRN_REG,MRN_PTNT, LAST_NAME,FIRST_NAME,MID_NAME,UVS_PTNT_NUM,
BRTH_DT,SSN,SEX_CD,INTNL_TST_NUM,TST_CD,TST_CD_DESC,TST_PRI_CD,PRI_CD_DESC,PROD_CD,PROD_CD_DESC,DONTN_ID1,CHK_DGT,TST_RST_FRE_DESC,
TST_PRFM_FCLTY_CD,TST_PRFM_LAB_DESC,VRFY_TECH_ID,VRFY_DTTM,VOL_NUM,EXP_DTTM,SEG_NUM,MANF_LOT_NUM,DONTN_ID,PROD_ABO,
PROD_RH,ORD_INTRFC_NUM,ORD_NUM,ORD_PHYSN_NUM,ORD_PHYSN_FRE_DESC,ORD_REQ_DTTM,ICD9_CD,ICD9_FRE_DESC,INTNL_PF_NUM,
INTNL_UNIT_NUM,INTNL_DRV_NUM,INTPRTN_CD_DESC,DIVISION,UNIT_STAT_CD,PRFM_FCLTY_CD,NAME_ALS_NUM,MRN_ALS_NUM
FROM V_RPT_TRANS3
wherer V_RPT_TRANS1,V_RPT_TRANS2,V_RPT_TRANS3 are as,
V_RPT_TRANS1
------------
CREATE VIEW V_RPT_TRANS1 AS
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, T.INTNL_TST_NUM, T.TST_CD, T1.TST_CD_DESC, T.TST_PRI_CD, T2.PRI_CD_DESC, U1.PROD_CD, U.PROD_CD_DESC, U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT, T.TST_RST_FRE_DESC, T.TST_PRFM_FCLTY_CD, Y.TST_PRFM_LAB_DESC, T.VRFY_TECH_ID, T.VRFY_DTTM, U1.VOL_NUM, U1.EXP_DTTM, U1.SEG_NUM,
U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID, U1.PROD_ABO, U1.PROD_RH, R.ORD_INTRFC_NUM, R.ORD_NUM,
R.ORD_PHYSN_NUM, R.ORD_PHYSN_FRE_DESC, R.ORD_REQ_DTTM, R.ICD9_CD, R.ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM, 0 AS INTNL_DRV_NUM, ICD.INTPRTN_CD_DESC, U1.DIVISION, U1.UNIT_STAT_CD,
T.TST_CMPLT_FCLTY_CD AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM dbo.PTNT_RGSTRTN A INNER JOIN dbo.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN dbo.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN dbo.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
INNER JOIN dbo.TST_XMTCH T0 ON D1.INTNL_PTNT_NUM = T0.INTNL_PTNT_NUM
INNER JOIN dbo.TST_MST T ON T0.INTNL_TST_NUM = T.INTNL_TST_NUM
INNER JOIN dbo.TST_CD T1 ON T.TST_CD = T1.TST_CD
INNER JOIN dbo.PRI_CD T2 ON T.TST_PRI_CD = T2.PRI_CD
INNER JOIN dbo.PF_MST P1 ON D1.INTNL_PTNT_NUM = P1.INTNL_PTNT_NUM AND T0.INTNL_TST_NUM = P1.INTNL_TST_MST_NUM
INNER JOIN dbo.UNIT U1 ON P1.INTNL_UNIT_NUM = U1.INTNL_UNIT_NUM
INNER JOIN dbo.PROD_CD U ON U1.PROD_CD = U.PROD_CD AND B.FCLTY_CD = U.FCLTY_CD
INNER JOIN dbo.INTPRTN_CD ICD ON ICD.INTPRTN_CD = T.TST_RST_FRE_DESC
INNER JOIN dbo.ORD_MSG_MST R ON T.INTNL_ORD_MSG_NUM = R.INTNL_ORD_MSG_NUM
LEFT OUTER JOIN dbo.TST_PRFM_LAB_CD Y ON T.TST_PRFM_LAB_CD = Y.TST_PRFM_LAB_CD
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
V_RPT_TRANS2
------------
CREATE VIEW V_RPT_TRANS2 AS
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, ISNULL(P1.INTNL_TST_MST_NUM, 0) AS INTNL_TST_NUM, '' AS TST_CD, ''
AS TST_CD_DESC, '' AS TST_PRI_CD, '' AS PRI_CD_DESC, U1.PROD_CD AS PROD_CD, U.PROD_CD_DESC AS PROD_CD_DESC,
U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT AS CHK_DGT, '' AS TST_RST_FRE_DESC, '' AS TST_PRFM_FCLTY_CD, ''
AS TST_PRFM_LAB_DESC, '' AS VRFY_TECH_ID, '' AS VRFY_DTTM, U1.VOL_NUM AS VOL_NUM, U1.EXP_DTTM AS EXP_DTTM,
U1.SEG_NUM AS SEG_NUM, U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID AS DONTN_ID, U1.PROD_ABO AS PROD_ABO,
U1.PROD_RH AS PROD_RH, '' AS ORD_INTRFC_NUM, '' AS ORD_NUM, '' AS ORD_PHYSN_NUM, '' AS ORD_PHYSN_FRE_DESC, '' AS ORD_REQ_DTTM, '' AS ICD9_CD,'' AS ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM AS INTNL_UNIT_NUM,0 AS INTNL_DRV_NUM, '' AS INTPRTN_CD_DESC, U1.DIVISION AS DIVISION, U1.UNIT_STAT_CD AS UNIT_STAT_CD, '' AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM DBO.PTNT_RGSTRTN A INNER JOIN DBO.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN DBO.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN DBO.PTNT_NAM D ON A.INTNL_PTNT_NUM = D .INTNL_PTNT_NUM
INNER JOIN DBO.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
INNER JOIN DBO.PF_MST P1 ON D1.INTNL_PTNT_NUM = P1.INTNL_PTNT_NUM
INNER JOIN DBO.UNIT U1 ON P1.INTNL_UNIT_NUM = U1.INTNL_UNIT_NUM
INNER JOIN DBO.PROD_CD U ON U1.PROD_CD = U.PROD_CD
WHERE U.FCLTY_CD = B.FCLTY_CD AND U.PROD_CLR_TYP = 'N'
UNION
SELECT A.INTNL_PTNT_NUM, A.REG_INTO_FCLTY_CD AS REG_FCLTY_CD, B.FCLTY_CD_DESC, A.MED_REC_NUM AS MRN_REG,
C.MED_REC_NUM AS MRN_PTNT, A.PTNT_LST_NAM AS LAST_NAME, A.PTNT_FST_NAM AS FIRST_NAME, A.PTNT_MID_NAM AS MID_NAME,
D1.UVS_PTNT_NUM, D1.BRTH_DT, D1.SSN, D1.SEX_CD, ISNULL(P1.INTNL_TST_MST_NUM, 0) AS INTNL_TST_NUM, '' AS TST_CD, ''
AS TST_CD_DESC, '' AS TST_PRI_CD, '' AS PRI_CD_DESC, U1.PROD_CD AS PROD_CD, U.PROD_CD_DESC AS PROD_CD_DESC,
U1.RCV_FCLTY_DONTN_ID AS DONTN_ID1, U1.CHK_DGT AS CHK_DGT, '' AS TST_RST_FRE_DESC, '' AS TST_PRFM_FCLTY_CD, ''
AS TST_PRFM_LAB_DESC, '' AS VRFY_TECH_ID, '' AS VRFY_DTTM, U1.VOL_NUM AS VOL_NUM, U1.EXP_DTTM AS EXP_DTTM,
U1.SEG_NUM AS SEG_NUM, U1.PROD_MANF_LOT_NUM AS MANF_LOT_NUM, U1.DONTN_ID AS DONTN_ID, U1.PROD_ABO AS PROD_ABO,
U1.PROD_RH AS PROD_RH, R.ORD_INTRFC_NUM AS ORD_INTRFC_NUM, R.ORD_NUM AS ORD_NUM, R.ORD_PHYSN_NUM AS ORD_PHYSN_NUM,
R.ORD_PHYSN_FRE_DESC AS ORD_PHYSN_FRE_DESC, R.ORD_REQ_DTTM AS ORD_REQ_DTTM, R.ICD9_CD AS ICD9_CD,
R.ICD9_FRE_DESC AS ICD9_FRE_DESC, P1.INTNL_PF_MST_NUM AS INTNL_PF_NUM, U1.INTNL_UNIT_NUM AS INTNL_UNIT_NUM,
0 AS INTNL_DRV_NUM, '' AS INTPRTN_CD_DESC, U1.DIVISION AS DIVISION, U1.UNIT_STAT_CD AS UNIT_STAT_CD, '' AS PRFM_FCLTY_CD, P1.INTNL_PTNT_ALS_NUM AS NAME_ALS_NUM, P1.INTNL_MED_REC_NUM AS MRN_ALS_NUM
FROM DBO.PTNT_RGSTRTN A
INNER JOIN DBO.FCLTY_CD B ON A.REG_INTO_FCLTY_CD = B.FCLTY_CD
INNER JOIN DBO.PTNT_MED_REC_NUM C ON A.MED_REC_NUM = C.MED_REC_NUM
INNER JOIN DBO.PTNT_NAM D ON A.INTNL_PTNT_NUM = D .INTNL_PTNT_NUM
INNER JOIN DBO.PTNT_MST D1 ON A.INTNL_PTNT_NUM = D1.INTNL_PTNT_NUM
IN
June 6, 2007 at 2:41 pm
I want to the screen where you gonna display that number of values simultaneously.
_____________
Code for TallyGenerator
June 6, 2007 at 3:18 pm
This is basically for generating a report.
June 7, 2007 at 4:32 am
You have both union and distinct. With union, distinct is redundent, since union removes duplicates anyway.
Do the views retrun duplicate data? If not, you can remove the distincts
Is there any chance of data overlap between the views? Will a row that appears in view 1 also appear in view 2? If not, change the union to union all.
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
June 7, 2007 at 7:39 am
Firstly, cannot eliminate distinct as the query returns duplicate records.
Secondly, there is overlap of data, so have to use union and not union all.
By eliminating distinct would there be any performance improvement?
I could squeeze the 2nd view by eliminating the union and combining in into a single query. But I gained only an improvement of 1sec.
Can you suggest me what are the other ways of improving the query performance.
I am pretty new to SQL, any suggestions would be helpful.
Thanks,
Uday
June 7, 2007 at 7:49 am
Best this is to run the query in management studio withn the execution plan enabled and look at where the high costs are. That should give you some indication where the problem is.
In my experience, duplicate data indicates either bad data or an incorrect query
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
June 7, 2007 at 10:44 am
Thank you Gail. Would check out and work on it.
June 7, 2007 at 1:36 pm
I see a lot of the same joins at the beginning and end of each view definition. Could you isolate the different parts of each of the three views, union the results, and then do the common joins only once?
June 8, 2007 at 7:24 am
My gut feel is that this is going to be a poor performer no matter what you do.
1) Too many joins to be efficient
2) Use of both distinct and union - each require tempdb and sorting --> inefficient.
3) No where clause --> all data
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 8, 2007 at 7:56 am
2) Use of both distinct and union - each require tempdb and sorting --> inefficient.
the optimiser is smart enough not to do both. In cases I've seen like this there's a single sort/distinct sort done at the end of the query. Point still stands though, it is slow and inefficient
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
June 11, 2007 at 1:08 am
Hi,
I have done little overview of your query code and following are some suggestions for query improvment and gaining the performance:
1. Please should reduce the number of joins because (maximum 3 or 4 number of joins are recommended) solution to reduce the number of joins create two or three variable temporary tables and dump your required data in relevant temp table using derived tables.
2. Create a Indexed Views instead of simple views, please note indexed views should only be created on your refined quries as mentioned in point 1
3. Create proper indexes on all the columns thats being used in WHERE calause and table JOINS.
Thanks & Best Regard,
Irfan Baig.
June 12, 2007 at 5:46 am
"(maximum 3 or 4 number of joins are recommended)"....recommended by whom? Please support by evidence.
SQL is powerful enough not to have a problem with joins involving far more tables than this. Proper indexing and avoiding data conversions on joins are key.
Please post SQL execution plan to show us what's happening behind the queries. What you have may be as good as it gets. Also what are the individual table sizes (records, mb, etc).
June 12, 2007 at 7:47 am
Recommended by lots of sources in my experience and readings, although the number of joins as a target max varies from 4-8. I don't think it is a matter of sql server or the hardware can't handle it. It is a matter of efficiency - mainly from a design perspective. Most texts or class material I have read or taught from dealing with database design recommend considering denormalization or architecture changes if you get much deeper than 4-8 levels of joining to get an answer to a query.
One could say that this is old-school since servers are so much more powerful these days. To which others could reply that data volumes are growing at an equal or greater pace.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 12, 2007 at 9:17 am
The join philosophy and optimizer work for choosing the best execution plan:
How many ways are there of joining two tables: A and B? There are in fact two ways: AB and
BA. What about three tables? There are six ways: ABC, ACB, BAC, BCA, CAB, and CBA.
What about four tables? The answer is four, or 1 * 2 * 3 * 4 = 24.
The number of ways, then, to join X tables is X!, or factorial X. If a query were to join 16
tables, we are talking about 20,922,789,888,000 possible ways of performing this join. A join
of ten tables would have 3,628,800 possible combinations, and SQL Server 2000 can join a
maximum of 256 tables in a SELECT statement!
Luckily, the query optimizer uses techniques internally to minimize the number of possible
combinations, but the fact still remains that the more tables in a join the longer the query
optimizer will take to work out the most efficient access strategy. Also, any inefficiency will
be magnified enormously, since we are basically placing loops within loops within loops
within a nested loops join.
If you are not happy with your query performance, then break the join down into parts, perhaps joining a subset of the
tables into a temporary table and then joining that with the remaining tables.
June 12, 2007 at 9:28 am
Irfan, you make a good point about the optimization cost/time which I don't think has been mentioned as of yet.
However, the "loops . loops . loops" statement is a bit off. SQL 6.5 was constrained to having only nested loop joins (which made it a true dog for larger systems at the time). But since SQL 7 the optimizer has also been able to do both merge and hash joins, which work much better for larger sorted/unsorted datasets.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply