Solved

Better a query that takes tooooo long to run

Posted on 2011-09-08
23
300 Views
Last Modified: 2012-05-12
I have the query below, is there a way to change the syntax and get the same results while making it faster ?  It takes about 30-40 seconds to return results  :(   should take a second or two the most. Help is appreciated.

---

SELECT activities.activityid as id, b.firstnm + ' ' + b.lastnm + ' ' + '('  + processcatalog.processcatalog + ')' + ' ' + '-' + ' ' + activities.processtep as actdesc,activities.partylist as longdesc, activities.schdemailon as datestarted, activities.schdemailon as datefinished, activities.actnote as repeat,activities.actnote as apptype  FROM dbo.Activities  
Inner join cases on activities.caseid = cases.id  Inner join atts on cases.id = atts.caseid  INNER JOIN Users ON atts.userid = users.userid  INNER JOIN Users as b On cases.alienid = b.userid INNER JOIN Processcatalog ON cases.Process = Processcatalog.ProcesscatalogID WHERE activities.FirmId = 71 and ActType ='HISTORY' and sent_by = 1 and atts.userid = 127273 and Schdemailon is not null  
UNION ALL  SELECT activities.activityid as id, activities.ActName as actdesc, activities.actdesc as longdesc,activities.dateinitiated as datestarted,activities.lastmodified as datefinished,activities.processtep as repeat,activities.Partylist as apptype
FROM ((dbo.Activities LEFT JOIN dbo.Cases ON dbo.Activities.CaseId = dbo.Cases.Id) )     LEFT JOIN dbo.Atts ON dbo.Atts.CaseId = dbo.Cases.Id    INNER JOIN Users ON Activities.sent_by = Users.UserId
WHERE Activities.FirmId = 71  AND  (Activities.ActType = 'Calendar')   AND sent_by = 127273


----
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
23 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504777
Hi BlueDot,

Run a small test and see which half of the query is taking all of the time.

Run the top half (above the UNION ALL) and see how long it takes.  Repeat for the lower half (below the UNION ALL).  Post the timings.


Kent
0
 

Author Comment

by:amucinobluedot
ID: 36504799
Actually the top part for this specific query does not have a problem. The problem is in the bottom part.

---

SELECT activities.activityid as id, activities.ActName as actdesc, activities.actdesc as longdesc,activities.dateinitiated as datestarted,activities.lastmodified as datefinished,activities.processtep as repeat,activities.Partylist as apptype
FROM ((dbo.Activities LEFT JOIN dbo.Cases ON dbo.Activities.CaseId = dbo.Cases.Id) )     LEFT JOIN dbo.Atts ON dbo.Atts.CaseId = dbo.Cases.Id    INNER JOIN Users ON Activities.sent_by = Users.UserId
WHERE Activities.FirmId = 71  AND  (Activities.ActType = 'Calendar')   AND sent_by = 127273

---

That is the part that takes forever.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504900
Ok.  This subquery starts with Cases, and left joins the Activities, Atts, and User tables.

Assuming you have the proper indexes on these tables, you may well see an improvement by filtering the rows from the Activities table before you left join the other tables.  See my example below, though I'm just guessing that "sent_by" is in the Activities table.


Kent

SELECT 
  activities.activityid as id, 
  activities.ActName as actdesc, 
  activities.actdesc as longdesc,
  activities.dateinitiated as datestarted,
  activities.lastmodified as datefinished,
  activities.processtep as repeat,
  activities.Partylist as apptype
FROM 
(
  SELECT * FROM dbo.Activities
  WHERE Activities.FirmId = 71  
    AND  (Activities.ActType = 'Calendar')   
    AND sent_by = 127273    
) t0
LEFT JOIN dbo.Cases 
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts 
  ON dbo.Atts.CaseId = dbo.Cases.Id    
INNER JOIN Users 
  ON Activities.sent_by = Users.UserId

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504913
Sorry, it starts with Activities, and left joins those other tables.  :)  
0
 

Author Comment

by:amucinobluedot
ID: 36504935
So, should I try the query above instead ?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504956

Sure.  :)
0
 

Author Comment

by:amucinobluedot
ID: 36504957
Ran the above query and got this error:

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "Activities.sent_by" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "activities.activityid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "activities.ActName" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "activities.actdesc" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "activities.dateinitiated" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "activities.lastmodified" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "activities.processtep" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "activities.Partylist" could not be bound.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504965

The query is what I intended, I just misspoke when I described the process.  :(

Apologies....
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36504980
Oops.  Take the qualifiers off of the selected items.  (Or rename the qualifier to 't0').


0
 

Author Comment

by:amucinobluedot
ID: 36504982
The query is not working  :(
0
 

Author Comment

by:amucinobluedot
ID: 36505017
Ummmm ... I may sound REALLY stupid but I wouldn't know how to do it, i tried the below query but same errors:

SELECT
  activities.activityid as id,
  activities.ActName as actdesc,
  activities.actdesc as longdesc,
  activities.dateinitiated as datestarted,
  activities.lastmodified as datefinished,
  activities.processtep as repeat,
  activities.Partylist as apptype
FROM
(
  SELECT * FROM dbo.Activities
  WHERE Activities.FirmId = 71  
    AND  (Activities.ActType = 'Calendar')  
    AND sent_by = 127273    
) t9
LEFT JOIN dbo.Cases
     ON t9.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts
  ON dbo.Atts.CaseId = dbo.Cases.Id    
INNER JOIN Users
  ON Activities.sent_by = Users.UserId
0
 

Author Comment

by:amucinobluedot
ID: 36505027
Also, if I make changes to this part of the query, will it affect the first part before the UNION ?
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 36505043
Sorry, bluedot.

Try this one.  :)


Kent

SELECT 
  t0.activityid as id, 
  t0.ActName as actdesc, 
  t0.actdesc as longdesc,
  t0.dateinitiated as datestarted,
  t0.lastmodified as datefinished,
  t0.processtep as repeat,
  t0.Partylist as apptype
FROM 
(
  SELECT * FROM dbo.Activities
  WHERE Activities.FirmId = 71  
    AND  (Activities.ActType = 'Calendar')   
    AND sent_by = 127273    
) t0
LEFT JOIN dbo.Cases 
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts 
  ON dbo.Atts.CaseId = dbo.Cases.Id    
INNER JOIN Users 
  ON Activities.sent_by = Users.UserId

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 36505267
Thanks, unfortunately it did not make it any faster, it went over 30 seconds then I had to stop it.

This is what I used:

SELECT
  t0.activityid as id,
  t0.ActName as actdesc,
  t0.actdesc as longdesc,
  t0.dateinitiated as datestarted,
  t0.lastmodified as datefinished,
  t0.processtep as repeat,
  t0.Partylist as apptype
FROM
(
  SELECT * FROM dbo.Activities
  WHERE Activities.FirmId = 71  
    AND  (Activities.ActType = 'Calendar')  
    AND sent_by = 127273    
) t0
LEFT JOIN dbo.Cases
     ON t0.CaseId = dbo.Cases.Id
LEFT JOIN dbo.Atts
  ON dbo.Atts.CaseId = dbo.Cases.Id    
INNER JOIN Users
  ON sent_by = Users.UserId

-
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36505454

Can you identify the indexes on these tables?
0
 

Author Comment

by:amucinobluedot
ID: 36505759
There are no indexes.  How can I create an index that would help the query above ?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36506087

The principal join entity seems to be the ID column in each of these tables.  Create an index on each of them

CREATE INDEX idxact001 on dbo.Activities (Activityid);
CREATE INDEX idxcas001 on dbo.Cases (id);
CREATE INDEX idxatt001 on dbo.Atts (Caseid);
CREATE INDEX idxusr001 on dbo.Users (UserId);

0
 

Author Comment

by:amucinobluedot
ID: 36506671
I am not familiar with indexes, what would the above code do ?  why those numbers ?
I just need to ask this so I fully understand what this will do since it is a LIVE database. My provider recommended the query below, it looks very different from the recomendation above, why would this be ?

What are the 'create statistics'  statements for ?

CREATE CLUSTERED INDEX [_dta_index_Activities_c_11_1702297124__K17_K5] ON [dbo].[Activities]
(
      [sent_by] ASC,
      [ActType] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
 
CREATE STATISTICS [_dta_stat_1702297124_1_9_10] ON [dbo].[Activities]([ActivityId], [DateInitiated], [LastModified])
go
 
CREATE STATISTICS [_dta_stat_1702297124_1_19_17] ON [dbo].[Activities]([ActivityId], [Schdemailon], [sent_by])
go
 
CREATE STATISTICS [_dta_stat_1702297124_17_1_9_10] ON [dbo].[Activities]([sent_by], [ActivityId], [DateInitiated], [LastModified])
go
 
CREATE STATISTICS [_dta_stat_1702297124_2_17_1_9_10] ON [dbo].[Activities]([CaseId], [sent_by], [ActivityId], [DateInitiated], [LastModified])
go
 
CREATE STATISTICS [_dta_stat_1702297124_1_19_9_10_17] ON [dbo].[Activities]([ActivityId], [Schdemailon], [DateInitiated], [LastModified], [sent_by])
go
 
CREATE STATISTICS [_dta_stat_1702297124_17_5_1_19_9_10] ON [dbo].[Activities]([sent_by], [ActType], [ActivityId], [Schdemailon], [DateInitiated], [LastModified])
go
 
CREATE STATISTICS [_dta_stat_1702297124_17_2_5_1_19_9_10] ON [dbo].[Activities]([sent_by], [CaseId], [ActType], [ActivityId], [Schdemailon], [DateInitiated], [LastModified])
go
 
CREATE NONCLUSTERED INDEX [_dta_index_Users_11_390292450__K1_6_7] ON [dbo].[Users]
(
      [UserId] ASC
)
INCLUDE ( [LastNm],
[FirstNm]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
 
CREATE STATISTICS [_dta_stat_309576141_1_67_16] ON [dbo].[Cases]([Id], [Process], [AlienId])
go
 
CREATE STATISTICS [_dta_stat_199671759_4_3] ON [dbo].[Atts]([UserId], [CaseId])
go--

0
 

Author Comment

by:amucinobluedot
ID: 36507947
I checked, the indexes where already there, still takes forever to display.  :(
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36510030
Hi BlueDot,

Let's see how much data we're dealing with.  Run this query:

  SELECT count(*)
  FROM dbo.Activities
  WHERE Activities.FirmId = 71  
    AND  (Activities.ActType = 'Calendar')  
    AND sent_by = 127273    


My guess is that it's a pretty big number, (database wise).


Kent
0
 

Author Comment

by:amucinobluedot
ID: 36510100
The problem where the joins, once I did that then we did have a LOT of data. thx for the help
0
 

Author Closing Comment

by:amucinobluedot
ID: 36510103
Where can I learn more about indexes and how to create them to make my database faster ?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36510313
Interesting question.  :)

Indexes that are added to a database after it has been operational for a while are usually the result of a bad design, or of a change in needs.  In your example, most of the fields that you're using as your join keys are probably primary keys, meaning that the designers intended for them to be used this way and indexed them.

Creating indexes on a running system can speed up queries, but there may be a serious performance trade-off.  Whenever a row is added, ALL of the indexes on the table have to be updated.  When a row is modified, every index that covers the changed column(s) must be updated.  Add a lot of indexes and you might find that inserts and updates take too long for your application to be effective.  (That's one of the reasons that OLTP (recording databases) and OLAP (reporting databases) systems are so different.  The OLTP puts a premium on efficiently storing data while the OLAP focuses on the retrieval of data.


You might start by reading about OLTP and OLAP systems.  A basic understanding of them will go a long way toward understanding how data is organized and how indexes can help/hurt a database.


Kent
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question