radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
the fields being used in the main stored procedure query are indexed. When I
run the stored procedure for the first time after a long time, it takes a
really long time to return results (2-3 minutes). But any runs after this ar
e
very fast (1 second or less).
Question: Does anyone know why this may be happening (my gut feeling is that
it has to do with indexes being cached in memory, but it would be good if
someone can explain this more clearly). What can I do to make sure that this
query always runs very fast? Is there a way to preload tables or indexes in
the memory as this query is one of the main queries in the application and
will be run a lot.Some more info on this performance issue:
When I run 'estimated execution plan' on the stored procedure in the Query
Analyzer, it seems to be saying there is full table scan on the the table
containing the businesses, even though the zipcode field is indexed.
What does this mean?
THanks,
Ashhad
"Ashhad Syed" wrote:
> I have written a stored procedure to obtain businesses in a given zipcode
> radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). Al
l
> the fields being used in the main stored procedure query are indexed. When
I
> run the stored procedure for the first time after a long time, it takes a
> really long time to return results (2-3 minutes). But any runs after this
are
> very fast (1 second or less).
> Question: Does anyone know why this may be happening (my gut feeling is th
at
> it has to do with indexes being cached in memory, but it would be good if
> someone can explain this more clearly). What can I do to make sure that th
is
> query always runs very fast? Is there a way to preload tables or indexes i
n
> the memory as this query is one of the main queries in the application and
> will be run a lot.
>
>|||On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>I have written a stored procedure to obtain businesses in a given zipcode
>radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
>the fields being used in the main stored procedure query are indexed. When
I
>run the stored procedure for the first time after a long time, it takes a
>really long time to return results (2-3 minutes). But any runs after this a
re
>very fast (1 second or less).
>Question: Does anyone know why this may be happening (my gut feeling is tha
t
>it has to do with indexes being cached in memory, but it would be good if
>someone can explain this more clearly). What can I do to make sure that thi
s
>query always runs very fast? Is there a way to preload tables or indexes in
>the memory as this query is one of the main queries in the application and
>will be run a lot.
>
Hi Ashhad,
Please post the table definitions (as CREATE TABLE statements, including
all constraints and indexes) and the code of the stored proc.
See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||I will do it later today. Thanks.
"Hugo Kornelis" wrote:
> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||Hugo:
Here is the sql script for all tables and stored procs involved:
Ashhad
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ZIPCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZIPCodes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[specialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[specialty]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vm_serviceprovider]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[vm_serviceprovider]
GO
CREATE TABLE [dbo].[ZIPCodes] (
[ZIPCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateAbbr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AreaCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Longitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[specialty] (
[spid] [int] NULL ,
[law_specialty] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[vm_serviceprovider] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneAreaCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Desc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarketingStatement] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NoOfEmp] [int] NULL ,
[YearsInBusiness] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MobileEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_No] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Expiration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneNotify] [int] NULL ,
[PhoneNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailNotify] [int] NULL ,
[CellNotify] [int] NULL ,
[CellNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FaxNotify] [int] NULL ,
[OtherCertification] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[LastReferralDate] [datetime] NULL ,
[Subscriber] [int] NULL ,
[Updated_By] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Updated] [datetime] NULL ,
[timezone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessHours] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[open_w
ends] [int] NULL ,[provide_loner_car] [int] NULL ,
[leadsquota] [int] NULL ,
[pic1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackageID] [int] NULL ,
[websiteurl] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[plan_enrollment_date] [datetime] NULL ,
[next_billing_date] [datetime] NULL ,
[credit] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adsize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[frachise] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[msa] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales] [money] NULL ,
[sales_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employee_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[internalrating] [float] NULL ,
[business_type] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[admin_notes] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_calls] [int] NULL ,
[last_call] [datetime] NULL ,
[sendalert] [int] NULL ,
[ph_referredby] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[college] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[marketing_flag] [int] NULL ,
[bar_association] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[monthly_budget] [money] NULL ,
[account_onoff] [int] NULL ,
[call_enabled] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [PK_vm_serviceprovider_1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_ZIPCodes] ON [dbo].[ZIPCodes]([ZIPCode]) ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_specialty] ON [dbo].[specialty]([zip]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [DF_vm_serviceprovider_total_calls] DEFAULT (0) FOR [total_calls]
GO
CREATE INDEX [IX_vm_serviceprovider_ph] ON
[dbo].[vm_serviceprovider]([Phone]) ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_username] ON [dbo].[vm_serviceprovider]([UserName])
ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_password] ON [dbo].[vm_serviceprovider]([Password])
ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_5] ON
[dbo].[vm_serviceprovider]([Company]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_6] ON
[dbo].[vm_serviceprovider]([LName], [FName], [State]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_7] ON
[dbo].[vm_serviceprovider]([LName]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_8] ON
[dbo].[vm_serviceprovider]([Address]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_9] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_10] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_11] ON
[dbo].[vm_serviceprovider]([Zip]) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusAssistant]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[RadiusAssistant]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[RadiusAssistant](
@.ZIPCode char(5),
@.Miles decimal(18, 9)
) RETURNS
@.MaxPoints TABLE (
Latitude decimal(10,8),
Longitude decimal(11,8),
MaxLat decimal(10,8),
MinLat decimal(10,8),
MaxLong decimal(11,8),
MinLong decimal(11,8))
AS
BEGIN
DECLARE
@.Latitude decimal(10,8),
@.Longitude decimal(11,8)
SELECT @.Latitude = Latitude,
@.Longitude = Longitude
FROM [dbo].[ZIPCodes]
WHERE ZIPCode = @.ZIPCode
AND CityType = 'D'
IF 0 = @.@.rowcount
RETURN /* invalid zip */
DECLARE @.MilesPerDegree decimal(10,8)
SET @.MilesPerDegree = 69.172
DECLARE
@.MaxLat decimal(10, 8),
@.MinLat decimal(10, 8),
@.MaxLong decimal(11, 8),
@.MinLong decimal(11, 8)
SET @.MaxLat = @.Latitude + @.Miles / @.MilesPerDegree
SET @.MinLat = @.Latitude - (@.MaxLat - @.Latitude)
SET @.MaxLong = @.Longitude + @.Miles / (Cos(@.MinLat * PI() / 180) *
@.MilesPerDegree)
SET @.MinLong = @.Longitude - (@.MaxLong - @.Longitude)
INSERT @.MaxPoints
SELECT @.Latitude, @.Longitude, @.MaxLat, @.MinLat, @.MaxLong, @.MinLong
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusSearch3]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[RadiusSearch3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[RadiusSearch3]
@.ZIPCode char(5),
@.Miles decimal(11, 6),
@.BusinessType varchar(50)
AS
/*'<A href=''http://www.mapquest.com/maps/map.adp?address='+ IsNUll(
t.Address,'') + '&city='+ t.City +'&state='+ t.state +'&zipcode='+ t.zip
+'&country=US&cid=lfmaplink'' target=_blank> Map </A>'*/
SELECT top 100 t.id, t.marketing_flag,
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
distance, '<b>' + t.FNAME + ' '+ t.LNAME + '</b>, ' + t.COMPANY as
companyname, '<b>' + t.FNAME + ' '+ t.LNAME + '</b><br>' + t.COMPANY +
'<br>' + t.ADDRESS + '<br>' + t.CITY + ', '+ t.State + ' ' + t.ZIP +
'<A href=''http://maps.google.com/maps?f=q&hl=en&q='+ IsNUll(
t.Address,'') + ', '+ t.City +', '+ t.state +' '+ t.zip +'''
target=_blank> Map </A>'+ '<b>'+ cast (cast
([dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
dec (5, 1)) as varchar) + ' miles away </b><br><br>' +
'<b> Attorney Experience: </b>'+ cast ((DATEPART(yyyy, GETDATE())-
t.YearsInBusiness) as varchar)+ ' years' + '<b> Firm Size: </b>'+
CASE
WHEN NoOfEmp IS NULL THEN 'Not Known'
WHEN NoOfEmp <= 5 THEN 'Small'
WHEN NoOfEmp> 5 and noOfEmp < 30 THEN 'Medium'
WHEN NoOfEmp>= 30 THEN 'Large'
ELSE 'Not Known'
END + '</b><br><br>' + substring ( IsNull( t.MarketingStatement, ''),
1, 200) + '<A href=SP.aspx?ID='+ convert(varchar,t.ID) + ' target=_blank>
More </A>' + '<br><br><b>PRACTICE FOCUS:</b><br>' + t.business_type as
[Attorney Info],
CASE call_enabled
WHEN 'Y' THEN '<A class=link href=CallAttorney.aspx?ID=' +
cast(t.id as varchar) + ' target=_blank> CLICK HERE TO CALL! </A>'
ELSE 'Call feature not available'
END as [call]
/* add any other fields here */
/* Distance Assistant required */
FROM [dbo].[ZIPCodes] z,
[dbo].[RadiusAssistant](@.ZIPCode, @.Miles) r
, [dbo].[specialty] u
, [dbo].[vm_serviceprovider] t
/* if you wanted to join this with your stores/dealers/users table simply
add a line of code here */
/* and see below for the other line of code */
/*
[dbo].[vm_serviceprovider] t
*/
WHERE 1=1
AND z.Latitude BETWEEN r.MinLat AND r.MaxLat
AND z.Longitude BETWEEN r.MinLong AND r.MaxLong
AND z.CityType = 'D' /* only one result per ZIP */
AND z.ZipType <> 'M' /* don't include "Military" ZIPs which don't have a
lat/long */
/* SQL caches the result of this function - so performance is not impacted
even though we run it twice */
AND
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) <=
@.Miles
AND u.ZIP = z.ZIPCode
AND u.law_specialty = @.BusinessType
AND t.id = u.spid
/* dealer locator join continued */
/* match the ZIP Code Download ZIPCode column to the ZIP Code column on the
users/stores/dealers table */
/*
AND u.ZIPCode = z.ZIPCode
*/
ORDER BY
t.marketing_flag desc, distance asc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Also the name of the procedure being used to return businesses in a zipcode
radius is Radius3
"Hugo Kornelis" wrote:
> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||I managed to solve the problem. Thanks for your offer to look into this.
"Hugo Kornelis" wrote:
> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||u need an index on the lattitudes, not the zipcodes.
you are selecting WHERE lattitude.
regards,
doug
No comments:
Post a Comment