Solved

SQL Temp Tables and Timeout Issues

Posted on 2008-10-10
4
574 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

756 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