Sql-server – Slow Performance of Parametrized Query in Excel due to Parameter Datatype

excelsql server

I'm pulling Data from a SQL Server 2008 into Excel using a Parametrized ODBC Query created by the Excel Wizard. (Data–>getExternalData/FromOtherSources–>FromDataConnectionWizard–>ODBC DSN)

My query faces a huge Performance Problem as soon as I'm using a parameter in it. Assuming that the Parameter1 points at a Cell which hold E20140718 the following Query times out:

SELECT Top 20 * 
FROM Viewname
Where RunId=?
Order by RelativeRWAVariation desc

However the following query has a runtime of only 2 seconds:

SELECT Top 20 * 
FROM Viewname
Where RunId='E20140718'
Order by RelativeRWAVariation desc

The Parametrization in the first query should work correct as this style of queries works very good for selecting from other, a bit less complicated Views.

I'm pretty sure, that the problem I'm facing is described in this thread. This means that if i could somehow tell the SQL Server that the Parameter he's getting is a varchar(9) the parametrized query would probably only take 2 seconds aswell. Is there any way in Excel to give the Parameter a datatype or another solution to the problem?

Convert(Varchar(9),?) doesn't work, since the Parameter can't be used as part of a forumla according to Excel 🙁

Edit:
I just tried the same thing in SQL Server Management System and surprisingly (to me) faced the same Problem

The following statement takes ages in SSMS:

Declare @Run varchar(9);
Set @Run='E20140718';

Select Top 20 *
From Viewname
Where RunId= @Run
Order by RelativeRWAVariation Desc

While the second Query from above performs very good.

Best Answer

The issue appears to be parameter sniffing as the link you provided indicates. I'm not sure how to fix the issue in Excel, but if you can figure out how to specify the datatype when it's creating the parameterized statement, that would fix it.

You have a few options that can help with the issue on the SQL Side. I would only choose one of them and each of them have their price. I think the best solution would be to use a stored procedure to mask it, but you will know your environment the best.

If you can modify the source table:

  • add a computed column of the NVARCHAR data type, equal to the RunID column, persisted. Increased storage and a minor hit when RunID is inserted or updated but cleanest solution overall. Modify your query from excel to use the computed column.

  • change the data type of the column to NVARCHAR. This would probably impact everything in your environment, but would be the easiest to implement.

  • Create a stored procedure that accepts an NVARCHAR as an argument, then cast that to a different variable but with the correct data type, then return the desired results. Clean solution, but requires a stored procedure be created and maintained.

To answer a question below (can't add a comment to my own answer, apparently).

You would create a stored procedure that accepts the NVARCHAR data type as the argument, then explicitly cast that value to a varchar type and use that in the query. It saves the execution plan from doing a conversion on every row. So something like this 

CREATE PROCEDURE sptest (@inputparam NVARCHAR(50)) 
AS 
DECLARE @queryparam VARCHAR(50) = CAST(@inputparam AS VARCHAR(50)) 
SELECT * FROM tabletest WHERE columnvalue = @queryparam
Related Question