Kom ihåg mig?
Home Menu

Menu

FAQ
Kalender
Forum FAQ

WN > Webbutveckling & webbhotell > Serversidans teknologier

Prestandaproblem med komplicerad SQL och 400.000 rader

 
Ämnesverktyg Visningsalternativ
Oläst 2011-12-02, 11:45 #1
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
  
Reg.datum: Aug 2005
Inlägg: 5 166
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Jag la upp en test i SQL-server 2008 R2 för att jämföra prestanda (och lära mig någt nytt om Global positionering). Jag upplever dock inget problem med prestandan. De skillnader jag har är att LONG och LAT är float som datatyper, satte upp två sammansatta index (IDX_LONGLAT(LONG,LAT) och IDX_LATLONG(LAT,LONG)) och att jag la upp SQL-frågorna som Stored Procedures, det går blixtsnabbt när jag kör frågan.

Först script för att skapa tabellen:
Kod:
USE [wntest]
GO

/****** Object:  Table [dbo].[geographic]    Script Date: 12/02/2011 11:38:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[geographic](
	[GEOGRAPHIC_ID] [int] NOT NULL,
	[LONG] [float] NOT NULL,
	[LAT] [float] NOT NULL,
 CONSTRAINT [PK_geographic] PRIMARY KEY CLUSTERED 
(
	[GEOGRAPHIC_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Stored procedure för att skapa 400 002 slumpmässigt genererade coordinater i databasen.
Kod:
/****** Object:  StoredProcedure [dbo].[CreateRandomPositions]    Script Date: 12/02/2011 11:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CreateRandomPositions]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @start INT 
	DECLARE @stop INT 
	DECLARE @count INT 
	DECLARE @seed float

	SET @count = 0
	SET @start = 1
	SET @seed = RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) );
	
	set @stop=@start+400000
	WHILE (@count <= @stop) 
	BEGIN 
		INSERT INTO [dbo].[geographic] ([GEOGRAPHIC_ID],[LONG], [LAT]) VALUES ((SELECT ISnull(MAX([GEOGRAPHIC_ID]),1) as c from geographic)+1,RAND()*180.0-90.0, RAND()*180.0-90.0) 
		set @count = @count + 1
	END 
END
GO

Stored procedure för att få fram det du ville ha:
Kod:
/****** Object:  StoredProcedure [dbo].[GetDist]    Script Date: 12/02/2011 11:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDist]
	-- Add the parameters for the stored procedure here
	@pLong float,
	@pLat float,
	@pDist float
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Justera FLOAT
	set @Justera = 0.35


	SELECT *
	FROM
	(
		SELECT *, ( 3959 * ACOS( COS( RADIANS(@pLong) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LONG ) - RADIANS(@pLat) ) + SIN( RADIANS(@pLong) ) * SIN( RADIANS( LAT ) ) ) ) AS distance 
		FROM geographic
                          -- Extra urvalsvillkor för att snabba upp frågan
		WHERE LAT > (LAT-@Justera) AND LAT < (LAT+@Justera)
		AND LONG > (LONG-@Justera) AND LONG < (LONG+@Justera)
	) AS geo
	WHERE distance < @pDist ORDER BY distance

END
GO

Jag var tvungen att först skapa testdata med min sp för detta:
Kod:
EXEC CreateRamdomPositions;
Sen kunde jag köra den normala SPn med varfria parametrar (jag fick ut 219 rader i resultatet):
Kod:
EXEC GetDist -84.093177, 35.922934, 50;
Kod:
GEOGRAPHIC_ID	LONG	LAT	                          distance
231748	36,2256386076705	-84,0477825755256	3,80884213288603
20005	35,7445105631688	-84,033263190999	4,3318317124107
186701	35,424433416423	-84,0558823440571	4,39150040757033
108005	36,5425297929339	-84,0921525164496	4,40681230355622
78602	35,5652975894876	-84,1533625394946	4,86788851297135
.
.
.
164845	29,1025556118211	-84,297305469497	49,6733354458806
169569	33,57303607886	-83,4206588728788	49,7015643727222
249101	42,920426204891	-84,214341976377	49,9240068355831
116678	31,319839796601	-83,5653389912852	49,9637363476681
352070	42,7163354066425	-84,3256493787583	49,9748637448209
Jag hade exekveringstid 00:00:00 dvs mindre än en sekund utan övriga optimeringar.

En klar rekommendation är att du kör så mycket som möjligt av urvalet i SQL så att systemet behöver skyffla så lite data som möjligt. SQL-motorn är mycket effektiv och har en hel del automatiska optimeringar som man slipper tänka på som utvecklare.

Jag har tidigare kört MySQL med den riktiga SQL-motorn (dvs ej ISAM som är en extremt gammal teknik) och nu går det ju från version 5 att köra SP i MySQL.

När jag testar performancetiderna med följande test:
Kod:
declare @stop Datetime
declare @start Datetime
set @start = GetDate()
EXEC GetDist -84.093177, 35.922934, 50;
set @stop = GetDate()
SELECT DateDiff(ms,@start,@stop) AS PerformanceTime
Så får jag varierande tider som 440, 476, 490 millisekunder på min gamla bärbara HP Compaq 6715b från 2007.

Efter att ha lagt till det extra urvalsvillkoret med en kvadrat innan den noggranna avståndskalkylen så ÖKADE körningstider till tider som överstiger 2200 millisekunder. Med 50 miles avståndskrav. Det tog m a o längre tid. Jag vet inte vad det beror på ännu.
Senast redigerad av Conny Westh den 2011-12-02 klockan 16:47
Conny Westh är inte uppkopplad   Svara med citatSvara med citat
Oläst 2011-12-03, 12:51 #2
Magnus_A Magnus_A är inte uppkopplad
Klarade millennium-buggen
  
Reg.datum: May 2006
Inlägg: 2 604
Magnus_A Magnus_A är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: May 2006
Inlägg: 2 604
Citat:
Ursprungligen postat av ConnyWesth Visa inlägg
Jag la upp en test i SQL-server 2008 R2 för att jämföra prestanda (och lära mig någt nytt om Global positionering). Jag upplever dock inget problem med prestandan. De skillnader jag har är att LONG och LAT är float som datatyper, satte upp två sammansatta index (IDX_LONGLAT(LONG,LAT) och IDX_LATLONG(LAT,LONG)) och att jag la upp SQL-frågorna som Stored Procedures, det går blixtsnabbt när jag kör frågan.

Först script för att skapa tabellen:
Kod:
USE [wntest]
GO

/****** Object:  Table [dbo].[geographic]    Script Date: 12/02/2011 11:38:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[geographic](
	[GEOGRAPHIC_ID] [int] NOT NULL,
	[LONG] [float] NOT NULL,
	[LAT] [float] NOT NULL,
 CONSTRAINT [PK_geographic] PRIMARY KEY CLUSTERED 
(
	[GEOGRAPHIC_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Stored procedure för att skapa 400 002 slumpmässigt genererade coordinater i databasen.
Kod:
/****** Object:  StoredProcedure [dbo].[CreateRandomPositions]    Script Date: 12/02/2011 11:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CreateRandomPositions]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @start INT 
	DECLARE @stop INT 
	DECLARE @count INT 
	DECLARE @seed float

	SET @count = 0
	SET @start = 1
	SET @seed = RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) );
	
	set @stop=@start+400000
	WHILE (@count <= @stop) 
	BEGIN 
		INSERT INTO [dbo].[geographic] ([GEOGRAPHIC_ID],[LONG], [LAT]) VALUES ((SELECT ISnull(MAX([GEOGRAPHIC_ID]),1) as c from geographic)+1,RAND()*180.0-90.0, RAND()*180.0-90.0) 
		set @count = @count + 1
	END 
END
GO

Stored procedure för att få fram det du ville ha:
Kod:
/****** Object:  StoredProcedure [dbo].[GetDist]    Script Date: 12/02/2011 11:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDist]
	-- Add the parameters for the stored procedure here
	@pLong float,
	@pLat float,
	@pDist float
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Justera FLOAT
	set @Justera = 0.35


	SELECT *
	FROM
	(
		SELECT *, ( 3959 * ACOS( COS( RADIANS(@pLong) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LONG ) - RADIANS(@pLat) ) + SIN( RADIANS(@pLong) ) * SIN( RADIANS( LAT ) ) ) ) AS distance 
		FROM geographic
                          -- Extra urvalsvillkor för att snabba upp frågan
		WHERE LAT > (LAT-@Justera) AND LAT < (LAT+@Justera)
		AND LONG > (LONG-@Justera) AND LONG < (LONG+@Justera)
	) AS geo
	WHERE distance < @pDist ORDER BY distance

END
GO

Jag var tvungen att först skapa testdata med min sp för detta:
Kod:
EXEC CreateRamdomPositions;
Sen kunde jag köra den normala SPn med varfria parametrar (jag fick ut 219 rader i resultatet):
Kod:
EXEC GetDist -84.093177, 35.922934, 50;
Kod:
GEOGRAPHIC_ID	LONG	LAT	                          distance
231748	36,2256386076705	-84,0477825755256	3,80884213288603
20005	35,7445105631688	-84,033263190999	4,3318317124107
186701	35,424433416423	-84,0558823440571	4,39150040757033
108005	36,5425297929339	-84,0921525164496	4,40681230355622
78602	35,5652975894876	-84,1533625394946	4,86788851297135
.
.
.
164845	29,1025556118211	-84,297305469497	49,6733354458806
169569	33,57303607886	-83,4206588728788	49,7015643727222
249101	42,920426204891	-84,214341976377	49,9240068355831
116678	31,319839796601	-83,5653389912852	49,9637363476681
352070	42,7163354066425	-84,3256493787583	49,9748637448209
Jag hade exekveringstid 00:00:00 dvs mindre än en sekund utan övriga optimeringar.

En klar rekommendation är att du kör så mycket som möjligt av urvalet i SQL så att systemet behöver skyffla så lite data som möjligt. SQL-motorn är mycket effektiv och har en hel del automatiska optimeringar som man slipper tänka på som utvecklare.

Jag har tidigare kört MySQL med den riktiga SQL-motorn (dvs ej ISAM som är en extremt gammal teknik) och nu går det ju från version 5 att köra SP i MySQL.

När jag testar performancetiderna med följande test:
Kod:
declare @stop Datetime
declare @start Datetime
set @start = GetDate()
EXEC GetDist -84.093177, 35.922934, 50;
set @stop = GetDate()
SELECT DateDiff(ms,@start,@stop) AS PerformanceTime
Så får jag varierande tider som 440, 476, 490 millisekunder på min gamla bärbara HP Compaq 6715b från 2007.

Efter att ha lagt till det extra urvalsvillkoret med en kvadrat innan den noggranna avståndskalkylen så ÖKADE körningstider till tider som överstiger 2200 millisekunder. Med 50 miles avståndskrav. Det tog m a o längre tid. Jag vet inte vad det beror på ännu.
Conny, om du nu kör SQL server 2008, varför använder du inte den inbyggda spatiala stödet och funktionen att räkna ut avstånd direkt?
Magnus_A är inte uppkopplad   Svara med citatSvara med citat
Oläst 2011-12-04, 00:24 #3
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
  
Reg.datum: Aug 2005
Inlägg: 5 166
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Citat:
Ursprungligen postat av Magnus_A Visa inlägg
Conny, om du nu kör SQL server 2008, varför använder du inte den inbyggda spatiala stödet och funktionen att räkna ut avstånd direkt?
Har aldrig räknat ut avstånd med longitud och latitud förut så jag är lika mycket nybörjare som någon anna ni just detta case.

Men såg att det fanns en speciell datatyp för detta, ska testa om jag kan pressa tiden med detta.... Kul tankenöt att jobba med dock...
Conny Westh är inte uppkopplad   Svara med citatSvara med citat
Svara

« Föregående ämne | Nästa ämne »

Aktiva användare som för närvarande tittar på det här ämnet: 3 (0 medlemmar och 3 gäster)
 

Regler för att posta
Du får inte posta nya ämnen
Du får inte posta svar
Du får inte posta bifogade filer
Du får inte redigera dina inlägg
BB-kod är på
Smilies är på
[IMG]-kod är på
HTML-kod är av
Forumhopp


Alla tider är GMT +2. Klockan är nu 13:50.

Programvara från: vBulletin® Version 3.8.2
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Svensk översättning av: Anders Pettersson
 
WN Domännamn
Copyright © 2017