Solved

SQL Temp Tables and Timeout Issues

Posted on 2008-10-10
4
573 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 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