QueryBuilder.all() is 30 times slower than java code about fluent-mysql-driver HOT 13 CLOSED

Cool, yeah I'd be interested to see what this looks like on Linux (in release mode). The issues I'm finding online about timegm seem to be related to MacOS but hard to tell for sure

the pleasures of cross platform programing ...

from fluent-mysql-driver.

       // at the call site converted to use straight SQL
       ...
       let start = Date()

       ...
       if let sql = req.db as? SQLDatabase {
            return sql
                .raw("SELECT * FROM `FA_REQUEST_POINT` where `CREATED` > '2020-06-10 04:00:00 +0000'")
                .all()
                .flatMap { (sqlRows) -> EventLoopFuture<[FARequestPoint]> in
                    print("sqlRows: '\(sqlRows.count)' fetched in: '\(start.elapsedTime)'")
                    let points = sqlRows
                        .map { FARequestPoint(sqlRow: $0) }
                        .sorted { $0.created > $1.created }
                    
                    print("points: '\(points.count)' fetched in: '\(start.elapsedTime)'")
                    return req.eventLoop.future(points)
                }.flatMap { (points) -> EventLoopFuture<[TrafficGroup]> in
	        ...

// converted FARequestPoint to struct
// added custom init on a struct
extension FARequestPoint {
    init(sqlRow: SQLRow) {
        self.id = (try? sqlRow.decode(column: "ID", as: Int.self)) ?? 0
        self.created = (try? sqlRow.decode(column: "CREATED", as: Date.self)) ?? Date.distantPast
        self.apiVersion = (try? sqlRow.decode(column: "API_VERSION", as: Int.self)) ?? 0
        self.bytesIn = (try? sqlRow.decode(column: "BYTES_IN", as: Int.self)) ?? 0
        self.bytesOut = (try? sqlRow.decode(column: "BYTES_OUT", as: Int.self)) ?? 0
        self.count = (try? sqlRow.decode(column: "COUNT", as: Int.self)) ?? 0
        self.customerID = (try? sqlRow.decode(column: "CUSTOMER_ID", as: Int.self)) ?? 0
        self.duration = (try? sqlRow.decode(column: "DURATION", as: Int.self)) ?? 0
        self.typeID = (try? sqlRow.decode(column: "TYPE_ID", as: Int.self)) ?? 0
        self.userID = (try? sqlRow.decode(column: "USER_ID", as: Int.self)) ?? 0
    }
}

This cut down the fetch time from 30 seconds to 11.
However the overhead of FARequestPoint.init(sqlRow: SQLRow) now adds 6 seconds to the process.
It seems the SQLRow.decode(column:as:) is now taking way too much cycles.
It boggles me that this compiled code is still way slower than a 10 year old java virtual machine (1.8)

I'm sure swift can do better.
PS.
When I do a profile build I get optimized down to 1.5 seconds.

from fluent-mysql-driver.

For what it's worth though, it's very possible that the slowdown happens on Linux as well, it's well noted that performance has yet to be truly tuned for the drivers (that's not to say it's not worth investigating further :))

from fluent-mysql-driver.

The following benchmarks actually reveal performance that is almost acceptable when running inside a docker on both Mac and Linux.
The overhead of creating the date objects is certainly acceptable.
My next step is to investigate the overhead of creating SQLRow (MySQLRow) instances.
Which remains the largest expenditure.

Thank you for the encouragement.
Let's make swift vapor a great platform.

Benchmarks.txt

from fluent-mysql-driver.

This is almost certainly due to the way the data that you get back from the database is decoded. Fluent uses Mirror to get all the fields of a model and then sets the values of those fields from the data coming in. Mirror is not exactly known for its speed. The good news is that this is fairly low hanging fruit and it shouldn't be too hard to improve the performance of Fluent.

from fluent-mysql-driver.

Since I know swift can do much much better, I went and did some profiling.
Inside MySQLTime.swift, we are spending a huge amount of time in the timegm() call

If I skip converting the self.created date, my code goes down to a somewhat acceptable 0.5 seconds to materialize 60k objects.

So why is timegm() so darn slow ??

    /// Converts this `MySQLTime` to a Swift `Date` using the current calendar and GMT timezone.
    public var date: Date {
        /// For some reason comps.nanosecond is `nil` on linux :(
        let nanosecond: Int
        #if os(macOS)
        nanosecond = numericCast(self.microsecond) * 1_000
        #else
        nanosecond = 0
        #endif
        
        var unixTime = tm()
        unixTime.tm_year = numericCast(self.year) - 1900
        unixTime.tm_mon = numericCast(self.month - 1)
        unixTime.tm_mday = numericCast(self.day)
        unixTime.tm_hour = numericCast(self.hour)
        unixTime.tm_min = numericCast(self.minute)
        unixTime.tm_sec = numericCast(self.second)
        
        let timeStamp = timegm(&unixTime)
        let date = Date(timeIntervalSince1970: TimeInterval(timeStamp) + (TimeInterval(nanosecond) / 1_000_000_000))

        /// For some reason comps.nanosecond is `nil` on linux :(
        #if os(macOS)
        return date
        #else
        return date.addingTimeInterval(TimeInterval(self.microsecond) / 1_000_000)
        #endif

    }

from fluent-mysql-driver.

Wow - so just to clarify, dropping the date conversion improves the time from 11 seconds to .5 seconds or from 30 seconds to .5 seconds? Both are obviously a massive improvement.

from fluent-mysql-driver.

Also perhaps some profiling via instruments would reveal where the cycles are getting eaten up in the MySQLTime stuff specifically?

from fluent-mysql-driver.

apparently this line is extremely slow
lots of people complaining on the google :-)

        let timeStamp = timegm(&unixTime)

from fluent-mysql-driver.

Have you tested this on Linux?

from fluent-mysql-driver.

Wow - so just to clarify, dropping the date conversion improves the time from 11 seconds to .5 seconds or from 30 seconds to .5 seconds? Both are obviously a massive improvement.

Actually my last profiled code was taking 1.25 seconds and .7 were in the timegm() func

from fluent-mysql-driver.

Have you tested this on Linux?

Not yet, I was playing on my Mac for now.
Actually let me do a linux test real quick.

from fluent-mysql-driver.

Cool, yeah I'd be interested to see what this looks like on Linux (in release mode). The issues I'm finding online about timegm seem to be related to MacOS but hard to tell for sure

from fluent-mysql-driver.