Help required to optimize the below sql query .

Mayank Tripathi
Mayank Tripathi used Ask the Experts™
on
SELECT  *
FROM
(
SELECT EntityId, [Key],Value FROM [dbo].[GA] with (nolock))t
PIVOT(MIN(Value)
      FOR [Key]
      IN (FirstName,LastName,Organization,StreetAddress,TrainingInstitution,EmploymentInstitution,SecondaryDegree,ZipPostalCode,CountryId,[city],StateProvinceId,[degree],LastVisitedPage,
UseRewardPointsDuringCheckout,SelectedShippingOption,SelectedPaymentMethod)
      )p
                where EntityId in (select id from dbo.cust with (nolock) where LastUpdated >=getdate()-5)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this and see it helps -

SELECT  *
FROM
(
     SELECT EntityId, [Key],Value FROM [dbo].GA g with (nolock)
     WHERE EXISTS ( select NULL from dbo.cust c with (nolock) where c.id = g.EntityId AND c.LastUpdated >= getdate()-5 )
     
)t
PIVOT
(
     MIN(Value) FOR [Key]
           IN (  FirstName,LastName,Organization,StreetAddress,TrainingInstitution,EmploymentInstitution,SecondaryDegree,ZipPostalCode,
                     CountryId,[city],StateProvinceId,[degree],LastVisitedPage,
                UseRewardPointsDuringCheckout,SelectedShippingOption,SelectedPaymentMethod
                )
)p         

Open in new window

Author

Commented:
Above solution is also taking too long time to execute.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
ohh.....
try adding temp table..

IF OBJECT_ID('tempdb..#YourTempTableName') IS NOT NULL
    DROP TABLE #YourTempTableName

SELECT EntityId, [Key],Value INTO [#YourTempTableName] FROM yourTablName [dbo].GA g with (nolock)
WHERE EXISTS ( select NULL from dbo.cust c with (nolock) where c.id = g.EntityId AND c.LastUpdated >= getdate()-5 )

SELECT  *
FROM #YourTempTableName t
PIVOT
(
     MIN(Value) FOR [Key]
	IN 
	(  
		FirstName,LastName,Organization,StreetAddress,TrainingInstitution,EmploymentInstitution,SecondaryDegree,ZipPostalCode,
		CountryId,[city],StateProvinceId,[degree],LastVisitedPage,
		UseRewardPointsDuringCheckout,SelectedShippingOption,SelectedPaymentMethod
	)
)p 

Open in new window

also can you share the execution plan.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial