Small SQL-question

Lorne Kates

Definitely slow, not very intuitive, it as Sales across the top. In your Cases may have a been a custom entity.
Today, I got caught nodding out.

Have fun. We use it at work. The web interface is one where double-clicking means something. Nothing else needs to be said.

Karla

@Lorne-Kates said in Small SQL-question:

@Karla said in Small SQL-question:

Definitely slow, not very intuitive, it as Sales across the top. In your Cases may have a been a custom entity.
Today, I got caught nodding out.

Have fun. We use it at work. The web interface is one where double-clicking means something. Nothing else needs to be said.

Yeah, we have HPCs in to do the initial development for our two main application and we will be left supporting it. Maybe I can excuse myself since my computer does not play nicely with the prerequisites.

The whole web interface is very non-intuitive.

Lorne Kates

@Karla said in Small SQL-question:

The whole web interface is very non-intuitive.

This dumbshit, I assume:

http://i.imgur.com/XoVynUp.png

Did you know that the drop-down next to cases (top bar) is different from the drop-down next to cases (purple)

And that clicking on Cases will also bring up a dropdown ribbon that has different things.

And that everything is main.aspx, so you can't bookmark a report or anything.

And that "Enter search terms" will probably timeout.

And that opening something in a new window on IE, and making a change, will most likely crash the entire browser?

IS THIS COVERED IN YOUR TRAINING?

AHHHHHHHHHHHHHHHHHHHHHHHH!

Groaner

@djls45 said in Small SQL-question:

We're not allowed to modify the database schema

So the system can never change? Sounds like a great opportunity for a competitor.

and for whatever reason, we don't use views or stored procedures

Aaand that's a great way to ensure that the system can never change!

Groaner

@Lorne-Kates said in Small SQL-question:

Solution: replace your DBAs, they're defective.

Usually, these kinds of boneheaded policies arise from lack of a DBA, or from someone who had a bad experience with database feature XYZ in a poorly-designed system twenty years ago and thus feels compelled to ban it forever.

If they're so scared of indexes, my next guess is that there are no clustering keys on any of the tables, so that all accesses escalate to table locks.

Karla

@Lorne-Kates said in Small SQL-question:

@Karla said in Small SQL-question:

The whole web interface is very non-intuitive.

This dumbshit, I assume:

http://i.imgur.com/XoVynUp.png

Yes

Did you know that the drop-down next to cases (top bar) is different from the drop-down next to cases (purple)

And that clicking on Cases will also bring up a dropdown ribbon that has different things.

And that everything is main.aspx, so you can't bookmark a report or anything.

And that "Enter search terms" will probably timeout.

We have an on-premises install...does that help?

And that opening something in a new window on IE, and making a change, will most likely crash the entire browser?

IS THIS COVERED IN YOUR TRAINING?

I probably slept through that part.

I started nodding so much this afternoon, I decided to work on something related to my actual job.

AHHHHHHHHHHHHHHHHHHHHHHHH!

Yeah, I haven't been thrilled with it and if they don't also make sure I get some more .NET projects soon it might be time to start looking for a new position.

djls45

@Groaner said in Small SQL-question:

@djls45 said in Small SQL-question:

We're not allowed to modify the database schema

So the system can never change? Sounds like a great opportunity for a competitor.

Oh, it can change, but only between different release versions of our program and so only by the development programming team, because of testing/approval/auditing rules. I'm on the operations programming team, so we do the specific customization for each instance for our clients using the code hooks that the dev team built into the program. I think we currently have something like seven different major versions (with several minor versions each, due to bugfixes and patches) of the code running on >100 instances for several dozen clients.

Lorne Kates

@Karla said in Small SQL-question:

We have an on-premises install...does that help?

No. Not at all. We've got an on-premises, too.

How much training are they providing for this?!? Someone is milking their consulting fees. For an end user, training is:

Click on the section you want.
Start a new case/customer/sales lead/whatever
Enter the data
Click "assign" or "close" or "convert" as needed
Done

I can see a three day course if you're going to be the one customizing it and building custom workflows. But otherwise .

djls45

@PleegWat said in Small SQL-question:

@Dragnslcr said in Small SQL-question:

If creating indexes might possibly potentially lose data, then your DBMS is so fragile that you're going to lose data eventually anyway.

I've seen it happen - oracle again. The insert sometimes didn't update the index correctly, causing records to not be returned if the index was in the access path.

Our older DBs are Oracle. Our newer ones are SQL Server.

@Groaner said in Small SQL-question:

If they're so scared of indexes, my next guess is that there are no clustering keys on any of the tables, so that all accesses escalate to table locks.

The DB is mostly well normalized, and it does have indices and clustering keys. But not every foreign key relationship is indexed, which I think is the cause of the terrible performance on some of the joins. (And the fact that the One Massive Query is written preferring sub-queries instead of joins, often several levels deep. Some of these queries [there are several One Massive Queries for multiple clients] are a mess!)

Karla

@Lorne-Kates said in Small SQL-question:

@Karla said in Small SQL-question:

We have an on-premises install...does that help?

No. Not at all. We've got an on-premises, too.

How much training are they providing for this?!? Someone is milking their consulting fees. For an end user, training is:

Click on the section you want.

Start a new case/customer/sales lead/whatever

Enter the data

Click "assign" or "close" or "convert" as needed

Done

I can see a three day course if you're going to be the one customizing it and building custom workflows. But otherwise .

It is 5 day. 3 days this week, 2 days next week.

We will be the ones customizing and building custom workflow, and plugins, etc.

The HPCs are doing all of the initial development and then handing it over for us to support. I'm pretty sure it is a fixed price project...and remember we are government it isn't like we will go out of business if it all fails miserably.

This is for 2 different applications.

The bigger one has a history of close to 10 years development and it is being combined with smaller ones that do roughly the same thing.

The other is far simpler and only a few years old and doesn't have the constant change that the first one has.

I can see the benefit of the security model as that first application has extremely complex security.

Groaner

@djls45 said in Small SQL-question:

@Groaner said in Small SQL-question:

@djls45 said in Small SQL-question:

We're not allowed to modify the database schema

So the system can never change? Sounds like a great opportunity for a competitor.

Oh, it can change, but only between different release versions of our program and so only by the development programming team, because of testing/approval/auditing rules. I'm on the operations programming team, so we do the specific customization for each instance for our clients using the code hooks that the dev team built into the program. I think we currently have something like seven different major versions (with several minor versions each, due to bugfixes and patches) of the code running on >100 instances for several dozen clients.

Okay, that sounds much more sane given such a distributed situation.

CoyneTheDup

SELECT tc.C3
FROM t AS tc
     LEFT JOIN
     t AS ta
     ON tc.C3 = ta.C1
     LEFT JOIN
     t AS tb
     ON tc.C3 = tb.C2
WHERE ta.C1 IS NULL
  AND tb.C2 IS NULL