Speed up execution of stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Speed up execution of stored procedure

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-03-18 : 10:22:52

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)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 11:33:01

Try running the sql directly with various variables set to NULL and have a look at the execution plans -- they will be different and you should then see which steps are taking up the most time.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ats
Starting Member

32 Posts

Posted - 2009-03-25 : 10:41:40

I have been working on this query for weeks now, I have got it to do what I wanted but I had to change a couple things to get it to work. mainly the 2 queries at the end named ad and p (highlighted in red). I had to add the company_id and group by company_id (highlighted in blue)which has caused the query to take over an hour to execute when it usually took 10 seconds. without those changes the query resulted in over a million lines when it previously was 500. I think the group by function is making it produce multiple rows of the same data as i get 1 line of data repeated 100 times. I need to have these compliance_id group by other wise it sums the whole database and I dont get the individual figure for each company. This is driving me crazy so any help would be appreciated.

alter PROCEDURE [dbo].[Searchccm]
(
@city nvarchar(255)= null,
@country nvarchar (255) = null,
@postcode nvarchar (255) = null,
@fuel int = null,
@distfrom int = null,
@distto int = null,
@p1 int = null,
@p2 int = null,
@d1 float = null,
@d2 float = null,
@name nvarchar (255) = null
)

as

SELECT m.*
FROM
(SELECT distinct company.company_name, company.company_id, person.city, installation.fuel_id, person.countrycode, person.zipcode, person.name
FROM installation
JOIN person ON installation.company_id=person.company_id
JOIN Company ON installation.company_id=company.company_id)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.commitmentperiod =2
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, compliance.company_id
FROM compliance
WHERE compliance.commitmentperiod = 1
group by compliance.company_id)ad
CROSS JOIN
(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS position, compliance.company_id
FROM compliance
WHERE compliance.commitmentperiod = 1
group by compliance.company_id)p
WHERE (@city is null or m.city = @city) and (@name is null or m.name like '%' + @name + '%') and (@country is null or m.countrycode = @country) and (@postcode is null or m.zipcode like @postcode + '%')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)and (@d1 is null or @d2 is null or t.myDivision between @d1 AND @d2)

souLTower
Starting Member

39 Posts

Posted - 2009-03-25 : 12:38:29

The best way to narrow these things down is to look at each part. How fast are the derived queries? As you mentioned the high number of rows I would think that the sum queries in the end probably take a while. What is this data reporting? I don't understand all of the cross joins.

God Bless

Ats
Starting Member

32 Posts

Posted - 2009-03-25 : 13:05:27

Hey Soul Tower I think the cross joins were the problem I have changed them to inner joins that has cut down the number of rows returned.

souLTower
Starting Member

39 Posts

Posted - 2009-03-25 : 13:25:04

That's great

God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources