SQL Server Performance Forum – Threads Archive

Temp table slow with MIN()

Hi, We have a SQL 2000 batch process using temp tables. Table #a has 116 rows of 15 (narrow) columns (SeqNum is identity column) "SELECT MAX(SeqNum) FROM #a" Takes 15 seconds!! For comparison
"SELECT SUM(SeqNum) FROM #a" Takes 0 seconds This is one of the performance problems we have encountered post sql7 to 3k upgraqde. Thanks

---

Did you use index for seqnum?
Madhivanan Failing to plan is Planning to fail

---

No index (will try), but even without index this should not be taking 14 seconds, should it !? Table only has got just over 100 rows in it.

---

Have you checked the execution plan for both of the statements, that helps how SQL is managing. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Am I reading this right, that you are running SQL Server 7.0 on Windows 2003?
This isn’t really supporeted by Microsoft and you should consider an upgrade to SQL Server 2000 or above. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

It shouldn’t, no index is needed in that case.

---

what build of SQL 2000 are you on, the early build have lots of strange problems,
by the upper 900’s, most are gone,
better yet, go to sp4 + hotfix (build 2040)

---

Frank – I meant to say sql7 to sql2k upgrade (sloppy typing, sorry) Build is win2000 + sql2000 sp3 + hotfix for correlated subqueries. More background: The table #a contains a subset from another temp table (#la) created earlier in the same batch. #la contains results of 5 complex unioned queries. Each of the unioned queries contain joins to derived tables and many where conditions, making rewrite of populating #la last resort solution (this set of unioned queries also had serious performance problem after 2k upgrade and OPTION (MAXDOP 1) had to be used). To test I tried writing both tables as "permanent" tables (not temp) into database and then the performance of the min() was as should. Problem does not manifest itself in the execution plan as its relative cost is 0.01%, even though outputting time difference of before and after the operation displays 14 seconds (which is 10% of the total duration). In fact any read operation to #a table will take 14 seconds (select top 1 * FROM #a takes 14 seconds!). Only "DROP TABLE" was instantanious. COMMENT: I have had to tune 3 jobs already using large temp tables by partitioning them. This has resulted in performance improvement 2 hours to 15 minutes. Is this something to do with sql 2k dealing with temp tables less efficiently that SQL7?

---

Full build info: Microsoft SQL Server 2000 – 8.00.837 (Intel X86) Jun 23 2003 14:50:30 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Reason we didnt go with sp4 (even thought initally planned to) was that hotfix for corerlated subqueries was not included in it…

---

Which sentence is correct one?
Can you explain how did you partition them? Is the code part of stored procedure? How have you measured the time? Have you used query analyzer or read it from trace?

---

OK, it gets a little complicated here.. but please stay with me Result after leaving the job as was in SQL7
"SELECT MAX(SeqNum) FROM #a" Takes 15 seconds
"SELECT MIN(SeqNum) FROM #a" Takes 15 seconds
"SELECT SUM(SeqNum) FROM #a" Takes 15 seconds
"SELECT TOP 1 * FROM #a" Takes 15 seconds Result after a alteration to first create the table with "create #a…." and then populated with subset of #la. Previously the #a is populated directly from #la without defining schema first. (sorry, left this important info out).
"SELECT SUM(SeqNum) FROM #a" Takes 0 seconds
"SELECT MAX(SeqNum) FROM #a" Takes 15 seconds (!)
"I have had to tune 3 jobs already using large temp tables by partitioning them." Those fixes, not part of this particular problem, but previous 7 -> 2000 performance issues included breaking where clause "or" statements in temp table #a population query in following way: instead of populating #r with "where (clause1) or (clause2) or (clause2)" insert into #r1 where clause1
insert into #r2 where clause2
insert into #r3 where clause3 …and then union then to create #r Not pretty but huge performance gain (original population would result in climbing disc queue for hour pretty much seizing the server).
"Is the code part of stored procedure? How have you measured the time? Have you used query analyzer or read it from trace?" -Code not part of stored procedure.
-debugging in QA by outputtting time difference between end of previous stmnt and after stmnt run (14 sec)
-checking relative time stmnt took in execution plan (0.01% which is not representative of query taking 14 seconds as whole batch takes about 90 sec.)
-trace (14 sec)

---

The behaviour is really strange. Other then checking if there is a bug related to mssql and os version I would check tempdb settings. On top of that, number of rows in #a is small enough to take advantage of table variables. I think performance improvement came from having 3 selects with better execution plan then original one with or. I mean you probably gained nothing inserting into 3 temp tables, you could use insert into with select … union select …. union select or 3 separate inserts into the same table (assuming intersection of recordsets is emtpy).

---

Thanks for that tip about table variables mmarovic, will try that. In fact by dropping all temp tables as they become not necessary seems to improve the performance but its still not satisfactory. The behaviour makes you think that table #a refers #la somehow making it slow. Joe Chang was also pointing to problems with sp3. It would be kind of nice to understand how. Maybe applying sp4 is the only option, but as far as I know, they still have not issued hotfix for the correlated subqueries in views…

---

]]>