Sorry, you'll have to enlighten me. Just got back from working in the pub and there were many chat notifications - a few saying I should be hired and others saying that there was beer available. I like both things :D
Wondering if I should ask about key rotation of encrypted data here or on another site. (I have asked here on DBA but having second thoughts, maybe programmers would be a better fit?)
I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database. The reason is that local logins will sometimes be required to machines as much as they are discouraged and we need to be able to look up root passwords. Access of...
Refer to Database Design solutions , there should be no null in Your DB , Except One Situation.
It Shouldn't Have Null , Because Reporting , And Writing Query in order to Pull Out Reports would be more, and there Would be more load on Engine.
You can make another table , that is Called 'Details o...
@ChrisTravers Where exactly?
Anyway, your key rotating Q is not quite clear to me
I am making a ecommerce site. In this site I want to categorise the items into three different layers
primary category sub category sub category
>>electronic >>smart phone samsung
cameras tablets nokia ...
I am going to disagree on large and complicated queries with datagod here. I see these only as problems if they are disorganized. Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information. However, large queries do ...
got voted down due to preferences over long vs short queries given that it appears to have been downvoted at the same time a post criticizing maintainability of long queries was upvoted.
As for my key exchange one, I want to rotate encryption keys but the question is what the best way of doing this is so that we can ensure that users can, in fact, access the sensitive information after a key change.
do we just email out new keys? (I would rather avoid that) or maybe it would be better to use public key encryption of a symmetric key?
@dezso I have concluded the difference is this: In theory a sql statement acts within a single state, so you can assume no state changes within the context of a single query.
views and CTEs are magic and can render big joins involving terrible pivots (well, crosstabs is PG) an easy pie (at least until some 'clever' query formatter does not ruin the whole lot with superfluous parentheses and arbitrarily placed whitespace)
this means you start looking at a 400 line queries by saying "may be either a bad filter or a bad join condition" and that narrows the likely block of code usually to a few lines....
Hm. "Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information."
I spend more than half my life simplifying over-complex SQL queries. Big queries give the optimizer more opportunities, in some cases, but much more frequently they give it scope for poor decisions. Big queries -> big plan search space -> more heuristic decisions and also the quality of cardinality estimations tends to decline exponentially with plan complexity.
The particular SQL in the question looks horrible to my eyes. Perhaps MySQL does not support EXISTS? In any case, it looks like SQL written by someone very new to the language, whose primary (only?) tool is IN.
@PaulWhite When you say simplifying are you talking about breaking up into multiple stages/round trips, or rewriting complex queries into simpler but still single queries?
My recommendation for complex queries is to move from MySQL to PostgreSQL in part because if you have big plan search space, geqo gives you some options for tuning heuristics thresholds, etc.
@ChrisTravers Typically multiple simpler queries in a stored procedure or function, using temporary tables to store intermediate results. Getting statistics on the intermediate results is often an easy win. Of course I am a fan of writing good SQL as well :)
I have NULLs in my database and I still can build nice and performant reports on top of it... At the same time, I don't see how a NULL would cause 'more load on Engine'. — dezso2 hours ago
@ConcernedOfTunbridgeWells Awesome answer btw on the NoSQL vs rdbms scalability question.
@AaronBertrand I sometimes use UDF's in check constraints. They are a wonderfully powerful took but a dangerous one to be used sparingly as a tool of last resort. They simplify RI checking when DRI is not sufficient for example.
@ChrisTravers Sorry, I was busy elsewhere. It's not so much length (SQL statements can become quite long for good reasons) as it is looking for 'red flags' either in the query text or in the execution plan.
@ChrisTravers if you know all of the potential problems, ok, but I would never recommend it on the site because people will run with it, think they're ok to use everywhere, and then get bitten and blame me
@AaronBertrand Part of the problem is hand-rolled referential integrity isn't the easiest thing in the world. If you have to go there, all of the tools can bite you.
@ChrisTravers but most of them don't randomly fail to enforce things in certain situations (which most of the audience of StackOverflow will never comprehend).
@ChrisTravers no I mean instead of a check constraint that doesn't use a function for no good reason other than "let's put all of our simple logic in a function"
@ChrisTravers and still I think a check constraint using a UDF would be my last option on earth to enforce anything. Of course you can bypass any checking you implement if you're determined, but it's the most brittle of all the options IMHO. I don't know how long you've been working with SQL Server but if you'd been bitten by this I think you'd be less of a proponent.
@ChrisTravers It sort of needs an answer like that to direct fanboi arguments to. If you suggest 'go and read this first before blathering on about web scale' (or words to that effect) then it becomes a FAQ with a go-to answer.
The tooltip on that page says:
Questions with the most links
It's not restricted to "closed as dup" links - any question from the same site that is mentioned (in the question, answers or comments) gets "Linked".
@ConcernedOfTunbridgeWells I have some (still vague) requirements for a project that includes "cohort analysis" of web analytics data. Is that an area you have worked?
@ypercube I'm pretty sure GA has a stream analysis tool built into it. You set up beacons in the site that GA uses to collect the stats and GA lets you do various types of analysis on them.
I've never set GA up on a site, although I did a gig once where they had.
I don't see how a dynamic SQL statement affects the execution plan... All you are doing is writing the statement to be executed dynamically, then executing it. — Aarolama Bluenk1 min ago
I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a gap between them. Once there is a gap of more than 30 days the chain is broken and does not cont...
@ypercube Quite possibly, yes. I must admit I didn't think about the problem very hard at all. At first I was hoping to give the guy the tool to do the job rather than present a completely finished solution, but responding to his comment I suppose I've kinda moved in that direction. Perhaps I'll look again later. I just can't get excited about the problem for some reason.
I am making a research about Machine Learning and planning to use NoSQL databases. I know that MongoDB is a powerful solution.
However I want to learn that where Hadoop stands for MongoDB side.
My question is that: Does it a core engine for MongoDB or can I use Hadoop itself without using Mong...
I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database. The reason is that local logins will sometimes be required to machines as much as they are discouraged and we need to be able to look up root passwords. Access of...
I am making a ecommerce site. In this site I want to categorise the items into three different layers
primary category sub category sub category
>>electronic >>smart phone samsung
cameras tablets nokia ...
The tooltip on that page says:
Questions with the most links
It's not restricted to "closed as dup" links - any question from the same site that is mentioned (in the question, answers or comments) gets "Linked".
I am making a research about Machine Learning and planning to use NoSQL databases. I know that MongoDB is a powerful solution.
However I want to learn that where Hadoop stands for MongoDB side.
My question is that: Does it a core engine for MongoDB or can I use Hadoop itself without using Mong...
