For those new to message boards please try to follow a few simple rules when posting your question.
Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
Keep the subject line brief, but descriptive. eg "File Serialization problem"
Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
No advertising or soliciting.
We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
Just a random question since I already have a different solution but I had the following situation.
I have a table with a billion rows (actually probably about 1.1 billion.)
I was trying to span the table, read every row and do an analysis.
Certainly couldn't load the entire table. I was using a paged query (limit/count). Each page took about 90 minutes for the query itself. So not really something that was going to allow me to do much analysis.
Any other ideas on spanning it or speeding it up?
At one point I was even considering just dumping it and writing an app to do the analysis outside of the database. That was about the only other solution I had.
The database was MySQL (AWS Aurora actually).
The relevant parts of the table were as follows and the id has a primary key. (I didn't design the table.)
I need to do the following
1. Verify that RefId1 and RefId2 are in a different table either in that order (1,2) or (2,1)
2. Report if neither or only one id is found.
3. Report if more than one match is found
4. If found report if a different column (not documented above) is same as in the second table.
The second table also has a billion rows.
Both tables have indexes on the id, and the two other columns documented above.
oh that is ugly - the 1,2 - 2,1 is going to cost you.
Can you do it in a couple of steps, inner join on the 2 layouts and eliminate them from the process, possibly even move the non matching records into another table for your reporting analysis (one assumes the majority have valid matching records).
Never underestimate the power of human stupidity
Paging was not part of the solution. Paging was necessary only because I could not read the entire collection into memory. However I did need to insure that all of the data was read and that the same row was not read twice.
That's why you need to have an index on the id. (or any unique column or combination of columns)
The index is already sorted per definition.
So the WHERE id > @PreviousMaxID just walks down the b-tree to the right id and starts counting leafnodes until it reaches @PageSize.
I don't know how AWS Aurora is organized, but if your table is clustered, you don't even need to lookup the pages and Bob's your uncle.
Hi - We migrated from sql server 2008 to 2012. We have a stored proc which inserts dates into a table. We insert an initial value wherein the identity key value is -1, with an insert statement. For the rest of the dates, we have a while loop. within the while loop, the next identity key value is resulting as 0 in sql server 2012 and as a 1 in sql server 2008. Both server have the table's field name defined with identity_seed as 0, and increment as 1.
Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1?
The fact you have a while-loop is filling me horror. However, without being able to see the SP code we can't possibly comment on what might be going wrong.
You have contradicted yourself however, you stated
We insert an initial value wherein the identity key value is -1,
but you state
Both server have the table's field name defined with identity_seed as 0, and increment as 1.
Without checking I can't confirm, but perhaps there was a hole in 2008 that allowed you to trample all over the IDENTITY column.
You should use IDENTITY (-1,1) if you want the first key to be -1 (but I suspect you don't).
Try posting the code so we can suggest ways of improving it ... like getting rid of the WHILE loop
If rows are present in the table, the next row is inserted with the new_reseed_value value. In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value.
So if you reseed to 0, and your increment is 1, the next value in SQL 2008 R2 or earlier will be 1, whereas the next value in SQL 2012 or later will be 0.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer