corsasport.co.uk
 

Corsa Sport » Message Board » General Chat » AutoTrader - Looks like a cool place to work!


New Topic

New Poll
  <<  1    2  >> Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author AutoTrader - Looks like a cool place to work!
kz
Member

Registered: 9th Aug 02
Location: Southend, Essex Drives: Mini Cooper S
User status: Offline
29th Mar 17 at 14:13   View User's Profile U2U Member Reply With Quote

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
29th Mar 17 at 14:49   View User's Profile U2U Member Reply With Quote

Far more impressive than their old place in Lower Earley
Kyle T
Premium Member

Avatar

Registered: 11th Sep 04
Location: Selby, North Yorkshire
User status: Offline
29th Mar 17 at 15:33   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 8th Jul 03
User status: Offline
30th Mar 17 at 09:54   View Garage View User's Profile U2U Member Reply With Quote

Wish my office was that tidy.. lol
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
3rd Apr 17 at 21:25   View Garage View User's Profile U2U Member Reply With Quote

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]


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
4th Apr 17 at 07:51   View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
4th Apr 17 at 12:20   View Garage View User's Profile U2U Member Reply With Quote

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.


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
4th Apr 17 at 12:24   View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
4th Apr 17 at 13:00   View Garage View User's Profile U2U Member Reply With Quote

Wow


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
4th Apr 17 at 13:07   View User's Profile U2U Member Reply With Quote

Can you guess what I do for a living
Generation
Member

Registered: 7th Jul 09
Location: Essex
User status: Offline
4th Apr 17 at 19:57   View User's Profile U2U Member Reply With Quote

Insult people?
GB123
Premium Member

Registered: 21st Nov 11
Location: Gillingham, Kent
User status: Offline
4th Apr 17 at 20:21   View Garage View User's Profile U2U Member Reply With Quote

Hpi checks
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
5th Apr 17 at 00:49   View Garage View User's Profile U2U Member Reply With Quote

Not sure but it doesn't appear to be run big databases


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
5th Apr 17 at 07:55   View User's Profile U2U Member Reply With Quote

Oooo am I about to get schooled by a part-time script kiddy?
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
5th Apr 17 at 11:47   View Garage View User's Profile U2U Member Reply With Quote

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.


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
5th Apr 17 at 14:58   View User's Profile U2U Member Reply With Quote

Do you normally dismiss things you don't understand?
Generation
Member

Registered: 7th Jul 09
Location: Essex
User status: Offline
5th Apr 17 at 15:24   View User's Profile U2U Member Reply With Quote

Can of worms gif
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
5th Apr 17 at 16:23   View Garage View User's Profile U2U Member Reply With Quote

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.


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
5th Apr 17 at 21:16   View User's Profile U2U Member Reply With Quote

It doesn't matter how many results you request the load on the indexes is the same..
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
6th Apr 17 at 00:54   View Garage View User's Profile U2U Member Reply With Quote

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.


Corsa B lap belts required
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
6th Apr 17 at 01:03   View Garage View User's Profile U2U Member Reply With Quote

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.


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
6th Apr 17 at 08:30   View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Offline
6th Apr 17 at 11:57   View Garage View User's Profile U2U Member Reply With Quote

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


Corsa B lap belts required
DaveyLC
Member

Registered: 8th Oct 08
Location: Berkshire
User status: Offline
6th Apr 17 at 12:24   View User's Profile U2U Member Reply With Quote

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
6th Apr 17 at 12:48   View User's Profile U2U Member Reply With Quote

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'


  <<  1    2  >>
New Topic

New Poll

Corsa Sport » Message Board » General Chat » AutoTrader - Looks like a cool place to work! 23 database queries in 0.0298381 seconds