Timeline for How to achieve high performance management of a huge list of distinct strings
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 17, 2016 at 23:57 | comment | added | Jordan Rieger | I just finished a test in my production environment, and it was 3x faster, presumably due to much better I/O. I also found that my parsing of the child lists was taking up more time than I thought during the inserts. But this answer set me on the right track. | |
| Jun 17, 2016 at 23:55 | vote | accept | Jordan Rieger | ||
| Jun 17, 2016 at 20:38 | comment | added | Jordan Rieger | Less logging, lower footprint? I dunno, I don't have any experience with it, which is part of the reason for my question. My use case is not very "relational", it's essentially two tables with a bunch of giant set/list operations. But my instinct is that SQL Server should be fine with the right hardware. | |
| Jun 17, 2016 at 15:37 | comment | added | Greg | I am not sure why you would think nosql would solve your issue. | |
| Jun 17, 2016 at 15:02 | comment | added | Jordan Rieger | Yes, I'm starting to think the same thing. I did the initial staged insert without any indexes, and it still took 50 minutes to insert 130 million rows. The total space used for the table was about 3 GB, meaning it was only working at about 1 MB/s. (But I will try to get more exact numbers from performance counters.) The Dev database I'm using is on an older VM and serves some other applications, so maybe it's heavily I/O bound. I'm going to have to setup a test on my faster Production DB to really tell whether SQL Server will cut it or if I should adopt some NoSQL tool. | |
| Jun 17, 2016 at 5:19 | comment | added | Greg | If you are disk bound, I don't think there is much more you can do. Can you tell us your disk Io numbers? | |
| Jun 16, 2016 at 21:08 | comment | added | Jordan Rieger | I think for my next attempt, I will try inserting without indexes, and then creating the index afterward while filtering duplicates. | |
| Jun 16, 2016 at 21:08 | comment | added | Jordan Rieger | I have the database configured to Simple recovery model, which I think is even faster than Bulk Logged. I'm not concerned about disaster recovery as we can get fresh data from the downloads each night. I know it's not auto-growing because I set the initial size to 10 GB and the tables are still under 6 GB total (plus no growth events are present in the trace log.) As for table variables, I'm not using them for anything other than passing the data to the SP that inserts into the staging table (i.e. no filtered queries against the table-valued parameter.) | |
| Jun 16, 2016 at 20:27 | history | edited | Greg | CC BY-SA 3.0 |
added 270 characters in body
|
| Jun 16, 2016 at 20:15 | history | answered | Greg | CC BY-SA 3.0 |