Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

SQL Temp Tables and Timeout Issues

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
jfergy
Asked:
jfergy
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
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
 
jfergyAuthor Commented:
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
 
momi_sabagCommented:
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
 
jfergyAuthor Commented:
There are currently 50,000 records in the listings table.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now