Anyone want to give me your best shot on a SQL query in a stored procedure?
I have an audit table from which I need to pull a user ID for three different events. After that, I need to pull the user's full name for each of those IDs from the employee table. Finally, I need to return these names in a tuple so that it can be easily converted to a different data type on return to a .NET application.
Currently, I just slapped together some nested queries, but it gives me an uneasy feeling at the very edge of sensation as though it is the sound of screaming from a great distance.
If you don't like the nested queries you could always break it out putting intermediate results in temp tables or table variables but that may well be overkill.
SELECT
User.Id,
User.Name,
Audit.EventId
FROM
Audit
INNER JOIN User ON Audit.UserId = User.Id
WHERE
Audit.EventId = @wantedId1
OR Audit.EventId = @wantedId2
OR Audit.EventId = @wantedId3
Add in applicable grouping (e.g. return one User per wanted EventId, return distinct list of Users, etc)
If you don't like the nested queries you could always break it out putting intermediate results in temp tables or table variables but that may well be overkill.
That is rarely going to be the answer, as SQL queries are a place where "ugly but better performing" is still expected. Temp tables and multiple statements just to avoid a nested query is going to be a bad idea in the majority of cases.
I was about to post the details of the query, but I might not have to at all. I've been doing more analysis instead of programming in my programmer / analyst role, and I might be able to cut out the stored procedure entirely.
Users don't know what they want. I WILL TELL YOU WHAT YOU WANT!
SELECT
User.Id,
User.Name,
Audit.EventId
FROM
Audit
INNER JOIN User ON Audit.UserId = User.Id
WHERE
Audit.EventId = @wantedId1
OR Audit.EventId = @wantedId2
OR Audit.EventId = @wantedId3
Add in applicable grouping (e.g. return one User per wanted EventId, return distinct list of Users, etc)
For a query of this kind, you probably should use IN.
SELECT
User.Id,
User.Name,
Audit.EventId
FROM
Audit
INNER JOIN User ON Audit.UserId = User.Id
WHERE
Audit.EventId = @wantedId1
OR Audit.EventId = @wantedId2
OR Audit.EventId = @wantedId3
Add in applicable grouping (e.g. return one User per wanted EventId, return distinct list of Users, etc)
That's close but not quite what they're expecting. For our engineering projects, there are users assigned to specific roles.
The tuple to be returned looks more like ProposingManagerId, ProposingManagerName, EngineeringManagerId, EngineeringManagerName, etc.
If I understand your query correctly, it will return that information but broken into rows such that the receiving C# code would have to reassemble it into the expected format. I was hoping to write a single query and keep it all in a single stored procedure, but that may well be moot now.
That is rarely going to be the answer, as SQL queries are a place where "ugly but better performing" is still expected. Temp tables and multiple statements just to avoid a nested query is going to be a bad idea in the majority of cases.
Depends... for something straightforward then then the nested queries are fine. I've run into cases though that changing it to use temp tables with indexes have resulted in some pretty nice performance improvements.
Sometimes when things get too hairy with crazy nested queries the query optimizer in MSSQL gives up and just uses the clustered index even when there may be an applicable covering index that would perform much better.
@templewulf: Well I had to go with the basics, I have no idea what your tables look like or what info you were returning. I was assuming the idea was to return a list of users who have hits in the audit log for the wanted events. The reassembling note confuses me as well since if the plan is to convert the data into objects you're going to have to do some type of serializing either through tags or something explicit like "user.Name = dataReader.getRow(i).getColumn(j).toString()" (forgive the horrible psuedo-code) or ...
@Infidel: Didn't want to start an IN vs OR war and was flipping a coin :P
@templewulf: Well I had to go with the basics, I have no idea what your tables look like or what info you were returning. I was assuming the idea was to return a list of users who have hits in the audit log for the wanted events. The reassembling note confuses me as well since if the plan is to convert the data into objects you're going to have to do some type of serializing either through tags or something explicit like "user.Name = dataReader.getRow(i).getColumn(j).toString()" or ... (forgive the horrible psuedo-code).
I didn't get to post more details before I discussed on alternate solution with one of the engineers. So, your query is very sensible.
It's actually converted into a DTO for "stakeholders" for each project. Having the query return a tuple that conforms to the standards of the DTO was what I was aiming for.
@templewulf: Well I had to go with the basics, I have no idea what your tables look like or what info you were returning. I was assuming the idea was to return a list of users who have hits in the audit log for the wanted events. The reassembling note confuses me as well since if the plan is to convert the data into objects you're going to have to do some type of serializing either through tags or something explicit like "user.Name = dataReader.getRow(i).getColumn(j).toString()" (forgive the horrible psuedo-code) or ...
@Infidel: Didn't want to start an IN vs OR war and was flipping a coin :P
Query planner will make it moot in efficiency difference but IN is much more maintainable due to only referencing the field once, so is objectively better!
Why, hello there complex.h. You are my new best friend.
On which note: Mathematica's default scaling values for the Fourier transform are interesting. I shall have to restructure to take account of these. Anyone had experience with implementing FFT? (I'm thinking Cooley-Tukey, but I'm open to suggestions)
And yes, writing it is justified. This is for practice.
I tried to write an FFT implementation once.
Actually, let me be honest: It was a DFT with some optimisations. It was terribly slow compared to MIT's FFTW, as you'd expect.
It was a nice thing to have tried.
Especially if you wanted to talk about "twiddle" factors* with a straight face.
How can I suck less at using the db? I never really learned how to use one 'right'. All I know is from one chapter in a PHP book, and an article about normalization which no longer exists on the MySQL site. I've heard of things like triggers, views, transactions, etc., but hell if I know exactly what they are, when I'd want to use them, or even how to build/use them. And that's without getting into more basic things, like @Infidel's suggestion about using IN above.
So, PA Programming Thread, where should I start on the path of not sucking with the db?
PSN/XBL/Nintendo/Origin/Steam: Nightslyr 3DS: 1607-1682-2948 Switch: SW-3515-0057-3813 FF XIV: Q'vehn Tia
If you're not designing the database (schemas, what columns and tables you need, normalization, indexes, etc.) but using it as a programmer then you mainly work with and should focus on joins and query syntax. Know which tools you're using and what brand of SQL you have at your disposal, and learn how to efficiently join tables. I don't have any specific book or site learning recommendations, but that is the area that you should be looking at them for. I just mostly picked it up as I went along.
You'll want to look into normalization and relational algebra to understand why a database should be organized in a certain way, and then learn the how of the SQL specifics.
Relationships and keys and fun stuff like that.
If you design a solid database, you're much more likely to write good queries for it. If you have a horrible design, your queries are already doomed. The creation and query bits are SQL, the design is not, so you want to worry about concepts before you do SQL in any great detail.
Concepts is why "hey Infidel, can you please handle the database and data layer for us, thanks" keeps happening when everyone else is otherwise fairly knowledgeable about SQL.
0
KakodaimonosCode fondlerHelping the 1% get richerRegistered Userregular
There are also a few different ways to design a database and which one you use is going to be dependent upon the applications. A bi-temporal database isn't going to be that great for something that's going to just store a list of transactions. But if you do need to start slicing by timelines and doing temporal queries, they're extremely useful.
There are also a few different ways to design a database and which one you use is going to be dependent upon the applications. A bi-temporal database isn't going to be that great for something that's going to just store a list of transactions. But if you do need to start slicing by timelines and doing temporal queries, they're extremely useful.
Man, this is going to be so much better. Maybe not dream job material, but still a so much more sane company than where I work now. Plus I get to actually learn new stuff and have other developers that I work directly with to learn even more.
It's really good timing, too. I just got an e-mail this morning from our dev team in Hong Kong asking for specs on how I've implemented a bunch of stuff in my platform so that they can copy it in theirs and asking if we can create a way for them to move all of the actual music/image/etc. content from mine to theirs. That sounds a whole lot like the CEO was planning to replace my main platform with this new one that I don't work on (and that doesn't work for shit) asap.
Man, this is going to be so much better. Maybe not dream job material, but still a so much more sane company than where I work now. Plus I get to actually learn new stuff and have other developers that I work directly with to learn even more.
Congratulations!
As someone who recently left a less-than-stellar job, I can tell you that it will feel like a dream job even if it doesn't seem like one on paper.
Man, this is going to be so much better. Maybe not dream job material, but still a so much more sane company than where I work now. Plus I get to actually learn new stuff and have other developers that I work directly with to learn even more.
Congratulations!
As someone who recently left a less-than-stellar job, I can tell you that it will feel like a dream job even if it doesn't seem like one on paper.
Oh I'm sure it will. It's not my dream job, but I know that it's so much better than where I currently work. Working with a team at all, and one that appears competent and organized as a bonus, working with relevant up and coming technology rather than dying tech, and a company that appears to actually value and understand quality.
The only thing I'm going to miss is being able to come to the office with a chain wallet and Lamb of God t-shirt on.
The only thing I'm going to miss is being able to come to the office with a chain wallet and Lamb of God t-shirt on.
Are you sure of that? The new place have a dress code or something?
Congrats on the new job.
Yeah, the new place is a big ass corporation. That's the only down side. I'd prefer to work somewhere smaller. The team had a nice mid-sized company feel to it, though. The dress code wasn't awful by any means, it looks like a polo shirt and any non-jeans is ok. One of the guys who interviewed me was wearing cargo pants if I remember right. So it's not terrible, I just can't roll in looking like I'm on my way to a heavy metal concert.
Yeah, like I said, it's really an up and coming thing here that's gaining a lot of traction very recently. I'll name names after the deal is 100% done, but for now, it's a large company that owns something like 12 tv stations, a ton of newspapers and magazines, and runs websites for all of those plus a few others for 3rd parties.
Capital One is also hiring Django developers locally. I was supposed to interview for that as well, but I haven't heard back about an interview in almost 2 weeks now. I've worked for them before and they aren't high on my list to return to anyway.
Yeah, Turner and Cox both use a lot of Python and Ruby along side traditional .NET/Java "Enterprisey" stuff. Once corporations get that big it depends on the team you're working with, rather than a corporate edict about programming technology.
Every time you write parallel fifths, Bach kills a kitten.
3DS Friend Code: 2707-1614-5576 PAX Prime 2014 Buttoneering!
Posts
I have an audit table from which I need to pull a user ID for three different events. After that, I need to pull the user's full name for each of those IDs from the employee table. Finally, I need to return these names in a tuple so that it can be easily converted to a different data type on return to a .NET application.
Currently, I just slapped together some nested queries, but it gives me an uneasy feeling at the very edge of sensation as though it is the sound of screaming from a great distance.
Nintendo ID: Incindium
PSN: IncindiumX
Add in applicable grouping (e.g. return one User per wanted EventId, return distinct list of Users, etc)
That is rarely going to be the answer, as SQL queries are a place where "ugly but better performing" is still expected. Temp tables and multiple statements just to avoid a nested query is going to be a bad idea in the majority of cases.
Users don't know what they want. I WILL TELL YOU WHAT YOU WANT!
For a query of this kind, you probably should use IN.
Audit.EventId IN (@wantedId1, @wantedId2, @wantedId3)
That's close but not quite what they're expecting. For our engineering projects, there are users assigned to specific roles.
The tuple to be returned looks more like ProposingManagerId, ProposingManagerName, EngineeringManagerId, EngineeringManagerName, etc.
If I understand your query correctly, it will return that information but broken into rows such that the receiving C# code would have to reassemble it into the expected format. I was hoping to write a single query and keep it all in a single stored procedure, but that may well be moot now.
Thanks anyway, though!
Depends... for something straightforward then then the nested queries are fine. I've run into cases though that changing it to use temp tables with indexes have resulted in some pretty nice performance improvements.
Sometimes when things get too hairy with crazy nested queries the query optimizer in MSSQL gives up and just uses the clustered index even when there may be an applicable covering index that would perform much better.
Nintendo ID: Incindium
PSN: IncindiumX
@Infidel: Didn't want to start an IN vs OR war and was flipping a coin :P
I didn't get to post more details before I discussed on alternate solution with one of the engineers. So, your query is very sensible.
It's actually converted into a DTO for "stakeholders" for each project. Having the query return a tuple that conforms to the standards of the DTO was what I was aiming for.
Query planner will make it moot in efficiency difference but IN is much more maintainable due to only referencing the field once, so is objectively better!
Pffth find/replace ya wussy! :P
I tried to write an FFT implementation once.
Actually, let me be honest: It was a DFT with some optimisations. It was terribly slow compared to MIT's FFTW, as you'd expect.
It was a nice thing to have tried.
Especially if you wanted to talk about "twiddle" factors* with a straight face.
* An actual thing.
How can I suck less at using the db? I never really learned how to use one 'right'. All I know is from one chapter in a PHP book, and an article about normalization which no longer exists on the MySQL site. I've heard of things like triggers, views, transactions, etc., but hell if I know exactly what they are, when I'd want to use them, or even how to build/use them. And that's without getting into more basic things, like @Infidel's suggestion about using IN above.
So, PA Programming Thread, where should I start on the path of not sucking with the db?
Switch: SW-3515-0057-3813 FF XIV: Q'vehn Tia
What if I am designing the db, too? I usually have to wear all web dev hats. I seem to be doing okay, so far, but I'm sure I could do much better.
Switch: SW-3515-0057-3813 FF XIV: Q'vehn Tia
Relationships and keys and fun stuff like that.
If you design a solid database, you're much more likely to write good queries for it. If you have a horrible design, your queries are already doomed. The creation and query bits are SQL, the design is not, so you want to worry about concepts before you do SQL in any great detail.
Concepts is why "hey Infidel, can you please handle the database and data layer for us, thanks" keeps happening when everyone else is otherwise fairly knowledgeable about SQL.
C.J. Date's SQL and Relational Theory: How to Write Accurate SQL Code is a good starting book. He does only go over the relational model in this.
http://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/0596523068/ref=sr_1_1?ie=UTF8&qid=1313451138&sr=8-1
Or there's this:
Switch: SW-3515-0057-3813 FF XIV: Q'vehn Tia
I want that book so much now.
Pixie dust and magic.
3DS Friend Code: 2707-1614-5576
PAX Prime 2014 Buttoneering!
Or shit and dung beetles.
Depends on the system.
Haha awesome...
Nintendo ID: Incindium
PSN: IncindiumX
Grats!
Congratulations!
As someone who recently left a less-than-stellar job, I can tell you that it will feel like a dream job even if it doesn't seem like one on paper.
The only thing I'm going to miss is being able to come to the office with a chain wallet and Lamb of God t-shirt on.
Are you sure of that? The new place have a dress code or something?
Congrats on the new job.
Nintendo ID: Incindium
PSN: IncindiumX
That's not common
Capital One is also hiring Django developers locally. I was supposed to interview for that as well, but I haven't heard back about an interview in almost 2 weeks now. I've worked for them before and they aren't high on my list to return to anyway.
3DS Friend Code: 2707-1614-5576
PAX Prime 2014 Buttoneering!