July 7, 2006 at 3:51 am
Hi,
I'm trying to speed up a query on a database table i'm using for a UK post code search. It has a considerable number records and was wandering if there was any way of increasing the speed of the query.
If anybody has any thoughts or comments it would be appreciated.
Cheers
Mark
July 7, 2006 at 4:20 am
Could you post the query?
July 7, 2006 at 4:23 am
Do your table have any indexes? or what columns? what type of indexes? how many records are retrieved by the query? how much time it takes now? is table statistics updated?
Unless you give more relevant information, how u could expect with such vague question?
helping us will help u !
July 7, 2006 at 4:24 am
I'm just selecting all from the database and working out the calculations on an ASP web page.
I just need to be able to return the amount of rows quicker, I don't know if theres any way round this.
No i'm not using indexes, there are 1610000 records returned. I've been looking at indexes and no tables have to be updated.
Many thanks Mark
July 7, 2006 at 4:27 am
And the columns are postcode, latitude and longitude.
Thanks again Mark
July 7, 2006 at 4:56 am
The shear amount of data transfered from the database to the web page exceed 36 mb!
Multiply that with the number of concurrent users and you most probably will congest the network and sink the web server.
If possible, do the calculations on the database server and return only the information you want.
N 56°04'39.16"
E 12°55'05.25"
July 7, 2006 at 5:08 am
Thanks for the advice Peter,
My post code database while still on the same SQL Server is a seperate database from my dealers database, could i still perform the processing on the database server?
Thanks mark
July 7, 2006 at 5:15 am
Yes. If the two databases are installed on the same database server, use fully qualified name to get the data.
MyDatabase.Owner.Table.Field -> Master.dbo.spt_Values.Number
If the data exists on a different database server, create a Linked Server to access the data you want.
N 56°04'39.16"
E 12°55'05.25"
July 7, 2006 at 5:22 am
My ASP Distance function calculates the longitude and latitude between two postcodes and uses mathematical functions, does SQL Server support these functions, any advice would be appreciated. Thanks mark
This is my ASP function:
'**********************************************************
'Distance function takes Latitude & Longitude of the users*
'postcode & Latitude & Longitude of Dealers post code *
'and calculates the distance between the the two postcodes*
'in miles *
'**********************************************************
Function Distance(dblLat1,dblLong1,dblLat2,dblLong2)
'degrees to radians
dblLat1 = dblLat1 * PI / 180
dblLong1 = dblLong1 * PI / 180
dblLat2 = dblLat2 * PI / 180
dblLong2 = dblLong2 * PI / 180
Dim dist: dist = 0
If dblLat1 dblLat2 Or dblLong1 dblLong2 Then
dist = Sin(dblLat1) * Sin(dblLat2) + Cos(dblLat1) * Cos(dblLat2) * Cos(dblLong2 - dblLong1)
dist = EARTH_RADIUS * (-1 * Atn(dist / Sqr(1 - dist * dist)) + PI / 2)
End If
Distance = dist
End Function
July 7, 2006 at 6:02 am
Begin with creating a function for calculating the distance between two coordinates, like this
CREATE FUNCTION dbo.fnPostalDist
(
@Lat1 FLOAT,
@Long1 FLOAT,
@Lat2 FLOAT,
@Long2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Dist FLOAT,
@Rad FLOAT
SELECT @RAD = 0.017453292519943295, -- PI() * 180
@Dist = SIN(@Lat1 * @RAD) * SIN(@Lat2 * @RAD) + COS(@Lat1 * @RAD) *
COS(@Lat2 * @RAD) * COS(@Long2 * @RAD - @Long1 * @RAD)
RETURN EARTH_RADIUS * (-1 * ATAN(@Dist / SQRT(1 - @dist * @dist)) + 1.5707963267948966)
-- last number is PI() / 2
END
Then you must have a stored procedure where you do the other work, like this
CREATE PROCEDURE uspGetDistances
(
@BoundingBox FLOAT = 0.001--Use any number appropiate
)
AS
SET NOCOUNT ON
-- 1,610,000 Postal codes
--
-- Every one of them has the distance calculated to every one else, inclusive itself.
-- That is n * n distances, which is 2,592,100,000,000 ~ 2,600 billion combinations!
--
-- This can be optimized.
--
-- A) There is no need for calulating the distance from A -> A, right?
-- B) The distance from A -> B is the same as the distance from B -> A, right?
--
-- Then we can estimate that only n * (n - 1) / 2 records will be returned,
-- which is still a staggering 1,296,050,805,000 distances ~ 1,300 billion distances
--
-- Yes, you read it right! 1,300 billion calculations!
-- But this is half the amount of the original distances anyway.
--
-- An other optimization technique we can use, is to have a bounding box.
-- Limit the number of combinations around a postal code so that only calculations
-- within a box of say 50 miles are returned.
SELECT mt1.PostalCode FromCode,
mt2.PostalCode ToCode,
dbo.fnPostalDist(mt1.Lat, mt1.Long, mt2.Lat, mt2.Long) Dist
FROM MyTable mt1
CROSS JOIN MyTable mt2
WHERE mt1.Lat < mt2.Lat -- No return trip
AND mt1.Long < mt2.Long -- No return trip
AND ABS(mt1.Lat - mt2.Lat) <= @BoundingBox -- Bounding box limit
AND ABS(mt1.Long - mt2.Long) <= @BoundingBox -- Bounding box limit
Good luck!
N 56°04'39.16"
E 12°55'05.25"
July 7, 2006 at 6:50 am
Thanks again Peter,
I've actually got the search feature working using ASP & I just looked back over my original question,
I am actually only returning the rowes from the postcode database that match my dealers database. It does work but there is a bit of a lag in the speed it returns the results to the webpage as the search involves a lot of records.
I was actually wandering if there was any way to actually speed up the search? Thanks again for your help and sorry I wasn't clear enough with my question.
Cheers Mark
July 7, 2006 at 7:24 am
I don't think it is the database that is the bottleneck of this. Transferring 1.6 million rows to the webpage is!
If these rows are all combinations of distances, you have only between 1,250 and 1,800 postal codes in your database table.
And the fact that your page has to iterate all rows (each is approx 30 bytes to send over the network) and calculate the distance for each and one of them. That is what is taking the time, not fetching from the database. Also, keeping a connection open and a recordset that is 36 mb in size for every simultaneous user, slows down the web server significantally.
Play with the thought that 1 row is processed in 1 millisecond only (very low estimate), the calculation for all rows take near 27 minutes. Not to mention the time spent sending one row sequentially from the database to your page. Plus the time marshalling the data and all the other overhead ASP and VbScript has.
A well estimated guess is that your page need more than 1 hour to process all rows. Could be as much an ten hours.
Compare that to the time needed for the database to fetch all rows as a resultset, all done and limited at database level. I would guess 10-20 seconds.
If you need to improve speed, start where the culprit is.
And to the question you asked "I was actually wandering if there was any way to actually speed up the search"... Yes there is, I posted a function and a stored procedure to you.
Good luck. It is not my intention to be mean to you. I am just setting things into perspective.
N 56°04'39.16"
E 12°55'05.25"
July 10, 2006 at 1:49 am
Thanks for your help Peter and posting the stored procedure and function, this has been a great help to me.
Many thanks
Mark
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply