I am working with WMS layer, that has over a million objects. If I want to get feature info from this layer, it doesn't work, because after 2 mins the request is denied (time out).
Then I tried to send request with filter option to get objects (WFS now) in current BBOX. The result was same as before.
I am using Geoserver, database MS SQL and OpenLayers webmap JS library. I think the problem is with Geoserver, because he has problem to serve layers with huge data. If I get info from another layers with lower amount of objects, it worked.
Is there other option to get data ?
There could be a number of problems causing this and it may not be just geoserver. Does the table in MS SQL have a spatial index. It you do queries against the table in MS SQL is it still slow. When you request data from geoserver it passes that query along to the database. If the database is taking along time to respond geoserver will eventually time out.
You can try speeding up the response from the server by tweeking your table and adding indexes and or query hints. You can break up the data into several tables based on location if that is not possible. You could try using postgis or oracle if the spatial component of MS SQL is not up to the challenge. (I have never used MS SQL for spatial work)
So please have a look at your database performance as it is more likely to be the problem than geoserver. If the performance of the database is fine then you will need to look at how geoserver connects to the database and how the layer is configured in geoserver.
For the connection check: Do you have the latest version of Geoserver. Is the Datastore plugin your using the latest and fully supported or still i ndevelopment. Have you set the store to expose the primary keys or added a Primary key metadata table. Is geoserver hitting the max connections limit with the amount of users layers and data. (Note depending on your version of Geoserver and / or plugin some of these options may not be available)
For the layer check: Is the layer being served out in its native projection. Is the layer bounding boxes set correctly.
Geven that this is happening on the get feature info request I still believe the problem is most likely in the database setup.
I hope that helps.