cti-taxii — archive

Re: [cti-taxii] Re: [EXT] [cti-taxii] topics from today's call - part 3

From: Jason Keirstead
To: [email protected]
Date: Wed, 13 Dec 2017 13:09:00 -0000

I'll copy/paste my reply on Slack... Were you using a result set/cursor as you did this paging? I can't see how you would have been.... https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor - Jason Keirstead STSM, Product Architect, Security Intelligence, IBM Security Systems www.ibm.com/security "Things may come to those who wait, but only the things left by those who hustle." - Unknown From: Bret Jordan <[email protected]> To: "[email protected]" <[email protected]> Date: 12/13/2017 03:39 AM Subject: [cti-taxii] Re: [EXT] [cti-taxii] topics from today's call - part 3 Sent by: <[email protected]> Here is the data that shows the performance impacts of doing queries with LIMIT/OFFSET versus the same query without the OFFSET. This performance problem all goes away, if you just do simple WHERE queries and you do them based on some incremental field. And since we already have that in TAXII, aka, the Date Added to micro second precision, this becomes super easy and super fast. Basic Query with 1000 records Individual Times (ms): 0 0 0 0 0 0 0 0 0 0 Average Time (ms): 0 Offset Query with 1000 records with offset 500 Individual Times (ms): 4 3 3 3 3 3 3 4 4 7 Average Time (ms): 4 Basic Query with 10000 records Individual Times (ms): 0 0 0 0 0 0 0 0 0 0 Average Time (ms): 0 Offset Query with 10000 records with offset 5000 Individual Times (ms): 44 32 34 29 34 27 28 32 38 27 Average Time (ms): 33 Basic Query with 50000 records Individual Times (ms): 0 0 0 0 0 0 0 0 0 0 Average Time (ms): 0 Offset Query with 50000 records with offset 25000 Individual Times (ms): 238 169 161 164 170 164 166 162 169 157 Average Time (ms): 172 Basic Query with 100000 records Individual Times (ms): 0 0 0 0 0 0 0 0 0 0 Average Time (ms): 0 Offset Query with 100000 records with offset 50000 Individual Times (ms): 702 379 384 351 373 354 349 367 361 366 Average Time (ms): 399 Basic Query with 250000 records Individual Times (ms): 1 0 0 0 0 0 0 0 0 0 Average Time (ms): 0 Offset Query with 250000 records with offset 300000 Individual Times (ms): 7457 6209 6216 6101 6116 6161 6134 6084 6095 6130 Average Time (ms): 6270 Bret From: [email protected] <[email protected]> on behalf of Bret Jordan <[email protected]> Sent: Tuesday, December 12, 2017 2:54:54 PM To: [email protected] Subject: [EXT] [cti-taxii] topics from today's call - part 3 I would like us to consider dropping the pagination by Items concept in TAXII 2.1 and replace it with a simple query of the date added to the collection. https://github.com/oasis-tcs/cti-taxii2/issues/23 Bret