Stored Procedures vs. Dynamic SQL Pass Through Query based on a View

[ions]

Hello MS Access Expert,

Are there any advantages to a Stored Procedures vs. a Dynamic SQL Pass Through Query based on a View?

Suppose I have a View and I need to include a user defined Parameter on the View from the MS Access Front End. I can create a Pass Through Query with a Where Clause on the View (Select * from myView Where x = y). If there are no Parameters I simply change the SQL to (Select * from myView). Is there any benefit if I were to recreate the above in a Stored Procedure instead of dynamically altering the Pass Through Query based on the View?

Thank you

 

[theDBguy]

Did you not want to create a linked table for that view?

 

[Isaac]

Hello MS Access Expert,

Are there any advantages to a Stored Procedures vs. a Dynamic SQL Pass Through Query based on a View?

Suppose I have a View and I need to include a user defined Parameter on the View from the MS Access Front End. I can create a Pass Through Query with a Where Clause on the View (Select * from myView Where x = y). If there are no Parameters I simply change the SQL to (Select * from myView). Is there any benefit if I were to recreate the above in a Stored Procedure instead of dynamically altering the Pass Through Query based on the View?

Thank you

I think the most truthful answer I can give is that there are dozens of different variables and factors going into whether the server might process your stored procedure quicker or your select from The View. In this answer I'm assuming that it's a pass-through query either way so the server is processing it. We would have to see your entire SQL for the stored procedure or the view to have any idea what might be faster. Or how to optimize either one. There is no short answer here

 

[cheekybuddha]

As @theDBguy suggests, just link the view in the same way you would link a table.

No need for either stored procs, dynamic sql, or pass-throughs.

Much simpler to let Access do the work for you.

 

[Isaac]

I'm just not sure how anyone can know whether selecting from the View vs. a stored procedure can be better when we haven't seen any code.
If I were forced at gunpoint to make such an ill-advised generalization, my money would be on one's ability to better optimize using a procedure only because it would be easier to utilize temp tables and such linear progressions to possibly optimize the joins and add indexes if needed rather than one giant select statement, AND because most corporate entities with a lot of sql server artifacts are filled to the brim with awful View situations, nesting, references 3 layers deep that nobody hardly knows or thinks about what is actually having to execute when they type select * from View, but still ...

I can't remember a time I've optimized a query without looking at code, but maybe I missed the AI wave

 

[cheekybuddha]

Isaac,

I'm not sure what is 'ill-advised'.

The view is the view; perhaps it needs optimising, but that is not what is being asked here.

The question is whether using a stored proc or a pass-through is better to perform the following queries:
Select * from myView Where x = y;
Select * from myView;

If those are the queries, then neither a stored proc nor a pass-through offers any advantage over straight linking.

If anything, they are worse than a straight link, since the returned data will be non-updateable, which might have a bearing.

 

[ions]

Thank you for everyone's responses.

The Where Predicate on the View can get complex (See code below) depending on the search criteria from the user but what I am gathering is I can link the View and still create the Dynamic SQL on an Access Query. MS Access will send the below Access Query directly to SQL Server and process everything on the Server side?

Thank you

SELECT *

FROM VMarketActivity2Final

WHERE [WasteClass] LIKE '111%'

    AND [WasteChar] = 'C'

    AND [TotalKG] > 10000

    AND [TotalWasteCount] > 500

    AND [CITY] LIKE 'Toronto%'

    AND [POSTALCODE] LIKE 'L%'

    AND [NAICS] LIKE '%Waste%'

ORDER BY TotalKG

 

[cheekybuddha]

The processing should take place on the server with the posted WHERE clause (you are not using any local/non T-SQL functions etc).

If you can, avoid selecting all records (ie without a WHERE clause) unless the view returns not many records.

However, ultimately, you ought to test the 3 different scenarios for best speed (linked view, pass-through, and stored proc), but my guess is that with what you have described pass-through and stored proc will not yield a major benefit, and will also render your resultsets un-updateable.

 

[ions]

Thank you CheekyBuddha and everyone else who contributed to this thread.

 

[Isaac]

Isaac,

I'm not sure what is 'ill-advised'.

The view is the view; perhaps it needs optimising, but that is not what is being asked here.

The question is whether using a stored proc or a pass-through is better to perform the following queries:
Select * from myView Where x = y;
Select * from myView;

If those are the queries, then neither a stored proc nor a pass-through offers any advantage over straight linking.

If anything, they are worse than a straight link, since the returned data will be non-updateable, which might have a bearing.

Yes, but the OP asked about a comparison between that and a yet-to-be-created "stored procedure". Which we have no way of analyzing.
I wanted to be sure OP understood that there was no way we could tell which would be more effective, selecting from the View or executing a stored procedure ending with a select statement which we hadn't yet seen and could introduce myriad other possibilities

 

[cheekybuddha]

Yes, but the OP asked about a comparison between that and a yet-to-be-created "stored procedure". Which we have no way of analyzing.

Ah OK!

I misunderstood what you meant - I think I was at a sub-optimal level of caffeination at that moment!

 

[Pat Hartman]

You should probably clean up the table so you can get rid of the Like's. Weightclass probably mushes multiple variables. Split them so you can use = "111". Same for Postal code and NAICS. Like for the city may also not make sense.

 

[ions]

You should probably clean up the table so you can get rid of the Like's. Weightclass probably mushes multiple variables. Split them so you can use = "111". Same for Postal code and NAICS. Like for the city may also not make sense.

Thanks for the suggestion Pat. Splitting the WasteClass into multiple components make sense.

 

[Pat Hartman]

I forgot to mention the reason for getting rid of the unnecessary LIKES - most of the time, they prevent the database engine from optimizing the query and forces it to do a full table scan of the table to process the LIKE. If you have a thousand records, it doesn't much matter but if you have 500,000, you are wasting a lot of processing power and will notice the sluggishness.

If you need LIKE, you need it and so are willing to pay the price. But don't just use it when = will work. Codes that include multiple pieces of data are candidates for splitting into their atomic values. Your sample seems to have three of them. City name can't be split but I'm not sure that using a wildcard with a city name makes sense. I use them when the customer isn't sure of a business or customer name. But with only the LIKE for city remaining, the database engine can use the other criteria to limit the selection and then use the LIKE on a much smaller set of data.

 

[cheekybuddha]

WHERE fld LIKE 'abc%'   -- will use index if available

WHERE fld LIKE '%abc%'  -- can not use index even if available