Speed: Temp Table vs. multiple Selects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Speed: Temp Table vs. multiple Selects

Author

Topic

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-07-16 : 18:41:15

I need to get a two-part result set into my web page. The two parts have the same fields, but are sorted differently, so a UNION query won't work. Will it be faster to do two statements that INSERT INTO a Temp Table, and then SELECT * the result set (all in a stored procedure), OR issue two separate SELECTS (coming from ASP, that would mean twice the connection-time overhead). OR, is there a third option that would be better?

Here's an example of the code that I would use to do the Temp Table solution, if I were running against the Northwind database:


CREATE TABLE #TempList
	(
	RID	int IDENTITY (1,1),
	EmpID int NOT NULL,
	FName varchar(10) NOT NULL,
	LName varchar(20) NOT NULL,
	HDate datetime NULL
	)

INSERT INTO #TempList (EmpID, FName, LName, HDate)
SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees 
WHERE HireDate < '1/1/94'
ORDER BY LastName, FirstName

INSERT INTO #TempList (EmpID, FName, LName, HDate)
SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees 
WHERE HireDate >= '1/1/94'
ORDER BY HireDate

SELECT * FROM #TempList

Other details... NT 4 Server, SQL 7, Expected Result Set < 100 rows total, may grow to 500 rows, actual Source Table has < 5000 rows, WHERE Clause will really be based on an inline formula.

This is my first production-level voyage into Temp Tables... Anything I'm overlooking?

TIA,

Mark Caldwell

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources