April 15, 2013 at 6:07 am
Hi all,
I have a question regarding the best way to approach a situation where an application user can query a set of tables in two different ways. Say there is a "View Mode" that indicates whether the user wants to search by Location or by Property ID range. The underlying Select statements would differ only in the WHERE clause, the table joins would be the same. If two PROCS (B and C) are used they would have different parameter lists and WHERE clauses.) It seems there are these choices:
1. PROC A would include View Mode and some optional parameters and would say: If ViewMode = 1 EXEC Proc B Else EXEC Proc C;
2: PROC A would include View Mode and some optional parameters and then branch to one of two different SELECT statements within the PROC. It would not use Procs B and C at all.
Am I correct in thinking that the execution plan for choice 2 would be inefficient at least part of the time? Would choice 1 be any better? We'll probably end up just letting the app code decide which proc (B or C) to call but I'm still curious about the general scenario.
Thanks
April 15, 2013 at 1:25 pm
Hi
It depends on other factors. For example it depends what settings are ON and OFF when your connection (ODBC,OLE DB) passes the queries to SQL Server and what the setting are there (some of the ANSI settings and other like CONCAT_NULL_YEALD_NULL and others) on the server.
Not knowing this, I think option 1 is better. With it you can even set the settings same as on the server and not touching anything else.
If the settings are same on the connection and on the server then it should not be any difference between option 1 and 2, considering that the joins do not change.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
April 15, 2013 at 1:33 pm
The difference could come down to indexes available. I'd probable go with option 1 rather than optiion 2.
April 15, 2013 at 1:36 pm
You would be correct, option 2 is prone to bad plans
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2013 at 2:08 pm
Hi
So, what is the difference between a query with missing index on a column in its WHERE clause executed as exec sp_prepexec @sql_stmt or exec sp_executesql @sql_stmt, and the same query executed via a SP? under same settings-conditions for execution?
As the connection drivers wrap the in-code queries as prepared for exec of sp_prepexec, I think there is no difference here.
In any case option 1 is more flexible than option 2.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
April 15, 2013 at 2:20 pm
IgorMi (4/15/2013)
So, what is the difference between a query with missing index on a column in its WHERE clause executed as exec sp_prepexec @sql_stmt or exec sp_executesql @sql_stmt, and the same query executed via a SP? under same settings-conditions for execution?
Don't understand what you're asking. Question has little to do with missing indexes and nothing to do with sp_prepexec or sp_executesql.
As the connection drivers wrap the in-code queries as prepared for exec of sp_prepexec, I think there is no difference here.
The connection drivers are irrelevant, since this is a stored procedure.
Edit: last comment removed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply