Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I make this query run faster?

Posted on 2010-11-24
21
Medium Priority
?
560 Views
Last Modified: 2012-06-27
Hello,

I have the following code, which is taking nearly an hour to run. Is there a way to make it run faster?


select *  
  from 
(select h.*  
                , row_number() over (partition by  h.product order by maxcreated desc) rn,   
  a.ClientNumber as Client, a.product as product_num, c.Result, g.statetypename as StateType, b.status, h.maxcreated as DateCompleted

FROM        client a
			left join workstation b on a.productid= b.productid
			left join validationsummary c on a.productid= c.productid
			left join note d on a.productid= d.productid
			left join snapshot e on a.productid= e.productid
			left join workstationstep f on b.workstationid = f.workstationid
			left join statetype g on e.statetypeid = g.statetypeid
			left join mostrecent h on a.product = h.product 

WHERE   (b.Template = 'MOD2')  

 ) sq  

where sq.rn = 1

Open in new window

0
Comment
Question by:SASnewbie
[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
  • 9
  • 5
  • 3
  • +3
21 Comments
 

Expert Comment

by:Integr8
ID: 34208009
One place to start is to specifically name the columns you are extracting. Simply requesting everything by using the asterisk is not a "best practice". If your table design changes your query could fail.  Next, ensure that you have proper indexing on your tables. For a clue, look at the conditions you are imposing in your Where clause(s). Finally look for ways to further restrict the range of what you are requesting, is there any possibility of isolating data by time, date, other classifications?  That's a start
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34208011
I would start of by making sure there is an index PER EACH field being linked, e.g.

workstation.productid
client.productid
note.productid
etc

Can you show the plan for that query?

Add before your query:
set showplan_text on;
and after:
GO
set showplan_text off;

press Ctrl-T, Ctrl-E, Ctrl-D in query window

Copy and paste the output here
0
 

Author Comment

by:SASnewbie
ID: 34208037
Thanks all,

I implemented this code from a question yesterday on how to retrieve the latest date:

select *  
  from (select t.*  
                , row_number() over (partition by product# order by DateComplete desc) rn  
              from yourtable t  
  ) sq  
where sq.rn = 1

Should I remove "t.* from the subquery?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:SASnewbie
ID: 34208043
There are indexes for all the tables I am joining to.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34208143
I would move all  joins out of the subselect which uses row_number() into the outer select.
select h.*, a.ClientNumber as Client, a.product as product_num, c.Result, g.statetypename as StateType, b.status, h.maxcreated as DateCompleted
     from client a
left join workstation b on a.productid= b.productid
left join validationsummary c on a.productid= c.productid
left join note d on a.productid= d.productid
left join snapshot e on a.productid= e.productid
left join workstationstep f on b.workstationid = f.workstationid
left join statetype g on e.statetypeid = g.statetypeid
left join
(select h.*, row_number() over (partition by  h.product order by maxcreated desc) rn
FROM  mostrecent h) h
on a.product = h.product and h.rn=1
WHERE   (b.Template = 'MOD2')

Open in new window

0
 

Author Comment

by:SASnewbie
ID: 34208165
Thanks Qlemo,

Let me try this and get right back.
0
 

Author Comment

by:SASnewbie
ID: 34208262
It is running quicker, but is not returning only the 1st row number for each product.
0
 

Author Comment

by:SASnewbie
ID: 34208312
I am mistaken. It is returning the first row, but is returning duplicates. There should be approximately 40K records, and it is now at 614K.

708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Original      Active      6/14/2010
708      000000012      6/14/2010      1      708      000000012      Pending      Current      Active      6/14/2010
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34208318
What about
select h.*, a.ClientNumber as Client, a.product as product_num, c.Result, g.statetypename as StateType, b.status, h.maxcreated as DateCompleted
     from client a
left join workstation b on a.productid= b.productid
left join validationsummary c on a.productid= c.productid
left join note d on a.productid= d.productid
left join snapshot e on a.productid= e.productid
left join workstationstep f on b.workstationid = f.workstationid
left join statetype g on e.statetypeid = g.statetypeid
left join
(select * from (select h.*, row_number() over (partition by  h.product order by maxcreated desc) rn
FROM  mostrecent h) h where rn = 1) h
on a.product = h.product 
WHERE   (b.Template = 'MOD2')

Open in new window

0
 

Author Comment

by:SASnewbie
ID: 34208359
Thanks for getting back to me so quickly. I will try it now.

Sorry, it is still returning dups.

708      000000012      8/2/2010      1      708      000000012      Denied      Original      Active      8/2/2010
708      000000012      8/2/2010      1      708      000000012      Denied      Current      Active      8/2/2010
708      000000012      8/2/2010      1      708      000000012      Denied      Modified      Active      8/2/2010
0
 

Author Comment

by:SASnewbie
ID: 34208382

These aren't dups....
0
 

Author Comment

by:SASnewbie
ID: 34208427
I am going to have to look over this some more.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34208575
In general, if you replace the * with the actual list of columns you are selecting, things will have a bit of an improvement.  

In general, if you take the small amount of time that is required to actually list what youa re selecting, you can contribute to readability, understandability, and maintanability.  Also, because the query won't be pulling back extraneous columns, it will cut down on data transfer related time issues.

I notice that you are doing a LEFT OUTER JOIN from the workstation b table to the workstationstep f but you do not seem to be using any columns from the workstationstep f  table.  This is wasted effort that only serves to slow down the process. ;-)

The same is true for the note d table . . . again, wasted effort slowing things down.

See the attached code.
SELECT *
FROM
       (SELECT h.*,
              row_number() over (partition BY h.product ORDER BY maxcreated DESC) rn,
              a.ClientNumber AS Client,
              a.product AS product_num,
              c.Result,
              g.statetypename AS StateType,
              b.status,
              h.maxcreated AS DateCompleted
       FROM   client a
       LEFT JOIN workstation b
       ON     a.productid= b.productid
       LEFT JOIN validationsummary c
       ON     a.productid= c.productid
--       LEFT JOIN note d
--       ON     a.productid= d.productid
       LEFT JOIN SNAPSHOT e
       ON     a.productid= e.productid
--       LEFT JOIN workstationstep f
--       ON     b.workstationid = f.workstationid
       LEFT JOIN statetype g
       ON     e.statetypeid = g.statetypeid
       LEFT JOIN mostrecent h
       ON     a.product = h.product
       WHERE (b.Template = 'MOD2')
       ) sq
WHERE  sq.rn = 1;

Open in new window

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 34208606

the workstation table is an inner join condition!
the note and workstationstep tables are not utilised so remove them!

are you sure that the other table relationships are really "outer" joins....?
could the h.product be replaced by a.product in the partition by clause.... (probably an
inner join condition in that case then)...?
why do you need two occurrences of the Maxcreated column in the ...?
specifying an order by clause on the final select may assist what order do
you want the data returned in...?


select *  
  from
(select h.*  
       , row_number() over (partition by  h.product order by maxcreated desc) rn,  
  a.ClientNumber as Client, a.product as product_num, c.Result, g.statetypename as StateType, b.[status]
, h.maxcreated as DateCompleted

FROM  client a
Inner join (select productid,[status]
              from workstation
             WHERE Template = 'MOD2'  
           ) as b
  on a.productid= b.productid
left join validationsummary c on a.productid= c.productid
--left join note d on a.productid= d.productid
left join snapshot e on a.productid= e.productid
--left join workstationstep f on b.workstationid = f.workstationid
left join statetype g on e.statetypeid = g.statetypeid
left join mostrecent h on a.product = h.product
 ) sq  

where sq.rn = 1
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34208914
@Lowfatspread,

the workstation table is an inner join condition!

How do you figure that?  The author may know the database well enough to know that there may or may not be workstation table rows corresponding to the client table.  If there are no corresponding workstation table rows, you will be eliminating some client table rows.

In light of your suggested improvement though, I would offer two additional options:
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34208940
CLicked the wrong button, the options are below:
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 1200 total points
ID: 34208950
*&^!@*&(#&)!!!!

Code still didn't attach!
Option 1:

SELECT *
FROM
       (SELECT h.*,
              row_number() over (partition BY h.product ORDER BY maxcreated DESC) rn,
              a.ClientNumber AS Client,
              a.product AS product_num,
              c.Result,
              g.statetypename AS StateType,
              b.status,
              h.maxcreated AS DateCompleted
       FROM   client a
       LEFT JOIN 
       (
        SELECT  productid
               ,[status]
        FROM   workstation 
        WHERE  b.Template = 'MOD2'
       ) b
       ON     a.productid= b.productid
       LEFT JOIN validationsummary c
       ON     a.productid= c.productid
--       LEFT JOIN note d
--       ON     a.productid= d.productid
       LEFT JOIN SNAPSHOT e
       ON     a.productid= e.productid
--       LEFT JOIN workstationstep f
--       ON     b.workstationid = f.workstationid
       LEFT JOIN statetype g
       ON     e.statetypeid = g.statetypeid
       LEFT JOIN mostrecent h
       ON     a.product = h.product)
       ) sq
WHERE  sq.rn = 1;


Option 2:

SELECT *
FROM
       (SELECT h.*,
              row_number() over (partition BY h.product ORDER BY maxcreated DESC) rn,
              a.ClientNumber AS Client,
              a.product AS product_num,
              c.Result,
              g.statetypename AS StateType,
              b.status,
              h.maxcreated AS DateCompleted
       FROM   client a
       LEFT JOIN workstation b
       ON     a.productid= b.productid AND
              b.Template = 'MOD2'
       LEFT JOIN validationsummary c
       ON     a.productid= c.productid
--       LEFT JOIN note d
--       ON     a.productid= d.productid
       LEFT JOIN SNAPSHOT e
       ON     a.productid= e.productid
--       LEFT JOIN workstationstep f
--       ON     b.workstationid = f.workstationid
       LEFT JOIN statetype g
       ON     e.statetypeid = g.statetypeid
       LEFT JOIN mostrecent h
       ON     a.product = h.product)
       ) sq
WHERE  sq.rn = 1;

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34209206
8080driver...

>>inner join

you are correct i don't know the data or relations... however since the asker has specified the  WHERE Template = 'MOD2'  
condition the workstation relation has effectively been made into an inner join condition... and so may as well be coded as such.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34209352
Lowfatspread,

Actually, since the constraint is based on a literal, I think the two options I provided still maintain the ability to retrieve all rows from the client table, which is what the author also (Because, isn't that the whole point of doing all those LEFT OUTER JOINs? ;-)

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34210368
yes but not when you have the where clause condition that excludes all "un joined " results on the table... so equivalent to an inner join...
0
 

Author Closing Comment

by:SASnewbie
ID: 34285103
Sorry for the late response.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

660 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