[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

SQL Case statement efficiency

I have a transaction table that stores shipment and receipt transactions between customers, vendors and warehouses.

Instead of storing the full customer/vendor/warehouse  names and addresses, I store an ID number.

The problem is that I have three separate tables that store this information:
CUST_TABLE
VENDOR_TABLE
WHSE_TABLE

When I run my entire report I do something like this to populate my data

Select
Serial_number,
Model,
Description,
Case
when trans_type = ‘VENDOR’ then (Select [name] from vendor_table)
when trans_type = ‘CUST’ then (Select [name] from cust_table)
when trans_type = ‘WHSE’ then (Select [name] from whse_table)
else ‘’ end as SHIP_TO,
Case
when trans_type = ‘VENDOR’ then (Select [address] from vendor_table)
when trans_type = ‘CUST’ then (Select [address] from cust_table)
when trans_type = ‘WHSE’ then (Select [address] from whse_table)
else ‘’ end as SHIP_ADDRESS,
TRANSACTION_DATE

from
SN_TRANSACTIONS


This system compiled over the years and we don’t have one location for all the address information. Ideally, I would have liked to put the data in one table but not all the data is uniform and merging the information will mess up the functionality of the entire system.

My query runs a little slow to my liking. The table has 20 million rows as of now and based on the date range I run the query on, it returns 1.5 million rows.

Is there a more efficient way of programming my example above?

I know indexing will be an issue, but I want to tackle my code first before to see if there is a better way.
0
mossmis
Asked:
mossmis
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
mossmisAuthor Commented:
Experts,

I forgot to add where clause to my case statement, here is the modified code:

Select
ST.Serial_number,
ST.Model,
ST.Description,
Case
when ST.trans_type = ‘VENDOR’ then (Select [name] from vendor_table where vend_name = ST.TRANS_TO)
when ST.trans_type = ‘CUST’ then (Select [name] from cust_table where cust_ID = ST.TRANS_TO)
when ST.trans_type = ‘WHSE’ then (Select [name] from whse_table where whse_name = ST.TRANS_TO)
else ‘’ end as SHIP_TO,
Case
when ST.trans_type = ‘VENDOR’ then (Select [address] from vendor_table where vend_name = ST.TRANS_TO))
when ST.trans_type = ‘CUST’ then (Select [address] from cust_table cust_ID = ST.TRANS_TO)
when ST.trans_type = ‘WHSE’ then (Select [address] from whse_table where whse_name = ST.TRANS_TO)
else ‘’ end as SHIP_ADDRESS,
ST.TRANSACTION_DATE

from
SN_TRANSACTIONS ST
0
 
CluskittCommented:
Wouldn't it be easier to just join the tables? Something like:
SELECT ST.Serial_number,
ST.Model,
ST.Description,
CASE ST.trans_type WHEN ‘VENDOR’ THEN VT.Name
  WHEN ‘CUST’ THEN CT.Name
  WHEN ‘WHSE’ THEN WT.Name
  ELSE ‘’ END as SHIP_TO,
CASE ST.trans_type WHEN ‘VENDOR’ THEN VT.address
  WHEN ‘CUST’ THEN CT.address
  WHEN ‘WHSE’ THEN WT.address
  ELSE ‘’ END as SHIP_ADDRESS,
ST.TRANSACTION_DATE

FROM SN_TRANSACTIONS ST
INNER JOIN vendor_table VT
ON VT.vend_name = ST.TRANS_TO
INNER JOIN cust_table CT
ON CT.cust_ID = ST.TRANS_TO
INNER JOIN whse_table WT
ON WT.whse_name = ST.TRANS_TO
0
 
Simone BCommented:
This might speed things up a bit, although you're correct indexing might need a look:

Select
ST.Serial_number,
ST.Model,
ST.Description,
V.[name] AS SHIP_TO,
V.ADDRESS AS SHIP_ADDRESS,
ST.TRANSACTION_DATE
FROM SN_TRANSACTIONS ST
INNER JOIN VENDOR_TABLE V ON ST.TRANS_TO = V.VEND_NAME
WHERE ST.trans_type = 'VENDOR'

UNION ALL

Select
ST.Serial_number,
ST.Model,
ST.Description,
C.[name] AS SHIP_TO,
C.ADDRESS AS SHIP_ADDRESS,
ST.TRANSACTION_DATE
FROM SN_TRANSACTIONS ST
INNER JOIN CUST_TABLE C ON ST.TRANS_TO = C.CUST_ID
WHERE ST.TRANS_TYPE = 'CUST'

UNION ALL

Select
ST.Serial_number,
ST.Model,
ST.Description,
W.[name] AS SHIP_TO,
W.ADDRESS AS SHIP_ADDRESS,
ST.TRANSACTION_DATE
FROM SN_TRANSACTIONS ST
INNER JOIN WHSE_TABLE W ON ST.TRANS_TO = W.whse_name
WHERE ST.TRANS_TYPE = 'WHSE'
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.

 
Dale FyeCommented:
I find it interesting the you are joining the Cust_ID field from your customer table to the Trans_To field in your Transaction table, but for the other two tables, you are using a ???_Name field.  I assume that Cust_ID is a string, not an autonumber or counter field?

Or another option would be to create a union query of the Customer, Vendor, and Warehouse tables into a single view which contains a Trans_Type, Trans_To, and Address fields.  Forgive the image, but my network is blocking my attempt at posting a union query.
sample union queryYou could then join this table to your SN_Transactions table on the Trans_Type and Trans_To fields.
0
 
mossmisAuthor Commented:
I believe that the first response from Cluskit is the best option for me. I will be combining this query with another question I asked here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27987401.html

The unions won't work for me here. I will be testing some options here and post as soon as I get some testing done.
0
 
Dale FyeCommented:
Can you explain why the unions won't work?

Believe those joins in Cluskit's response should probably be Outer Joins (LEFT JOIN) , not INNER JOINs, but I could be wrong.
0
 
CluskittCommented:
LEFT JOIN would only be necessary if the tables didn't have a corresponding ID. That would depend on the code that saves new rows/IDs/etc... if there is a chance that you won't have an equivalence to TRANS_TO in the 3 tables, then change it to LEFT JOIN. Otherwise, INNER works fine.
0
 
Dale FyeCommented:
OK, I'm no SQL Server expert, but if I use an INNER JOIN in Access and attempt to join two different tables, to the same field in a third table, if both of those other tables do not contain a matching foreign key value, then I will get no records returned.

I will only get the record from SN_Transactions if I use Left (outer) JOINs between that table and each of the other tables.
0
 
CluskittCommented:
That's what I said. If there are no corresponding values in the 3 tables, then you need a LEFT JOIN. If, however, you ensure key integrity (usually through code when inserting new values) then you know for sure that there are corresponding values.

Also, sometimes INNER JOIN would be preferable. A missing record might be preferable to a NULL value. It depends on the way your app flows and your specific needs. I personally usually ensure key integrity and use INNER JOINS.
0
 
Dale FyeCommented:
In this instance, it is clear that the [Trans_To] field only applies to a single table for each record in table SN_TRANSACTIONS, not all three tables simultaneously.

Therefore, OP will need to use LEFT JOIN syntax.
0
 
CluskittCommented:
What I understood from his original query was that TRANS_TO had an equivalent ID for each of the 3 tables. Otherwise, the CASE statements would return NULL. But the OP should know his data better and can either use inner or left depending on whether he wants nulls or to exclude the row altogether.
0
 
mossmisAuthor Commented:
Cluskitt had what I was looking for using joins, fyed first mentioned that outer joins would be better than the inner that Cluskitt used and that worked. AS far as using unions, it would have worked, but I was going to use what I learned here with another question I had here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27987401.html

It's rather complex and lengthy to give an overview, but if you are curious as to why I chose what I did, I encourage you to check it out.

Thank you all for your suggestions.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now