March 1, 2012 at 6:50 am
Great Plains Query:
-----------------------------
SELECT * FROM SOP30300
WHERE SOPNUMBE IN
(
SELECT SOPNUMBE FROM #StatusHistory
)
#StatusHistory has 8 rows and the leading column of the primary key of SOP30300 is SOPNUMBE. But I get a full table scan.
Table definitions:
#StatusHistory is a temp table built from selecting a set of SOPNUMBEs from another table. In that table the SOPNUMBE column is an nvarchar(50).
SOP30300 table:
CREATE TABLE [dbo].[SOP30300](
[SOPTYPE] [smallint] NOT NULL,
[SOPNUMBE] [char](21) NOT NULL,
[LNITMSEQ] [int] NOT NULL,
[CMPNTSEQ] [int] NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[NONINVEN] [smallint] NOT NULL,
[DROPSHIP] [smallint] NOT NULL,
[UOFM] [char](9) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[ORUNTCST] [numeric](19, 5) NOT NULL,
[UNITPRCE] [numeric](19, 5) NOT NULL,
[ORUNTPRC] [numeric](19, 5) NOT NULL,
[XTNDPRCE] [numeric](19, 5) NOT NULL,
[OXTNDPRC] [numeric](19, 5) NOT NULL,
[REMPRICE] [numeric](19, 5) NOT NULL,
[OREPRICE] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[OREXTCST] [numeric](19, 5) NOT NULL,
[MRKDNAMT] [numeric](19, 5) NOT NULL,
[ORMRKDAM] [numeric](19, 5) NOT NULL,
[MRKDNPCT] [smallint] NOT NULL,
[MRKDNTYP] [smallint] NOT NULL,
[INVINDX] [int] NOT NULL,
[CSLSINDX] [int] NOT NULL,
[SLSINDX] [int] NOT NULL,
[MKDNINDX] [int] NOT NULL,
[RTNSINDX] [int] NOT NULL,
[INUSINDX] [int] NOT NULL,
[INSRINDX] [int] NOT NULL,
[DMGDINDX] [int] NOT NULL,
[ITMTSHID] [char](15) NOT NULL,
[IVITMTXB] [smallint] NOT NULL,
[BKTSLSAM] [numeric](19, 5) NOT NULL,
[ORBKTSLS] [numeric](19, 5) NOT NULL,
[TAXAMNT] [numeric](19, 5) NOT NULL,
[ORTAXAMT] [numeric](19, 5) NOT NULL,
[TXBTXAMT] [numeric](19, 5) NOT NULL,
[OTAXTAMT] [numeric](19, 5) NOT NULL,
[BSIVCTTL] [tinyint] NOT NULL,
[TRDISAMT] [numeric](19, 5) NOT NULL,
[ORTDISAM] [numeric](19, 5) NOT NULL,
[DISCSALE] [numeric](19, 5) NOT NULL,
[ORDAVSLS] [numeric](19, 5) NOT NULL,
[QUANTITY] [numeric](19, 5) NOT NULL,
[ATYALLOC] [numeric](19, 5) NOT NULL,
[QTYINSVC] [numeric](19, 5) NOT NULL,
[QTYINUSE] [numeric](19, 5) NOT NULL,
[QTYDMGED] [numeric](19, 5) NOT NULL,
[QTYRTRND] [numeric](19, 5) NOT NULL,
[QTYONHND] [numeric](19, 5) NOT NULL,
[QTYCANCE] [numeric](19, 5) NOT NULL,
[QTYCANOT] [numeric](19, 5) NOT NULL,
[QTYORDER] [numeric](19, 5) NOT NULL,
[QTYPRBAC] [numeric](19, 5) NOT NULL,
[QTYPRBOO] [numeric](19, 5) NOT NULL,
[QTYPRINV] [numeric](19, 5) NOT NULL,
[QTYPRORD] [numeric](19, 5) NOT NULL,
[QTYPRVRECVD] [numeric](19, 5) NOT NULL,
[QTYRECVD] [numeric](19, 5) NOT NULL,
[QTYREMAI] [numeric](19, 5) NOT NULL,
[QTYREMBO] [numeric](19, 5) NOT NULL,
[QTYTBAOR] [numeric](19, 5) NOT NULL,
[QTYTOINV] [numeric](19, 5) NOT NULL,
[QTYTORDR] [numeric](19, 5) NOT NULL,
[QTYFULFI] [numeric](19, 5) NOT NULL,
[QTYSLCTD] [numeric](19, 5) NOT NULL,
[QTYBSUOM] [numeric](19, 5) NOT NULL,
[EXTQTYAL] [numeric](19, 5) NOT NULL,
[EXTQTYSEL] [numeric](19, 5) NOT NULL,
[ReqShipDate] [datetime] NOT NULL,
[FUFILDAT] [datetime] NOT NULL,
[ACTLSHIP] [datetime] NOT NULL,
[SHIPMTHD] [char](15) NOT NULL,
[SALSTERR] [char](15) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[PRCLEVEL] [char](11) NOT NULL,
[COMMNTID] [char](15) NOT NULL,
[BRKFLD1] [smallint] NOT NULL,
[BRKFLD2] [smallint] NOT NULL,
[BRKFLD3] [smallint] NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[SOPLNERR] [binary](4) NOT NULL,
[DOCNCORR] [char](21) NOT NULL,
[ORGSEQNM] [int] NOT NULL,
[ITEMCODE] [char](15) NOT NULL,
[PURCHSTAT] [smallint] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ODECPLCU] [smallint] NOT NULL,
[EXCEPTIONALDEMAND] [tinyint] NOT NULL,
[TAXSCHID] [char](15) NOT NULL,
[TXSCHSRC] [smallint] NOT NULL,
[PRSTADCD] [char](15) NOT NULL,
[ShipToName] [char](65) NOT NULL,
[CNTCPRSN] [char](61) NOT NULL,
[ADDRESS1] [char](61) NOT NULL,
[ADDRESS2] [char](61) NOT NULL,
[ADDRESS3] [char](61) NOT NULL,
[CITY] [char](35) NOT NULL,
[STATE] [char](29) NOT NULL,
[ZIPCODE] [char](11) NOT NULL,
[CCode] [char](7) NOT NULL,
[COUNTRY] [char](61) NOT NULL,
[PHONE1] [char](21) NOT NULL,
[PHONE2] [char](21) NOT NULL,
[PHONE3] [char](21) NOT NULL,
[FAXNUMBR] [char](21) NOT NULL,
[Flags] [smallint] NOT NULL,
[CONTNBR] [char](11) NOT NULL,
[CONTLNSEQNBR] [numeric](19, 5) NOT NULL,
[CONTSTARTDTE] [datetime] NOT NULL,
[CONTENDDTE] [datetime] NOT NULL,
[CONTITEMNBR] [char](31) NOT NULL,
[CONTSERIALNBR] [char](21) NOT NULL,
[ISLINEINTRA] [tinyint] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CONSTRAINT [PKSOP30300] PRIMARY KEY NONCLUSTERED
(
[SOPNUMBE] ASC,
[SOPTYPE] ASC,
[CMPNTSEQ] ASC,
[LNITMSEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
March 1, 2012 at 6:59 am
aurato (3/1/2012)
Great Plains Query:-----------------------------
SELECT * FROM SOP30300
WHERE SOPNUMBE IN
(
SELECT SOPNUMBE FROM #StatusHistory
)
...
Have you ever heard of JOIN's in SQL?
SELECT s.*
FROM SOP30300 s
JOIN #StatusHistory h ON h.SOPNUMBE = s.SOPNUMBE
Do you have table scan of SOP30300? Does this table has clustered index? If not it is "heap" one. You would not expect good performance of it.
So, advices:
1. Create clustered index - !!!!!!!!!!!!!!!
2. Create index for SOPNUMBE on SOP30300 to help queries joining on it or filtering
March 1, 2012 at 6:59 am
change the dataype of the sopnumber in the temp table to char or varchar.
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2012 at 7:04 am
Eugene Elutin (3/1/2012)
aurato (3/1/2012)
Great Plains Query:-----------------------------
SELECT * FROM SOP30300
WHERE SOPNUMBE IN
(
SELECT SOPNUMBE FROM #StatusHistory
)
...
Have you ever heard of JOIN's in SQL?
SELECT s.*
FROM SOP30300 s
JOIN #StatusHistory h ON h.SOPNUMBE = s.SOPNUMBE
Do you have table scan of SOP30300? Does this table has clustered index? If not it is "heap" one. You would not expect good performance of it.
So, advices:
1. Create clustered index - !!!!!!!!!!!!!!!
2. Create index for SOPNUMBE on SOP30300 to help queries joining on it or filtering
I have heard of JOINs, thanks for not being condescending. It results in the same execution plan. And I don't know if there's any reason, a priori, to assume that a heap can't perform well on joins. At least, I've never seen it suggested.
EDIT: It does have an index that should support SOPNUMBE joins/filtering.
March 1, 2012 at 7:07 am
sturner (3/1/2012)
change the dataype of the sopnumber in the temp table to char or varchar.
I had a hunch this might be it. Is it safe to change the data type of a column in production? The Max(LEN(SOPNUMBE)) in that table is 17, so making it a varchar(21) to match the char(21) of the SOP table won't have any truncation issue.
March 1, 2012 at 7:33 am
Eugene Elutin (3/1/2012)
aurato (3/1/2012)
Great Plains Query:-----------------------------
SELECT * FROM SOP30300
WHERE SOPNUMBE IN
(
SELECT SOPNUMBE FROM #StatusHistory
)
...
Have you ever heard of JOIN's in SQL?
SELECT s.*
FROM SOP30300 s
JOIN #StatusHistory h ON h.SOPNUMBE = s.SOPNUMBE
Why, since IN is faster than a join?
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
March 1, 2012 at 7:37 am
aurato (3/1/2012)
sturner (3/1/2012)
change the dataype of the sopnumber in the temp table to char or varchar.I had a hunch this might be it. Is it safe to change the data type of a column in production? The Max(LEN(SOPNUMBE)) in that table is 17, so making it a varchar(21) to match the char(21) of the SOP table won't have any truncation issue.
Should be perfectly safe to use alter table alter column to change the data type. You will probably get a warning about possible data loss.
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2012 at 7:39 am
I suspect there are two main reasons this is a table scan.
1) The data types of the two join columns don't match. They should be both char/varchar or both nchar/nvarchar
2) The index (the primary key) is nonclustered and because of the SELECT * is not a covering index, so SQL would have to do key lookups if it used the index.
That said, the pk is a little wide to be a good clustered index, so it is a tradeoff. Do you really need all columns? If not, replace the SELECT * with SELECT <column list> and specify just the columns you need and it might be possible to create a covering nonclustered index.
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
March 1, 2012 at 7:41 am
sturner (3/1/2012)
aurato (3/1/2012)
sturner (3/1/2012)
change the dataype of the sopnumber in the temp table to char or varchar.I had a hunch this might be it. Is it safe to change the data type of a column in production? The Max(LEN(SOPNUMBE)) in that table is 17, so making it a varchar(21) to match the char(21) of the SOP table won't have any truncation issue.
Should be perfectly safe to use alter table alter column to change the data type. You will probably get a warning about possible data loss.
Msg 5074, Level 16, State 1, Line 1
The index 'ix_PLCTRACK_STATHIST_Perf' is dependent on column 'SOPNUMBE'.
Msg 5074, Level 16, State 1, Line 1
The index 'ixPLCTRACK_STATUSHISTORY_Perf' is dependent on column 'SOPNUMBE'.
Msg 5074, Level 16, State 1, Line 1
The index 'IX_PLCTRACK_SH_SOPNUMBE' is dependent on column 'SOPNUMBE'.
Msg 4922, Level 16, State 9, Line 1
Not sure what to do with those errors. Those are all indexes on the table I'm trying to alter.
March 1, 2012 at 7:46 am
aurato (3/1/2012)
sturner (3/1/2012)
aurato (3/1/2012)
sturner (3/1/2012)
change the dataype of the sopnumber in the temp table to char or varchar.I had a hunch this might be it. Is it safe to change the data type of a column in production? The Max(LEN(SOPNUMBE)) in that table is 17, so making it a varchar(21) to match the char(21) of the SOP table won't have any truncation issue.
Should be perfectly safe to use alter table alter column to change the data type. You will probably get a warning about possible data loss.
Msg 5074, Level 16, State 1, Line 1
The index 'ix_PLCTRACK_STATHIST_Perf' is dependent on column 'SOPNUMBE'.
Msg 5074, Level 16, State 1, Line 1
The index 'ixPLCTRACK_STATUSHISTORY_Perf' is dependent on column 'SOPNUMBE'.
Msg 5074, Level 16, State 1, Line 1
The index 'IX_PLCTRACK_SH_SOPNUMBE' is dependent on column 'SOPNUMBE'.
Msg 4922, Level 16, State 9, Line 1
Not sure what to do with those errors. Those are all indexes on the table I'm trying to alter.
You evidently have some indexes that include that particular colum. You will have to drop them first, change the data type then recreate them.
No big deal, just do it in a transaction, or if the table is empty no big deal.
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2012 at 7:46 am
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraints
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
March 1, 2012 at 7:53 am
Yes, you want to change the nvarchar() to varchar() not the other way around. I thought that was in your temp table?
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2012 at 7:56 am
GilaMonster (3/1/2012)
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraints
This makes more sense I guess. I do eventually want to change the source table because I'm sure this join happens in lots of other queries (both very heavily used tables around here).
March 1, 2012 at 8:02 am
aurato (3/1/2012)
GilaMonster (3/1/2012)
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraintsThis makes more sense I guess. I do eventually want to change the source table because I'm sure this join happens in lots of other queries (both very heavily used tables around here).
No point in doubling the storage size unnecessarily. If that column is 17-20 ASCII characters, then changing it to nvarchar wastes up to 20 bytes per row. On a 3 million row table that's 57MB of wasted space in the table and in index that the column appears in.
Rather make sure that any table joining to it is char/varchar of an appropriate length
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
March 1, 2012 at 8:03 am
GilaMonster (3/1/2012)
aurato (3/1/2012)
GilaMonster (3/1/2012)
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraintsThis makes more sense I guess. I do eventually want to change the source table because I'm sure this join happens in lots of other queries (both very heavily used tables around here).
No point in doubling the storage size unnecessarily. If that column is 17-20 ASCII characters, then changing it to nvarchar wastes up to 20 bytes per row. On a 3 million row table that's 57MB of wasted space in the table and in index that the column appears in.
Rather make sure that any table joining to it is char/varchar of an appropriate length
Did I mistype somewhere? I actually want it to go from unicode to non-unicode.
EDIT: In this case nvarchar(50) to varchar(21), as in GP the SOPNUMBE is a char(21)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply