April 17, 2015 at 6:02 pm
Hi All,
I have a query that has me completely baffled. This query is part of a third party application that runs on our SQL servers. Things have been working fine for years, but all of a sudden the query is running extremely slow (several minutes vs a second). I'll give as much detail as possible (table definitions, indexes, etc.) and what I did to try to optimize the query. I'm hoping the SSC brain trust can help out.
Background:
This is a retail transaction third party application that runs at a retail store using SQL server as the back end. The installation is SQL Server Express 2008 R2. I've checked the SQL server configuration, memory, CPU, etc. All hardware is ok and the machine hardware is quite capable of handling the given load. This particular client has the largest amount of customers of all our clients. However, the tables in question only have about 200,000 rows. So, we're not talking millions of rows here. I mention this because we have hundreds of installations of this application across all our client base and this particular client is the only one experiencing the problem. So, we're thinking that it's a table size issue, but again there are only ~200K rows of data.
Here is the troubling query:
SELECT TOP 1
C.[ID],
C.[LastUpdated],
C.[DBTimeStamp],
C.[AccountNumber],
CL.[RecordID],
CL.[LocalLastUpdatedTS],
C.[PrimaryShipToID]
FROM
dbo.Customer C WITH (NOLOCK)
LEFT MERGE JOIN dbo.MessageTypeTrack CL WITH (NOLOCK)
ON C.[ID] = CONVERT(NVARCHAR(20), CL.RecordID)
AND CL.[MessageType] = 'Customer'
AND CL.[MessageTypeVersion] = '1.0'
WHERE
CONVERT(VARBINARY(8), C.[DBTimeStamp]) > CONVERT(VARBINARY(8), CL.[LocalLastUpdatedTS])
OR CL.[RecordID] IS NULL
OR CL.[LocalLastUpdatedTS] IS NULL
ORDER BY C.[LastUpdated] ASC
I know that there are LOTS of things wrong with this query (SARGabilty issues, join hints, improper data conversions, etc.), but remember it's a third party application (FYI it's actually a Microsoft product).
The Customer table has ~210,000 rows, the MessageTypeTrack table has ~220,000 rows.
The table definitions are:
CREATE TABLE dbo.MessageTypeTrack
(
MessageType NVARCHAR(100) NOT NULL,
MessageTypeVersion NVARCHAR(6) NOT NULL,
RecordID NVARCHAR(50) NOT NULL,
RemoteLastUpdatedDT DATETIME NULL,
RemoteLastUpdatedTS VARBINARY(8) NULL,
LocalLastUpdatedDT DATETIME NULL,
LocalLastUpdatedTS VARBINARY(8) NULL,
CONSTRAINT PK_MessageTypeTrack PRIMARY KEY CLUSTERED
(
MessageType ASC,
MessageTypeVersion ASC,
RecordID ASC
)
CREATE TABLE dbo.Customer(
AccountNumber nvarchar(20) NOT NULL CONSTRAINT DF_Customer_AccountNumber DEFAULT (''),
AccountTypeID int NOT NULL CONSTRAINT DF_Customer_AccountTypeID DEFAULT ((1)),
Address2 nvarchar(50) NOT NULL CONSTRAINT DF_Customer_Address2 DEFAULT (''),
AssessFinanceCharges bit NOT NULL CONSTRAINT DF_Customer_AssessFinanceCharges DEFAULT ((0)),
Company nvarchar(50) NOT NULL CONSTRAINT DF_Customer_Company DEFAULT (''),
Country nvarchar(20) NOT NULL CONSTRAINT DF_Customer_Country DEFAULT (''),
CustomDate1 datetime NULL,
CustomDate2 datetime NULL,
CustomDate3 datetime NULL,
CustomDate4 datetime NULL,
CustomDate5 datetime NULL,
CustomNumber1 float NOT NULL CONSTRAINT DF_Customer_CustomNumber1 DEFAULT ((0)),
CustomNumber2 float NOT NULL CONSTRAINT DF_Customer_CustomNumber2 DEFAULT ((0)),
CustomNumber3 float NOT NULL CONSTRAINT DF_Customer_CustomNumber3 DEFAULT ((0)),
CustomNumber4 float NOT NULL CONSTRAINT DF_Customer_CustomNumber4 DEFAULT ((0)),
CustomNumber5 float NOT NULL CONSTRAINT DF_Customer_CustomNumber5 DEFAULT ((0)),
CustomText1 nvarchar(30) NOT NULL CONSTRAINT DF_Customer_CustomText1 DEFAULT (''),
CustomText2 nvarchar(30) NOT NULL CONSTRAINT DF_Customer_CustomText2 DEFAULT (''),
CustomText3 nvarchar(30) NOT NULL CONSTRAINT DF_Customer_CustomText3 DEFAULT (''),
CustomText4 nvarchar(30) NOT NULL CONSTRAINT DF_Customer_CustomText4 DEFAULT (''),
CustomText5 nvarchar(30) NOT NULL CONSTRAINT DF_Customer_CustomText5 DEFAULT (''),
GlobalCustomer bit NOT NULL CONSTRAINT DF_Customer_GlobalCustomer DEFAULT ((0)),
HQID int NOT NULL CONSTRAINT DF_Customer_HQID DEFAULT ((0)),
LastStartingDate datetime NULL,
LastClosingDate datetime NULL,
LastUpdated datetime NOT NULL CONSTRAINT DF_Customer_LastUpdated DEFAULT (getdate()),
LimitPurchase bit NOT NULL CONSTRAINT DF_Customer_LimitPurchase DEFAULT ((0)),
LastClosingBalance money NOT NULL CONSTRAINT DF_Customer_PreviousBalance DEFAULT ((0)),
PrimaryShipToID int NOT NULL CONSTRAINT DF_Customer_PrimaryShipToID DEFAULT ((0)),
[State] nvarchar(20) NOT NULL CONSTRAINT DF_Customer_State DEFAULT (''),
StoreID int NOT NULL CONSTRAINT DF_Customer_StoreID DEFAULT ((0)),
ID int IDENTITY(1,1) NOT NULL,
LayawayCustomer bit NOT NULL CONSTRAINT DF_Customer_LayawayCustomer DEFAULT ((0)),
Employee bit NOT NULL CONSTRAINT DF_Customer_Employee DEFAULT ((0)),
FirstName nvarchar(30) NOT NULL CONSTRAINT DF_Customer_FirstName DEFAULT (''),
LastName nvarchar(50) NOT NULL CONSTRAINT DF_Customer_LastName DEFAULT (''),
[Address] nvarchar(50) NOT NULL CONSTRAINT DF_Customer_Address DEFAULT (''),
City nvarchar(50) NOT NULL CONSTRAINT DF_Customer_City DEFAULT (''),
Zip nvarchar(15) NOT NULL CONSTRAINT DF_Customer_Zip DEFAULT (''),
AccountBalance money NOT NULL CONSTRAINT DF_Customer_Balance DEFAULT ((0)),
CreditLimit money NOT NULL CONSTRAINT DF_Customer_CreditLimit DEFAULT ((0)),
TotalSales money NOT NULL CONSTRAINT DF_Customer_TotalSales DEFAULT ((0)),
AccountOpened datetime NOT NULL CONSTRAINT DF_Customer_AccountOpened DEFAULT (getdate()),
LastVisit datetime NOT NULL CONSTRAINT DF_Customer_LastVisit DEFAULT (getdate()),
TotalVisits int NOT NULL CONSTRAINT DF_Customer_TotalVisits DEFAULT ((0)),
TotalSavings money NOT NULL CONSTRAINT DF_Customer_TotalSavings DEFAULT ((0)),
CurrentDiscount real NOT NULL CONSTRAINT DF_Customer_CurrentDiscount DEFAULT ((0)),
PriceLevel smallint NOT NULL CONSTRAINT DF_Customer_PriceLevel DEFAULT ((0)),
TaxExempt bit NOT NULL CONSTRAINT DF_Customer_TaxExempt DEFAULT ((0)),
Notes ntext NULL CONSTRAINT DF_Customer_Notes DEFAULT (''),
Title nvarchar(20) NOT NULL CONSTRAINT Df_Customer_Title DEFAULT (''),
EmailAddress nvarchar(255) NOT NULL CONSTRAINT DF_Customer_EmailAddress DEFAULT (''),
DBTimeStamp timestamp NULL,
TaxNumber nvarchar(20) NOT NULL CONSTRAINT DF_Customer_TaxNumber DEFAULT (''),
PictureName nvarchar(50) NOT NULL CONSTRAINT DF_Customer_PictureName DEFAULT (''),
DefaultShippingServiceID int NOT NULL CONSTRAINT DF_Customer_DefaultShippingServiceID DEFAULT ((0)),
PhoneNumber nvarchar(30) NOT NULL CONSTRAINT Df_Customer_PhoneNumber DEFAULT (''),
FaxNumber nvarchar(30) NOT NULL CONSTRAINT Df_Customer_FaxNumber DEFAULT (''),
CashierID int NOT NULL CONSTRAINT Df_Customer_CashierID DEFAULT ((0)),
SalesRepID int NOT NULL CONSTRAINT Df_Customer_SalesRepID DEFAULT ((0)),
CONSTRAINT PK_Customer PRIMARY KEY NONCLUSTERED
(
ID ASC
)
CREATE UNIQUE CLUSTERED INDEX IX_Customer_TimeStamp ON dbo.Customer (DBTimeStamp)
CREATE NONCLUSTERED INDEX IX_AccountNumber ON dbo.Customer (AccountNumber)
CREATE NONCLUSTERED INDEX IX_Company ON dbo.Customer (Company)
CREATE NONCLUSTERED INDEX IX_Customer_LastName_INC ON dbo.Customer (LastName)
INCLUDE (PictureName, AccountNumber, FirstName, Company, City, PhoneNumber, EmailAddress, ID, Zip)
CREATE NONCLUSTERED INDEX IX_HQID ON dbo.Customer (HQID)
CREATE NONCLUSTERED INDEX IX_ID ON dbo.Customer (ID)
I know that the table & index design are pretty bad, but I can't do anything about that. But, we have leeway in adding indexes and re-writing the query.
Things I've done:
1) I've taken a backup of the client's DB and restored it to a local dev instance of 2008R2 Enterprise. This issue manifests itself in the exact manner as on the client's site.
2) I've removed the needless varbinary conversions of timestamp columns
From this:CONVERT(VARBINARY(8), C.[DBTimeStamp]) > CONVERT(VARBINARY(8), CL.[LocalLastUpdatedTS])
To this: C.[DBTimeStamp] > CL.[LocalLastUpdatedTS]
Didn't help.
3) The MessageTypeTrack.RecordId is declared as NVARCHAR(50), but is always an integer. To avoid an implicit conversion, I explicitly converted it to an INT.
From this: CONVERT(NVARCHAR(20), CL.RecordID) to this: CONVERT(INT, CL.RecordID). Didn't help
4) I removed the merge join hint. This made things even worse. When I made the query a simple LEFT JOIN, the query never returns. The CPU spikes to 100% and I have to stop the query execution after several minutes.
5) I've tried different indexes all to no avail.
What I noticed is that:
* If I remove the MERGE join hint and leave the rest of the query unchanged, it never returns (made the problem even worse), as mentioned in #4.
* If I remove the MERGE join hint AND remove the TOP 1 operator, the query returns very fast (albeit with more than 1 row).
* If I leave the TOP 1 operator and convert the LEFT join to an INNER join, without the MERGE hint, the query returns very fast (I changed to an INNER join just for testing, it must remain a LEFT join).
* If I remove the MERGE join hint and change the TOP 1 to a TOP 20, the query returns very fast. Trying several different TOP N values, the minimum I can make N is 19. Anything less than or equal to TOP 19 (and no MERGE hint) the query never returns. Anything over TOP 19 and the query returns the row set very fast.
* If I remove the MERGE join hint, leave the TOP 1 operator (which made things worse, #4 above), but change the data conversion in the join to CONVERT(NVARCHAR(50), C.[ID]) = CL.RecordID, the query returns in about 4 seconds. I'm not sure why converting the int column to a lower data type precedence of NVARCHAR works, but converting CL.RecordId to an INT (higher precedence) doesn't.
It seems that the MERGE join hint is the only way that the query will work with the TOP 1 operator. WTF???
So, how can I get this query to run quickly against the 200K row tables with the TOP 1 operator in place?
Sorry for the very long post, but I wanted to provide as much detail as possible to clarify the issue. I've also attached an image of the query plan of the original query. I'm at a loss with trying to get this query to work with any reasonable speed and any insights anyone could offer would be most appreciated!
Thanks!
Peter W.
April 18, 2015 at 4:19 am
This should be straightforward - fetch the top row from the customer table (ordered by LastUpdated) and any row from MessageTypeTrack which matches the filter criteria. I think the optimiser is confused by one of those filter criteria:
CONVERT(VARBINARY(8), cl.LocalLastUpdatedTS) < CONVERT(VARBINARY(8), c.DBTimeStamp)
which could lead to an expensive triangular join.
Bearing in mind that a query is a description of the result set, not a set of instructions to SQL Server as to how to accomplish the task, I think a better instruction for SQL Server would be something like this:
SELECT
c.ID,
c.LastUpdated,
c.DBTimeStamp,
c.AccountNumber,
oa.RecordID,
oa.LocalLastUpdatedTS,
c.PrimaryShipToID
FROM (
SELECT TOP 1
ID,
LastUpdated,
DBTimeStamp,
AccountNumber,
PrimaryShipToID
FROM dbo.Customer
ORDER BY LastUpdated ASC
) c
OUTER APPLY ( -- we don't care which - if any - row we get back
SELECT TOP 1
cl.RecordID,
cl.LocalLastUpdatedTS
FROM dbo.MessageTypeTrack cl
WHERE CONVERT(NVARCHAR(20), cl.RecordID) = c.ID
AND CONVERT(VARBINARY(8), cl.LocalLastUpdatedTS) < CONVERT(VARBINARY(8), c.DBTimeStamp)
AND cl.MessageType = 'Customer'
AND cl.MessageTypeVersion = '1.0'
) oa
Note that there are currently no indexes on either table to properly support the query - whichever way you choose to write it.
Give the query a try. It's not the only query which will work, there are a few options. Post up the actual execution plan of this query and your original as .sqlplan attachments (plans contain a ton of useful information). This will help folks figure out optimal index structure for you.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2015 at 1:08 am
Hi
Firstly, Merge joins use sorted data sets, so take those out.
Secondly, Converting on the join is never recommended, and since you're converting to the same data type, it is very unnecessary. Remember that converting on joins means it does row based joins and not data set based joins
Also move the AND predicates down to the WHERE clause as they filter and have not bearing on the join.
SELECT TOP 1
C.[ID],
C.[LastUpdated],
C.[DBTimeStamp],
C.[AccountNumber],
CL.[RecordID],
CL.[LocalLastUpdatedTS],
C.[PrimaryShipToID]
FROM
dbo.Customer C WITH (NOLOCK)
LEFT MERGE JOIN dbo.MessageTypeTrack CL WITH (NOLOCK)
ON C.[ID] = CL.RecordID --Convert RecordID to INT or leave it be, no use converting to same datatype, performance tradeoff is not worth it.
WHERE
(CONVERT(VARBINARY(8), C.[DBTimeStamp]) > CONVERT(VARBINARY(8), CL.[LocalLastUpdatedTS])
OR CL.[RecordID] IS NULL
OR CL.[LocalLastUpdatedTS] IS NULL)
AND CL.[MessageType] = 'Customer' --Moved to WHERE clause
AND CL.[MessageTypeVersion] = '1.0' --Moved to WHERE clause
ORDER BY C.[LastUpdated] ASC
See if this helps you any.
April 20, 2015 at 2:51 am
coetzeehuman (4/20/2015)
Remember that converting on joins means it does row based joins and not data set based joins
No, it just means that indexes aren't of any use to the join and that the conversion has to be done on each and every row of the resultset.
Also move the AND predicates down to the WHERE clause as they filter and have not bearing on the join.
Since it's an outer join, moving the predicates from the join to the WHERE is going to change the logic of the query and will likely change the result. You can move predicates from JOIN to WHERE without a logic change only when the join is an INNER JOIN.
And the conversion is still a conversion on a join, even if that join is done in the WHERE clause
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2015 at 3:11 pm
Since you're trying to do a keyed lookup on the CL table, the conversion should be other way around to allow SQL to do a seek on all three key values:
...
LEFT MERGE JOIN dbo.MessageTypeTrack CL WITH (NOLOCK)
ON CL.RecordID = CONVERT(NVARCHAR(50), C.[ID])
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply