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 :|
SeriousEion
@SeriousEion
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
Latest posts made by SeriousEion
-
RE: FoxPro is ten times faster than MSSql
The word "cursor" is used in several different contexts.
Whoops, yes. I forgot that there's two different definitions of cursors.We're not using MSSQL cursors, just using raw
SELECT
statements with ODBC which has has the choice of four different cursors to connect to a database (static, dynamic, forward, keyset). Someone tried to explain it as client-side versus server-side cursors, but that kind of confused me more than it helped :) -
RE: FoxPro is ten times faster than MSSql
I've just finished up moving our benchmarking rig to The Cloud last night and was waiting for the first batch of result times to come back before commenting. The new rackspace server comes with quad core Xeons, 4GiB ram and some SSD drive (a massive step up from the dual core 32bit Pentium something it used to be running on), it also has a newer version of MSSQL (v12... which is 2014?) with all the latest service packs. I haven't yet updated MySQL and Postgres to the newer versions yet, so they're still running Postgres 9.4 (one version older than latest) and MySQL 5.6.23 (very old now!). It'd also be interesting to try with the latest MSSQL (v14 2016?) for comparison.
There was, unsurprisingly, across the board improvement. All of the benchmarking used to take 6 hours to run on the old server, and only 2 hours on the new server. I removed the ram limits on the new server. The new benchmarking times (to the nearest second) were:
- Foxpro 613s
- MySQL 847s
- MSSQL 1,382s
- SQLite 945s
- Postgres 617s
We have had an MSSQL contractor working with us for the past few weeks, mostly to set up some 'real world' benchmarking (simulating load, rather than just blindly running each report once like the benchmarking I had set up) but he's also been looking into some of our bottlenecks that we're having with MSSQL. The main one is that the cursors we were using were being inefficient (something about running
sp_cursor
on the server a lot), by switching to forward-only cursors we were now running the queries on the server rather than one cursor lookup per row - maybe that makes more sense to MSSQL peopleSo switching to forward-only cursors gives us about a 20% improvement on MSSQL from what we've seen so far, but it's at the penalty of rewriting all our queries to make sure that all TEXT columns are the last columns in the SELECT, a not-small undertaking - and also means we can't count up the number of results using ODBC functions, we have to do a separate
SELECT COUNT(*)
query. So we are testing speeds with forward-only cursors too, but because there's too many errors the numbers aren't very reliable. -
RE: FoxPro is ten times faster than MSSql
Wut? Outer joins are part of the ANSI standard.
https://msdn.microsoft.com/en-us/library/ms714641.aspx has more info about it. It's mostly for older, crappier ODBC drivers *coughfoxprocough*
-
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
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
Our test system that we do the benchmarking against is only 13GiB, it's pretty small compared to the live systems. Just annoying that MSSQL is only using 300MiB of the 500MiB/2GiB that we're giving it which is a WTF unto itself; when we see MSSQL on other client machines its always trying to steal all the ram on the system. I'm probably going to blame the ODBC driver here again, that maybe it's insistence on server-side cursors causes nothing to be cached?
I tried making a pitch to management a couple of weeks ago to say that we really shouldn't be running with MSSQL but that's still "under discussion" since our sales team want us to sound good by throwing "MSSQL" around everywhere (and hey, that's probably a little fair since they've had to deal with too many years of trying to find a nice way of saying "We use FoxPro")
-
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
Are the primary keys indexed? They should be by default.
Yup yupDo indexes cover the fields you most frequently search/join on?
Almost! There's definitely room for improvement, and as we go through and optimise for MSSQL, that clever query optimiser tool is helping us pick out more/more suitable indexes.Do the indexes cover the right fields?
For the most part, yeah. Again, still room for improvement. They are at least covering the same fields between databases. Sometimes it gets a little tricky when we want the same index setup for two systems but the index might perform better written differently for a different DBMS. Generally if the difference is less than a second we pick the least-worst option and move along looking for bigger improvements elsewhere. -
RE: FoxPro is ten times faster than MSSql
How are the indexes set up?
Not quite sure what you mean here? We set up indexes on all the databases the same with your run of the mill "CREATE INDEX blah ON (blah1, blah2)" statement. -
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?