Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-24 : 21:43:15
|
| cDc writes "my view joins two tables of approx 350,000 rows each and I have created a unique clustered index on the view plus a second index on the field I wish to query. Why is is that when I run a query on the view from query analyzer i get the expected execution plan and hence fast performance for my query - however if I run the same query from either a stored procedure or an ASP script it does not use the index, the execution plan is different and my query takes much longer. is there a way to force my query to use the index on the view?Cheers in advance!Chris." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-24 : 21:56:18
|
| You can indicate index hints in your SELECT statement, either in query analyzer or your stored procedure. Books Online describes these under the "SELECT" statement, in the "FROM" clause (you have to drill down 1-2 links to find the Table Hints section).Have you tried executing your stored procedure WITH RECOMPILE:EXEC myStoredProcedure WITH RECOMPILEThis will recompile the SP's plan and could very well choose the same query plan you get with query analyzer. There are some circumstances where this will not work either, but you can also drop the procedure and re-create it, this will almost certainly work. |
 |
|
|
cDc
Starting Member
30 Posts |
Posted - 2002-02-25 : 04:40:54
|
| No luck with the recompile - so I started investigating the SET options as It seemed like the stored procedure was ignoring any SET options i tried to run. But it is no big deal as my ASP script is where it is needed - executing "SET ARITHABORT ON" in my ASP script and adding with(noexpand) to my query and the problem is solved (but still dunno why the option will not set from inside the stored procedure - any hints on that one?) I assume that the stored procedure runs with the option that are set at time of creation?tachris. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-25 : 08:21:46
|
| Yes, that's correct. Changing settings within the procedure will not affect it's execution, you will need to set them before you create the procedure. |
|
|
|
|
|
|
Indexed View Performance