0

I am currently setting up sql server and geoserver in order to render my own Maps using openlayers on the client web browser. The issue i'm running into is that my SQL Server database contains TONS of roads( every road in texas ) and a simple select on the table takes FOREVER ( almost 2 minutes with 2,348,638 rows ). Does anyone have any advice or recommendations that i can use to speed up this query so that my mapserver doesn't take a million years to render the map tiles for the client. Thanks

user2309367
  • 317
  • 2
  • 8
  • You should post your SQL and table structure if you want anyone to even have a chance at helping you. – thatidiotguy Feb 10 '14 at 20:14
  • Are you selecting all columns and all rows? Why? Is someone really going to usefully consume all 2 million rows, and every single attribute about every road? Also are you running this query from Management Studio on another machine? If so, you need to factor in that transferring 2+ million rows over the network and then having the application render them is going to contribute to some of the slowness. Why not try to focus on solving performance problems that occur when you run real, meaningful queries against the table? – Aaron Bertrand Feb 10 '14 at 20:15
  • 1
    Also it seems to me that, since roads don't really change all that much, you should consider pre-materializing map tiles etc. – Aaron Bertrand Feb 10 '14 at 20:15
  • Do you have any useful links for pre-materializing map tiles? – user2309367 Feb 10 '14 at 20:23
  • 1
    Since we don't know anything else, I'm going to assume you haven't indexed your search filter columns, which is forcing SQL Server to do full table scans every single time. That's the first thing I would look into. Of course, if you could post a sample definition of the table, and the query you're using to select data from it, it would go a long way to help us help you. :) – Kahn Feb 11 '14 at 06:32
  • You absolutely need to be pre rendering the tiles. It's crazy to rerender the tiles every time someone requests them.... – OGHaza Feb 16 '14 at 18:09

1 Answers1

0

Same as for your other question, on partial rendering: index your data. Index on geometry fields and on any other field that you are likely to filter on.

Community
  • 1
  • 1
b2Wc0EKKOvLPn
  • 2,054
  • 13
  • 15