3

I have a closed source ASP.NET system that is calling a very expensive SQL view:

SELECT TOP 100 PERCENT * 
FROM [v]  
WHERE [u] =  9999 
ORDER BY [ID]

I have access to the database, so I can change the view - there is no easy way to make the view faster.

I would like to programmatically change the behaviour of the view based on which IIS server is performing the request.

Is this possible?

E.g. I would like to create a stored procedure that would be called from the view, and this stored procedure would return a table that mimics the current view, but if called from a particular IIS server would return something different.

Is it possible to modify the view to call a stored procedure so that the asp.net code doesn't need any modifications?

I can see from other stack overflow articles how to call a stored procedure from a view, but how can I access the where clause inside the stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1279887
  • 489
  • 4
  • 20
  • 1
    What do you mean by "call a stored procedure from a view"? That doesn't make any sense to me at all. You aren't going to magically make this faster by using a stored procedure instead of a view. You most likely to add some proper indexing. Please post the table and index definitions and we can help. – Sean Lange Sep 12 '16 at 21:37
  • "SELECT TOP 100 PERCENT"? This is just silly to begin with. You dotn call procs from inside views (hence the previous comment). You can create a stored procedure and pass the server name to it, then execute different selects based on that parameter. but you still need to modify the .NET code to make the call to the procedure instead of the view. Short answer, what you are trying to do is not feasible to my knowledge. – Ricardo C Sep 12 '16 at 21:54
  • a view is a structured query that SQL treats similar to a table (many exceptions) in that it is encapsulates logic of a query. You cannot call a proc that alters a view's structure (it would not be a view then), but you can use the proc to return a query that works like you want. I guess the question is do you need the persistent query? Is there some reporting tool that needs it? Also, please consult MSDN next time regarding DDL. Though this [Query vs View](http://stackoverflow.com/questions/324177/query-vs-view) is good – clifton_h Sep 13 '16 at 02:34
  • Some exceptions apply, but simply put, a view is a query that is reused. – clifton_h Sep 13 '16 at 02:40
  • User Defined Function that returns a table type. – Sql Surfer Sep 13 '16 at 02:47
  • Thanks for your comments - the whole point is to not actually return the real view in some contexts, but an equivalent table that has only one row. That way for users that don't need the real data (isolated to a particular IIS instance), I can speed up their user experience. Apparently you can call a stored proc from a view. – user1279887 Sep 13 '16 at 15:03

1 Answers1

0

Given that your ASP.NET code presumably has a Select * From [view] code embedded in it and you cannot change that, you have the following hurdles:

  1. You need to create a multi-statement table-valued function that can detect which IIS instance you're querying from without any tailored inputs from the application (e.g., look at the user or service account, IP address). The benefit of using a multi-statement table-valued view is that you can actually create two separate Select statements and then conditionally return one or the other. You can do this in a Stored Procedure as well, but I think you'll find it simpler to call the multi-statement table-valued function from a view than the stored procedure.

  2. You need to change the view to do a Select * from your new multi-statement table-valued function.

Caveats

When you change the definition of the view utilized by your application, you will also be changing the view for any other user or application that accesses it. Here is a discussion on how to see who has been using a particular view and how often. I would recommend looking into whether or not you have any other applications or reporting tools that are pulling from that view, as changing the underlying code could potentially break those other applications.

Community
  • 1
  • 1
Christopher Brown
  • 2,170
  • 1
  • 13
  • 19