Solved

How can I make this query run faster?

Posted on 2010-11-24
21
548 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
  • 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
 

Author Comment

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

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 68

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 200 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 300 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

12 Experts available now in Live!

Get 1:1 Help Now