• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 39
  • Last Modified:

Help required to optimize the below sql query .

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

0
Mayank Tripathi
Asked:
Mayank Tripathi
  • 2
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Mayank TripathiAuthor Commented:
Above solution is also taking too long time to execute.
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
Mayank TripathiAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now