1

SQL Server 2014

I am trying to learn better SQL code practices to improve performance. I am inheriting some old code, and wanted to get an idea of how someone who actually knows what they're doing would improve it.

I have tried to shorten the code to just the main part I'm working on right now. It seems like there is a better way to get the Reason Codes (PersonalTraining, Tennis, etc.) than running multiple Correlated Subqueries? But, so far I'm having trouble getting the results to return on one row per customer.

The final results must stay the same for a 3rd party.

A customer can have zero to multiple Reason Codes that are stored in the table "asajoinmbr"

Table asajoinmbr:

CREATE TABLE [dbo].[ASAJoinmbr](
 [cust_code] [char](10) NOT NULL,
 [mbr_code] [char](10) NOT NULL,
 [reason_code] [char](3) NULL,
 [associate_code] [char](10) NULL,
 [join_note] [ntext] NULL,
 [wants_contact] [char](1) NULL,
 [club] [smallint] NULL,
 [region] [char](4) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

enter image description here

Table asamembr:

CREATE TABLE [dbo].[ASAMembr](
 [cust_code] [nvarchar](10) NULL,
 [mbr_code] [nvarchar](10) NULL,
 [lname] [nvarchar](20) NULL,
 [fname] [nvarchar](20) NULL,
 [status] [nvarchar](6) NULL,
 [bdate] [datetime] NULL,
 [email] [nvarchar](80) NULL,
 [Club] [nvarchar](3) NULL,

) ON [PRIMARY]

Table strcustr:

CREATE TABLE [dbo].[StrCustr](
[cust_code] [nvarchar](10) NULL,
[bridge_code] [nvarchar](10) NULL,
[bus_name] [nvarchar](30) NULL,
[status] [nvarchar](1) NULL,
[phone] [nvarchar](20) NULL,
[address1] [nvarchar](30) NULL,
[address2] [nvarchar](30) NULL,
[city] [nvarchar](20) NULL,
[state] [nvarchar](10) NULL,
[post_code] [nvarchar](10) NULL,
[cntry_abbrev] [nvarchar](3) NULL,
[cust_type] [nvarchar](10) NULL,
[obtained_date] [date] NULL,
[geo_code] [nvarchar](9) NULL,
[email] [nvarchar](80) NULL,
[club] [smallint] NULL,

) ON [PRIMARY]
GO

Any thoughts on improving any part of this code?

select 

SC.Club as [Location],
(LTRIM(RTRIM(SC.Cust_Code))+Mem.Mbr_code) as [Customer Number],
Replace(LTRIM(RTRIM(mem.fname)),'"','-')  as [Customer First Name],
LTRIM(RTRIM(mem.lname)) as [Customer Last Name],
mem.email as [Customer Email Address],

(select 'Personal Training' from ASAJoinmbr ajm where sc.cust_code = 
ajm.cust_code and reason_code = 'PT' group by cust_code) as PersonalTraining,

(select 'Group Fitness' from ASAJoinmbr ajm where sc.cust_code = 
ajm.cust_code and (reason_code = 'GE' or reason_code = 'GF') group by 
cust_code) as GroupFitness,

(select 'Cardio/Weight'from ASAJoinmbr ajm where sc.cust_code = 
ajm.cust_code and (reason_code = 'CT' or reason_code = 'CV' or reason_code = 
'WT') group by cust_code) as CardioWeight,

(select 'Tennis'from ASAJoinmbr ajm where sc.cust_code = ajm.cust_code and 
(reason_code = 'TE' or reason_code = 'TN') group by cust_code) as Tennis,

(select 'Aquatics' from ASAJoinmbr ajm where sc.cust_code = ajm.cust_code 
and reason_code = 'AQ' group by cust_code) as Aquatics

from strcustr SC
INNER JOIN ASAMEMBR MEM 
    on SC.cust_code=MEM.Cust_code 
    and sc.club=mem.Club

Where  
DATEDIFF(year,(mem.bdate),GETDATE() ) >19
and (DATEDIFF(day, convert(date,sc.obtained_date,101),GETDATE() )= 14
        or (DATEDIFF(day, convert(date,sc.obtained_date,101),GETDATE() )=93 
and sc.status='A'))
and mem.status = 'A'
and mem.email is not null 
and  mem.email <>''
and (sc.bridge_code <> '94811' or sc.geo_code <> 'Goldsmbr')
and sc.cust_type<>'E'

group by 
sc.club,sc.cust_code,mem.mbr_code,mem.fname,mem.lname,mem.email

Results: enter image description here

Gemini
  • 109
  • 2
  • 15
  • Instead of the table image provide some DDL. – Chris Albert Dec 14 '17 at 18:57
  • Can you also add some insert statements with sample data? – Chris Albert Dec 14 '17 at 19:14
  • All of those subqueries are very strange. I would use a join for all of those instead of those oddball subqueries. But the real performance problem is your where predicates. You are using functions in there which means it has to run that function for every single row in the table which completely ruins your chances at utilizing indexing. If this was handed to me I would keep as an example of the desired output and start with a brand new query. – Sean Lange Dec 14 '17 at 19:28
  • Yeah, that's basically what I'm doing is starting a new query using what I'm learning about newer proper techniques. I tried using a LEFT JOIN on asajoinmbr with CASE statements for each Reason Code, but then it gives me a row for each [reason_code] per customer (so multiple rows per customer), instead of a single row as in the screenshot. How would you apply the functions in the Where clause elsewhere? In the Joins? Thanks Sean – Gemini Dec 14 '17 at 19:34
  • For example you could change `DATEDIFF(year,(mem.bdate),GETDATE() ) > 19` to `mem.bdate < DATEADD(YEAR, -19, CAST(GETDATE() AS date))` – Chris Albert Dec 14 '17 at 20:07
  • Chris: it's interesting that SQL would process the statement differently by re-writing from the DATEDIFF to DATEADD. I'll have to play with that in the Execution Plan. Thanks! – Gemini Dec 15 '17 at 17:03

0 Answers0