Stored Proc vs View - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc vs View

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-11-05 : 07:30:01

I know, I know...

This is with a particular focus...

Mainly SQL <----------------> Crystal Reports

and there are MANY things Crystal can perform which SQL Server cannot. Believe it or not.

Current view below.
Needs no parameters. Runs on getdate() and adjustments of it for start and end of month periods, etc.
Crystal takes those 10 columns and deals with those. ~170k rows, increasing every day. That's it! Boss wants all Crystal-side calculations to be server-side. Can anything you see in this view be improved for performance by making it an SP? I think it's down to me to translate any of my Crystal syntax to SQL. I wouldn't be using Crystal if it could all be in SQL.

I've tried many WHERE clause criteria to get the ~170k rows down to ~6k (time window, service classification, etc)and THAT is performance! but there are SUTBLE differences between the dashboard gauges and it just all has to go back to standard.

Thanks Heroes

Here's my view:

USE [ServiceDeskDB]
GO
/****** Object: View [Reports].[v_service_today_guages] Script Date: 11/05/2008 09:45:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [Reports].[v_service_today_guages] as
SELECT -- 1
T10101.prj_description AS description
,T10101.prj_id AS id
,T10101.prj_actual_finish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T10101.prj_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T10112.cdl_name AS priority
,T10115.rct_name AS status_service_today_
,T10116.tem_name AS template_name
,T10130.wog_name AS to_workgroup_name
,T10101.prj_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_projects T10101
LEFT OUTER JOIN itsm_workgroups T10130 ON (T10130.wog_oid = T10101.prj_ass_wog_oid))
LEFT OUTER JOIN rep_templates T10116 ON (T10116.tem_oid = T10101.prj_tem_oid))
LEFT OUTER JOIN rep_codes T10114 ON (T10114.rcd_oid = T10101.prj_sta_oid))
LEFT OUTER JOIN rep_codes_text T10115 ON (T10115.rct_rcd_oid = T10114.rcd_oid))
LEFT OUTER JOIN itsm_codes T10111 ON (T10111.cod_oid = T10101.prj_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T10112 ON (T10112.cdl_cod_oid = T10111.cod_oid))
WHERE ((T10112.cdl_lng_oid IS NULL OR T10112.cdl_lng_oid = 1033)
AND (T10115.rct_lng_oid IS NULL OR T10115.rct_lng_oid = 1033))
UNION
SELECT -- 2
T30101.wor_description AS description
,T30101.wor_id AS id
,T30101.wor_actualfinish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T30101.wor_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T30112.cdl_name AS priority
,T30115.rct_name AS status_service_today_
,T30116.tem_name AS template_name
,T30130.wog_name AS to_workgroup_name
,T30101.reg_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_workorders T30101
LEFT OUTER JOIN itsm_workgroups T30130 ON (T30130.wog_oid = T30101.ass_workgroup))
LEFT OUTER JOIN rep_templates T30116 ON (T30116.tem_oid = T30101.wor_tem_oid))
LEFT OUTER JOIN rep_codes T30114 ON (T30114.rcd_oid = T30101.wor_sta_oid))
LEFT OUTER JOIN rep_codes_text T30115 ON (T30115.rct_rcd_oid = T30114.rcd_oid))
LEFT OUTER JOIN itsm_codes T30111 ON (T30111.cod_oid = T30101.wor_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T30112 ON (T30112.cdl_cod_oid = T30111.cod_oid))
WHERE ((T30112.cdl_lng_oid IS NULL OR T30112.cdl_lng_oid = 1033)
AND (T30115.rct_lng_oid IS NULL OR T30115.rct_lng_oid = 1033))
UNION
SELECT -- 3
T50101.pro_description AS description
,T50101.pro_id AS id
,T50101.pro_actualfinish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T50101.pro_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T50112.cdl_name AS priority
,T50115.rct_name AS status_service_today_
,T50116.tem_name AS template_name
,T50130.wog_name AS to_workgroup_name
,T50101.reg_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_problems T50101
LEFT OUTER JOIN itsm_workgroups T50130 ON (T50130.wog_oid = T50101.ass_wog_oid))
LEFT OUTER JOIN rep_templates T50116 ON (T50116.tem_oid = T50101.pro_tem_oid))
LEFT OUTER JOIN rep_codes T50114 ON (T50114.rcd_oid = T50101.pro_sta_oid))
LEFT OUTER JOIN rep_codes_text T50115 ON (T50115.rct_rcd_oid = T50114.rcd_oid))
LEFT OUTER JOIN itsm_codes T50111 ON (T50111.cod_oid = T50101.pro_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T50112 ON (T50112.cdl_cod_oid = T50111.cod_oid))
WHERE ((T50112.cdl_lng_oid IS NULL OR T50112.cdl_lng_oid = 1033)
AND (T50115.rct_lng_oid IS NULL OR T50115.rct_lng_oid = 1033))
UNION
SELECT -- 4
T70101.ser_description AS description
,T70101.ser_id AS id
,T70101.ser_actualfinish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T70101.ser_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T70112.cdl_name AS priority
,T70115.rct_name AS status_service_today_
,T70116.tem_name AS template_name
,T70130.wog_name AS to_workgroup_name
,T70101.reg_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_servicecalls T70101
LEFT OUTER JOIN itsm_workgroups T70130 ON (T70130.wog_oid = T70101.ser_ass_wog_oid))
LEFT OUTER JOIN rep_templates T70116 ON (T70116.tem_oid = T70101.ser_tem_oid))
LEFT OUTER JOIN rep_codes T70114 ON (T70114.rcd_oid = T70101.ser_sta_oid))
LEFT OUTER JOIN rep_codes_text T70115 ON (T70115.rct_rcd_oid = T70114.rcd_oid))
LEFT OUTER JOIN itsm_codes T70111 ON (T70111.cod_oid = T70101.ser_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T70112 ON (T70112.cdl_cod_oid = T70111.cod_oid))
WHERE ((T70112.cdl_lng_oid IS NULL OR T70112.cdl_lng_oid = 1033)
AND (T70115.rct_lng_oid IS NULL OR T70115.rct_lng_oid = 1033))
UNION
SELECT -- 5
T80101.inc_description AS description
,T80101.inc_id AS id
,T80101.inc_actualfinish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T80101.inc_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T80112.cdl_name AS priority
,T80115.rct_name AS status_service_today_
,T80116.tem_name AS template_name
,T80130.wog_name AS to_workgroup_name
,T80101.reg_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_incidents T80101
LEFT OUTER JOIN itsm_workgroups T80130 ON (T80130.wog_oid = T80101.inc_assign_workgroup))
LEFT OUTER JOIN rep_templates T80116 ON (T80116.tem_oid = T80101.inc_tem_oid))
LEFT OUTER JOIN rep_codes T80114 ON (T80114.rcd_oid = T80101.inc_sta_oid))
LEFT OUTER JOIN rep_codes_text T80115 ON (T80115.rct_rcd_oid = T80114.rcd_oid))
LEFT OUTER JOIN itsm_codes T80111 ON (T80111.cod_oid = T80101.inc_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T80112 ON (T80112.cdl_cod_oid = T80111.cod_oid))
WHERE ((T80112.cdl_lng_oid IS NULL OR T80112.cdl_lng_oid = 1033)
AND (T80115.rct_lng_oid IS NULL OR T80115.rct_lng_oid = 1033))
UNION
SELECT -- 6
T90101.cha_description AS description
,T90101.cha_id AS id
,T90101.cha_actualfinish + CAST(1.0 AS DECIMAL)/24 AS closed_date
,T90101.cha_deadline + CAST(1.0 AS DECIMAL)/24 AS deadline
,T90112.cdl_name AS priority
,T90115.rct_name AS status_service_today_
,T90116.tem_name AS template_name
,T90130.wog_name AS to_workgroup_name
,T90101.reg_created + CAST(1.0 AS DECIMAL)/24 AS created
FROM (((((( itsm_changes T90101
LEFT OUTER JOIN itsm_workgroups T90130 ON (T90130.wog_oid = T90101.ass_wog_oid))
LEFT OUTER JOIN rep_templates T90116 ON (T90116.tem_oid = T90101.cha_tem_oid))
LEFT OUTER JOIN rep_codes T90114 ON (T90114.rcd_oid = T90101.cha_sta_oid))
LEFT OUTER JOIN rep_codes_text T90115 ON (T90115.rct_rcd_oid = T90114.rcd_oid))
LEFT OUTER JOIN itsm_codes T90111 ON (T90111.cod_oid = T90101.cha_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T90112 ON (T90112.cdl_cod_oid = T90111.cod_oid))
WHERE ((T90112.cdl_lng_oid IS NULL OR T90112.cdl_lng_oid = 1033)
AND (T90115.rct_lng_oid IS NULL OR T90115.rct_lng_oid = 1033))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources