Help with Views - 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

General SQL Server Forums

New to SQL Server Programming

Help with Views

Author

Topic

ccruz222
Starting Member

2 Posts

Posted - 2010-08-23 : 11:53:09

I am in need of some serious help. I am working with SQL Server 2005 and have created several views which I am using in Crystal Reports 2008.

I am linking the views by the only thing they have in common (a samlldatetime field). The views individually run super fast, however, when joined in Crystal, it is taking extremely long to get the results. Any suggestions?

From what I can tell, there are no indexes on the views and it appears that crystal is running each of the views individually then trying to match the date fields.

How can I (if possible) create the indexes on the view to see if it will speed up the results?

2 of my views are in SQL Server 2005 and a 3rd view is in MySQL.

Any help would be greatly appreciated.

Thanks,

Charles Cruz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-23 : 11:55:07

You probably do not need to index the views, but rather you probably need to index the tables inside the view.

Could you post the view code, DDL for the tables in the views, and the indexes for those tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ccruz222
Starting Member

2 Posts

Posted - 2010-08-23 : 12:20:34

Now that I think about it, my views are using other views to collect my data (my tables are actually views). From what I can see the tables behind these views are temp tables. Does this mean that there are no indexes? What I am having a hard time understanding is that when I run each of these views in SQL Server, the results come back within seconds (literally about 2-3sec or less). Its only when they are run thru Crystal that it takes a long time? Would you happen to know how I can create these views as stored procedures in SQL Server? Or is it possible to pass thru a parameter from Crystal to the SQL View?

Here is the code from the main view:
SELECT DISTINCT
TOP (100) PERCENT dbo.Skill_Group_Half_Hour.DateTime AS CallDate, SUM(dbo.Call_Type_Half_Hour.CallsOfferedHalf) / 2 AS TotalOffered,
SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf) / 2 AS TotalAnswered, ISNULL(CONVERT(float, SUM(dbo.Call_Type_Half_Hour.AnswerWaitTimeHalf))
/ NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0) AS AveSpeedAnswer, SUM(dbo.Call_Type_Half_Hour.TotalCallsAbandToHalf)
/ 2 AS TotalAbandonedCall, ISNULL(CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallDelayAbandTimeToHalf)) / NULLIF (CONVERT(float,
SUM(dbo.Call_Type_Half_Hour.TotalCallsAbandToHalf)), 0), 0) AS AveSpeedAbandoned, ISNULL(CONVERT(float,
SUM(dbo.Call_Type_Half_Hour.TotalCallsAbandToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsOfferedHalf)), 0), 0) * 100 AS PercentAbandon,
ISNULL(CONVERT(float, SUM(dbo.Call_Type_Half_Hour.TalkTimeHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0)
+ ISNULL(CONVERT(float, SUM(dbo.Call_Type_Half_Hour.HoldTimeToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0)
+ ISNULL(CONVERT(float, SUM(dbo.Skill_Group_Half_Hour.WorkReadyTimeToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)),
0), 0) + ISNULL(CONVERT(float, SUM(dbo.Skill_Group_Half_Hour.WorkNotReadyTimeToHalf)) / NULLIF (CONVERT(float,
SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0) AS AveHandleTime, ISNULL(CONVERT(float, SUM(dbo.Call_Type_Half_Hour.TalkTimeHalf))
/ NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0) AS AveTalkTime, ISNULL(CONVERT(float,
SUM(dbo.Call_Type_Half_Hour.HoldTimeToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0) AS AveHoldTime,
ISNULL(CONVERT(float, SUM(dbo.Skill_Group_Half_Hour.WorkReadyTimeToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0),
0) + ISNULL(CONVERT(float, SUM(dbo.Skill_Group_Half_Hour.WorkNotReadyTimeToHalf)) / NULLIF (CONVERT(float,
SUM(dbo.Call_Type_Half_Hour.CallsAnsweredToHalf)), 0), 0) AS AverageAfterWorkCalls, ISNULL(dbo.Call_Type_Half_Hour.ServiceLevelHalf, 0) * 100 AS SLPercent,
ISNULL(0 + (ISNULL((SUM(dbo.Skill_Group_Half_Hour.TalkTimeToHalf) + SUM(dbo.Skill_Group_Half_Hour.HoldTimeToHalf))
/ NULLIF (SUM(dbo.Skill_Group_Half_Hour.CallsAnsweredToHalf), 0), 0) + ISNULL((SUM(dbo.Skill_Group_Half_Hour.WorkReadyTimeToHalf)
+ SUM(dbo.Skill_Group_Half_Hour.WorkNotReadyTimeToHalf)) / NULLIF (SUM(dbo.Skill_Group_Half_Hour.CallsAnsweredToHalf), 0), 0)) / NULLIF (CONVERT(float,
SUM(dbo.Skill_Group_Half_Hour.LoggedOnTimeToHalf)) / NULLIF (CONVERT(float, SUM(dbo.Skill_Group_Half_Hour.CallsAnsweredToHalf)), 0), 0), 0)
* 100 AS OccupancyPercent, 0 * 100 AS Technology_Avail, CONVERT(varchar, dbo.Skill_Group_Half_Hour.DateTime, 120) AS D2,
SUM(dbo.Call_Type_Half_Hour.AnswerWaitTimeHalf / 2) AS AnsWaitTime, dbo.Call_Type_Half_Hour.CallDelayAbandTimeToHalf AS CallDelayAbanTime,
dbo.Call_Type_Half_Hour.ServiceLevelCallsHalf AS SvcLvlCalls, dbo.Call_Type_Half_Hour.TalkTimeHalf AS TalkTot,
dbo.Call_Type_Half_Hour.HoldTimeToHalf AS HoldTot, SUM(dbo.Skill_Group_Half_Hour.WorkReadyTimeToHalf) AS WrapTot1,
SUM(dbo.Skill_Group_Half_Hour.WorkNotReadyTimeToHalf) AS WrapTot2, CASE WHEN (CallTypeID = 5042) THEN 'English' WHEN (CallTypeID = 5043)
THEN 'Spanish' ELSE 'Other' END AS Skill_Group
FROM dbo.Skill_Group INNER JOIN
dbo.Skill_Group_Half_Hour ON dbo.Skill_Group.SkillTargetID = dbo.Skill_Group_Half_Hour.SkillTargetID INNER JOIN
dbo.Media_Routing_Domain ON dbo.Skill_Group.MRDomainID = dbo.Media_Routing_Domain.MRDomainID RIGHT OUTER JOIN
dbo.Call_Type_Half_Hour ON dbo.Skill_Group_Half_Hour.DateTime = dbo.Call_Type_Half_Hour.DateTime
WHERE (dbo.Skill_Group_Half_Hour.SkillTargetID IN (5760, 5761)) AND (dbo.Call_Type_Half_Hour.CallTypeID IN (5042, 5043))
GROUP BY dbo.Skill_Group_Half_Hour.DateTime, CONVERT(varchar, dbo.Skill_Group_Half_Hour.DateTime, 120), ISNULL(dbo.Call_Type_Half_Hour.ServiceLevelHalf, 0) * 100,
dbo.Call_Type_Half_Hour.ServiceLevelCallsHalf, dbo.Call_Type_Half_Hour.TalkTimeHalf, dbo.Call_Type_Half_Hour.HoldTimeToHalf,
dbo.Call_Type_Half_Hour.CallDelayAbandTimeToHalf, dbo.Call_Type_Half_Hour.CallTypeID
HAVING (dbo.Skill_Group_Half_Hour.DateTime >= CONVERT(DATETIME, '2010-07-13 00:00:00', 102))
ORDER BY CallDate DESC

Charles Cruz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources