4

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.

  • I assume you cannot run this query server side? – ron tornambe Aug 4 '14 at 16:55
  • 1
    Are your index statistics up-to-date on all indices for this table? It sounds like the parameter query is doing a table scan when it shouldn't. Have you inspected the execution plans for both queries in SSMS? What indices exist on the table? – Pieter Geerkens Aug 4 '14 at 17:14
  • Added a few Indices on the Source Tables of the View and now the parametrized query is almost as fast as the non-parametrized. I'm still not sure why the difference between the parametrized and non-parametrized query was so large but it works now. Thanks! – Hellvetia Aug 4 '14 at 17:34
  • 2
    Query hint OPTION (RECOMPILE) should help you. – Kin Shah Aug 4 '14 at 20:11
  • Why are you using varchar's to store coded data information in your tables? – Pieter Geerkens Oct 5 '14 at 13:47
1

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
  • Just how exactly would creating a stored procedure mask the parameter sniffing? Parameter sniffing is the bane of stored procedures and occasionally the reason one must resort to dynamic SQL. Besides, the use of a parameterized query creates a temporary stored procedure on the server side anyways. – Pieter Geerkens Oct 5 '14 at 13:44
  • Well this would help with parameter sniffing... if the proc also contained WITH RECOMPILE – Nick.McDermaid May 26 '17 at 9:57
0

I faced the same issue. Excel does support entering the following for SQL query. The ? below points to the Excel Parameter1 variable. I had to also add SET NOCOUNT ON for this to work as expected.

SET NOCOUNT ON;

Declare @Run varchar(9) = ?;

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

For reference to others, the following link has a comprehensive explanation of parameter sniffing SQL Server problem http://www.sommarskog.se/query-plan-mysteries.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy