August 11, 2008 at 1:26 pm
Hi all...I am running a query where I am joining to a temp table filled with locations. I am also passing in a parameter for 'home' 'current' or 'both'. I am check the parameter and if it is 'home' then bring me back everything in the temp table that starts with H. 'Current' is everything that starts with P. 'Both' is bring me back EVERYTHING!
ok, my issue here is when I pass in 'BOTH' it just runs and runs and runs. 'Home' and 'Current' filter out fine. I was wondering if I could put an index on a temp table to make it run faster when I am specifying 'Both' ?? Can I do that?. I am running this piece of code on SQL 2005, but there may be a chance that it is run on SQL 2000 as well.
DECLARE @tmpLocation TABLE
(
UnitID uniqueidentifier
, HLocationID uniqueidentifier
, HRegionAbbrev nvarchar(100)
, HLocationName nvarchar(100)
, HLocationCode char(4)
, HLatitude decimal(20,3)
, HLongitude decimal(20,3)
, PLocationID uniqueidentifier
, PRegionAbbrev nvarchar(100)
, PLocationName nvarchar(100)
, PLocationCode char(4)
, PLatitude decimal(20,3)
, PLongitude decimal(20,3)
)
Thank you for any/all input! I appreciate it....
August 11, 2008 at 1:46 pm
Can you post the query please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2008 at 4:08 pm
Hi Gail...
as I was pasting in myquery, I thought about trying something first. In my query, I changed a straight INNER JOIN on the temp table to a INNER JOIN select statement. I went from running over 3 minutes without getting the query to complete to 1-6 seconds!!!! What a difference! I am happy..it is bringing me back my results fast!
Thank you, I appreciate you trying to help me!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply