0

Top level: I have this query I'm running. It's been running for several weeks exactly as I expected, but the last couple of days I'm getting pretty random incorrect results when I run the query through ADO.Net but still getting the "correct" results when I run the exact same query in SSMS, and I am at a loss to see why.

I'm writing a process to harvest SEC filings from the Edgar site. Every night, the SEC publishes a small text file with the "light metadata" of the filings done on a given day (id for the company it's about, what type of filing it is, the id number of the filing). You can whip through these really fast and get the general idea of what's happening. It's the first stage of the project I wrote, and I could get the light metadata this way for 27 years or so of filings loaded in about an hour.

The thing that's really time consuming is fetching the whole filing and parsing that. As that side of the project has grown, every day I want to get the new data and then fetch and process a few extra full filings to backfill the filings I know about from the light meta data.

Another thing the process has to accommodate is that a company can submit a filing on Monday and upload a new version on Friday, or next week, or next year. So I have to recognize when I've gotten an update to a filing I've processed before.

In other words, people care about the more recent stuff, so I want to keep current and fill in the old stuff as time allows.

So I have the following query that, in high level terms, asks "give me the top X filings that a) I haven't fully processed before or b) got and update and I need to reprocess it"

FilingIndex is the table that gets the light metadata from the nightly file SEC posts about the daily activity

FilingCiks (Cik being a govt company id) is a one-to-many table about the companies referenced by the filing (at least one of these ids is required to fetch the full text for processing

FilingContent is my table where I keep the information from the full text I've fetched and parsed for a file.

So every night I run the process and get the list of "what's new" for today; that goes into FilingIndex and FilingCiks.

Then I run this query, saying "in order of the newest filings I've heard about, get me what I need to call the SEC for the full text". @max is the number of filings I found in the prior step + some number of thousands to help backfill.

I expect to see a list of all the filings I just got from the previous step, followed by entries where my backfilling last left off. So the first 5000 rows are today's/last night's filings, the next 5000 may pick up somewhere in 2018 where I left off.

I've processed a couple years of filings this way and the results have been what I expected every time. But yesterday for no reason I can find, the query below started returning scattershot results when run through ADO.Net but still returns what I expect in SSMS.

Through ADO.Net, the results are still reverse chron, but a) they don't start with the newest, and b) they skip lightly through the existing rows.

Any ideas why a) ADO.Net and SSMS would run so differently and b) why the ADO.Net run would just start behaving differently in the last day or so?

As I've said, I've been running this process for a while. I've back and filled back to August 2018 and the query was running just as I expected. But now it's jumping around in the calendar.

DECLARE @max int = 5000;
SELECT TOP (@max) idx.accession_number, ciks.cik, cast(case when idx.fetched < idx.crawlerdate then 1 ELSE 0 END as bit) as cleanup, idx.CrawlerDate
FROM
(SELECT idx.accession_number, idx.crawlerdate, idx.filingType, ROW_NUMBER() OVER (PARTITION BY idx.accession_number ORDER BY idx.crawlerdate DESC) AS rn, content.fetched
  FROM raw.FilingIndex idx
  LEFT JOIN raw.FilingContent content ON content.accession_number = idx.accession_number
  WHERE idx.accession_number < 999999999700000000 -- paper filings with no good data to mine
       AND (content.accession_number is null OR content.Fetched < idx.crawlerdate)
) idx
CROSS APPLY (SELECT TOP 1 cik from raw.filingciks ciks WHERE ciks.accession_number idx.accession_number AND ciks.crawlerdate = idx.crawlerdate) ciks
WHERE idx.rn = 1
ORDER BY idx.crawlerdate DESC, idx.Accession_Number asc
2
  • 2
    I'm always suspicious of top statements without an order by as in your ciks query. If that started returning a different arbitrary row based on session-level optimizer settings or something along those lines, that could certainly cause results to be non-deterministic. I'm not sure if your row_number is deterministic or whether the outer order by is guaranteed to be deterministic as well. Jan 8, 2021 at 23:03
  • Thanks for the reply, Justin. In that particular instance, there can be multiple companies associated with a filing, but I only need one (any one) to fetch the full filing from the SEC, so the cross apply subselect is not fussed with the order. The row_number + order by on the outer query is trying to get the newest entry for the filing we've heard about. But again, I'm obviously missing something here. Jan 11, 2021 at 15:10

2 Answers 2

1

Are your idx.crawlerdate field's values UNIQUE for each value within each PARTITION of idx.accession_number?

In other words, if you have two values for crawlerdate that are the same within the same PARTITION then ROW_NUMBER() will randomly select which record wins in the tie when generating values for your rn field. (This is due to the nondeterministic nature of ordering data sets on a non-unique field.)

To fix this, you either need to add another field to uniqify the logic for the ORDER BY clause of the ROW_NUMBER() function OR you can use RANK() or DENSE_RANK() to generate your row numbers. If you use RANK() or DENSE_RANK() then rows with the same crawlerdate within the same PARTITION will get assigned the same rank number.

This is a great read on the nondeterministic ordering that can occur when using the ROW_NUMBER() function.

Also, I agree that with @JustinCave that your ciks query SELECT TOP 1 cik from raw.filingciks ciks WHERE ciks.accession_number idx.accession_number AND ciks.crawlerdate = idx.crawlerdate is nondeterminstic because you're using the TOP operator without an ORDER BY operator. Depending on your data, you can also get random results there.

1
  • Thanks for the reply, JD. I apologize for not putting more of the pertinent information there. The FilingIndex table has Accession number and crawler date together as the primary key, so they would be unique. I'll read the article about non-deterministic ordering. On the cross apply, while many companies can be associated with a filing, I only need 1 - any 1 - to fetch the whole filing from the SEC, so I wasn't fussed about the ordering there. What puzzles me is running the query in SSMS behaves as I'd expect, but when running it in ADO.Net gives very different results. Jan 11, 2021 at 15:16
0

Well, now I feel stupid...

I added some code to the C# to get the ADO.Net query plan, and pulling that thread took it all the way back to some changes I made in how the command line arguments were getting processed.

The reason the results were so different was because it wasn't running what I thought it was, and the one dynamic variable in the C# creating the query had an unexpected value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.