All geek questions in one place

Large data set (from SQL to C #), long load time

I have a website that I create, an application that creates mail merges (more or less ...) based on several user settings. It can generate Cartesian data associations without problems, but as a result, the needs of the enterprise complicate life ...

I need to create an application so that after checking the zip codes of remote employees, it creates emails for multimedia purposes, based on how far the media object is used from this employee. Say, for example, employees know the volunteers well where they work. The company wants to send the media within a 5-mile radius of these employees a message about the work performed by the employee. Here everything becomes messy ... I have several options here that I will describe on attempts and failures:

  • The largest radius is 20 miles. I am creating a database table that stores records of each zip code in the USA, connected to each zip code within 20 miles of this zip code. The data set looks something like this (the names are different, this is for the sake of argument): [SourceZip] | [City] | [State] | [CloseZip] | [City] | [State] | [Distance] Failure: for example, NY has 350 thousand records from the above data set (and other conditions are worse!). Average load time on this page? 6 minutes ... Not happening. I checked this by setting breakpoints, it is at the dataadapter.fill () stage that a break occurs.

  • (This question has never been implemented due to a logistic problem). I am doing a database connection for each zip employee for target zip carriers with a distance of x or less. Except that the source files and multimedia targets are combined, they can exceed 34 thousand individual letters. 34k DB connections? even if I could develop a way to re-search postal codes, I checked some test checks in the database and found that there are 500 different postal codes in NY where employees worked. 500 dB connections? I doubt it will work, but I may be surprised.

  • , , , - , .net, , :
    [zip] | [] | []
    , , . -. ?

    ?

    , , .

: , SQL2k:(. visual studio 2005, framework 2.0. SQL2005 VS2008 .

+3
5

, , 3 ...

SELECT *
FROM Employees_List
   INNER JOIN 
       (Media_List INNER JOIN Distance_List ON Media_List.Zip = Distance_List.Target_Zip)
   ON Employees_List.Zip = Distance_List.Source_Zip
WHERE distance_Miles <=5

Employee Media Distance.

+1

ZIP- , " " Haversine (. ).

- .

:

select * from zip where 
   dbo.udf_Haversine(zip.lat,zip.long, @lat, @lon) < 20   -- (miles)

, ( CTE), , .

+2

Haversine - , ... , , db ( :))

, long/lat long/lat, , .

, , №2. - . , , , , , SQL2k .

zip zip , ~ 2bn . , , , , ..

0

SELECT 350K ( NY) 6 , SOURCEZIP (ALTER TABLE.. ORDER BY (SOURCEZIP)) MySQL. ... ALTER ( ), , .

0

Source: https://habr.com/ru/post/1705307/

More articles:


All Articles