asked
SP Very Long Time to Execute
Hi I have SP executing very long time. So it leads to Deadlock How to optimise it? How to reduce sp execution time? Any alternative for this code? It returns 2 Lakhs Records in 40 Mints. CREATE procedure [dbo].[GetUserActivationDetailsAllRec] @ActivationStartDate datetime, @ActivationEndDate datetime as select rm.lotSerialnumber, rm.LicenseEndDate, (Select distinct CompanyName From dbo.CompanyMaster Where CompanyID=rm.companyid) Institution , rm.Release as version, IT.itemName,rm.createddate as ActivationDate, (Select Count(distinct NodeLock) From dbo.RegistrationTrans Where LotSerialNumber= rm.lotserialnumber) as Installations, rm.Activationlimit, (CASE WHEN EXISTS (SELECT serialnumber FROM dbo.sitelicenseuser WHERE serialnumber = rm.lotserialnumber) THEN 'Yes' ELSE 'No' END) IsSitelicense , (Select firstname From dbo.Users Where UserID = (Select top 1 UserID From dbo.Email Where EmailAddress=EmailAddress1)) Firstname, (Select LastName From dbo.Users Where UserID = (Select top 1 UserID From dbo.Email Where EmailAddress=EmailAddress1)) LastName, (Select Username From dbo.Users Where UserID = (Select top 1 UserID From dbo.Email Where EmailAddress=EmailAddress1)) EmailAddress, (Select top 1 City from dbo.Addresses Where UserID = (Select top 1 UserID From dbo.Email Where EmailAddress=EmailAddress1)) city, (Select top 1 RegionID from dbo.Addresses Where UserID = (Select top 1 UserID From dbo.Email Where EmailAddress=EmailAddress1)) Region, (Select distinct Value from products.dbo.VIEW_ItemAttributeValue where Itemnumber =RM.InventoryID and attributename = 'Platform') as ProductPlatform, rm.Registrationmethod, CASE WHEN (Select Value from products.dbo.VIEW_ItemAttributeValue Where AttributeName = 'Media' ANd Value = 'Site License' AND ItemNumber = RM.InventoryID) = 'Site License' THEN 'Site License' WHEN ItemName NOT LIKE '%Trial%' THEN 'Paid' ELSE 'Trial' END as 'SiteLicense' From dbo.RegistrationMaster RM inner Join dbo.ContactMaster CTM on CTM.ContactID=RM.ContactID and CTM.CompanyID = RM.CompanyID inner Join dbo.CompanyMaster CM on CM.CompanyID=RM.CompanyID inner Join products.dbo.item IT on IT.ITEMNUMBER=RM.InventoryID inner join products.dbo.itemactivationattributes IAA on IAA.itemid =IT.itemid where rm.createddate between @ActivationStartDate and @ActivationEndDate Find the attachment for Execution Plan I have attached execution plan for your ref: [Query Text][1] [link text][2] I cant attach actual execution plan due to file too large size (Filesize: 0.726 MB) But i give u uploaded link for Actual execution plan your ref:
http://s000.tinyupload.com/download.php?file_id=56623562022647459311&t=5662356202264745931145398 [1]: /storage/temp/2509-sp-code.txt [2]: /storage/temp/2513-sp-executionplan.sqlplan
That looks like an estimated plan. Can you save the actual plan instead, please? There will be much more useful information in there.
There is a lot of bad code smell in here. You have subqueries nested inside subqueries. And many of those are the same subquery over and over. You are using top with no order by. A number of your subqueries would be better suited as joins. I count 15 occurrences of the word SELECT in a single query. This is a sign that it needs some serious help.
Hi Daviwimbush I have attached as you requested Actual Execution plan Kindly see and suggest me I cant attach actual execution plan due to file too large size (Filesize: 0.726 MB) so i give u uploaded link for your ref:
http://s000.tinyupload.com/download.php?file_id=56623562022647459311&t=5662356202264745931145398
We don't have a lot of details to work with here but I took a shot in the dark at removing most of the subqueries and removed all of the redunancy. No clue if this will work because I have nothing to test with. declare @ActivationStartDate datetime, @ActivationEndDate datetime select rm.lotSerialnumber, rm.LicenseEndDate, CM.CompanyName as Institution, rm.Release as version, IT.itemName,rm.createddate as ActivationDate, NodeLock.Installations, rm.Activationlimit, case when rt.SerialNumber is null then 'No' else 'Yes' end as IsSiteLicense, u.FirstName, u.LastName, u.Username, u.City, u.RegionID, ProductPlatform.Value as ProductPlatform, rm.Registrationmethod, case when SiteLicense.Value = 'Site License' then SiteLicense.Value WHEN SiteLicense.ItemName NOT LIKE '%Trial%' THEN 'Paid' ELSE 'Trial' END as 'SiteLicense' From dbo.RegistrationMaster RM inner Join dbo.ContactMaster CTM on CTM.ContactID = RM.ContactID and CTM.CompanyID = RM.CompanyID inner Join dbo.CompanyMaster CM on CM.CompanyID = RM.CompanyID inner Join products.dbo.item IT on IT.ITEMNUMBER = RM.InventoryID inner join products.dbo.itemactivationattributes IAA on IAA.itemid = IT.itemid left join dbo.RegistrationTrans rt on rt.LotSerialNumber = rm.LotSerialNumber left join products.dbo.VIEW_ItemAttributeValue SiteLicense on SiteLicense.ItemNumber = RM.InventoryID and SiteLicense.AttributeName = 'Media' and SiteLicense.Value = 'Site License' join Email e on e.EmailAddress = CM.EmailAddress1 join Users u on u.UserID = e.UserID left join products.dbo.VIEW_ItemAttributeValue ProductPlatform on ProductPlatform.ItemNumber = RM.InventoryID and AttributeName = 'Platform' join ( select COUNT(distinct NodeLock) as Installations, LotSerialNumber from dbo.RegistrationTrans rt group by LotSerialNumber ) NodeLocks on NodeLocks.LotSerialNumber = rm.LotSerialNumber where rm.createddate between @ActivationStartDate and @ActivationEndDate
Hi Seanlange Thanks for ur quick suggestion But When I checked code as u provided using joins it gives records quickly but cant achieve same records as previous one uses subquery. But still results are not same as per previous one So What to do? prevoius code(using suquery) gives 471 records in 1 Mint altered code(using joins) gives 486 records in 8 sec
Here is the code I am removed subquery and converted to joins prevoius code(using suquery) gives 471 records in 1 Mint altered code(using joins) gives 486 records in 8 sec Any suggestion??????? See below select rm.lotSerialnumber, rm.LicenseEndDate, CM.CompanyName as Institution, rm.Release as version, IT.itemName,rm.createddate as ActivationDate, N.Installations, rm.Activationlimit, case when sl.serialnumber is null then 'No' else 'Yes' end as IsSiteLicense, U.FirstName, U.LastName, U.Username, A.City, A.RegionID, csview.Value as ProductPlatform, rm.Registrationmethod, CASE WHEN (Select Value from products.dbo.cs_vu_ItemAttributeValue C Where C.AttributeName = 'Media' ANd C.Value = 'Site License' AND C.ItemNumber = RM.InventoryID) = 'Site License' THEN 'Site License' WHEN csview.ItemName NOT LIKE '%Trial%' THEN 'Paid' ELSE 'Trial' END as 'SiteLicense' From dbo.RegistrationMaster RM inner Join dbo.ContactMaster CTM on CTM.ContactID = RM.ContactID and CTM.CompanyID = RM.CompanyID inner Join dbo.CompanyMaster CM on CM.CompanyID = RM.CompanyID inner Join products.dbo.item IT on IT.ITEMNUMBER = RM.InventoryID inner join products.dbo.itemactivationattributes IAA on IAA.itemid = IT.itemid LEFT join chemstore.dbo.sitelicenseuser sl on sl.serialnumber = rm.LotSerialNumber join products.dbo.cs_vu_ItemAttributeValue csview on csview.ItemNumber = RM.InventoryID and attributename = 'Platform' left join CSWebUsers.dbo.Email E ON E.EmailAddress=ctm.EmailAddress1 join CSWebUsers.dbo.users U on U.UserID=E.UserID join cswebusers.dbo.Addresses A on A.UserID=E.UserID and A.UserID=U.UserID join ( select COUNT(distinct RT.NodeLock) as Installations, RT.LotSerialNumber from dbo.RegistrationTrans RT join RegistrationMaster R on R.LotSerialNumber = RT.lotserialnumber group by RT.LotSerialNumber ) AS N on N.LotSerialNumber = RM.lotserialnumber where convert(varchar(10),rm.createddate,121) between '2009-04-24' and '2009-04-26'
If you really want some help you should take a look at this article.
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
18 People are following this question.
