Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

How can I make this query run faster?

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
SASnewbie
Asked:
SASnewbie
  • 9
  • 5
  • 3
  • +3
2 Solutions
 
Integr8Commented:
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
 
cyberkiwiCommented:
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
 
SASnewbieAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SASnewbieAuthor Commented:
There are indexes for all the tables I am joining to.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
SASnewbieAuthor Commented:
Thanks Qlemo,

Let me try this and get right back.
0
 
SASnewbieAuthor Commented:
It is running quicker, but is not returning only the 1st row number for each product.
0
 
SASnewbieAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
SASnewbieAuthor Commented:
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
 
SASnewbieAuthor Commented:

These aren't dups....
0
 
SASnewbieAuthor Commented:
I am going to have to look over this some more.
0
 
8080_DiverCommented:
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
 
LowfatspreadCommented:

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
 
8080_DiverCommented:
@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
 
8080_DiverCommented:
CLicked the wrong button, the options are below:
0
 
8080_DiverCommented:
*&^!@*&(#&)!!!!

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
 
LowfatspreadCommented:
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
 
8080_DiverCommented:
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
 
LowfatspreadCommented:
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
 
SASnewbieAuthor Commented:
Sorry for the late response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now