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.