WN

WN (https://www.wn.se/forum/index.php)
-   Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)
-   -   Prestandaproblem med komplicerad SQL och 400.000 rader (https://www.wn.se/forum/showthread.php?t=1051232)

gregoff 2011-11-30 21:35
Prestandaproblem med komplicerad SQL och 400.000 rader
 
Nu fick jag en svårare nöt att knäcka. Jag har en tabell som innehåller ca 400.000 rader och alla (nästan) har en latitud/longitud koordinat. Jag vill då skriva frågor som innebär att lista de närmaste raderna inom en viss radie utifrån en given punkt. Exempel:

SELECT *, ( 3959 * ACOS( COS( RADIANS(AAA) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LONG ) - RADIANS(OOO) ) + SIN( RADIANS(AAA) ) * SIN( RADIANS( LAT ) ) ) ) AS distance FROM tabellen_ifråga HAVING distance < X ORDER BY distance

Där är AAA latituden, OOO är longituden och X är avståndet (radien) utifrån utgångspunkten.

Problemet är inte frågan i sig (utan den får de svar den ska ha), utan mitt problem är prestandan. Att köra ovannämnda fråga kan ta ca 1 sekund och i vissa fall kan jag behöva att köra den 3 gånger på en sidvisning.

Tabeller ser ut enl. nedan:

ID - PK
LONG - VARCHAR(15) - INDEX
LAT - VARCHAR(15) - INDEX

Exempel på en koordinat kan vara:
LONG: -84.093177
LAT: 35.922934

Jag kör MySQL 5.1.58 och MyISAM som db-motor (standard)

Vad kan jag göra? Jag kör detta på en vanlig VPS hos Glesys med Debian 6, 64-bit

Tips mottages gladligen!

tartareandesire 2011-11-30 21:50
Jag brukar köra en betydligt enklare avståndsformel i databasqueryn och först därefter räkna mer exakt avstånd på de utvalda. Trigonometriska funktioner är tunga.

gregoff 2011-11-30 21:54
Citat:
Ursprungligen postat av tartareandesire (Inlägg 20425752)
Jag brukar köra en betydligt enklare avståndsformel i databasqueryn och först därefter räkna mer exakt avstånd på de utvalda. Trigonometriska funktioner är tunga.
Har du något exempel? Det exakta avståndet räknar jag ändå ut med php, så det är bara rätt urval jag behöver att utgå ifrån.

Magnus_A 2011-11-30 22:34
Pröva gärna postgresql och ta fram avstånd direkt med ST_Distance i postgis.

gregoff 2011-11-30 22:35
Citat:
Ursprungligen postat av Magnus_A (Inlägg 20425760)
Pröva gärna postgresql och ta fram avstånd direkt med ST_Distance i postgis.
Har tyvärr inte möjlighet att byta sql-databas.

abergman 2011-12-01 03:33
Vad har du för prestanda på den maskinen? Har du provat att lägga på mer power och se om det blir något större förändring?

Westman 2011-12-01 06:57
Varför köra den flera gånger, är det med olika X?

gregoff 2011-12-01 06:58
På en VPS hos Glesys kan man välja antal kärnor man vill ha, men inte hur "snabba" dom ska vara. Av eller på, typ.

Citat:
Ursprungligen postat av abergman (Inlägg 20425772)
Vad har du för prestanda på den maskinen? Har du provat att lägga på mer power och se om det blir något större förändring?

andreasp 2011-12-01 09:38
Själva grundproblemet här är att din query inte kan använda några index -- och blir därför väldigt slö eftersom den måste söka igenom hela tabellen och applicera din formel på alla rader. En snabbare CPU/biffigare server är knappast lösningen på problemet. 400 000 rader som är indexerade kräver inte någon extrem hårdvara att söka igenom snabbt.

En lösning skulle kunna vara typ:
SELECT *, <lång formel> AS distance FROM tabellen_ifråga WHERE (lat BETWEEN A AND B) AND (lon BETWEEN C AND D) HAVING distance < X ORDER BY distance

Där A, B, C och D är yttre gränser för platser som inte är intressanta. Om gränserna uppskattas vettigt så borde de skära bort den största delen av alla rader effektivt, och sen kan resten sökas igenom med den "tunga" formeln. Det är viktigt att lat/lon är indexerade så att de kan sökas effektivt (som du har skrivit att de redan är). Är inte helt säker på hur bra jämförelserna blir när du lagrar LAT/LONG som VARCHAR (minustecknet kan ställa till problem), det vore nog lämpligare (och garanterat effektivare) med en numerisk datatyp.

MySQL känns i det här fallet inte som den bästa lösningen på problemet. Som Magnus_A skriver så skulle PostgreSQL/PostGIS lösa problemet väldigt enkelt med ett spatial index och ST_Distance. MySQL har även en liknande spatial extension, som inte är speciell komplett, och är inte säker på att den kan lösa problemet effektivt överhuvudtaget (men jag kan ha fel!).

MongoDB har även stöd för spatial indexes och distans-queries, om du kunde lägga dina koordinater där och göra själva distans-sökningen där skulle det kunna vara ett alternativ.

Jag är rädd att det inte finns någon riktigt bra lösning med MySQL förutom någon slags workaround som ovan, som både är krånligare, mindre effektiv och framför allt: fulare! ;)

gregoff 2011-12-01 09:50
Känns ändå som att detta gav mig lite ledtrådar.

Istället för att använda en jobbig formel (som troligen är ganska krävande) så skulle man kanske kunna använda sig av en kvadrat istället för radie. Exempelvis att man ställer frågan utifrån LAT plus/minus X och LONG plus/minus X (med hjälp av t.ex. BETWEEN). Att jag får lite mer svar än jag skulle fått med den jobbiga formeln gör inte så mycket för det kan jag enkelt sortera bort med en liknande php-formel.

Kanske kunde vara något för mig att testa.

Kan nog vara värt att testa att konvertera columnen till lämplig datatyp med innan man gör något annat först.

Citat:
Ursprungligen postat av andreasp (Inlägg 20425790)
Själva grundproblemet här är att din query inte kan använda några index -- och blir därför väldigt slö eftersom den måste söka igenom hela tabellen och applicera din formel på alla rader. En snabbare CPU/biffigare server är knappast lösningen på problemet. 400 000 rader som är indexerade kräver inte någon extrem hårdvara att söka igenom snabbt.

En lösning skulle kunna vara typ:
SELECT *, <lång formel> AS distance FROM tabellen_ifråga WHERE (lat BETWEEN A AND B) AND (lon BETWEEN C AND D) HAVING distance < X ORDER BY distance

Där A, B, C och D är yttre gränser för platser som inte är intressanta. Om gränserna uppskattas vettigt så borde de skära bort den största delen av alla rader effektivt, och sen kan resten sökas igenom med den "tunga" formeln. Det är viktigt att lat/lon är indexerade så att de kan sökas effektivt (som du har skrivit att de redan är). Är inte helt säker på hur bra jämförelserna blir när du lagrar LAT/LONG som VARCHAR (minustecknet kan ställa till problem), det vore nog lämpligare (och garanterat effektivare) med en numerisk datatyp.

MySQL känns i det här fallet inte som den bästa lösningen på problemet. Som Magnus_A skriver så skulle PostgreSQL/PostGIS lösa problemet väldigt enkelt med ett spatial index och ST_Distance. MySQL har även en liknande spatial extension, som inte är speciell komplett, och är inte säker på att den kan lösa problemet effektivt överhuvudtaget (men jag kan ha fel!).

MongoDB har även stöd för spatial indexes och distans-queries, om du kunde lägga dina koordinater där och göra själva distans-sökningen där skulle det kunna vara ett alternativ.

Jag är rädd att det inte finns någon riktigt bra lösning med MySQL förutom någon slags workaround som ovan, som både är krånligare, mindre effektiv och framför allt: fulare! ;)

Westman 2011-12-01 12:07
andreasp har svarat samma sak kom jag på

pelmered 2011-12-01 15:51
Testa att byta datatyp till FLOAT.

Dina index blir väl som sagt inte så effektiva eftersom queryn ändå måste söka igenom allt för att göra beräkningar.
Tyvärr är det ju även svårt att cahcea den typen av query då det är sällan man får exakt samma koordinater som indata vilket gör att det blir få cahce-träffar.

Har du en valtrafikerad site kanske du kan lösa det på något sätt med någon sökmotorsmjukvara som t.ex. Solr eller ElasticSearch. Det kommer dock innebära en hel del konfigurerade.

Edit igen: Det verkar finnas en del om man söker lite:
http://www.google.se/search?q=Elasti...h+geo+distance
http://www.elasticsearch.org/guide/r...nce-facet.html

Magnus_A 2011-12-01 19:59
Citat:
Ursprungligen postat av andreasp (Inlägg 20425790)
...

MySQL känns i det här fallet inte som den bästa lösningen på problemet. Som Magnus_A skriver så skulle PostgreSQL/PostGIS lösa problemet väldigt enkelt med ett spatial index och ST_Distance. MySQL har även en liknande spatial extension, som inte är speciell komplett, och är inte säker på att den kan lösa problemet effektivt överhuvudtaget (men jag kan ha fel!).

MongoDB har även stöd för spatial indexes och distans-queries, om du kunde lägga dina koordinater där och göra själva distans-sökningen där skulle det kunna vara ett alternativ.

Jag är rädd att det inte finns någon riktigt bra lösning med MySQL förutom någon slags workaround som ovan, som både är krånligare, mindre effektiv och framför allt: fulare! ;)
Mysql kan inte beräkna avstånd, och kan endast räkna på relationer till bounding-box. Det blir nog aldrig mer än så eftersom oracle har egna spatiala databaser till salu.

Sqlite har också en spatial extension men det är knappast praktiskt möjligt med de antal rader du har.

gregoff 2011-12-01 21:53
Tack alla för feedback!

Jag har kommit fram till att trimma min befintliga lösning. Ändrade datatypen till float och gjorde en avgränsning av koordinaterna i sql-frågan (med hjälp av BETWEEN och +- en viss marginal på utgångskoordinaten) för att sen finjustera med php.

Resultatet blev att laddningstiden sjönk nästan med hälften. En laddningstid på drygt en sekund på de tunga sidorna känns som att jag får acceptera. De flesta sidorna har en laddningstid på 0,1 sekunder numera (mot ca 0,4 innan).

Laddningstid = tid för att generera färdig html.

coredev 2011-12-01 23:41
Citat:
Ursprungligen postat av gregoff (Inlägg 20425894)
Tack alla för feedback!

Jag har kommit fram till att trimma min befintliga lösning. Ändrade datatypen till float och gjorde en avgränsning av koordinaterna i sql-frågan (med hjälp av BETWEEN och +- en viss marginal på utgångskoordinaten) för att sen finjustera med php.

Resultatet blev att laddningstiden sjönk nästan med hälften. En laddningstid på drygt en sekund på de tunga sidorna känns som att jag får acceptera. De flesta sidorna har en laddningstid på 0,1 sekunder numera (mot ca 0,4 innan).

Laddningstid = tid för att generera färdig html.
Hej gregoff,

Vore intressant om ville dela med dig av följande:
* hur skapar du den rektangeln som du selekterar SQL-en med - utifrån från centrumpunkten och utvidgar den till en fyrkant med (cirkelradien * 2) som sidlängd?
* hur arbetar du med finjusteringen i PHP

Jag har gjort liknande saker, och en något som snabbade upp för mig var att bryta ut long + lat + "POI-id" till en egen tabell och lägga på index på long och lat. Men det beror ju lite på databasdesign & mängd data om det lönar sig. Vissa värden i sql-cirkelformeln går även att förkalkylera utanför databaslagret.

Mvh Robert

pelmered 2011-12-02 08:06
Citat:
Ursprungligen postat av gregoff (Inlägg 20425894)
Tack alla för feedback!

Jag har kommit fram till att trimma min befintliga lösning. Ändrade datatypen till float och gjorde en avgränsning av koordinaterna i sql-frågan (med hjälp av BETWEEN och +- en viss marginal på utgångskoordinaten) för att sen finjustera med php.

Resultatet blev att laddningstiden sjönk nästan med hälften. En laddningstid på drygt en sekund på de tunga sidorna känns som att jag får acceptera. De flesta sidorna har en laddningstid på 0,1 sekunder numera (mot ca 0,4 innan).

Laddningstid = tid för att generera färdig html.
Smart lösning!
Jag skulle också tycka att det skulle vara väldigt intressant att höra lite mer om hur du gjort eller till och med få se lite kod.
Gör du beräkningen och avståndet och sorteringen i (My)SQL eller PHP?

Det där fungerar nog bra så länge du inte har allt för många besökare, men det är ju en sån sak som kan sänka servern vid mycket trafik.

gregoff 2011-12-02 08:09
Absolut!

I grova drag handlar problemet om att jag vill ha ut närliggande koordinater utifrån en fast punkt.

Jag började med att räkna ut hur stor en kvadrat blir som precis omger cirkeln med samma radie som jag är ute efter (ex 20 miles). En av sidorna på kvadraten är därmed 40 miles. Det längsta avståndet inom kvadraten mätt ifrån mittpunkten är till ett av hörnen och blir i detta fallet ca 28,3 miles (pythagoras sats).

Andra delen i lösningen är att hitta rätt felmarginal i koordinaterna som motsvarar max 28,3 miles (jag landade på ca 29 miles enl. nedanstående exempel).

Exempel (koordinaten är tagen ur luften):
$lat: 30.1234
$long: -63.5552
$p: 0.35 (kom jag fram till via trial & error)

($p är "felmarginalskoordinat")

Så jag körde en SELECT med dom data jag ville ha ut och körde med villkoren

$sql = "SELECT LAT,LONG FROM tabellen
WHERE (LAT BETWEEN ".($lat-$p)." AND ".($lat+$p).")
AND (LONG BETWEEN ".($long-$p)." AND ".($long+$p).")";

Därefter kör jag en avståndsberäkning mellan svaren och min fasta punkt och sorterar med PHP enl. tex:
http://www.web-max.ca/PHP/misc_2.php

Efter sorteringen kan jag enkelt sortera bort dom som ligger utanför cirkelradien.

Ungefär så...

Citat:
Ursprungligen postat av coredev (Inlägg 20425904)
Hej gregoff,

Vore intressant om ville dela med dig av följande:
* hur skapar du den rektangeln som du selekterar SQL-en med - utifrån från centrumpunkten och utvidgar den till en fyrkant med (cirkelradien * 2) som sidlängd?
* hur arbetar du med finjusteringen i PHP

Jag har gjort liknande saker, och en något som snabbade upp för mig var att bryta ut long + lat + "POI-id" till en egen tabell och lägga på index på long och lat. Men det beror ju lite på databasdesign & mängd data om det lönar sig. Vissa värden i sql-cirkelformeln går även att förkalkylera utanför databaslagret.

Mvh Robert

Conny Westh 2011-12-02 11:45
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.

pelmered 2011-12-02 11:55
Okej, tack!

Om man vill variera radien borde konstanten vara någonstans runt 0,009 per km om man kollar här:
http://en.wikipedia.org/wiki/Latitude#Degree_length

1/112(för att vara säker på att få med allt) ~ 0,008 per km

Konstanten kan man ju anpassa en del och ha olika för latituden och longituden efter vart man är för longituden varierar ju väldigt mycket beroende på vart man är.

gregoff 2011-12-03 12:12
Kan nämna att jag fick ner laddningstiden till i snitt 0.15 sekunder. Lösningen var att fixa ett nybörjarmisstag...

Det var inte databassökningarna som var problemet. Problemet var PHP. Jag tänkte inte på att avgränsa mina sql-frågor, så vissa frågor gav 30.000 rader som svar, och det tar en stund för PHP att bearbeta (att skicka från databasen till en variabel i php antar jag).

I och med att jag aldrig vill ha mer än 20 svar i min lista, så körde jag en LIMIT 0,20 på sql-frågan och vips var laddningstiden ner till en tiondel på de tyngsta sidladdningarna.

Ifall att någon ville veta ;-)

Magnus_A 2011-12-03 12:51
Citat:
Ursprungligen postat av ConnyWesth (Inlägg 20425926)
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?

Conny Westh 2011-12-04 00:24
Citat:
Ursprungligen postat av Magnus_A (Inlägg 20426044)
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...

frecco 2011-12-06 22:24
Citat:
Ursprungligen postat av gregoff (Inlägg 20426042)
Kan nämna att jag fick ner laddningstiden till i snitt 0.15 sekunder. Lösningen var att fixa ett nybörjarmisstag...

Det var inte databassökningarna som var problemet. Problemet var PHP. Jag tänkte inte på att avgränsa mina sql-frågor, så vissa frågor gav 30.000 rader som svar, och det tar en stund för PHP att bearbeta (att skicka från databasen till en variabel i php antar jag).

I och med att jag aldrig vill ha mer än 20 svar i min lista, så körde jag en LIMIT 0,20 på sql-frågan och vips var laddningstiden ner till en tiondel på de tyngsta sidladdningarna.

Ifall att någon ville veta ;-)
Använde du ett gemensamt index för båda kordinaterna? Jag fick lite bättre tider när jag gjorde så på stora datamängder.

Först gjorde jag som jag tror att du gjort och körde sedan alter table positions add index (lat, lon); vilket gjorde det lite lite snabare.

gregoff 2011-12-07 07:07
Citat:
Ursprungligen postat av frecco (Inlägg 20426427)
Använde du ett gemensamt index för båda kordinaterna? Jag fick lite bättre tider när jag gjorde så på stora datamängder.

Först gjorde jag som jag tror att du gjort och körde sedan alter table positions add index (lat, lon); vilket gjorde det lite lite snabare.
Nja jag har enskilda index per kolumn. Har inte testat så som du skriver.


Alla tider är GMT +2. Klockan är nu 11:24.

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