Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dcallshouse
Starting Member
2 Posts |
 Posted - 2011-05-11 : 09:54:53
|
| To anyone who can help. I have a query that will be put into an SSRS report and then pulled from a sharepoint website. I need the query to respond within 3-5 seconds and at this point I am consistently returning results in the 2 minute range. Here is the current querySELECT sub.subr_id_nbr, ed.memb_frst_nm, ed.memb_last_nm, g.gndr_cd, ed.mbr_bth_dt, ed.ATTRIB_PRAC_ID, SUM(ed.allwd_amt) as allwd_amt, CASE WHEN RIGHT(ed.mbr_bth_dt,2)%2 = 0 THEN 'YES' ELSE 'NO' END AS CONSENTFROM [CBID_STG].[dbo].[_pcmh_dom_episode_v4] ep inner join [CBID_STG].[dbo].[_pcmh_dom_epsd_drill_thru] ed on ep.ATTRIB_PRAC_ID = ed.ATTRIB_PRAC_ID and ep.memb_life_id_skey = ed.memb_life_id_skey left join [CBID_PROD].Fact.Member mem on ed.memb_skey = mem.memb_skey and mem.svc_fom_dt = '2011-03-01' inner join [CBID_PROD].PHI.Subscriber sub on sub.subr_skey = mem.subr_skey left join [CBID_PROD].Dim.Gender g on ed.gndr_id_int = g.gndr_id_intWHERE (ep.ATTRIB_PRAC_ID IS NULL OR ep.[ATTRIB_PRAC_ID] = '****') and ep.panel_id = '***********' and ep.[epsd_cd] = '****' GROUP BY sub.subr_id_nbr, ed.memb_frst_nm, ed.memb_last_nm, g.gndr_cd, ed.mbr_bth_dt, ed.ATTRIB_PRAC_ID, CASE WHEN RIGHT(ed.mbr_bth_dt,2)%2 = 0 THEN 'YES' ELSE 'NO' ENDORDER BY SUM(ed.allwd_amt) DESC, CASE WHEN RIGHT(ed.mbr_bth_dt,2)%2 = 0 THEN 'YES' ELSE 'NO' ENDthe results I am getting are below: (stars are hiding personal info...not a data issue) and starred fields in query will be parameters that the user can supply with information. 213062126**** L****** Q******* F 19**-01-01 S398 1161546.96 NO213622682**** M**** N******* F 19**-01-06 S398 399038.48 YESI am not encountering issues with my results, instead I am seeking to optimize the query to return my result set as quick as possible...any help would be great. Thanks! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-11 : 10:04:52
|
| Do any of the tables have indexes? What does the Execution Plan show?JimEveryday I learn something that somebody else already knew |
 |
|
|
dcallshouse
Starting Member
2 Posts |
Posted - 2011-05-11 : 10:10:14
|
quote:
Originally posted by jimf
Do any of the tables have indexes? What does the Execution Plan show?JimEveryday I learn something that somebody else already knew
---------------------------------------------------------------------the _pcmh tables have indexes on several of the columns. Of the columns that I am pulling, attrib_pcp_id is indexed and memb_life_id_skey has an index. Unfortunately I am still new and do not have sysadmin access to the execution plan or SQL Profiler. I am working to get that as we speak.-Dave |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-11 : 10:28:01
|
| There's not much you can do without those, other than taking guesses and seeing if it runs any faster. If you don't get permissions on showplan or profiler, then optimizing the query is somebody else's problem.JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Optimizing query