ADO.NET Query optimisation

Question

I have a bit of a strange one happening. The first query I got from running a profiler on a C# ADO.NET application. What it is doing is not as interesting as the way the parameters are being passed - This query is taking 250+ seconds to complete, but when I modify it (Query 1) by changing the way the parameters are passed (see Query 2), then it takes <0.001 seconds to complete.

What is at play here? Is there anything we can do to force ADO.NET to generate a more optimal query?

-----------------------------------------------------------------------
-- Query 1
-----------------------------------------------------------------------
exec sp_executesql N'
SELECT 
    * 
FROM 
    BSM.Instruments 
WHERE 
    DataBlockId=@0 AND 
    InstrumentId IN (
     SELECT 
      DISTINCT InstrumentId 
     FROM 
      BSM.InstrumentPositions 
     WHERE 
      DataBlockId=@0 AND 
      PortfolioId IN (
       SELECT 
        DISTINCT PortfolioId 
       FROM 
        BSM.PortfolioLeaves(@1,@2)
      )
    )'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0='B1C69A98-50D3-11DE-A173-00155DE1F500'
,@1='ABADF5F7-50D3-11DE-A173-00155DE1F500'
,@2='ABADFF41-50D3-11DE-A173-00155DE1F500'

-----------------------------------------------------------------------
-- Query 2
-----------------------------------------------------------------------
declare @0 uniqueidentifier = 'B1C69A98-50D3-11DE-A173-00155DE1F500'
declare @1 uniqueidentifier = 'ABADF5F7-50D3-11DE-A173-00155DE1F500'
declare @2 uniqueidentifier = 'ABADFF41-50D3-11DE-A173-00155DE1F500'

exec sp_executesql N'
SELECT 
    * 
FROM 
    BSM.Instruments 
WHERE 
    DataBlockId=@0 AND 
    InstrumentId IN (
     SELECT 
      DISTINCT InstrumentId 
     FROM 
      BSM.InstrumentPositions 
     WHERE 
      DataBlockId=@0 AND 
      PortfolioId IN (
       SELECT 
        DISTINCT PortfolioId 
       FROM 
        BSM.PortfolioLeaves(@1,@2)
      )
    )
'
,N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier'
,@0
,@1
,@2

Answer 1

Hi,

The best way to guarantee optimal performance is to put your query into a stored procedure. Then use query analyzer to analyze the query plan and verify that the SQL Server optimizer is using meaningful indexes (as opposed to table scans) to retrieve the data.

Hope this helps,

Bill

Answer 2

You should also get rid of the SELECT * and replace it with SELECT and the column definitions. Also using a GUID will be slower than using an int when querying data.