Solved

SQL Temp Tables and Timeout Issues

Posted on 2008-10-10
4
572 Views
Last Modified: 2012-05-05
I have a stored procedure that creates a temp table to run it's query results on. When I run that stored procedure it causes my main table results that are being used on my site to time out until my stored procedure code has completed. Shouldn't temp tables take the data and create a seperate table that wont interfere with my main site searches? Why would it cause a timeout? Any ideas or suggestions?
0
Comment
Question by:jfergy
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22692847
your procedure probably takes a long time to populate the temp table
post here the code you use
have you looked at access plans?
0
 

Author Comment

by:jfergy
ID: 22692869
Here it is. I'm not sure what you mean by an Access plan?
CREATE  PROCEDURE proc_GetallListingsSmarter 
	@MinPrice			money		= null,
	@MaxPrice			money		= null,
	@MinBedrooms			int		= null,
	@MinBathrooms			int		= null,
	@AssessedSqFt			nvarchar(15)	=null,
	@MinSquarefeet		int		= null,
	@ListingNumber			varchar(20)	= null,
	@PropTypeID			int		= null,
	@SubPropTypes		varchar(255)	= null,
	@Regions			varchar(2000)	= null,
	@Areas				varchar(2000)	= null,
	@Address			varchar(32)	= null,
	@ZipCode			varchar(15)	=null,
	@PersonnelID			int		= null,
	@OfficeID			int		= null,
	@DistinctProp			tinyint		= null,
	@MinYearBuilt			int		= null,
	@MaxYearBuilt			int		= null,
	@Acreage			nvarchar(10)	= null,
	@MinListingDate		datetime	= null,
	@MinLastPriceReduction	datetime	= null,
	@NewConstruction		tinyint		= null,
	@NeighborhoodID		int		= null,
	@TopCount			int		= null,
	@SortKey			varchar(28)	= null
AS
 
 
-- allocate local variables
declare	@nRecordCount	int
declare	@SQL			varchar(8000)
 
 
-- build temp table for the first phase of searching (all the easy stuff)
select 		ListingID
into		#tblTempsmart
from		tblListings
where		Active = 1
and  		((Price >= @MinPrice) or (@MinPrice is null))
and  		((Price <= @MaxPrice) or (@MaxPrice is null))
and  		((Bedrooms >= @MinBedrooms) or (@MinBedrooms is null) or (@MinBedrooms < 2))
and  		((Bathrooms >= @MinBathrooms) or (@MinBathrooms is null) or (@MinBathrooms < 2))
and  		((AssessedSqFt >= @MinSquarefeet) or (@MinSquarefeet is null) or (@MinSquarefeet = 0))
and  		((ListingNumber = @ListingNumber) or (@ListingNumber is null))
and  		((ListingAgentID = @PersonnelID) or (SecondAgentID = @PersonnelID) or (@PersonnelID is null))
and  		((ListingOfficeID = @OfficeID) or (SecondListingOfficeID = @OfficeID) or (@OfficeID is null))
and  		((DistinctProp = @DistinctProp) or (@DistinctProp is null))
and  		((Address Like '%' + @Address + '%') or (@Address is null))
and  		((YearBuilt >= @MinYearBuilt) or (@MinYearBuilt is null))
and  		((YearBuilt <= @MaxYearBuilt) or (@MaxYearBuilt is null))
and  		((ZipCode = @ZipCode) or (@ZipCode is null))
and  		((Acreage >= @Acreage) or (@Acreage is null))
and  		((ListingDate >= @MinListingDate) or (@MinListingDate is null))
and  		((LastPriceReduction >= @MinLastPriceReduction) or (@MinLastPriceReduction is null))
and  		((NewConstruction = @NewConstruction) or (@NewConstruction is null))
 
-- count the records
select @nRecordCount = count(*) from #tblTempsmart
 
-- build dynamic sql statement to return the final results - merge in sort field and top count
select @SQL = '	select		top ' + Convert(varchar, IsNull(@TopCount, @nRecordCount)) + '
				l.ListingID,
				l.ListingNumber,
				l.ListingOfficeID,
				O.OfficeWebTitle,
				l.MLSTableID,
				l.Address, 
				l.Price, 
				l.Bedrooms,
				l.Bathrooms,
				l.Description,
				l.LOFC, 
                           		l.LOFCN,
				l.DistinctProp,
				l.ListingDate,
				l.LastPriceReduction,
				l.ZipCode,
				l.LeaseRate,
				l.Rent,
				l.YearBuilt,
				l.AssessedSqFt,				
				l.Acreage,
				l.SchoolDist,
				l.Elementary,
				l.JuniorHigh,
				l.HighSchool,
				p1.PersonnelID,
				p1.FirstName,
				p1.LastName,
				p1.Email,
				p1.Phone,
				p1.CrestID,
				p2.PersonnelID as PersonnelID2,
				p2.FirstName as FirstName2,
				p2.LastName as LastName2,
				p2.Email as Email2,
				p2.Phone as Phone2,
				p2.CrestID as CrestID2,
				c.CityName,
				s.StateName,
				s.StateAbbr,
				l.AltDescription,
				sp.TBSubPropTypeName,
				vt.TourUrl,
				l.VirtualTourNumber,
				' + Convert(varchar(8), @nRecordCount) + ' as nRecordCount
		from		tblListings l
		left join		tblOffices O on (l.ListingOfficeID = O.OfficeID)
		left join		tblPersonnel p1 on (l.ListingAgentID = p1.PersonnelID)
		left join		tblPersonnel p2 on (l.SecondAgentID = p2.PersonnelID)
		left join		tblCities c on (l.CityID = c.CityID)
		left join		tblCounties co on (c.CountyID = co.CountyID)
		left join		tblStates s on (co.StateID = s.StateID)
		left join		tblListingSubPropertyJoin pr on (pr.ListingID = l.listingID)
		left join		tblTBSubPropTypes sp on (sp.TBSubPropTypeID=pr.ListingSubPropTypeID)
		left join		tblVirtualTours vt	on (l.VirtualTourID = vt.VirtualTourID)
		where		l.ListingID in (select ListingID from #tblTempsmart)
		order by		l.ListingID'
 
 
-- return results
exec(@SQL)
GO

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22692875
you need to get the access plan (by clicking show access plan in the management studio)
you are probably missing some indexes
how many records goes into the temp table?
0
 

Accepted Solution

by:
jfergy earned 0 total points
ID: 22692977
There are currently 50,000 records in the listings table.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question