Großer Datensatz (SQL zu C#), lange Ladezeit behoben

Question

Ich habe eine Website, die ich baue, es ist eine Anwendung, die Seriendrucke (mehr oder weniger...) auf der Grundlage einer Reihe von Benutzereinstellungen erstellt. Es kann problemlos kartesische Verknüpfungen im Wert von Daten erzeugen, aber die Anforderungen von Unternehmen machen das Leben ein bisschen schwieriger...

Ich muss die Anwendung so aufbauen, dass sie nach der Überprüfung der Postleitzahlen entfernter Mitarbeiter E-Mails an Medienziele erstellt, je nachdem, wie weit das Medienziel von diesem Mitarbeiter entfernt ist. Nehmen wir an, die Mitarbeiter sind dort, wo sie arbeiten, als Freiwillige bekannt. Das Unternehmen möchte den Medien in einem Radius von 5 Meilen um diese Mitarbeiter eine Nachricht über die Arbeit des Mitarbeiters schicken. Hier wird es unübersichtlich... Ich habe hier mehrere Möglichkeiten, deren Versuche und Misserfolge ich erläutern werde:

1. Der größte Radius beträgt 20 Meilen. Ich erstelle eine Datenbanktabelle, die Datensätze zu jeder Postleitzahl in den USA enthält, die mit jeder Postleitzahl im Umkreis von 20 Meilen verbunden sind. Der Datensatz sieht in etwa so aus (die Namen sind unterschiedlich, dies nur der Argumentation halber):
   [SourceZip] | [City] | [State] | [CloseZip] | [City] | [State] | [Distance]
   Scheitert: Zum Beispiel hat NY 350k Datensätze aus dem obigen Datensatz (und andere Staaten sind noch schlimmer!). Durchschnittliche Ladezeit auf dieser Seite? 6 Minuten... Das passiert nicht. Ich habe dies überprüft, indem ich Haltepunkte gesetzt habe. Es ist während der dataadapter.fill()-Phase, in der die Verbindung unterbrochen wird.

2. (Diese wurde aufgrund eines logistischen Problems nie implementiert) Ich stelle eine Datenbankverbindung für jede Mitarbeiter-Zip zu Medienziel-Zips mit einer Entfernung von x oder weniger her. Allerdings können die Quelldateien und die Medienziele zusammen bis zu 34k individualisierte E-Mails umfassen. 34k DB-Verbindungen? selbst wenn ich einen Weg zur Wiederverwendung von Postleitzahlensuchen entwickeln könnte, habe ich einige Testüberprüfungen in der DB durchgeführt und festgestellt, dass es 500 verschiedene Postleitzahlen in NY gibt, in denen Mitarbeiter gearbeitet haben. 500 DB-Verbindungen? Ich bezweifle, dass das funktionieren würde, aber ich könnte überrascht sein.

3. Mein neuester Plan, um das Problem zu umgehen, besteht darin, zu hoffen, dass der Webserver ein besseres Spiel als das .net-Dataset-Objekt ausführt, indem er ein neues Dataset erhält, wie es aussieht:
   [Postleitzahl] | [Längengrad] | [Breitengrad]
   Dann wird eine Abstandsformel erstellt, um herauszufinden, ob die Daten funktionieren. Dies hängt stark von den Prozessoren des Webservers ab. Ist dies ein lohnendes Unterfangen, oder werde ich auch bei diesem Versuch die gleichen Ladezeitschäden feststellen?

   Gibt es einen besseren Weg?

   Ich bin für jeden Beitrag dankbar, auch wenn er meine Befürchtungen bestätigt, dass dieses Projekt funktioniert vielleicht nicht .

Zusätzliche Hinweise : Ich habe keine Kontrolle über den Server, und ich verwende SQL2k :(. Ich programmiere die Seite in Visual Studio 2005, Framework 2.0. Möglicherweise werde ich in den nächsten Monaten auf SQL2005 und VS2008 aufgerüstet.

Answer 1

Wenn Sie eine Postleitzahlendatenbank mit Längen- und Breitenkoordinaten haben, können Sie die Entfernung mit meiner Haversine-Funktion berechnen (siehe meine Antwort auf diese Frage ).

Dies funktioniert sehr gut in Webanwendungen mit den gesamten US-Postleitzahlendaten.

Die Abfrage würde in etwa wie folgt aussehen:

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

Sie würden dies nicht auf jede Empfängeradresse anwenden, sondern zunächst die Postleitzahlen in Ihrem Umkreis ermitteln (in einer verschachtelten Abfrage oder mit einer CTE) und dann alle Adressen hinzufügen, an die Sie eine E-Mail senden müssen.

Answer 2

Wenn Sie einen Datensatz für Ihre Mitarbeiter, einen Datensatz für Ihre Medien und einen dritten Datensatz für die Entfernung zwischen Quell- und Ziel-Zip haben, können Sie ein wenig Zeit sparen, indem Sie die 3 Tabellen miteinander verbinden...

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

Auf diese Weise legen Sie die Beziehungen zwischen dem Mitarbeiter und den Medien über den Abstand fest.

Answer 3

EDIT Nach der Untersuchung ist die Antwort mit der Haversine-Funktion der Weg, den ich nehmen würde... sie ist nicht so intensiv wie die Funktion, die unsere Datenbank verwendet (was behoben werden wird :))

Sie sollten no die Entfernungen jedes Mal zu berechnen, ist es eine schwere Berechnung von lang/lang zu lang/lang, und wenn Sie es mehr als einmal tun, ist es unnötig.

Davon abgesehen weiß ich nicht, warum Sie Option 2 bereits abgeschrieben haben. Wir machen tatsächlich etwas Ähnliches wie das hier. Vielleicht verwirren mich die Zahlen, aber was Sie erwähnen, sollte für SQL2k kein Problem darstellen.

Selbst wenn Sie offline die Entfernung von Postleitzahl zu Postleitzahl in den USA berechnen, gibt es nur ~2 Mrd. Zeilen. Ja, das ist eine Menge, aber sie ist relativ statisch, könnte gesplittet werden, wenn sie langsam ist, usw.

Answer 4

SELECT von 350K Zeilen (Ihr Beispiel für NY) wird keine 6 Minuten dauern, wenn Sie die Tabelle und den Index nach SOURCEZIP ordnen (ALTER TABLE .. ORDER BY (SOURCEZIP) ) in MySQL. Es sollte nur einen Bruchteil einer Sekunde dauern ... Das ALTER wird eine lange Zeit in Anspruch nehmen (oder Sie könnten die Tabelle in dieser Reihenfolge erstellen) - aber da es sich um eine statische Tabelle handelt, wäre das nichts wert.

Answer 5

Verwenden Sie SQL 2008? Dann könnten die neuen Geodatenfunktionen genau das Richtige für Sie sein. Sie können Koordinaten, die sich in einem anderen Bereich befinden, genauso einfach finden wie mit einem "LIKE"-Vergleich für Zeichenketten.

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx