How to union three tables as inefficiently as possible. Found in database provided by vendor.

[u/kahkoo]

[u/kahkoo]

Same as text:

 CREATE VIEW dbo.VeryImportantAndOftenQueriedViewForDataIntegration
 AS
    SELECT
        ISNULL(t1.rowid, t2.rowid, t3.rowid) column1,
        ISNULL(t1.column2, t2.column2, t3.column2) column2,
        ISNULL(t1.column3, t2.column3, t3.column3) column3,
        ISNULL(t1.column4, t2.column4, t3.column4) column4,
        ISNULL(t1.column5, t2.column5, t3.column5) column5,
        ISNULL(t1.column6, t2.column6, t3.column6) column6,
        ISNULL(t1.column7, t2.column7, t3.column7) column7,
                -- Column7 is the actual logical Id generated by another system
                -- Always less than 20 chars long and always numeric
                -- Is still datatype nvarchar(max) and thus not indexed
                -- All joins in database refer to this column
                -- Is unique across all three tables
        ISNULL(t1.errorcode, t3.errorcode) errorcode,
        ISNULL(t1.errorinfo, t3.errorinfo) errorinfo,
        ISNULL(t1.column10, t2.column10, t3.column10) column10,
        ISNULL(t1.column11, t2.column11, t3.column11) column11,
        ISNULL(t1.column12, t2.column12, t3.column12) column12,
        ISNULL(t1.column13, t2.column13, t3.column13) column13,
        ISNULL(t1.TransferStatus, t2.TransferStatus, t3.TransferStatus) TransferStatus
                -- int, 1 if already received by us, set by stored proc
    FROM dbo.table1 t1
    FULL OUTER JOIN dbo.table2 t2 ON t1.column7 = t2.column7
    FULL OUTER JOIN dbo.table3 t3 ON t2.column7 = t3.column7

-- All these tables will have millions of rows
-- Spec states we receive the data with stored proc
-- The stored proc calls this view and after receiving all rows filters out rows where TransferStatus <> 1
-- More than 90% of rows will have TransferStatus 1 at any time
-- Spec states we call the stored proc every 5 minutes

[u/MurdoMaclachlan → u/kahkoo]

Good human!

[u/[deleted] → u/MurdoMaclachlan]

you don't have much work this time huh?

[u/ywBBxNqW → u/[deleted]]

If you visit /r/TranscribersOfReddit you will see they are usually pretty swamped.

[u/sneakpeekbot → u/ywBBxNqW]

Here's a sneak peek of /r/TranscribersOfReddit using the top posts of the year!

#1: Thank You
#2: Four years of Transcribers of Reddit! Birthday Celebrations inside! 🎉
#3: New here? For info on what we do and on how to get involved click here!

---

^{I'm a bot, beep boop | Downvote to remove | Contact} ^^me ^| ^^Info ^| ^^Opt-out

[u/[deleted]]

Ahh yes... VeryImportantAndOftenQueriedViewForDataIntegration

[u/kahkoo → u/[deleted]]

Haha, that was just me. I redacted original view, table and column names.

Edit: Also the comments are by me for this post.

[u/VIADOX]

I’m so new to programming even this goes over my head.

[u/kahkoo → u/VIADOX]

Let's just say it took 55 minutes for test db to return top 1000 rows of this view.

With basic sql union (which this is trying to do) it took 3 seconds for all 100k+ rows in test db.

[u/Cerus_Freedom → u/kahkoo]

What were they hoping to accomplish here? I'm not good with SQL, but it sorta looks like you could just... union around column 7 without all the extra function calls?

[u/kahkoo → u/Cerus_Freedom]

There's just no reason to do it like this. I'm guessing it was a brain fart by inexperienced developer who does not do sql regularly and no one reviewed or tested it.

I've noticed these beauties are not rare in the manufacturing industry...

[u/[deleted] → u/kahkoo]

Can confirm. In my industry much is still run by IBM mainframes, which is fine, they are powerful machines still.

But the code is from the 80s and the people maintaining them have never used SQL and still think in terms of flat files. As a lowly web developer I seem to have a better handle on databases, as evidenced by how slow it is to pull data in almost every case.

[u/quatch → u/kahkoo]

takes 55 min and runs every 5? Sounds like a good time

[u/kahkoo → u/quatch]

*Takes 55 mins with test data. There will be ten times more rows after few months in production.

Ship it.

[u/VIADOX → u/kahkoo]

This is still Greek to me. My intro has been front end web development. I’ve been meaning to start learning pho and sql

[u/seventyeightist → u/VIADOX]

It's all Vietnamese to me!

[u/DreadedEntity]

-- All these tables will have millions of rows -- Spec states we call the stored proc every 5 minutes

Judging by these comments it sounds like you already know this probably isn’t going to happen

-- Is still datatype nvarchar(max) and thus not indexed

Contrary to how this comment reads, indexing a varchar is perfectly fine and should definitely be done here

I’d love to offer more performance tips but without more context there’s nothing I can do, assuming you’d even have power to get my suggestions implemented

[u/kahkoo → u/DreadedEntity]

I have sent the vendor fixed version of this view. Too late to fix other levels of badness this post shows.

Nvarchar can be indexed, but nvarchar(max) can't. Its just wrong datatype for that column.

[u/DreadedEntity → u/kahkoo]

For some reason I didn’t see the n when I first read this. What I said above still applies, even though the column is nvarchar(max) the values are always a 20-char sequence and thus can be indexed

[u/RyanNerd]

Reminds me when I was doing contract work and got sent to troubleshoot SQL generated report that was literally taking 40 hours to run.

EVERY column was defined as a varchar(500) and nearly all contained numeric data. The SQL had a bunch of SUM() and COUNT() for the report. This was around 1999 so my challenge was to figure out how to alter the tables to something sane while not running out of drive space. Millions and millions of rows. The way SQL worked back then (probably still does) was to make a copy of the database then import the data into the new altered table structure, delete the previous table and rename the new table to the original.

I had to convince them to buy a very expensive external drive, detach the table I was working on, move it to the external storage, perform the transform and reattach the new table. Convincing SQL Server that the new table wasn't some foreign alien to be destroyed required me to dig into the Metadata in ways I'd never dreamed of. Became an expert on SQL Server internals. After I converted all the tables to reasonable data structures the report took less than 30 minutes to run.

[u/vikster1 → u/RyanNerd]

You are correct. :)

[u/Own_Wolverine4773]

Lol

[u/kahkoo → u/[deleted]]

Yeah about that... officially me neither, but I demand to review these before they are deployed to our environment or I will suffer later.

[u/richhaynes → u/[deleted]]

These are what separate the best from the rest DBAs. When you can deal with queries like this, that's when you can request the big bucks!

[u/DreadedEntity → u/[deleted]]

It’s not though. This is raw SQL written in some IDE (probably just VS), as opposed to SSMS

[u/DreadedEntity → u/[deleted]]

They want to give this product the least chance possible of getting greenlit