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:
When I run my entire report I do something like this to populate my data
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,
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,
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.