Giant SELECT used a store proc parameter. Is there a better way?

Transact-SQL
1

Hi,

Troubleshooting an inefficient proc which includes several parameters including the final parameter being an entire large SELECT statement string with numerous joins. Takes 30 minutes.

Is there a better method to obtain the values retrieved in the SELECT and the pass as parameter. Could consider VIEW or FUNCTION however I'm told the SELECT string is subject to change, perhaps every time it's issue from CRM (Dynamics)

Example

Declare @ResultCode int
Declare @Results varchar(max)
exec @ResultCode = dbo.someproc 2, null, null, null, N'SELECT <massive 2000 row select statement''>

Thanks in advance for any advice

2

Pretty hard to help without knowing a lot more. For one thing, 30 minutes may be reasonable -- even good! Depends on the size of the data and resulting row set.

One thing you can try. break the giant SELECT into sub-selects, writing the results to temp tables, then combine the temp tables at the end with a JOIN. Sometimes the query optimizer stumbles when there are more than 6 or 7 tables being joined. If you have views that are being joined, the problem can quickly get worse.

3

Agreed. Execution time is relative and SELECT is reasonably optimal but as its for a DWH use case, lots of heavy lifting.

More looking for guidance whether passing in a parameter via a huge SELECT statement is industry standard. The proc would have to wait for the outer query to produce results before passed into the parameter which feels extremely inefficient.

If the SELECT was converted to a VIEW, clustered index applied ,down side does not except parameters. Maybe FUNCTION would work as parameters can be applied then apply an index.

4

I see. Not sure there is an industry standard covering your situation. You;re right about the view -- no parameters. However, you can't index a function! so, you may be stuck unless you can optimize the long-running query somehow.

5

You can use table functions to effectively get a "view with params". And an in-line tvf has the huge advantage of being "compiled" directly into the code, so they perform extremely well.

6

Thanks both.

Was considering to experiment with converting SELECT into a proc then parameterizing to mitigate overheads of issuing TSQL over the network.

declare @var1 nvarchar(4000)
declare @var2 int
set @sql = N'
select ...

exec sp_executesql @sql, N'@Var1 int, @var2 decimal'

Will also try the table function and in-line tvf method and update this post.

Thank you