Solved

SQL Temp Tables and Timeout Issues

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now