kz
Member
Registered: 9th Aug 02
Location: Southend, Essex Drives: Mini Cooper S
User status: Offline
|
https://officesnapshots.com/2015/01/14/autotrader-manchester-offices/
Looks like something straight out of the Google offices 
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
Far more impressive than their old place in Lower Earley 
|
Kyle T
Premium Member
Registered: 11th Sep 04
Location: Selby, North Yorkshire
User status: Offline
|
There are some pretty awesome offices out there nowadays, Google certainly kicked off the trend.
Went into the new Pret a Manger offices a few weeks ago and they're amazing - the whole place is like a massive coffee shop, each bit themed like a different country.
Didn't have an Audi Quattro parked up though 
Lotus Elise 111R
Impreza WRX STi
|
chris_uk
Premium Member
Registered: 8th Jul 03
User status: Offline
|
Wish my office was that tidy.. lol
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
All that bullshit and you still can't search for a car by equipment.
If I had the time I'd be screen-scraping the adverts and cross referencing the reg numbers against the original build info.
BMW options are public domain I think?
Ford data is in the parts catalogue
Vauxhall stuff last time I looked wasn't particularly comprehensive but its available in some form.
You wonder what they're up to if they can't even manage that simple job.
[Edited on 03-04-2017 by Ian]
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
quote:
Originally posted by Ian
All that bullshit and you still can't search for a car by equipment.
If I had the time I'd be screen-scraping the adverts and cross referencing the reg numbers against the original build info.
BMW options are public domain I think?
Ford data is in the parts catalogue
Vauxhall stuff last time I looked wasn't particularly comprehensive but its available in some form.
You wonder what they're up to if they can't even manage that simple job.
[Edited on 03-04-2017 by Ian]
The queries required would be phenomenally slow or you'd have a DB full of indexes which in turn would make inserts and deletes really slow.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
You think? If you were being specific on model before you put the options on then the actual set of candidates is very small. Fewer rows than discriminating mileage across the whole table.
Even if you didn't ask the user for it, surely the option would give you manufacturer and therefore cut your table down considerably before you started looking for specific rows in the junction tables? In fact query the junctions first, feature id could be a key and that wouldn't slow down the write, then join on to the human readable stuff when you've already lost most of the data you don't want.
And nothing wrong with indexes, surely the insert issue would be sorted with duplication and you sync when you can afford the locking? No expert on that but quite sure its in no way an obstacle.
Never occurred to me speed would be a problem - result set being small should mean its fast - think of how much data you can quickly overlook. This isn't an expensive query unless you write it so badly that you're looking in Vauxhall for options fitted to Fords. And even then if you were - cruise control for example - surely if you go in the junction first and get a load of manufacturer-specific keys even that isn't anywhere n2 complexity because you only need to visit the table for the rows which you know match your key. You wouldn't look in the whole table for a manufacturer-specific option, very quick and easy to narrow it down.
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
For every single equipment option there would need to be a flag of some sort which would make the database very complex.
To be fair the most efficient way would be to use bit shifting, each option would be denoted by setting a bit on a VERY long integer but even that would be slow if you weren't searching for an exact match.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Wow
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
Can you guess what I do for a living 
|
Generation
Member
Registered: 7th Jul 09
Location: Essex
User status: Offline
|
Insult people?
|
GB123
Premium Member
Registered: 21st Nov 11
Location: Kent
User status: Offline
|
Hpi checks
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Not sure but it doesn't appear to be run big databases
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
Oooo am I about to get schooled by a part-time script kiddy? 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Not really but I find it difficult to believe that encoding bits in a massive integer is more efficient than leaving a DB to manage lots of human readable records with good keys which are well indexed. Surely the DB is designed to do exactly that? Couldn't be arsed programming at bit level to save a few cycles. How many cars are we starting with? Only 460k currently on sale so worst case you're 5% the size of the CS posts table and that has to join to the member table in every thread to tell you who posted, their location, reg date and if it was turned on, post num at that time. Ideally you'd faff about and have the author details in the post table so there was no join but that's just going to cause you concurrency problems later and fundamentally - be arsed - after a few goes the DB has found a way to serve the whole thing in hundredths of a second anyway.
Even if the results were delayed - you search something and go to a holding page while it spends a few seconds doing it - the tech still exists to do it.
Every fairly complex flight query I've ever made, I get a little spinning plane for a bit which is fine.
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
Do you normally dismiss things you don't understand?
|
Generation
Member
Registered: 7th Jul 09
Location: Essex
User status: Offline
|
Can of worms gif 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
I've not dismissed bit level operations, I just think they're faff when a DB will probably manage it for you.
I've also not dismissed searching for cars based on factory options, baffles me why no one has done it because it sounds like a good idea and I don't think query time is the reason.
You could even pre-compute and cache the result sets and have a few Gb of cache with whatever typical queries you thought would come in. Few million combinations but each record set is only 10 cars long so you're not going to worry about storing or querying those.
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
It doesn't matter how many results you request the load on the indexes is the same..
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Load less of an issue if you're precomputing a load of cache content.
You'd also have far fewer rows if you discriminated the cars table using data you hold about the options, so a specific example but there are others, you would only search Ford if you were looking for a specific nav head unit on a breaker (I am to keep for myself). You could also use the data set to min and max the date range so you could discard anything outside the range
So 460k cars becomes 50k Fords becomes 4k in your date range the option was available.
Date indexes very efficiently.
Also read again the one about the results not needing to appear straight away, we're not even that worried about speed but you seem to think it absolutely precludes the idea.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Another example - my own car I ended up buying - was looking for integrated Bluetooth which was an option. Wasn't even listed in the advert, only that I watched the video on the car and saw the phone tab on the screen that I knew it had it fitted. No mention anywhere in the advert but its in the build record which I have in a DB here, which contains the VIN, which is available if you know the reg, which was on the photos.
And because I also wanted a turbo convertible, you'd only need to search two years' worth of cars.
I should probably make something and see how fast it is because I really can't see a reason why its not a good idea, especially not because it might be a bit slow. Which it won't BTW.
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
quote:
Originally posted by Ian
You'd also have far fewer rows if you discriminated the cars table using data you hold about the options, so a specific example but there are others, you would only search Ford if you were looking for a specific nav head unit on a breaker (I am to keep for myself). You could also use the data set to min and max the date range so you could discard anything outside the range
This is why you use bit logic, its a single field with an finite number of combinations and as such fewer distinct values..
Tables with lots of indexes are massively inefficient and having a flag to denote each feature would require an index either for each field or one index across all of the fields either would be cumbersome..
If bit 1 was EPS, bit 2 A/C, bit 4 SatNav and bit 8 Heated Seats
A car with EPS and sat nav would look like this:
1010 (Simply denoted by the value of 10 in the column)
A car with EPS and heated seats would look like this
1001 (Simply denoted by the value of 9 in the column).
it would be a very efficient index and the query extremely simplistic.
The other massive bonus is the onus is on the UI to render it to be human readable and its very little payload from the DB (a number).
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
182 distinct features for Pumas
Feature key is a 4 bit hex word so potentially 65536 distinct features across all Fords in that catalogue. Although probably fewer in practice and you'd know when you imported it whether all hex words were used. Still a loooooooong integer.
I think the approach is excellent in some circumstances BTW, I've always thought restaurants should use this when you build a burger or a pizza. Be far better when the server comes over instead of reeling off a list of cheese, bacon, jalapenos, extra beef, pulled pork, whatever is in the list of n items you'd just give those each a number 2^n of their position in the list - so 1, 2, 4 as you describe then you'd just ask the server for a 157 and the kitchen would either have a chef who can add up or a little app that converted it back to binary and switched in the item name for each bit.
Do think it has some applications.
Might try it with the Puma stuff and see if a 182 bit int gets unwieldy. Also seems a bit of a waste of bits as most cars would only have 30 or so items so although you've the capacity for a number 2^182 you're only ever going to have a far smaller number of car configs. Just had a look - 14202 different spec arrays in 129219 cars built. 14202 being somewhat smaller than 2^182. That's what I was saying about precomputing the results - be no money at all to store 14k record sets of ~10 rows each and they'd dispense very quickly.
Suppose if you were feeling flash you could group the options together and get that integer down a bit. Might work better if you went to a higher base for each bit so you could store 0 is no engine specified, 1 is a 1.4, 2 is a 1.7, 3 is 1.6, not sure if that puts you back in speed in terms of getting the integer out. Or if indeed you'd save anything because you're still leaving massive gaps - you'd need a high base to get all the options stacked in one digit and that introduces waste.
Interestttttttttttinnnnnggggggggggggg
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
In the database you'll want to use a BINARY type for such a massive "number", but you can still BIT shift it (for setting options) and BIT mask it for queries.
I was thinking about 'features' in the broader and generic sense, not specific manufacturer optional items as that would just be barmy for something like Autotrader!
|
DaveyLC
Member
Registered: 8th Oct 08
Location: Berkshire
User status: Offline
|
e.g.
quote:
DECLARE @Feature VARBINARY(MAX) = 0
SET @Feature = @Feature ^ 2048
SET @Feature = @Feature ^ 8192
PRINT @Feature
IF (@Feature & 2048 = 2048) PRINT 'Feature 2048'
IF (@Feature & 8192 = 8192) PRINT 'Feature 8192'
|
