• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

union query - what is the best way

what is the best way:

i have sql statements like this: but i want to improve the speed, do you think i can do anything more to improve this query?

my query:
-===========

SELECT    
 contractid = a.contract_id,    
 subid = a.sub_id,          
 EIN = a.client_id,    
 planid = a.plan_id,    
 product = (Select name from pproduction where product_id = a.product_id),      
 servicetype = (Select text from rsctable where codetable_id = 'stype' and code = a.service_type),          
 plantype = (Select text from rsctable where codetable_id = 'ptype' and code = a.plan_type),          
 contractstatus = (Select text from rsctable where codetable_id = 'cstatus' and code = a.status),          
 nbistatus = (Select text from rsctable where codetable_id = 'case_status' and code = b.case_status),          
 casereceived = convert(varchar(20), b.case_received, 101),          
 clientname = c.name + d.name,    
 ed.UserID, LoggedIn = DATEDIFF ( mi , ed.expirytime , getdate() ) , ed.expirytime        
FROM          
 rscon a LEFT OUTER JOIN rsnbi_caseinfo b          
ON  a.contract_id=b.contract_id AND a.sub_id=b.sub_id          
   LEFT  OUTER JOIN rsclient c          
ON  a.client_id=c.client_id          
   LEFT  OUTER JOIN pgclient d          
ON  a.client_id=d.client_id    
   INNER JOIN edusersession ed      
ON   a.contract_id = ed.Contract_ID      
AND  a.sub_id = ed.Sub_ID

UNION       --//using union to combine two query

SELECT      
 contractid = a.contract_id,    
 subid = a.sub_id,          
 EIN = a.client_id,    
 planid = a.plan_id,    
 product = (Select name from pproduction where product_id = a.product_id),      
 servicetype = (Select text from rsctable where codetable_id = 'stype' and code = a.service_type),          
 plantype = (Select text from rsctable where codetable_id = 'ptype' and code = a.plan_type),          
 contractstatus = (Select text from rsctable where codetable_id = 'cstatus' and code = a.status),          
 nbistatus = (Select text from rsctable where codetable_id = 'case_status' and code = b.case_status),          
 casereceived = convert(varchar(20), b.case_received, 101),        
 clientname = c.name + d.name,    
 ed.UserID, LoggedIn = DATEDIFF ( mi , ed.expirytime , getdate() )  , ed.expirytime      
FROM          
 pgcon a LEFT OUTER JOIN rsnbi_caseinfo b          
ON  a.contract_id=b.contract_id AND a.sub_id=b.sub_id          
   LEFT  OUTER JOIN rsclient c          
ON  a.client_id=c.client_id          
   LEFT  OUTER JOIN pgclient d          
ON  a.client_id=d.client_id        
   INNER JOIN edusersession ed      
ON   a.contract_id = ed.Contract_ID      
AND  a.sub_id = ed.Sub_ID    
0
service2001
Asked:
service2001
1 Solution
 
mikelittlewoodCommented:
Because you are taking data from two different sources, even though it is similar information, there isnt much you can do to speed up the process really.

Might be an idea to see if you can remove some of those nested selects to speed up the process.
0
 
service2001Author Commented:
i forgot to write:

INSERT INTO #Temp  
SELECT   .... (whatever i posted above the select statements)

.....
....

SELECT top 3 * FROM #Temp  ORDER BY LoggedIn  

0
 
Brendt HessSenior DBACommented:
You can improve the speed at base by using JOINS instead of nested selects:

SELECT    
 contractid = a.contract_id,    
 subid = a.sub_id,          
 EIN = a.client_id,    
 planid = a.plan_id,    
 product = pp.Name, --(Select name from pproduction where product_id = a.product_id),      
 servicetype = ct.Text, --(Select text from rsctable where codetable_id = 'stype' and code = a.service_type),          
 plantype = ct2.Text, --(Select text from rsctable where codetable_id = 'ptype' and code = a.plan_type),          
 contractstatus = ct3.Text, --(Select text from rsctable where codetable_id = 'cstatus' and code = a.status),          
 nbistatus = ct4.Text, --(Select text from rsctable where codetable_id = 'case_status' and code = b.case_status),          
 casereceived = convert(varchar(20), b.case_received, 101),          
 clientname = c.name + d.name,    
 ed.UserID, LoggedIn = DATEDIFF ( mi , ed.expirytime , getdate() ) , ed.expirytime        
FROM          
 rscon a
LEFT OUTER JOIN rsnbi_caseinfo b
    ON  a.contract_id=b.contract_id AND a.sub_id=b.sub_id          
LEFT  OUTER JOIN rsclient c
    ON  a.client_id=c.client_id          
LEFT  OUTER JOIN pgclient d
    ON  a.client_id=d.client_id    
INNER JOIN edusersession ed
    ON   a.contract_id = ed.Contract_ID      
    AND  a.sub_id = ed.Sub_ID
LEFT JOIN pproduction pp
    ON pp.product_id = a.product_id
LEFT JOIN rsctable ct
    ON ct.code = a.service_type
    AND ct.codetable_id = 'stype'
LEFT JOIN rsctable ct2
    ON ct.code = a.plan_type
    AND ct.codetable_id = 'ptype'
LEFT JOIN rsctable ct3
    ON ct.code = a.status
    AND ct.codetable_id = 'cstatus'
LEFT JOIN rsctable ct4
    ON ct.code = b.case_status
    AND ct.codetable_id = 'case_status'

....

The multiple nested selects usually are significantly slower than JOINS.  

Now, to finish the speedup, try:
INSERT INTO #Temp
SELECT  TOP 3
 contractid = a.contract_id,    
 subid = a.sub_id,          
 EIN = a.client_id,    
 planid = a.plan_id,    
 product = pp.Name, --(Select name from pproduction where product_id = a.product_id),      
 servicetype = ct.Text, --(Select text from rsctable where codetable_id = 'stype' and code = a.service_type),          
 plantype = ct2.Text, --(Select text from rsctable where codetable_id = 'ptype' and code = a.plan_type),          
 contractstatus = ct3.Text, --(Select text from rsctable where codetable_id = 'cstatus' and code = a.status),          
 nbistatus = ct4.Text, --(Select text from rsctable where codetable_id = 'case_status' and code = b.case_status),          
 casereceived = convert(varchar(20), b.case_received, 101),          
 clientname = c.name + d.name,    
 ed.UserID, LoggedIn = DATEDIFF ( mi , ed.expirytime , getdate() ) , ed.expirytime        
FROM          
 rscon a
LEFT OUTER JOIN rsnbi_caseinfo b
    ON  a.contract_id=b.contract_id AND a.sub_id=b.sub_id          
LEFT  OUTER JOIN rsclient c
    ON  a.client_id=c.client_id          
LEFT  OUTER JOIN pgclient d
    ON  a.client_id=d.client_id    
INNER JOIN edusersession ed
    ON   a.contract_id = ed.Contract_ID      
    AND  a.sub_id = ed.Sub_ID
LEFT JOIN pproduction pp
    ON pp.product_id = a.product_id
LEFT JOIN rsctable ct
    ON ct.code = a.service_type
    AND ct.codetable_id = 'stype'
LEFT JOIN rsctable ct2
    ON ct.code = a.plan_type
    AND ct.codetable_id = 'ptype'
LEFT JOIN rsctable ct3
    ON ct.code = a.status
    AND ct.codetable_id = 'cstatus'
LEFT JOIN rsctable ct4
    ON ct.code = b.case_status
    AND ct.codetable_id = 'case_status'
ORDER BY ed.expirytime desc

INSERT INTO #Temp
SELECT TOP 3
 contractid = a.contract_id,    
 subid = a.sub_id,          
 EIN = a.client_id,    
 planid = a.plan_id,    
 product = pp.Name, --(Select name from pproduction where product_id = a.product_id),      
 servicetype = ct.Text, --(Select text from rsctable where codetable_id = 'stype' and code = a.service_type),          
 plantype = ct2.Text, --(Select text from rsctable where codetable_id = 'ptype' and code = a.plan_type),          
 contractstatus = ct3.Text, --(Select text from rsctable where codetable_id = 'cstatus' and code = a.status),          
 nbistatus = ct4.Text, --(Select text from rsctable where codetable_id = 'case_status' and code = b.case_status),          
 casereceived = convert(varchar(20), b.case_received, 101),        
 clientname = c.name + d.name,    
 ed.UserID, LoggedIn = DATEDIFF ( mi , ed.expirytime , getdate() )  , ed.expirytime      
FROM          
 pgcon a
LEFT OUTER JOIN rsnbi_caseinfo b          
    ON  a.contract_id=b.contract_id AND a.sub_id=b.sub_id          
LEFT  OUTER JOIN rsclient c          
    ON  a.client_id=c.client_id          
LEFT  OUTER JOIN pgclient d          
    ON  a.client_id=d.client_id        
INNER JOIN edusersession ed      
    ON   a.contract_id = ed.Contract_ID      
    AND  a.sub_id = ed.Sub_ID    
LEFT JOIN pproduction pp
    ON pp.product_id = a.product_id
LEFT JOIN rsctable ct
    ON ct.code = a.service_type
    AND ct.codetable_id = 'stype'
LEFT JOIN rsctable ct2
    ON ct.code = a.plan_type
    AND ct.codetable_id = 'ptype'
LEFT JOIN rsctable ct3
    ON ct.code = a.status
    AND ct.codetable_id = 'cstatus'
LEFT JOIN rsctable ct4
    ON ct.code = b.case_status
    AND ct.codetable_id = 'case_status'
ORDER BY ed.expirytime desc

-- Note - when selecting from a large TEMP table, adding an index is advisable
-- In this case, you do not need to
SELECT top 3 * FROM #Temp  ORDER BY LoggedIn -- this will now only look at 6 records!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Brendt HessSenior DBACommented:
Let's change the order of the ORDER BY clauses, since you are apparently looking for the oldest, not the newest, records.  Remove the 'desc' clause on the Order By statements
0
 
Scott PletcherSenior DBACommented:
If you don't need to remove duplicate rows, using:

UNION ALL

instead of UNION could also help noticeably; the more rows, the more performance improvement this would be.
0
 
service2001Author Commented:
i will try and get back to you guys
thank you
0
 
service2001Author Commented:
thanks bhess1
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now