Performance issue with this query

Question

Performance issue with this query

Gangadhara MS

Hall of Fame

Points: 3377
More actions
June 11, 2015 at 3:07 pm

#299045

Below are my Table schema.
CREATE TABLE [DML].[ExtCPCosmosCommerceAccount]
(
[AccountID] [bigint] NULL,
[CAId] [nvarchar](1000) NULL,
[OrganizationName] [nvarchar](1000) NULL,
[CAKey] [bigint] NULL,
[CountryCode] [nvarchar](1000) NULL,
[IsMigratedToMINT] [nvarchar](5) NULL,
[ActiveBillingSystemName] [nvarchar](1024) NULL,
[EDWExtractGrouping] [int] NULL,
[EDWIsDeletedFlag] [bit] NULL,
[BISIsDeleted] [int] NULL,
[BISIsTestData] [int] NULL
)
CREATE TABLE [DML].[StageMintTenantMigration](
[CountryCode] NCHAR (20) NULL,
[OrganizationName] NVARCHAR(256) NULL,
[EDWExtractGrouping] INT NULL,
[EDWIsDeletedFlag] BIT NULL,
[IsMigratedToMINT] BIT NULL,
[MINTAccountIdentifier] BIGINT NULL,
[OMSTenantIdentifier] UNIQUEIDENTIfier NULL,
[OriginalTenantKey] BIGINT NULL,
[TenantKey] BIGINT NULL,
[CurrentBillingSystemName] VARCHAR(100) NULL,
[MTAccountIdentifier] BIGINT NULL,
[MINTAccountCreatedDate] DATETIME NULL,
[SubscriptionID] UNIQUEIDENTIfier NULL,
[BisIsDeleted] BIT NULL,
[SubscriptionKey] BIGINT NULL,
[BisIsTestData] BIT NULL,
[SubscriptionEventFactKey] BIGINT NULL,
[SubscriptionEventDate] DATETIME NULL,
[TenantTypeName] NVARCHAR(70) NULL
);
------------------------ Below Query I am running -------------------
SELECT DISTINCT OMSTenantIdentifier,MTAccountIdentifier,MINTAccountCreatedDate
INTO #StageTenant
FROM DML.StageMintTenantMigration
CREATE NONCLUSTERED INDEX [IX_OMSAccount]
ON [dbo].[#StageTenant] ([OMSTenantIdentifier])
INCLUDE ([MTAccountIdentifier],[MINTAccountCreatedDate])
GO
-----------------------------------------------------------------------------
SELECT
ca.CAID AS OMSTenantIdentifier
, ca.CAKey AS OriginalTenantKey
, ca.CAKey AS TenantKey
, ca.CountryCode AS CountryCode
, ca.OrganizationName AS OrganizationName
, CASE WHEN ca.IsMigratedToMINT = 'Y' THEN 1 ELSE 0 END AS IsMigratedToMINT
, ca.AccountID AS MINTAccountIdentifier
, ca.ActiveBillingSystemName AS CurrentBillingSystemName
, ca.EDWExtractGrouping AS EDWExtractGrouping
, ca.EDWIsDeletedFlag AS EDWIsDeletedFlag
, ca.BisIsDeleted AS BisIsDeleted
, ca.BisIsTestData AS BisIsTestData
, st.MTAccountIdentifier
, st.MINTAccountCreatedDate
FROM DML.ExtCPCosmosCommerceAccount ca
LEFT JOIN #StageTenant st on ca.CAID = cast(st.OMSTenantIdentifier as nvarchar(100))
WHERE LEN(CAID) =36
----- Few Data statistics
--#StageTenant row count - 7,98,094
--DML.ExtCPCosmosCommerceAccount row count - 3,52,461
My Query is taking lot of time complete> 7 hours
This is my Estimated Execution plan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 1

I am getting this warning message as well in select.

Type conversion in expression (CONVERT_IMPLICIT(uniqueidentifier,[ca].[CAId],0)) may affect "CardinalityEstimate" in query plan choice.

Please help me where i need to create data conversion on which column.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

We could if you provided the DDL for the table(s) involved, sample data for the tables, and expected results based on the sample data.

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 3

please look for my first post,i have included the schema as well as execution plan by editing it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Gangadhara MS (6/11/2015)
please look for my first post,i have included the schema as well as execution plan by editing it.

Please post the execution plan as a .sqlplan attachment, not a picture of the plan as it is useless.

Best guess? Your query is blocked by another process, probably a hanging transaction if it's been waiting for this long. Your DBA should resolve this issue for you. Using a mockup of your tables, the query takes 5 seconds in my environment when datataypes of the join columns are matched, and about 15 seconds when they are not.

-----------------------------------------------------------------------------------------

-- Make up some sample data

-----------------------------------------------------------------------------------------

IF OBJECT_ID('Tempdb..#StageMintTenantMigration') IS NOT NULL DROP TABLE #StageMintTenantMigration;

CREATE TABLE #StageMintTenantMigration (OMSTenantIdentifier Uniqueidentifier, MTAccountIdentifier BIGINT NULL, MINTAccountCreatedDate DATE NULL)

INSERT INTO #StageMintTenantMigration (OMSTenantIdentifier, MTAccountIdentifier, MINTAccountCreatedDate)

SELECT NewPK, ABS(CHECKSUM(NewPK)), DATEADD(MINUTE,rn,GETDATE())

FROM (

SELECT NewPK = NEWID(), rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM

(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n),

(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) e (n),

(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) f (n),

(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) g (n),

(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) h (n),

(VALUES (1),(1),(1),(1),(1)) i (n)

) dd

IF OBJECT_ID('Tempdb..#ExtCPCosmosCommerceAccount') IS NOT NULL DROP TABLE #ExtCPCosmosCommerceAccount;

CREATE TABLE #ExtCPCosmosCommerceAccount

(

[AccountID] [bigint] IDENTITY(1,1) ,

[CAId] [nvarchar](1000) NULL,

[OrganizationName] [nvarchar](1000) NULL,

[CAKey] [bigint] NULL,

[CountryCode] [nvarchar](1000) NULL,

[IsMigratedToMINT] [nvarchar](5) NULL,

[ActiveBillingSystemName] [nvarchar](1024) NULL,

[EDWExtractGrouping] [int] NULL,

[EDWIsDeletedFlag] [bit] NULL,

[BISIsDeleted] [int] NULL,

[BISIsTestData] [int] NULL

)

INSERT INTO #ExtCPCosmosCommerceAccount (CAID, OrganizationName, CAKey, CountryCode, IsMigratedToMINT, ActiveBillingSystemName, EDWExtractGrouping, EDWIsDeletedFlag, BISIsDeleted, BISIsTestData)

SELECT

CAID = CAST(OMSTenantIdentifier AS NVARCHAR(1000)),

OrganizationName = CAST(NEWID() AS NVARCHAR(1000)),

CAKey = ABS(CHECKSUM(NEWID())),

CountryCode = CAST(NEWID() AS NVARCHAR(1000)),

IsMigratedToMINT = LEFT(CAST(OMSTenantIdentifier AS NVARCHAR(1000)),1),

ActiveBillingSystemName = CAST(NEWID() AS NVARCHAR(1000)),

EDWExtractGrouping = ABS(CHECKSUM(NEWID())),

EDWIsDeletedFlag = ABS(CHECKSUM(NEWID()))%2,

BISIsDeleted = ABS(CHECKSUM(NEWID())),

BISIsTestData = ABS(CHECKSUM(NEWID()))

FROM #StageMintTenantMigration

-----------------------------------------------------------------------------------------

-- Check out different permutations of the query

-----------------------------------------------------------------------------

-- Check if the WHERE clause is doing any useful work:

SELECT COUNT(*) FROM #ExtCPCosmosCommerceAccount

SELECT COUNT(*) FROM #ExtCPCosmosCommerceAccount WHERE LEN(CAID) = 36

-- If the counts are the same, then remove the WHERE clause from the query

-- Cast OMSTenantIdentifier to match CAID at this stage to avoid the implicit conversion in the query

IF OBJECT_ID('Tempdb..#StageTenant') IS NOT NULL DROP TABLE #StageTenant;

SELECT

OMSTenantIdentifier = CAST(OMSTenantIdentifier AS NVARCHAR(36)),

--OMSTenantIdentifier,

MTAccountIdentifier,

MINTAccountCreatedDate

INTO #StageTenant

FROM #StageMintTenantMigration

GROUP BY OMSTenantIdentifier, MTAccountIdentifier, MINTAccountCreatedDate

CREATE UNIQUE CLUSTERED INDEX [ucx_StageTenant] ON [dbo].[#StageTenant]

([OMSTenantIdentifier], [MTAccountIdentifier], [MINTAccountCreatedDate])

GO

-- Run the query

IF OBJECT_ID('Tempdb..#Results') IS NOT NULL DROP TABLE #Results;

SELECT

ca.CAID AS OMSTenantIdentifier

, ca.CAKey AS OriginalTenantKey

, ca.CAKey AS TenantKey

, ca.CountryCode AS CountryCode

, ca.OrganizationName AS OrganizationName

, CASE WHEN ca.IsMigratedToMINT = 'Y' THEN 1 ELSE 0 END AS IsMigratedToMINT

, ca.AccountID AS MINTAccountIdentifier

, ca.ActiveBillingSystemName AS CurrentBillingSystemName

, ca.EDWExtractGrouping AS EDWExtractGrouping

, ca.EDWIsDeletedFlag AS EDWIsDeletedFlag

, ca.BisIsDeleted AS BisIsDeleted

, ca.BisIsTestData AS BisIsTestData

, st.MTAccountIdentifier

, st.MINTAccountCreatedDate

INTO #Results

FROM #ExtCPCosmosCommerceAccount ca

LEFT JOIN #StageTenant st

ON ca.CAID = st.OMSTenantIdentifier

--ON ca.CAID = CAST(st.OMSTenantIdentifier AS NVARCHAR(1000))

WHERE LEN(ca.CAID) = 36

-- (500000 row(s) affected) / 00:00:05

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mayank_sanghvi SSC Enthusiast Points: 190 More actions · Answer 5

I agree first you need to check the blockage if any. If not i will try below things

Always check the query time with first run (or fresh by clearing the cache).

I hope Index is available on ca.CAID column of DML.ExtCPCosmosCommerceAccount. If not create it(Assuming no impact by creating a new index).

SELECT DISTINCT cast(OMSTenantIdentifier as nvarchar(100)) OMSTenantIdentifier,MTAccountIdentifier,MINTAccountCreatedDate

INTO #StageTenant

FROM DML.StageMintTenantMigration

CREATE NONCLUSTERED INDEX [IX_OMSAccount]

ON [dbo].[#StageTenant] ([OMSTenantIdentifier])

INCLUDE ([MTAccountIdentifier],[MINTAccountCreatedDate])

GO

-----------------------------------------------------------------------------

SELECT

ca.CAID AS OMSTenantIdentifier

, ca.CAKey AS OriginalTenantKey

, ca.CAKey AS TenantKey

, ca.CountryCode AS CountryCode

, ca.OrganizationName AS OrganizationName

, CASE WHEN ca.IsMigratedToMINT = 'Y' THEN 1 ELSE 0 END AS IsMigratedToMINT

, ca.AccountID AS MINTAccountIdentifier

, ca.ActiveBillingSystemName AS CurrentBillingSystemName

, ca.EDWExtractGrouping AS EDWExtractGrouping

, ca.EDWIsDeletedFlag AS EDWIsDeletedFlag

, ca.BisIsDeleted AS BisIsDeleted

INTO #TempTable

FROM DML.ExtCPCosmosCommerceAccount

WHERE LEN(CAID) =36

-----------------------------------------------------------------------------

SELECT

ca.CAID AS OMSTenantIdentifier

, ca.CAKey AS OriginalTenantKey

, ca.CAKey AS TenantKey

, ca.CountryCode AS CountryCode

, ca.OrganizationName AS OrganizationName

, CASE WHEN ca.IsMigratedToMINT = 'Y' THEN 1 ELSE 0 END AS IsMigratedToMINT

, ca.AccountID AS MINTAccountIdentifier

, ca.ActiveBillingSystemName AS CurrentBillingSystemName

, ca.EDWExtractGrouping AS EDWExtractGrouping

, ca.EDWIsDeletedFlag AS EDWIsDeletedFlag

, ca.BisIsDeleted AS BisIsDeleted

, ca.BisIsTestData AS BisIsTestData

, st.MTAccountIdentifier

, st.MINTAccountCreatedDate

FROM #TempTable ca

LEFT JOIN #StageTenant st

on ca.CAID = st.OMSTenantIdentifier

--Finally Please share the final execution time.

Almighty SSCrazy Points: 2001 More actions · Answer 6

HI warning Is because u r using the expression in where clause .. avoid that use something else whose output will be same as ur expression or satsisfy u r where clause . u r index will be useless if u use expression in where clauses . dont ask SQL to Evaulate first and then compare .

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Gangadhara MS (6/11/2015)
please look for my first post,i have included the schema as well as execution plan by editing it.

Two things. One, a picture of the execution plan is as helpful as saying "it doesn't work" with no details. Two, we need the actual execution plan not the estimated execution plan, even if that means waiting 7 hours or more to get it. Once you have the execution plan, save it as a .sqlplan file and upload that and then we have something we can use to help you.