search

gflewis / sndml3

ServiceNow Data Mart Loader: an application to load SQL databases from ServiceNow
MIT License
30 stars 19 forks source link

Partition data by Minute #25

Closed idns1 closed 3 years ago

idns1 commented 3 years ago

Hi,

First of all Thank You for this beautiful software. It has helped a lot.

We have a huge table with 46 million records, I am trying to pull data but it times out. Any way can we include Partition by Minute too to the list. Thanks

gflewis commented 3 years ago

This was actually a pretty small change to the code. I modified the source code, but I did not create a new release. Can you try rebuilding your jar from the latest source code which is 3.2.1, and let me know if it works? (Use mvn package to build the jars.)

You need to be careful that the created date range is not too big. Use created: [startdate, enddate] to restrict the size of the created date range. The java has to build an internal structure to keep track of every partition. In my testing, it seemed to freeze up if the range was more than 2 years (probably ran out of memory).

gflewis commented 3 years ago

Hi. 46 million records is a big table. Can you tell me if, by any chance, the table is sys_email? If so, the small partition will not help. You are timing out on one or more individual records that have a gigantic payload. You need to find these records and exclude them based on the sys_id or the created date. You will know that you have found them because the ServiceNow UI will fail if you try to view them. If they are attached to an Incident then the Incident form will fail to paint. If you try to view them using the sys_email form, the form will freeze.

Since the records cannot be downloaded and they cannot be viewed, the only way that I know to find them is to iteratively zero in on them by using successively smaller created date ranges. If you only have couple of them, then it should be possible to find them and exclude them using a filter. If you have lots of them, then it is a problem.

gflewis commented 3 years ago

Did you resolve your timeout problem? Can this issue be closed?

idns1 commented 3 years ago

Thank You for your feedback, I was able to break down the data & load it successfully.