TSQL Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TSQL Optimization

Author

Topic

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2011-09-27 : 14:34:37

Hi Guys,

Could you please take a look at the query below and let me know how can I optimize it... I will check the indexes. I am pretty new to SQL Server.. wanted to see what changes I can do in the query that is obvious to you guys...

Thanks,
Laura

SELECT      TOP 300 p.member_id, p.ssn,
                                          p.prefix, p.fname, p.mname, p.lname, p.suffix,
                                          p.birthdate, p.coast_guard_ind, pe.email,
                                          p.create_date, uv.fname + ' ' + uv.lname AS created_by, p.update_date, uv2.fname + ' ' + uv2.lname AS updated_by
                  FROM  member p
                        LEFT JOIN member_email pe ON pe.member_id = p.member_id
                              AND pe.active_ind = 1
                        LEFT JOIN vw_users uv ON uv.user_id = p.created_by
                        LEFT JOIN vw_users uv2 ON uv2.user_id = p.updated_by
                  WHERE 1=1
                              AND   p.create_date BETWEEN '2011-07-18 00:00:00.0' AND '2011-09-22 23:59:59.999'
                        AND
                              (
                                    p.first_association_code = 'GDM'
                                    OR
                                    EXISTS
                                    (
                                          SELECT 'true'
                                          FROM  member_course pc
                                                INNER JOIN ps_course c ON pc.course_id = c.course_id
                                                INNER JOIN ps_association o ON o.org_id = c.org_id
                                          WHERE p.member_id = pc.member_id
                                                AND o.sponsor_code = 'GDM'
                                                AND pc.active_ind = 1
                                    )
                                    OR
                                    EXISTS
                                    (
                                          SELECT 'true'
                                          FROM  member_association po
                                                INNER JOIN ps_association o ON o.org_id = po.association_id
                                          WHERE p.member_id = po.member_id
                                                AND o.sponsor_code = 'GDM'
                                                AND po.active_ind = 1                                 
                                    )
                              )
                        AND p.active_ind = 1                      
                  ORDER BY p.lname, p.fname

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 15:43:52

I don't see any obvious problems with the code. I would think that with proper keys and indexing this should perform well. Are you experiencing a problem?

If you need help you should post the DDL for all these objects including keys and indexes. Then post the execution plan and some statistics would be nice too:

--to get the plan
set showplan_text on

--to show statistics
set showplan_text off
set statistics io on
set statistics time on

Be One with the Optimizer
TG

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2011-09-27 : 15:48:14

Thanks for the response TG. I think the problem was with correlated queries... I took that out and CTE. Worked much faster.. I need to test further. Thanks a lot.

quote:
Originally posted by TG

I don't see any obvious problems with the code. I would think that with proper keys and indexing this should perform well. Are you experiencing a problem?

If you need help you should post the DDL for all these objects including keys and indexes. Then post the execution plan and some statistics would be nice too:

--to get the plan
set showplan_text on

--to show statistics
set showplan_text off
set statistics io on
set statistics time on

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 20:02:24

The OR conditions may be slow. Try three queries and a UNION instead?



SELECT TOP 300 *
FROM
(
SELECT      TOP 300 p.member_id, p.ssn,
          p.prefix, p.fname, p.mname, p.lname, p.suffix,
          p.birthdate, p.coast_guard_ind, pe.email,
          p.create_date, uv.fname + ' ' + uv.lname AS created_by, p.update_date, uv2.fname + ' ' + uv2.lname AS updated_by
  FROM  member p
        LEFT JOIN member_email pe ON pe.member_id = p.member_id
              AND pe.active_ind = 1
        LEFT JOIN vw_users uv ON uv.user_id = p.created_by
        LEFT JOIN vw_users uv2 ON uv2.user_id = p.updated_by
  WHERE 1=1
              AND   p.create_date BETWEEN '2011-07-18 00:00:00.0' AND '2011-09-22 23:59:59.999'
        AND
      (
            p.first_association_code = 'GDM'
      )
        AND p.active_ind = 1                      
  ORDER BY p.lname, p.fname
UNION
SELECT      TOP 300 p.member_id, p.ssn,
          p.prefix, p.fname, p.mname, p.lname, p.suffix,
          p.birthdate, p.coast_guard_ind, pe.email,
          p.create_date, uv.fname + ' ' + uv.lname AS created_by, p.update_date, uv2.fname + ' ' + uv2.lname AS updated_by
  FROM  member p
        LEFT JOIN member_email pe ON pe.member_id = p.member_id
              AND pe.active_ind = 1
        LEFT JOIN vw_users uv ON uv.user_id = p.created_by
        LEFT JOIN vw_users uv2 ON uv2.user_id = p.updated_by
  WHERE 1=1
              AND   p.create_date BETWEEN '2011-07-18 00:00:00.0' AND '2011-09-22 23:59:59.999'
        AND
            EXISTS
            (
                  SELECT 'true'
                  FROM  member_course pc
                        INNER JOIN ps_course c ON pc.course_id = c.course_id
                        INNER JOIN ps_association o ON o.org_id = c.org_id
                  WHERE p.member_id = pc.member_id
                        AND o.sponsor_code = 'GDM'
                        AND pc.active_ind = 1
            )
        AND p.active_ind = 1                      
  ORDER BY p.lname, p.fname
UNION
SELECT      TOP 300 p.member_id, p.ssn,
          p.prefix, p.fname, p.mname, p.lname, p.suffix,
          p.birthdate, p.coast_guard_ind, pe.email,
          p.create_date, uv.fname + ' ' + uv.lname AS created_by, p.update_date, uv2.fname + ' ' + uv2.lname AS updated_by
  FROM  member p
        LEFT JOIN member_email pe ON pe.member_id = p.member_id
              AND pe.active_ind = 1
        LEFT JOIN vw_users uv ON uv.user_id = p.created_by
        LEFT JOIN vw_users uv2 ON uv2.user_id = p.updated_by
  WHERE 1=1
              AND   p.create_date BETWEEN '2011-07-18 00:00:00.0' AND '2011-09-22 23:59:59.999'
        AND
            EXISTS
            (
                  SELECT 'true'
                  FROM  member_association po
                        INNER JOIN ps_association o ON o.org_id = po.association_id
                  WHERE p.member_id = po.member_id
                        AND o.sponsor_code = 'GDM'
                        AND po.active_ind = 1                                 
            )
        AND p.active_ind = 1                      
  ORDER BY p.lname, p.fname
) AS X
  ORDER BY lname, fname

Not sure if I can get away with those ORDER BY on each union'd query, or if they each need to be wrapped by


SELECT *
FROM
(
 ... query ...
) AS X

I recommend you take the hyphens out of the dates - they are not fully un-ambiguous with hyphens, i.e. change


'2011-07-18 00:00:00.0'


'20110718 00:00:00.0'

SQLNOVICE999
Yak Posting Veteran

62 Posts

Posted - 2011-09-27 : 23:48:38

Thanks a lot Kristen... this is something I did using CTE. But this is very helpful. I will compare both and use the best one. THanks again for taking time to help me on this.

Laura.

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 03:27:54

I'm a bit sceptical that the UNION will be faster. But it might ...

If not post the Query Plan for your original query:


set showplan_text on
GO
... your query ...
GO
set showplan_text OFF
GO

or before you execute it in SSMS turn on Actual Query Plan and see if that shows any recommended indexes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources