tippingpoint
asked on
Optimize my Union Select SQL statement... 500pts
This is a follow up. My table structure can be found here:
https://www.experts-exchange.com/questions/21210881/SQL-merge-fields-into-column-dependant-on-grouping-in-another-field-and-type.html
I need to make the following code run efficiently, its takign about 15 minutes to run.
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[First name] & ' ' & t1a.[Last name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'client' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'joint' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'company' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'super' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'other' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'partner' AND t1b.grouptype = 'parent';
If you have any questions, let me know.
Thanks.
tp
https://www.experts-exchange.com/questions/21210881/SQL-merge-fields-into-column-dependant-on-grouping-in-another-field-and-type.html
I need to make the following code run efficiently, its takign about 15 minutes to run.
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[First name] & ' ' & t1a.[Last name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'client' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'joint' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'company' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'super' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'other' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'partner' AND t1b.grouptype = 'parent';
If you have any questions, let me know.
Thanks.
tp
ASKER
The tablehas 100,000 records.
My query uses 6 selects, your first one uses 3 and was significantly faster.
I would like to try your second one becuase it only uses 2 commands but I get th error: "Select statement uses a reserved word...."
Also please note (sorry I did a typo in my code) that Client and Partner have the same structure and Joint, Company, Other, Partner share the same.
Your first code would look like this:
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[First name] & ' ' & t1a.[Last name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('client', 'partner') AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('joint', 'company', 'super', 'other') AND t1b.grouptype = 'parent';
Could you please amend and check the second bit of code with my adjustment.
Thanks heaps.
My query uses 6 selects, your first one uses 3 and was significantly faster.
I would like to try your second one becuase it only uses 2 commands but I get th error: "Select statement uses a reserved word...."
Also please note (sorry I did a typo in my code) that Client and Partner have the same structure and Joint, Company, Other, Partner share the same.
Your first code would look like this:
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[First name] & ' ' & t1a.[Last name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('client', 'partner') AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('joint', 'company', 'super', 'other') AND t1b.grouptype = 'parent';
Could you please amend and check the second bit of code with my adjustment.
Thanks heaps.
Sure. Sorry about the error -- it was caused by the extraneous comma following "spare1". Using your adjustment, the second syntax would be:
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & IIf(t1a.grouptype IN ('client', 'partner'), t1a.[First name] & ' ' & t1a.[Last name], t1a.[Company name]) AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('client', 'partner', 'joint', 'company', 'super', 'other') AND t1b.grouptype = 'parent';
Jeff
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & IIf(t1a.grouptype IN ('client', 'partner'), t1a.[First name] & ' ' & t1a.[Last name], t1a.[Company name]) AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('client', 'partner', 'joint', 'company', 'super', 'other') AND t1b.grouptype = 'parent';
Jeff
ASKER
Hi Jeff,
The query works great. Alot faster.
Just one more thing. How do I put this into a new table or existing empty table of the same structure.
Thx
The query works great. Alot faster.
Just one more thing. How do I put this into a new table or existing empty table of the same structure.
Thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, trying it now
I've got another question for 500:
https://www.experts-exchange.com/questions/21232669/Update-column-depending-on-content-in-field.html#12778501
I'm trying to do multiple update commands in one query. It needs to be done in Access. There will end up being about 40 of them.
eg. for column1 update fields
A
B
C
D
if A then 1
if B then 2
if C then 3
if D then 4
Thanks.
I've got another question for 500:
https://www.experts-exchange.com/questions/21232669/Update-column-depending-on-content-in-field.html#12778501
I'm trying to do multiple update commands in one query. It needs to be done in Access. There will end up being about 40 of them.
eg. for column1 update fields
A
B
C
D
if A then 1
if B then 2
if C then 3
if D then 4
Thanks.
The SQL can be streamlined a bit, though I can't be 100% certain how great an effect it will have on performance. Try one of these:
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[First name] & ' ' & t1a.[Last name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype = 'client' AND t1b.grouptype = 'parent';
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & t1a.[Company name] AS spare1
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('joint', 'company', 'super', 'other', 'partner') AND t1b.grouptype = 'parent';
or:
SELECT 2_RI_Converted_DB_New.*, [First Name] & ' ' & [Last name] & ' group' AS newspare1
FROM 2_RI_Converted_DB_New
WHERE grouptype = 'parent'
UNION SELECT t1a.*, t1b.[First name] & ' ' & t1b.[Last name] & ' group : ' & IIf(t1a.grouptype = 'client', t1a.[First name] & ' ' & t1a.[Last name], t1a.[Company name]) AS spare1,
FROM 2_RI_Converted_DB_New t1a INNER JOIN 2_RI_Converted_DB_New t1b ON t1a.[Client group code] = t1b.[Client group code]
WHERE t1a.grouptype IN ('client', 'joint', 'company', 'super', 'other', 'partner') AND t1b.grouptype = 'parent';
You should also check to make sure you have proper indexes in 2_RI_Converted_DB_New. At a minimum, [Client group code] and grouptype need to be indexed. If none of these things get you acceptable performance, let me know. We may need to go at this from a different angle.
Jeff