SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Any tips on optimizing this sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnnybax
Starting Member

3 Posts

Posted - 04/16/2013 :  20:59:00  Show Profile  Reply with Quote
Below sql takes about 2 secs to get 10 records. First subquery returns 30K records and second subquery returns 20K records. Tried indexing and was able to bring it down to 2 secs.
Please share if you have any other tips on re-writing this query or making it more efficient.

 SELECT TOP 10 *
FROM   ((SELECT DISTINCT 1                                   AS ToolId,
                         r.date,
                         r.link,
                         r.title,
                         r.summary,
                         COALESCE(ia.name, 'Unknown Author') AS AuthorName
         FROM   vwBlogresults r
                LEFT JOIN Blogauthor ia
                       ON r.id = ia.id
                INNER JOIN BlogSection b
                        ON r.id = b.articleid
         WHERE  b.Sectionid IN ( 1, 2, 3, 4,
                               5, 6, 7, 8,
                               9, 10, 36, 38, 40 )
                AND isnewswire = 0
                AND isnewswire = 0)
        UNION
        (SELECT DISTINCT 3                AS ToolId,
                         r.date,
                         r.link,
                         r.title,
                         r.summary,
                         'Unknown Author' AS AuthorName
         FROM   vwvideoresults r
                INNER JOIN videoSection b
                        ON r.id = b.videoid
         WHERE  b.Sectionid IN ( 1, 2, 3, 4,
                               5, 6, 7, 8,
                               9, 10, 36, 38, 40 ))) res
ORDER  BY date DESC  


Thank you for reading this far.

Edited by - Johnnybax on 04/16/2013 21:52:18

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/16/2013 :  22:32:42  Show Profile  Visit russell's Homepage  Reply with Quote
No one can really say without seeing the DDL for the tables and views and the indexes.

But...you may see performance gains by changing the UNION to UNION ALL (the hard-coded 1 and 3 guarantee it won't change the results) and perhaps by putting the values in the IN clause in a table and joining to it instead of using IN -- which is equivalent to multiple ORs.

Perhaps if you post the execution plan here we can offer more help.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/16/2013 :  22:41:33  Show Profile  Reply with Quote
As Russell says, hard to say but here's some ideas:
- You're just getting your top 10, so you should be able to TOP 10 in each of your UNION elements too (don't forget the ORDER BY inside them as well). That could save a bunch of time on the inner queries if there is a date index.
- Try to remove the need for DISTINCT.
- if your join to BlogSection & videoSection is 1 to 1 then remove DISTINCT
- if not, select DISTINCT b.articleID where sectionID in(...) as a sub-select and join R to that (again, get rid of distinct)

Hope this gives you some pointers
Go to Top of Page

mikgri
Starting Member

39 Posts

Posted - 04/17/2013 :  16:00:24  Show Profile  Reply with Quote
Union statement does a SELECT DISTINCT on the result set.
You don't need to specify distinct keyword on subqueries.
Go to Top of Page

backend
Starting Member

8 Posts

Posted - 04/17/2013 :  17:10:31  Show Profile  Reply with Quote
- Get rid of DISTINCT and try a JOIN. Test/compare speed.

- Derived table large tables
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
91 Posts

Posted - 04/18/2013 :  05:43:27  Show Profile  Reply with Quote
Hi Jonnybax,

Just reduce the number of values IN clause with AND operator.

IN clause will convert to OR clauses results increase the scan count and logical reads for query execution.

Try this!

where ((b.Sectionid >=1 AND b.Sectionid <=10) or b.Sectionid in(36,38,40))


Thanks,

M.MURALI kRISHNA
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/18/2013 :  05:48:35  Show Profile  Reply with Quote
In addition, you may want to check
1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"
2) vwBlogresults and vwvideoresults seems to be views: a) try if you can improve them further 2) Even better if you can revise and accomodate the whole logic in a signle query (without union(s), and views)

Cheers
MIK

Edited by - MIK_2008 on 04/18/2013 05:59:47
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/18/2013 :  12:11:04  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

The join order shouldn't make a difference as the oprimizer is cost-based. But, I agree it's a good habbit to put the outer joins last if possible.

Another thing to check here is how the optimizer is handling the left join. For example I had a very similar query recently, two talbes inner joined and then left outer joined to another table. This simple query took 50 minutes to run (which I got down to about 12 seconds). A couple of things that are possible solutions:
  • Create a temp table for the results of the inner joins then join to the outer table.

  • Proper indexing - Somtimes the optimizer picks a bad plan to due to poor indexes. (PS - If you look at the actual execution plan, that might help guide you to a missing index that'll help solve the issue)

  • Somtimes the optimizer it "too smart" for it's own good and if you do the inner joins first and add a FORCE ORDER hint that will get thing back in shape.



Edited by - Lamprey on 04/18/2013 12:12:48
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/18/2013 :  12:58:48  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

quote:
Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

The join order shouldn't make a difference as the oprimizer is cost-based. But, I agree it's a good habbit to put the outer joins last if possible.



Needs a little more explanation. Actually I mentioned this based on the logic(which a read somewhere in an article). its better to join table in terms of number of records in ascending order.
e.g.
Table1 = 2M records
table2 = 1Mrecords
table3 = 10 records

if my desired data is based on the joining of these three tables and I join them as
table 1 join table2 join table3
then what SQL would do is to pick data of the first join "table1 with table2", let say the resulting records are 1M, and then this data set is joined with table3, resulting the 10 records required.

However if I change the order table3 join table2 join table1. the SQL would only focus on the 10 records of the table 3 and would search only the corresponding (10) records in other two tables. won't it Turn to be faster!!?

Thanks!

Cheers
MIK
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/18/2013 :  13:25:02  Show Profile  Visit chadmat's Homepage  Reply with Quote
This is not correct. The optimizer will evaluate in whatever order it determines is optimal.

-Chad

quote:
Originally posted by MIK_2008

quote:
Originally posted by Lamprey

quote:
Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

The join order shouldn't make a difference as the oprimizer is cost-based. But, I agree it's a good habbit to put the outer joins last if possible.



Needs a little more explanation. Actually I mentioned this based on the logic(which a read somewhere in an article). its better to join table in terms of number of records in ascending order.
e.g.
Table1 = 2M records
table2 = 1Mrecords
table3 = 10 records

if my desired data is based on the joining of these three tables and I join them as
table 1 join table2 join table3
then what SQL would do is to pick data of the first join "table1 with table2", let say the resulting records are 1M, and then this data set is joined with table3, resulting the 10 records required.

However if I change the order table3 join table2 join table1. the SQL would only focus on the 10 records of the table 3 and would search only the corresponding (10) records in other two tables. won't it Turn to be faster!!?

Thanks!

Cheers
MIK

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000