March 18, 2009 at 8:17 am
I have created a stored procedure which is quite complex, It seems to work effciently when all the criteria is filled in if 2 of the 5 criteria are left blank it seems to take an awfully long time to produce results . I will paste a copy of the query below any help with this would be gratefully appreciated.
CREATE PROCEDURE [dbo].[Searchccm]
(
@city nvarchar(255) = null,
@fuel int = null,
@distfrom int = null,
@distto int = null,
@p1 int = null,
@p2 int = null
)
as
SELECT m.*
FROM
(SELECT distinct installation.id,installation.name, installation.company_id, installation.installationidentifier, installation.permitidentifier, installation.mainactivitytypecode, installation.countrycode,person.city,person.zipcode, person.address1,installation.mainactivitytypecodelookup,installation.person,installation.account,installation.eperidentification,installation.latitude,installation.longitude,installation.archive,installation.fuel_id
FROM installation, person, Company)m
INNER JOIN
(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivision
FROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
WHERE compliance.year1 between 2008 and 2012
GROUP BY compliance.Company_ID, compliance.year1) AS a
JOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERs
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)t
ON t.company_id = m.company_id
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM compliance
WHERE year1 between 2008 AND 2012)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position
FROM compliance
WHERE compliance.year1 between 2008 AND 2012)p
WHERE (@city is null or m.city = @city) and (@fuel is null or m.Fuel_ID = @fuel) and (@distfrom is null or @distto is null or ad.allowanceDistributed between @distfrom AND @distto) and (@p1 is null or @p2 is null or p.position between @p1 AND @p2)
March 18, 2009 at 8:27 am
you are suffering from "parameter sniffing"; you can search here on SSC for the suggested solutions;
the issue is the default values for your parameters...
since they are assigned NULL, the compiler assumes they are the most common values to be used,
so it builds an execution plan based on those parameters...
so when other values are entered insteadl of NULL, the execution plan may or may not be optimized for those values, and a bad execution plan is used, so it's incredibly slow.
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply