When Telstra were still in NZ (before Vodafone bought them) I tried going into one of their centres to have a face-to-face meeting with them. Even though there was 4 floors of call centre staff that you could see from outside, the receptionist claimed that no one here could help me with my problem, and sat me down on one of their entry foyer couches and gave me a phone so that I could call their standard support number. The best bit was that the call center staff recognised the caller ID and laughed that I was in the same building :|
Best posts made by SeriousEion
-
RE: Telstra: The Vortex of Incompetence
-
RE: FoxPro is ten times faster than MSSql
Nope, no experts here. I have a fairly good understanding of them all these days, but definitely not an expert. Most of us come from MySQL backgrounds and have been slowly corrupted by FoxPro over the years. We initially tried porting it all to MySQL but the rollout went disastrously, so we ended up backtracking and trying all the databases we could to see which would suit our application best, "maximum gain for as little time as possible" kinda thing.
There were a couple of problems with the 1 hour query. The first was that it was "where"ing on one (indexed) column but "group by"ing on two columns. Somehow that triggered MSSQL to sort very slowly by the 'group by' columns, but we didn't really care about the output order, just that itw as grouped. I have a snippet of an execution plan that I took to show someone who thought I was lying about that http://i.imgur.com/cHyZf8R.png and that's probably more than I should be showing. ( I guess I'm a bit dismayed by the MSSQL community too compared to the others, as I thought MSSQL might have a way to turn off sorting of a result like other DBMS's do, and when asking about it I was met with "you're doing it wrong" rather than "no there isn't" or "this is how you might turn off sorting" kinda thing; but that's more a side-note to the technology.)
The other problem with that query was that it was using a concat() for the where clause, which MSSQL realllly doesn't like. Wasn't even a large data set, only roughly 50k rows for this query. Swapping around that query brought its time down to 20s like the other dbms's were to begin with, but brought the others down to 1s.
I guess the one upside to optimising for MSSQL is that in the end it's also help optimise for the other DBMS's too. I'm quite happy chugging away optimising queries, but to be honest, I'd rather be working on the backlog and rolling out new features/bugs ;)
-
RE: Databases are Monogamous
I realise that I'm a bit late to the party here, but I saw this thread in my email digest and though I'd throw my 2c into the pool
We have a 20 year old legacy FoxPro system that had a web frontend added on a few years ago using ODBC which gave us a unique opportunity to migrate to several other databases and just use that ODBC connection for everything. So now we run on FoxPro, MySQL, MSSQL, SQLite and Postgres. We have conversion scripts between them all too.
The interesting part of it all is that we have nightly benchmarking scripts that run to test all the changes to make sure there's not some subtle bug that only appears on one database engine that we weren't expecting. It also times the speeds of everything.
What we found was that Postgres was easily the fastest of all the dbms followed by FoxPro (it's amazing how fast a 20 year old piece of software will run on a nice modern SSD) then SQLite, MySQL and in last place roughly ten times slower than everything else is MSSQL.
So our management has said that MSSQL is clearly the best choice because that's what potential clients want to hear we sell to them. And we've been working for the past few months and will continue for the next few months speeding up those queries for MSSQL rather than picking a decent dbms and getting new features out the door. Argh
-
RE: FoxPro is ten times faster than MSSql
I feel like this thread is quickly turning to the usual "you're doing it wrong" chorus that I get. While it would be easy to descend into a rant of "mssql is garbarge argleblargle" the point I'm mostly making is that MSSQL isn't right for us. Most of the penalty of talking to MSSQL comes in the form of its ODBC driver that seems to impose a 30% penalty just for using it. But that's kind of beside the point that I just really want to work on new features and not have to optimise for a database that isn't really suitable for us.
I do have a unique perspective on different databases and their tools though, so hopefully I can spin this conversation into something more positive. Like the fact that that new query optimiser thing in MSSQL that tells you what indexes you could add to your tables to speed up your queries, is pretty damn amazing. Or that Postgres will reorganise itself on the fly to make its on-disk storage work faster with your common queries which is so cool. Or maybe you want to hear bad stuff, I dunno? :)
What I think would be great to see out there though is some kind of benchmark like those browser benchmarks, where you could run a query on a particular DBMS and see how long it took to run on each. Does anyone know if such a thing exists?
-
RE: FoxPro is ten times faster than MSSql
Yeah, I thought that comment might have attracted a few WTFs from passers by. I should really blog about it.
Basically, we have a subquery-heavy reporting system that uses ODBC to do the database calls, so we keep our code fairly agnostic. Our test machine runs through every report (roughly 300 of them) and times the total time it took to run them all. Each database is restricted to 500 meg of ram just to see how it performs in equal conditions.
We have been working on speeding things up for MSSQL so these numbers are improved on what they previously were, but the times (to the nearest 10s) are
- Foxpro 1,400s
- MySQL 1,660s
- MSSQL 9,370s
- SQLite 1,610s
- Postres 1,280s
To be fair on MSSQL a lot of the slowdown comes down to the ODBC driver. But yeah, that's just how it is. Happy to answer any questions.
-
RE: FoxPro is ten times faster than MSSql
This is a major reason why 500MB is a big smell to me on a query that's taking an hour. But it also sounds like there are much deeper structural issues with the DB.
I think I mentioned before that it was because MSSQL didn't like the string concat in the lookup, which was in there for a Foxpro speed boost, and happened to work acceptably on other DBs. It took just as long when running it on a production server, solely dedicated to MSSQL (32gb ram, SSD, rackspace somethingarather). Changing it to be a proper, non-concat lookup sped it up for everyone (except Foxpro, so that gets to keep the old query)
Everyone seems to be hung up on the 500MiB ram thing (even though mentioned repeatedly that MSSQL isn't even trying to use all of it - which is a pain in the butt) so I'll see if I can rearrange my schedule a bit and get our benchmark tester loaded up on a production-equivalent server this week, with a dataset much larger than the tiny 18GiB we play with. Then we can get some production-equivalent numbers.
-
RE: FoxPro is ten times faster than MSSql
Would it be fair to say that using the dbms that required the least amount of ram for our needs, in order to save costs, be a good thing? At a stretch could you say that lower memory requirements would mean more simultaneous users in the same amount of memory?
As mentioned in the post just before yours, I'll try out bumping the memory for MSSQL (and the others) to 2GiB but I'm not really expecting much improvement considering it's not using all 500MiB its been given at the moment
-
RE: FoxPro is ten times faster than MSSql
I've found this helpful....
Awesome, thanks. That gave us 6 indexes that hadn't come up in testing before.It's pretty schwifty to be able to use the tools/scripts/etc of all the different dbms's in order to improve all the others too. :D Like, MySQL's EXPLAIN keyword has helped track down an optimisation in MSSQL and Postgres a couple of times.
-
RE: FoxPro is ten times faster than MSSql
Yeah, we rely heavily on the relational model for our reporting. ODBC smooths over a lot of the implementation quirks between DBMSs which is nice and gives a standardised way of calling scalar functions which is double-nice. The 5 databases we test on all support ANSI JOINs, and ODBC provides syntax to support outer joins in the rare occasion that we need that.
What I was really hoping would come out of the testing was statements like "database X is really good for our customers who use feature Y the most" but what it did come out with was "database X is better at feature Y than Z but isn't as good at it as database A" or "the mssql odbc driver is penalising us" kinda thing.
-
RE: FoxPro is ten times faster than MSSql
Totally agree that DB engine matters and that's why in our specific case I don't think we should be using MSSQL.
The original aim of the game was to port everything over to anything that wasn't FoxPro and benchmark and pick the best choice accordingly.
TRRWTF is that we're still trying to optimise for MSSQL