I'm planning to start developing a forum website this summer and I'm looking for some advice on what technologies to use and best methods. I have already completed modules at college in asp.net and ado.net but I haven't been shown how to provide user authentication and creation.
My questions ar...
I wonder why my data inside my "mysql" table "general_log" is not writing more record, stock given the last day was yesterday 04/27/2014.
My my.cnf file is as follows:
[mysqld]
open_files_limit=84138
binlog-do-db=cartaodo
binlog-do-db=todasuac
expire_logs_days=25
[mysql] ...
Concatenating nothing with something produces the same something. If you make a meal of just cheese and add nothing else to it, what do you have? The fact your empty hand moved towards the cheese doesn't change that. — FreshPhilOfSO24 secs ago
If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.
The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree.
This makes the index smaller because it's not part of the tree
This means it isn't really useful ...
At SQL Saturday, @brento mentioned that using INCLUDE is no better than including columns explicitly in the index definition
Is there any real reason to use INCLUDE? (question for all)
Example: I want the NC index to be unique. Adding the INCLUDES to the key bollixes this because I have extra columns now. Or I add another index to enforce uniqueness... not.
@SimonRigharts and this
@swasheck and this
@Kermit Brent said this? Well, I've had disagreements with him before on this site in answers.
@gbn Actually, if certain columns already form a unique key, expanding that key with more columns will not eliminate the uniqueness. (Or maybe I misunderstood your point.)
Ah, yes, I was thinking only in terms of current state of data. Of course, a unique index is not just an index but also a constraint and adding more columns would change that constraint in a way that the former key would no longer be guaranteed to be unique. That's what I didn't think about.
@ypercube My suspicion is that the parser treats a(ny number of) leading + characters as a unary plus, which has no effect on anything and so is ignored. Strictly, unary plus only applies to numerics, but + is so overloaded in Transact-SQL it's hard to know.
@gbn Brent says a lot of things, including things that are controversial. In context he is usually correct, or at least arguably so. My guess would be he was saying includes add little over keys for non-unique indexes.
@PaulWhite I turned down a contract in Switzerland recently. Bad climate for contractors in banking. But I'm still in shorts and t-shirt at the office in Malta
@JackDouglas Oracle and PG separate SQL and Programming aspects a lot
You do have the option of CLR programming within T-SQL, but it's not quite as integrated as we might hope, and the whole DBA vs Dev thing means crossover hasn't been great.
Right. T-SQL lacks a way to write .NET code within SSMS, so it is quite "separated" in that respect. More to the point, not many DBAs can/want to write .NET code.
I pity anyone that ever has to migrate a database from one platform to another.
@JackDouglas I don't believe I understand the difference between a script and a language, but anyway, what specifically is not so languagey about T-SQL?
My wife's going to be in the UK shortly. On previous trips, her flight marked the exact moment NZ went into Winter and the UK hit record summer temperatures.
I have a query which is taking too long to execute.
I used database tuning advisor to check this query and it suggested some missing indexes and statistics. The problem is I can't create missing index and statistics on those tables because it will slowdown insert/update and affect other scripts...
I have a query which is taking too long to execute.
I used database tuning advisor to check this query and it suggested some missing indexes and statistics. The problem is I can't create missing index and statistics on those tables because it will slowdown insert/update and affect other scripts...
@AndriyM it feels to me like a series of sql statements with some procedural extensions bolted on, whereas PL/SQL is an ADA-like language with the ability to execute SQL statements. This seems to sum it up.
both ways have their advantages, I'm not doing one down
that said, I far prefer procedural programming in postgres than either
I think sometimes he forgets to qualify statements. None of these things he's saying are always true - that's simply not possible. Are there situations where they make sense? Of course. Are they situations where - were you to implement his suggestion - you would really wish you hadn't? Absolutely.
@PaulWhite oh absolutely - controversy draws traffic
@JackDouglas it's also very specific to applications that can implement an app-layer caching easily, and don't need real-time results. If you are paging Amazon's inventory it's quite unlikely you're going to want to send all 500 million keys to the client and then sort on slightly cheaper CPUs.
It also assumes your workload is CPU-bound. I come across a lot of workloads, and not many are CPU-bound.
@AaronBertrand yeah, equally unlikely you are going to want to implement locking in the app level for all that cached data if there are updates going on
As a DBA, here's the real answer.
The best database platform in the world can be unable to handle the tiniest of loads if you don't have staff that knows how to use it.
A fairly low-end database platform can scale up to huge loads if you have staff that knows it inside and out and works around ...
@Kermit Some of us do stuff every day, quietly. Some of us talk about it
@AaronBertrand I'd use the best tool for the job in hand, based on what I'm trying to do. I'd take advisement by reading stuff like that, but I'd consider things based on my shop
Now, I'm a BI DBA with a few TBs of data and lots of ETL. Few app servers. Qlikview and Cube presentations layers.
@Kermit Let's change a column to nvarchar(2000) to capture some notes. Can't add to key. I would personally make that unique too because it includes ID. More information for the optimiser. But anyway, it's misleading.
@JackDouglas That really outlines it well for me, thank you. And I also went on to read a little on the whole "scripting vs programming" thing (should have done it before asking my question), just to be sure what's the general agreement on the difference is. And now I've got a clear picture of what you meant, so, thanks again.
@gbn I've been building a DW (small enough to qualify as a mart so far) for one side of the business, the other side has QV with hooks into 10+ odd OLTP systems and is also in service as an ETL tool for some outbound customer data feeds. It's lovely.
Well for starters I loved the way he kept reminding people that you shouldn't just go around doing this all over. There were a few audience member who seemed to think he was suggesting TOP = magic and his handling of those people was brilliant.
There are a heap of times where we are looking for loans based on x funding date. So there are many places people use a query to get that funding date and then go collect all of those loans.
@gbn This is what I am saying: when a covering index is what you want, you have a choice between adding a column to the index key, or having it as an include. Putting it in the include has a few benefits, one being that Halloween Protection in DML plans is only required for key modifications, not includes.
@Zane Right, and how does a row goal come into that scenario?
However even though the query will only return 1 funding date SQL Server doesn't know that it's using the stats and giving back 3K estimated rows. If I top 1 that query now it's estimating one and the downstream effects are surprisingly fantastic.
@PaulWhite I never thought that deep. To me, it's just clear separation of key and data, better query plans, slightly more efficient B-tree, and consistency (why have some INCLUDEs, some in the key?)
@Zane Oh. So you have a plan that would be great if the query actually returned heaps of rows, but it's not great for the one row you know will be returned. Ok.
@PaulWhite It's a scenario where it's SELECT Blah From LoanTable WHERE BlahDate = (SELECT TOP 1 MAX(WhateverDate) from Sometable where criteria = x) This is obviously an oversimplified example however I know ahead of time that the sub query will only return 1 row but the estimate is much higher than that. So I add that top 1 and now the estimated row size is only 1.
@Zane I see one of the demos uses TOP (1) on a MAX to prevent an optimizer transformation to a different kind of join. Yes, in general I'm against it. I prefer to write queries that do not fight the optimizer, or rely on things that happen to have some desirable effect today, but which are not guaranteed to work in future.
I say "prefer" for a reason. I have used the same sorts of tricks in real queries, but only when there was no sensible alternative, and right-now performance was more important than maintainability.
I don't plan on just pushing this strategy all over the place and don't plan on telling my coworkers of this strategy. The way some of them use the tools they already have frighten me.
I see where you're coming from and don't disagree, however in this scenario I feel pretty confident that it won't bite me in the ass, and it's definitely fun to play with.
@Zane But the only thing you changed was to put a TOP 1 in front of the MAX. That happened to give you a different plan. What if the next version of the optimizer sees through that trick (as it does for TOP 100 PERCENT) and you get the old slow plan again?
Why, for example, wouldn't you prefer to execute the subquery and store the result in a variable, then execute the outer query with ... WHERE BlahDate = @BlahVariable OPTION (RECOMPILE)?
It's a bit like the "intermediate materialization" example with TOP (big_number) where a simple change to the query does the same job, without any silly tricks.
i.e.
WITH y (ManagerId, theCount) AS
(
SELECT p.ManagerId, COUNT_BIG(*)
FROM HumanResources.Employee p
GROUP BY p.ManagerId
)
SELECT x.EmployeeID, COUNT_BIG(*) AS TheCount
FROM HumanResources.Employee x
JOIN y ON y.ManagerId = x.ManagerId
GROUP BY x.EmployeeId;
I'm glad you enjoyed the session, and took something useful away. I just have my reservations about promoting these "tricks" where some fraction of the audience may not be qualified to use them.
@PaulWhite I definately agree with you there. Some of those people in that session that he was talking magic and in spite of him repeatedly saying. This is just a thing. It may be a good thing or it may be a bad thing. There were certainly people who came out of it with stars in their eyes.
I'm just kinda feeling it out in DEV and it's pretty interesting.
@swasheck Brent was saying (according to reports) that there is rarely much benefit in putting a column in the INCLUDE list rather than putting it in the keys.
@JackDouglas Multi-column indexes do come with multi-column statistics (for the keys only). It's only average density information, but it can be better than nothing.
@PaulWhite so turning your unique index to a non-unique to to make it cover some columns doesn't entirely mess up those statistics, but it is going to make them less useful?
@AaronBertrand Your reputation just saved me from several hours of unnecessary grief while updating an ancient SQL Server 2005 cluster! After an hour of troubleshooting, I saw a suggestion about pausing the inactive node and decided against it until I saw your name against a "this is the best way to do it" comment and thought, hey, if he says so, it must be true...
@PaulWhite oh. i see what you're saying. the uniqueness of each index is different if both are created as unique. i thought he was just saying on the INCLUDE version.
@PaulWhite if the optimizer knows that (a,b) is unique, then you take away that info and tell it that (a,b,c) is unique, it's got less accurate stats (because a,b is really still unique)
@JackDouglas That goes back to something gbn was saying earlier. If you have another constraint or index that enforces uniqueness for a subset of the changed index's keys, that may or may not be ok depending on the optimizer. Ultimately, it seems better to mark indexes that are actually unique as such, and to only have keys that are used to for uniqueness, sorting, and/or searching. INCLUDE is useful to make (some of) those distinctions.
So (a,b) include (c) would be preferable if (a,b) is a key, even if a or b is also a key.
Ultimately, Brent's statement is technically true in some circumstances, but there will almost always be more important considerations. I certainly wouldn't recommend that people ignore the flexibility the INCLUDE clause provides.
@PaulWhite I'd never heard of INCLUDE before today (Oracle doesn't have something similar). I was looking for something just a day or two ago in pg by coincidence for a project I'm working on though. Ended up with a covering index non-unique and a unique PK which was a shame as space is tight
@PaulWhite If I recall correctly(and I don't want to be putting words in his mouth) he wasn't even referring directly to Unique non-clustered indexes. He was instead suggesting you just include them in the index key columns.
Seriously, it's marginal. I was going to VtC as unclear before I noticed Rolando had already answered it (and therefore, understood it clearly). It's also a non-SQL-Server question, and I give them more leeway due to my knowledge and parsing limitations.
I down-voted the question though. It is quite poorly done.
@PaulWhite sure, I'm tending to lean towards that lately too. However, there is almost no actual question in the "question". Pretty hard to see that being useful to any future visitor.
He was talking about for a covering index if it's being used in predicates often then include it in the Key Columns and how if there is a key look up and the columns are small and frequently used then add them to INCLUDE.
I'm planning to start developing a forum website this summer and I'm looking for some advice on what technologies to use and best methods. I have already completed modules at college in asp.net and ado.net but I haven't been shown how to provide user authentication and creation.
My questions ar...
I wonder why my data inside my "mysql" table "general_log" is not writing more record, stock given the last day was yesterday 04/27/2014.
My my.cnf file is as follows:
[mysqld]
open_files_limit=84138
binlog-do-db=cartaodo
binlog-do-db=todasuac
expire_logs_days=25
[mysql] ...
