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.
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)asSELECT 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_idFROM installation, person, Company)mINNER JOIN(SELECT a.company_id, b.SurrenderedCers/nullif(a.cer,0) AS MyDivisionFROM (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifierWHERE compliance.year1 between 2008 and 2012GROUP BY compliance.Company_ID, compliance.year1) AS aJOIN (SELECT compliance.company_id, Sum(compliance.surrenderedCERs) SurrenderedCERsFROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifierGROUP BY compliance.Company_ID) AS b ON a.company_id = b.company_id)tON t.company_id = m.company_idCROSS JOIN(SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributedFROM complianceWHERE year1 between 2008 AND 2012)adCROSS JOIN(SELECT SUM(compliance.allowanceDistributed) - SUM(compliance.verifiedEmissions) AS positionFROM complianceWHERE compliance.year1 between 2008 AND 2012)pWHERE (@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 1736The 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)asSELECT 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_idJOIN 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 greatGod Bless |
|
|
|
|
|
|
|