I have an application that has some in line SQL that builds a query in a webpage that selects * from a view if a user does not enter in any search criteria on the page. This happens accidentally (no one clicks search wide open on purpose in this case) and the page takes a while to load and a lot of times throws out of memory errors depending on the machine hardware.
I was wondering if there was a way to limit the number of records returned from a view, but also allow the view to return the expected search criteria when the user actually enters something into the search boxes.
Without really thinking I added a TOP (250) to the view, and then realized that there are only 250 records to search through when using the field parameters so that won't work. I was trying to think of a quick way I could alter the view to only return 250 records if there was no WHERE clause in the selection, but also allow for the entire dataset to be searched and have the requested data returned.
As I'm tying this, I'm thinking that's not possible and I'll have to get the application folks to make the change on their side to prevent wide open searches, but they're a lot slower
Since I typed this out I figured I'd ask anyway just in case there's a slick dynamic way to handle this.
if @criterea.count = 0
select top 250 from view
else
select * from view where ...
but do the users know if only returned 250? I would not go this route. I would prevent any searches from happening in the front end unless at least 1 criteria is selected
We can change the application code, but that's another department that will take a few days to make the change, even if it's just to add a TOP (250) to the in line string building sql.
I was trying to think if there was some sort of way to limit the selection on the database side as we can do that in hours as opposed to days.
For the SELECT FROM View without a WHERE you could try a Plan Guide with the USE PLAN option.
ie Look for:
SELECT * FROM YourView;
and use the USE PLAN option with the XML plan from:
SELECT TOP (250) * FROM YourView;
This would at least limit the rows returned without a WHERE clause.
ps
I just tried this on AdventureWorks but unfortunately it comes up with 'USE PLAN hint contains plan that could not be verified to be legal for query.' ie It will not work.
SET SHOWPLAN_XML ON;
GO
SELECT TOP (250) *
FROM [Person].[Person];
GO
SET SHOWPLAN_XML OFF;
GO
SELECT *
FROM [Person].[Person]
-- Copy plan from above
OPTION (USE PLAN N'');
GO
SET SHOWPLAN_XML ON;
GO
SELECT *
FROM [Person].[Person]
ORDER BY BusinessEntityID
OFFSET 0 ROWS FETCH FIRST 250 ROWS ONLY;
GO
SET SHOWPLAN_XML OFF;
GO
SELECT *
FROM [Person].[Person]
-- Copy plan from above
OPTION (USE PLAN N'');
GO
I saw that and tried the OFFSET first, but if I alter the view itself to contain the OFFSET, then it limits the dataset down when there's no WHERE clause which is great, but it also limits the total amount of data the user can search for as the view itself is limited to the number of rows in the FETCH.
I think the fastest way to handle it is to change the application code to only select the TOP (250) until the engineers can put some field validators on the search box to make sure the user can't just hit search without putting in any criteria, or handle the paging better or whatever they want to do.
I'm thinking this would be a whole lot easier if you converted the view to a stored procedure. That would also make it so that you wouldn't need to make front end changes if the requirements changed.
