December 12, 2012 at 3:06 pm
I'm not sure why it is using index scans and not seeks. I have indexes on all the tables.
SELECT T.TXID, T.TXAMAZONORDERNUM,T.TXSENDTOAMAZON,TSN.TSNTRACKINGNUMBER,
'CARRIER' = CASE
WHEN ts.ship_typeid = 15 then 'UPS' else ST.CARRIER end,
'DESCRIPTION' = case
when ts.ship_typeid = 15 then 'Mail Innovations' else ST.DESCRIPTION end
FROM TRANSACTIONS T with (nolock)
INNER JOIN TRANSACTION_SHIPPING_NOTES TSN with (nolock) ON T.TXID = TSN.TXID
INNER JOIN TRANSACTION_SHIPPING TS with (nolock) ON T.TXID = TS.TXID
INNER JOIN SHIP_TYPE ST with (nolock) ON TS.SHIP_TYPEID = ST.SHIP_TYPEID
inner join transaction_notes tn with (nolock) on t.txid = tn.txid
WHERE (T.TXSALESCHANNEL = 'Amazon' or txpmtmethod = 'CBA')
and txsendtoamazon = 1 and
tn.tnStage='Shipped' and
txamazonordernum is not null
AND coalesce(txSubtype,'') <> 'BACKORDER'
With this there is a index scan on transaction_shipping & transaction_shipping_notes.
Also this takes alot of cpu usage and it seems like it shouldn't for such a simple query.
It only returns around 200 records.
I have attached the execution plan.
Thanks for all the help
December 12, 2012 at 3:14 pm
Can you post the actual table and index definitions? Both of those index scans look like your stats might be stale. They both have an estimated row count of about 1.1M and actual row counts of about 16K.
As a side note...why all the NOLOCK hints?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 3:27 pm
how do i upload the table definition, it says does not allow .sql as attachments.
Is that not good to use nolocks? I use them alot on queries that i do not need total accuracy.
December 12, 2012 at 3:51 pm
Simply post the DDL into your reply, and if you want to get all fancy place it between the [ code ="sql"] INSERT TSQL HERE [/ code]
Of course remove the spaces between the [ Code and / code
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 12, 2012 at 3:59 pm
CREATE TABLE [dbo].[transactions](
[txID] [int] IDENTITY(1,1) NOT NULL,
[storeNumber] [int] NULL,
[txType] [varchar](10) NULL,
[txStartDate] [datetime] NULL,
[txEndDate] [datetime] NULL,
[txReferrer] [varchar](50) NULL,
[txStatus] [varchar](50) NULL,
[cuID] [int] NULL,
[adrIDShip] [int] NULL,
[adrIDBill] [int] NULL,
[returnfor] [int] NULL,
[txParentID] [int] NULL,
[txRef] [int] NULL,
[txBackorder] [int] NULL,
[txExchange] [int] NULL,
[txExchangeParentID] [int] NULL,
[txHighRiskCheckoutStatus] [varchar](10) NULL,
[QuoteID] [int] NULL,
[ToBePrinted] [tinyint] NULL,
[txEbayCheckout] [tinyint] NULL,
[txStorePickup] [int] NULL,
[txSalesChannel] [varchar](10) NULL,
[txSendToAmazon] [tinyint] NULL,
[txAmazonOrderNum] [varchar](100) NULL,
[txPCMagSentDate] [datetime] NULL,
[txPmtMethod] [varchar](6) NULL,
[txDatePmtReceived] [datetime] NULL,
[txDatePmtFailed] [datetime] NULL,
[txDatePmtCleared] [datetime] NULL,
[PayPalTranID] [varchar](20) NULL,
[HoldOrderShipment] [datetime] NULL,
[HoldOrderShipmentReason] [varchar](500) NULL,
[txNotes] [varchar](2000) NULL,
[PayPalPending] [varchar](50) NULL,
[ResetDate] [datetime] NULL,
[ResetPmtInfoAction] [tinyint] NULL,
[ResetPmtTypesToOffer] [varchar](100) NULL,
[ResetShippingAddressAction] [tinyint] NULL,
[txPaypalReminder1SentDate] [datetime] NULL,
[txPaypalReminder2SentDate] [datetime] NULL,
[NetDollarsPaid] [money] NULL,
[PayPalPaymentType] [varchar](50) NULL,
[checkout_adrID] [int] NULL,
[paypal_adrid] [int] NULL,
[txInvoiceDate] [datetime] NULL,
[txAmazonFeedbackReminderSentDate] [datetime] NULL,
[txAmazonDoNotSendFeedbackFlag] [tinyint] NULL,
[txClarkPhotoSentDate] [datetime] NULL,
[storeQuoteID] [int] NULL,
[txAmazonPaidDate] [datetime] NULL,
[txAmazonCommissionPaid] [money] NULL,
[CGIRemoteAddress] [varchar](100) NULL,
[ebay_adrid] [int] NULL,
[txSubType] [varchar](15) NULL,
[txRefundReason] [varchar](10) NULL,
[PickerMfrGroup] [varchar](15) NULL,
[PickerItemID] [int] NULL,
[InUseByUserID] [int] NULL,
[BingCashBackOrder] [tinyint] NULL,
[BingCashBackProcessDate] [datetime] NULL,
[BingCashBackAmount] [money] NULL,
[EbayMarkedPaidDate] [datetime] NULL,
[txSendToExternal] [tinyint] NULL,
[txExternalOrderNum] [varchar](50) NULL,
[FBA] [tinyint] NULL,
[WebsiteOrderNum] [int] NULL,
[PickerLocGroup] [varchar](10) NULL,
[PickerLocSort] [varchar](3) NULL,
[pickerloc] [varchar](5) NULL,
[EligForSIQ] [tinyint] NULL,
[OrigPayPalTranID] [varchar](20) NULL,
[txCJCancelSentDate] [datetime] NULL,
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[txID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[transaction_shipping_notes](
[txID] [int] NOT NULL,
[tsnTrackingNumber] [varchar](50) NULL,
[tsnNotes] [varchar](4000) NULL,
[tsnShippingMailSentDate] [datetime] NULL,
[tsnSendShippingMailFlag] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[transaction_shipping](
[txID] [int] NOT NULL,
[liID] [int] NOT NULL,
[stID] [int] NULL,
[srID] [int] NULL,
[shippingID] [int] NOT NULL,
[shippingCost] [money] NULL,
[shippingNoticeParsed] [bit] NULL,
[shippingNotice] [varchar](2000) NULL,
[ship_typeid] [int] NULL,
[ActualShippingCost] [money] NULL,
[PostmarkDate] [datetime] NULL,
[InsuranceFee] [money] NULL,
[shippingOverrideFlag] [tinyint] NULL,
[AmazonShipType] [varchar](10) NULL,
[ActualDimWt] [int] NULL,
[ActualShippingWeight] [int] NULL,
[HighPriorityFlag] [tinyint] NULL,
[ItemValueOverrideForInsurance] [money] NULL,
[MailInnovationsFlag] [tinyint] NULL,
[ship_cost] [money] NULL,
[insur_cost] [money] NULL,
[EbayMarkedShippedDate] [datetime] NULL,
[ExternalShipType] [varchar](35) NULL,
[ReqSigFlag] [tinyint] NULL,
[ShippingComputer] [tinyint] NULL,
[LateShipment] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[transaction_notes](
[tnID] [int] IDENTITY(1,1) NOT NULL,
[txID] [int] NULL,
[txNotes] [varchar](4000) NULL,
[tnStage] [varchar](20) NULL,
[adminID] [int] NULL,
[tnDate] [datetime] NULL,
[tnExported] [datetime] NULL,
CONSTRAINT [PK_transaction_notes] PRIMARY KEY NONCLUSTERED
(
[tnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ship_type](
[ship_typeid] [int] NOT NULL,
[carrier] [varchar](10) NULL,
[description] [varchar](50) NOT NULL,
[carrier_sort] [int] NULL,
[ship_type_sort] [int] NULL,
[ratetable] [varchar](50) NULL,
[source] [varchar](50) NULL,
[active] [tinyint] NULL,
[created_datetime] [datetime] NOT NULL,
[created_by] [varchar](75) NOT NULL,
[updated_datetime] [datetime] NOT NULL,
[updated_by] [varchar](75) NOT NULL,
[pickerShipSort] [int] NULL,
[pickerShipName] [varchar](40) NULL,
[RequireSignature] [tinyint] NULL,
[internal_only] [tinyint] NOT NULL,
[discount_pct] [float] NOT NULL,
[base_min] [money] NULL,
[insurance_multiplier] [float] NOT NULL,
[sig_reqd_possible] [tinyint] NOT NULL,
[sig_reqd_for_fraud] [tinyint] NOT NULL,
[isGeneric] [tinyint] NULL,
[CustomerDisplay] [varchar](50) NULL,
CONSTRAINT [IX_ship_type] UNIQUE NONCLUSTERED
(
[ship_typeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
December 12, 2012 at 4:01 pm
The reason nearly everyone on here will chine in on the use of NOLOCK (locking hints in particular) is primary because of the byproduct of using it altogether (dirty reads/potentially bad). The most common misconception with this is using it makes things run faster (and this isn't the case). It's typically best to let SQL manage it's own locking/escalations.
In looking at your attached execution plan, it looks like you used the Tuning Advisor already (IMHO, take what that thing provides you with a grain of salt). I agree with Sean, the differences in your estimate/actual may be due to out of date statistics. Consider, updating them before running your query or completely rebuild the following indexes:
- MT_20121207_Transactions_forselectforamazon_1
- _dta_index_transaction_shipping_9_464772763__K9
- _dta_index_transaction_shipping_notes_7_660197402__K1_2
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 12, 2012 at 4:39 pm
i updated the stats, and rebuilt the indexes. Still the same large difference between the estimated and actual, and still have the index scans. Very strange
December 13, 2012 at 7:38 am
Michael T2 (12/12/2012)
Is that not good to use nolocks? I use them alot on queries that i do not need total accuracy.
Actually NOLOCK is far worse than simply dirty reads. You can get duplicate or even missing data. It can cause bugs that are impossible to reproduce and happen sporadically.
Here are a few articles that explain this hint in detail.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
Now I am not saying that there is not a use for this but it is very important that you understand exactly what it is doing. Often times using an isolation level (snapshot isolation) is preferred because you don't have the consistency issues that you do with NOLOCK.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 7:52 am
Remove this from the where clause and see if you still get a scan:
AND coalesce(txSubtype,'') <> 'BACKORDER'
Then, do a search on this site for SARGABLE.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2012 at 8:00 am
Michael L John (12/13/2012)
Remove this from the where clause and see if you still get a scan:AND coalesce(txSubtype,'') <> 'BACKORDER'
Then, do a search on this site for SARGABLE.
You can still include this check but you need to do it a little differently. First of all there is no point in using coalesce here. NULL can't be equal to anything so just remove it.
txSubType > 'BACKORDER' OR txSubType < 'BACKORDER'
That should keep it sargable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 10:10 am
if i take out
AND coalesce(txSubtype,'') <> 'BACKORDER'
no records are returned. thats why i am using the coalesce. Most of the records i need have a null in that column
December 13, 2012 at 10:17 am
Michael T2 (12/13/2012)
if i take out
AND coalesce(txSubtype,'') <> 'BACKORDER'
no records are returned. thats why i am using the coalesce. Most of the records i need have a null in that column
Can't you just eliminate the NULLS from the equation altogether?
AND txtSubType IS NOT NULL
AND txtSubType <> 'BACKORDER'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 13, 2012 at 10:20 am
i dont want to eliminate them, i need the ones with the nulls
December 13, 2012 at 10:21 am
Michael T2 (12/13/2012)
if i take out
AND coalesce(txSubtype,'') <> 'BACKORDER'
no records are returned. thats why i am using the coalesce. Most of the records i need have a null in that column
So you want the rows that are NULL and the rows where there is a value that is not 'BACKORDER'
The following example will do that without using any functions on a column.
create table #Test
(
ID int identity,
txSubType varchar(20)
)
insert #Test
select 'Something' union all
select NULL union all
select 'BackOrder'
select *
from #Test
where coalesce(txSubtype,'') <> 'BACKORDER' --The coalesce makes this nonSARGable
select *
from #Test
where txSubtype > 'BACKORDER'
or txSubtype > 'BACKORDER'
or txSubType is null
drop table #Test
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 10:32 am
using that, i still have the table scans. if i only use
and (txSubtype > 'BACKORDER'
or txSubtype < 'BACKORDER'
then the scans go away.Strange.
I am going to read up on sargable now.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply