Project taking too long to build and run after adding complex query

Question

I have integrated SQLite.swift framework in one of my swift project and everything was working fine until i added the below query. After adding the below query project is taking too long to build. I waited for 30 mins but still project is n't complied.

do
{
let stmt = try DB!.prepare ("SELECT e." + ENDPOINT_ID + " as _id, lk." + HUB_ID + ", e." + X_ENDPOINT_ID + ", e." + ENDPOINT_DESC + ", e." + ENDPOINT_TYPE_ID +", et." + ENDPOINT_STATUS_MIN + ", et." + ENDPOINT_STATUS_MAX + ", e." + ENDPOINT_STATUS + " FROM " + TABLE_ENDPOINT + " as e INNER JOIN " + TABLE_ENDPOINT_TYPE + " as et " +
" ON e." + ENDPOINT_TYPE_ID + " = et." + ENDPOINT_TYPE_ID +
" INNER JOIN " + TABLE_LINKING + " as lk " +
" ON e." + ENDPOINT_ID + " = lk." + ENDPOINT_ID +
" INNER JOIN " + TABLE_NODE + " as n " +
" ON lk." + NODE_ID + " = n." + NODE_ID +
" INNER JOIN " + TABLE_NODE_TYPE + " as nt " +
" ON n." + NODE_TYPE_ID + " = nt." + NODE_TYPE_ID +
" WHERE lk." + SECTION_ID + "=" + section_Id +
" AND nt." + NODE_CATEGORY + " = "S" " +
" ORDER BY e." + ENDPOINT_ID + " ASC")

        let arr = Array(try stmt.run)

        print("\(arr)")
        return arr
    } catch {
        print("failed: \(error)")
        return []
    }

If i comment the above code and try to run the project it takes hardly a minute to run the project but after adding this code, it's taking hell lot of time. I have waited for almost 30 mins but still the project is not compiled nor it's throwing any error. Thanks in advance for help

score 0 · Answer 1 · answered Jul 06 '17 at 06:17

0

The Swift compiler has difficulties to deal with big string literals. See also this question for further hints.

I would recommend to split up the sql and to build a string variable in small steps:

var sql = "SELECT e." + ENDPOINT_ID
sql = sql + HUB_ID + ", e."
sql = sql + X_ENDPOINT_ID + ", e."
...
let stmt = try DB!.prepare(sql)

answered Jul 06 '17 at 06:17

zisoft

22,770
10
62
73

Why use any string concatenation? Use a single string and string interpolation or use a string format. – rmaddy Jul 06 '17 at 06:20
Sure, the are always multiple ways to solve a problem. This is one of them. Building a single string with 20+ variables it it to interpolate is IMHO not a good choice from a maintability aspect. – zisoft Jul 06 '17 at 06:29

score 0 · Accepted Answer · answered Jul 11 '17 at 13:17

  let query = (TABLE_ENDPOINT.select(TABLE_ENDPOINT[ENDPOINT_ID], TABLE_LINKING[HUB_ID], TABLE_ENDPOINT[ETCT_ENDPOINT_ID], TABLE_ENDPOINT[ENDPOINT_DESC], TABLE_ENDPOINT[ENDPOINT_TYPE_ID], TABLE_ENDPOINT_TYPE[ENDPOINT_STATUS_MIN], TABLE_ENDPOINT_TYPE[ENDPOINT_STATUS_MAX], TABLE_ENDPOINT[ENDPOINT_STATUS]).join(TABLE_ENDPOINT_TYPE, on: TABLE_ENDPOINT[ENDPOINT_TYPE_ID] == TABLE_ENDPOINT_TYPE[ENDPOINT_TYPE_ID]).join(TABLE_LINKING, on: TABLE_ENDPOINT[ENDPOINT_ID] == TABLE_LINKING[ENDPOINT_ID]).join(TABLE_NODE, on: TABLE_LINKING[NODE_ID] == TABLE_NODE[NODE_ID]).join(TABLE_NODE_TYPE, on: TABLE_NODE[NODE_TYPE_ID] == TABLE_NODE_TYPE[NODE_TYPE_ID])
                .filter(TABLE_LINKING[SECTION_ID] == section_Id && TABLE_NODE_TYPE[NODE_CATEGORY] == "S")
                .order(TABLE_ENDPOINT[ENDPOINT_ID].asc))

            let arr = Array(try DB!.prepare(query))

Project taking too long to build and run after adding complex query

2 Answers2