Solved

Better a query that takes tooooo long to run

Posted on 2011-09-08
23
266 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
  • 12
  • 11
23 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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:Kdo
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Sorry, it starts with Activities, and left joins those other tables.  :)  
0
 

Author Comment

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

Expert Comment

by:Kdo
Comment Utility

Sure.  :)
0
 

Author Comment

by:amucinobluedot
Comment Utility
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:Kdo
Comment Utility

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

Apologies....
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Oops.  Take the qualifiers off of the selected items.  (Or rename the qualifier to 't0').


0
 

Author Comment

by:amucinobluedot
Comment Utility
The query is not working  :(
0
 

Author Comment

by:amucinobluedot
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:amucinobluedot
Comment Utility
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:
Kdo earned 500 total points
Comment Utility
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
Comment Utility
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:Kdo
Comment Utility

Can you identify the indexes on these tables?
0
 

Author Comment

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

Expert Comment

by:Kdo
Comment Utility

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
Comment Utility
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
Comment Utility
I checked, the indexes where already there, still takes forever to display.  :(
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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
Comment Utility
Where can I learn more about indexes and how to create them to make my database faster ?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now