Speed up SQL Query

Hi
I am running trying to run the SQL query below, without the commented put lines being commented pt -

SELECT 
CL.JBCLID,  
CL.JBCLName,  
CL.JBCLPhone,  
CL.JBCLLogo,  
CL.JBCLAccountType,  
CL.JBCLCompanyType,  
CL.JBCLBillingContact,  
CONVERT(CHAR(11),CL.JBCLDateRegisterred,106) AS JBCLDateRegisterred,  
CL.FreeTrial,  
CL.JBCLMCVSearch,  
IsNull(AP.Applications,0) AS Applications,  
IsNull(LAD.Adverts,0) AS LiveAdverts,  
/*IsNull(AD.Adverts,0) AS TotalAdverts, */ 
IsNull(EM.Employees,0) AS Employees,  
IsNull(OD.Orders,0) AS Orders/*,  
IsNull(CONVERT(CHAR(11),AD.JBADatePosted,106),'Yet to Advertise') AS JBADatePosted */ 
From dbo.JBClient CL 
left join (SELECT AP.JBAPClientID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPClientID) AP on AP.JBAPClientID = CL.JBCLID  
/*left join (SELECT JBAClientID, MAX(JBADatePosted) JBADatePosted, COUNT(JBAID) Adverts FROM (select JBAClientID, JBAID, JBADatePosted from dbo.JBAdvert union all select JBAClientID, JBAID, JBADatePosted from dbo.JBExpiredAdvert) o GROUP BY JBAClientID) AD on AD.JBAClientID = CL.JBCLID */
left join (SELECT LAD.JBAClientID, COUNT(LAD.JBAID) Adverts FROM dbo.JBAdvert LAD Where JBADatePosted >= DATEADD(d,-JBAPostFor,GETDATE()) GROUP BY LAD.JBAClientID) LAD on LAD.JBAClientID = CL.JBCLID  
left join (SELECT EM.JBEClientID, COUNT(EM.JBEID) Employees FROM dbo.JBEmployee EM GROUP BY EM.JBEClientID) EM on EM.JBEClientID = CL.JBCLID  
left join (SELECT OD.JBOClientID, SUM(OD.JBOTokens) Orders FROM dbo.JBOrder OD Where OD.JBOOrderStatus = 'OK' GROUP BY OD.JBOClientID) OD on OD.JBOClientID = CL.JBCLID 
WHERE CL.JBCLSiteID = 30 
ORDER BY CL.JBCLName ASC

Open in new window



runs fine in under a second with lines commented out, however without lines commented out it takes several seconds - 20 plus -

Please advice --

thank you  
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvjaCommented:
Hi

   View the execution plan and see whether it suggests any indexes or not.
 

If so, create the indexes required and run the query.
0
dwe761Software EngineerCommented:
Do you have an index on CL.JBCLID?

When you run this do you get a lot of duplication?
select JBAClientID, JBAID, JBADatePosted from dbo.JBExpiredAdvert
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please post the execution plan of the query/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LowfatspreadCommented:
try this...

ie removed the union all
and put the live advert count into the count of "current" adverts ...

the Union subquery isn't directly matchable the individual queries are more likely to be
and we've reduce the query count by 1 by combining the liveadvert /current advert count...



SELECT 
CL.JBCLID,  
CL.JBCLName,  
CL.JBCLPhone,  
CL.JBCLLogo,  
CL.JBCLAccountType,  
CL.JBCLCompanyType,  
CL.JBCLBillingContact,  
CONVERT(CHAR(11),CL.JBCLDateRegisterred,106) AS JBCLDateRegisterred,  
CL.FreeTrial,  
CL.JBCLMCVSearch,  
coalesce(AP.Applications,0) AS Applications,  
coalesce(p1.liveAdverts,0) AS LiveAdverts,  
coalesce(p1.Adverts,0)+coalesce(p2.adverts,0) AS TotalAdverts,  
coalesce(EM.Employees,0) AS Employees,  
coalesce(OD.Orders,0) AS Orders,  
coalesce(CONVERT(CHAR(11),case when p1.JBADatePosted is null then p2.jbadateposted
                               when p2.jbadateposted is null then p1.jbadateposted
                               when p1.jbadateposted > p2.jbadateposted then p1.jbadateposted
                               else p2.jbadateposted 
                               end
                    ,106),'Yet to Advertise') AS JBADatePosted 
From dbo.JBClient CL 
left join (SELECT AP.JBAPClientID, COUNT(AP.JBAPID) Applications 
               FROM dbo.JBApplication AP GROUP BY AP.JBAPClientID) AP 
  on AP.JBAPClientID = CL.JBCLID  

left outer join (select JBAClientID, count(JBAID) as adverts, max(JBADatePosted) as jbadateposted
                       ,count(case when jbadateposted>=>= DATEADD(d,-JBAPostFor,GETDATE())
                                then jbaid end) as LiveAdverts
                    from dbo.JBAdvert 
                   GROUP BY JBAClientID) as p1
   on  CL.JBCLID = p1.JBAClientID 
left outer join (select JBAClientID, count(JBAID) as adverts, max(JBADatePosted) as jbadateposted
                   from dbo.JBExpiredAdvert 
                GROUP BY JBAClientID) p2 
    on  CL.JBCLID = p2.JBAClientID
  
left join (SELECT EM.JBEClientID, COUNT(EM.JBEID) Employees 
            FROM dbo.JBEmployee EM GROUP BY EM.JBEClientID) EM 
  on EM.JBEClientID = CL.JBCLID  

left join (SELECT OD.JBOClientID, SUM(OD.JBOTokens) Orders 
            FROM dbo.JBOrder OD Where OD.JBOOrderStatus = 'OK' 
           GROUP BY OD.JBOClientID) OD 
  on OD.JBOClientID = CL.JBCLID 

WHERE CL.JBCLSiteID = 30 
ORDER BY CL.JBCLName ASC

Open in new window

0
LowfatspreadCommented:
sorry  DOUBLED UP THE >=
SELECT 
CL.JBCLID,  
CL.JBCLName,  
CL.JBCLPhone,  
CL.JBCLLogo,  
CL.JBCLAccountType,  
CL.JBCLCompanyType,  
CL.JBCLBillingContact,  
CONVERT(CHAR(11),CL.JBCLDateRegisterred,106) AS JBCLDateRegisterred,  
CL.FreeTrial,  
CL.JBCLMCVSearch,  
coalesce(AP.Applications,0) AS Applications,  
coalesce(p1.liveAdverts,0) AS LiveAdverts,  
coalesce(p1.Adverts,0)+coalesce(p2.adverts,0) AS TotalAdverts,  
coalesce(EM.Employees,0) AS Employees,  
coalesce(OD.Orders,0) AS Orders,  
coalesce(CONVERT(CHAR(11),case when p1.JBADatePosted is null then p2.jbadateposted
                               when p2.jbadateposted is null then p1.jbadateposted
                               when p1.jbadateposted > p2.jbadateposted then p1.jbadateposted
                               else p2.jbadateposted 
                               end
                    ,106),'Yet to Advertise') AS JBADatePosted 
From dbo.JBClient CL 
left join (SELECT AP.JBAPClientID, COUNT(AP.JBAPID) Applications 
               FROM dbo.JBApplication AP GROUP BY AP.JBAPClientID) AP 
  on AP.JBAPClientID = CL.JBCLID  

left outer join (select JBAClientID, count(JBAID) as adverts, max(JBADatePosted) as jbadateposted
                       ,count(case when jbadateposted>= DATEADD(d,-JBAPostFor,GETDATE())
                                then jbaid end) as LiveAdverts
                    from dbo.JBAdvert 
                   GROUP BY JBAClientID) as p1
   on  CL.JBCLID = p1.JBAClientID 
left outer join (select JBAClientID, count(JBAID) as adverts, max(JBADatePosted) as jbadateposted
                   from dbo.JBExpiredAdvert 
                GROUP BY JBAClientID) p2 
    on  CL.JBCLID = p2.JBAClientID
  
left join (SELECT EM.JBEClientID, COUNT(EM.JBEID) Employees 
            FROM dbo.JBEmployee EM GROUP BY EM.JBEClientID) EM 
  on EM.JBEClientID = CL.JBCLID  

left join (SELECT OD.JBOClientID, SUM(OD.JBOTokens) Orders 
            FROM dbo.JBOrder OD Where OD.JBOOrderStatus = 'OK' 
           GROUP BY OD.JBOClientID) OD 
  on OD.JBOClientID = CL.JBCLID 

WHERE CL.JBCLSiteID = 30 
ORDER BY CL.JBCLName ASC

Open in new window

0
garethtnashAuthor Commented:
Hi All - sorry about the delayed response, winter cold :(

Sorry - the Query still seems to take forever....

dbo.JBExpiredAdvert doesn't have a PK is that possibly the issue here?

Thanks again
0
LowfatspreadCommented:
it maybe a factor...

what is the reason for it not to have a primary key?

what indexes does the table have?
what indexes are available for all the tables , please indicate the clustering indexes...

and index on the advert tables for the ClientId would probably assist

(viz JBAClientID, JBADatePosted desc,JBAID)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.