Solved

SQL Case statement efficiency

Posted on 2013-01-09
12
480 Views
Last Modified: 2013-01-15
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
Comment
Question by:mossmis
  • 4
  • 4
  • 3
  • +1
12 Comments
 

Author Comment

by:mossmis
ID: 38759638
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
 
LVL 18

Accepted Solution

by:
Cluskitt earned 400 total points
ID: 38759691
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
 
LVL 11

Expert Comment

by:Simone B
ID: 38759722
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38759801
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
 

Author Comment

by:mossmis
ID: 38760027
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 38760060
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:Cluskitt
ID: 38760479
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38760609
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38760680
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38760711
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38760822
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
 

Author Closing Comment

by:mossmis
ID: 38778609
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now