Inserting lots of data into a temp table, does not keep sort order
-
Hi everyone,
I cannot get my temp tables to hold, love, and forever cherish the order of the data they contain and later share with other temp tables! When there is lots of data, the data comes out unalphabetized in chuncks!
I have a 2 SQL server Stored procs.
Proc1 creates a temp table, and executes Proc2 to fill the temp table from another temp table that Proc2 has laboriously produced. The data coming from Proc 2 is always in the right alphabetical order.
Proc1:
create #TMP_EMP
insert into #TMP_Emp (EmpId, EmpFName, EmpLname, EmpMI, EmpNo)exec Proc2 @vc_UserId, @i_EmpId, 3, 0, 0, 1, -1, @vc_SearchFilter
Do a bunch of other junk after this, but it's too late, the data is out of order, and I cannot reorder it no matter how I try.
Why does SQL Server mess up the order of the data in Proc2's nice Temp table while inserting into Proc1's temp table? And what can I do about it?
CLUE: If I have Proc1 create a regular table instead of a teemp table, then the data from Proc2 get inserted and stays in alphabetical order! I obviously can't have proc creating and dropping real tables just to do data munging....what do I do?
Any ideas are greatly appreciated.
Rhonda
-
Well this is what the microsoft ratbastards passing themselves off as product developers excel at: changing fundamental functionality from version to version and causing people to have to go back and change their application code.
The answer is that now they expect you to use the ORDER BY in statements when you care about sequence (sometimes regardless of the indexes you have on the table).
-
I might be mistaken, but in a relational database (no matter which product) ordering is meaningless and irrelevant.
While having an index works in most cases the ONLY reliable way to force SQL Server to return an ordered resultset is the use of ORDER BY. No need to blame Microsoft for this!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You're right of course, Frank.
SQL, not just SQL Server's behavior, should not order anything meaningfully unless you use an ORDER BY clause. Any order without that clause is an artifact of the physical storage, which is separate (and should be hidden) from the relational database model. You could use an ORDER BY clause in the stored procedure to get the desired result set, but that's not the correct method either because even selecting from such an "ordered" set is not guaranteed to be in any order unless you use ORDER BY. Any order other than that created by the ORDER BY clause should never be assumed in any implementation of SQL (yes; Oracle's ROWNUM is a bad thing from a pure SQL perspective). That said, the difference you are seeing is probably due to query parallelization being used for statements that hit the cost threshold; results sets from parallel queries will probably not expose the clustered index.
--Jonathan -
Just to be clear, I AM using an order by clause, I am ordering the RS by LastName. The results are in Proc1's temp table in the correct order.
When I execute Proc2 to insert the data from that temp table to the one in Ppoc1, the will not go in the same ORDER.
insert into #TMP_Emp (EmpId, EmpFName, EmpLname, EmpMI, EmpNo)
exec Proc2
All I need to know if how to get the records from 1 temp table to another in a a certain order. With lots of date, not even making a third temp table works:
Insert into TMP3 (col1,col2)
select Col1, Col2
from TMP2
order by Col1
-
Try this:
Insert into TMP3 (col1,col2)
select Col1, Col2
from TMP2
order by Col1 OPTION (MAXDOP 1)
What version (and service pack) of SQL Server are you using? There used to be problems like this in SQL Server 7...
--Jonathan -
And how do you retrieve the data?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for the suggestion.
I am using SQL Server 2000 sp3.
The MAXDOP option gives a little boost so rows are in order a bit farther down the line, but it is not right.
I have 36,974 employee records to be returned by last name asc, first name asc. SQL server is "attempting" because I can try just first name in our example and see rows ordered by first name for about the first 10% of the way. Then they are out of order in chunks.
So weird, that using a real table instead of a temp table and this works. Like it's a SQL memory issue.
-
I retrieve the data in asp that has some pagination code. I suspected a bug in the pagination code at first, but it looks good.
And, when I create real tables, not temp tables, it is beautiful when paginating both large and small sets.
-
Try this, then:
Insert into TMP3 (col1,col2)
select TOP 100 PERCENT Col1, Col2
from TMP2
order by Col1
--Jonathan -
Hmm, these ideas all sound good, but they are not working.
When I create my tmp table, I have an identity column (SeqNo), could that be getting in the way?
-
I suspect there's a flaw in your SQL code. You evidently haven't shared much of it. Without seeing the actual code, it's near impossible to guess at the cause of your problem.
--Jonathan -
The fact that microsoft offers clustered indexes (and performance/tuning tips and tricks, etc. to make use of these) belies your statement.
Hey, I used to teach the courses which provided samples to make use of these early features (physical storage order an example).
Relational theory (as well as relational database theory) is quite different from these products and what they offer--and even how they suggest you make use of them. In concept relational databases don't care, but in reality, the users waiting for a result set do [care].
-
Yes, relational database theory as Dr. Codd has defined it is not very good implemented in SQL. But that's the tools we are given and have to live with.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
John,
Well this is what the microsoft ratbastards passing themselves off as product developers excel at: changing fundamental functionality from version to version and causing people to have to go back and change their application code.
The answer is that now they expect you to use the ORDER BY in statements when you care about sequence (sometimes regardless of the indexes you have on the table).
Is this meant seriously or some kind of humour which I as a non-native speaker do not understand?
Not to be picky, but after several cups of coffee, I realized that my posts might have been misleading (likely due to my bad english
) I should have written"ordering of a result set is meaningless and irrelevant without ORDER BY."
I always thought that requiring an ORDER BY is perfectly in compliance with relational theory. Also I think that this has NOT changed from version to version.
Hey, I used to teach the courses which provided samples to make use of these early features (physical storage order an example).
Should this imply something?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply