May 6, 2011 at 2:14 am
Hi,
I have a query that I have tuned to some extent and from its earlier execution time of 15 seconds I could manage it to bring down to 8 seconds. But still there is a part where I am seeing a non-clustered index of a table called 'transactions' with 20 million+ rows in it. The query is mentioned below and the most expensive portion is when there is a join on this monster table.
Declare @pUserID as int
Set @pUserID =-768476
Declare @T Table(
FileNum int Not Null ,
FilerecordCount int
);
Declare @ListofRecordKeys table(
recordkey nvarchar(18) Primary Key
)
Insert into @T (FileNum, FilerecordCount )
Select distinct
wh.RecID ,
wh.FileRecordCount
from dbo.WH_FileuploadLog wh
left join moc_dt.dbo.inet_invoicesprInteD inv
on inv.invoiceno = wh.recid
and inv.userid = @pUserID
Where wh.ProcessStatus = 'Processed'
and wh.TimeStamp >= '12/01/2010'
and wh.RecID >= 33868--this is the min
and inv.invoiceno is null
insert into @ListofRecordKeys(
recordkey )
SELECT distinct
t.RecordKey
FROM dbo.Transactions T
left join dbo.Transactions_CustomFields tc
on tc.RecordKey = t.RecordKey
WHERE T.source = 'WH'
AND t.FileID in (Select FileNum from @T)--
and isnull(tc.Custom5, '') = ''
select distinct
c.DG_cust_no,
c.DG_ship_to
from @ListofRecordKeys lr
join dbo.Transactions t
on t.RecordKey = lr.recordkey
join dbo.Company c
on c.Company_Link = t.Company_Link
Can anyone show me some light ?
Regards
Chandan
May 6, 2011 at 2:20 am
How many rows in the table variable?
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
May 6, 2011 at 2:37 am
GilaMonster (5/6/2011)
How many rows in the table variable?
In the table variable @T the row count is 4834
in the table variable @listofrecordkeys the row count is 123
May 6, 2011 at 3:12 am
Can you post DDL including index definitions for dbo.Transactions and dbo.Company?
May 6, 2011 at 3:53 am
HowardW (5/6/2011)
Can you post DDL including index definitions for dbo.Transactions and dbo.Company?
I am attaching the DDL schema for tables company and transactions. For indexes, which of the table here you want the script of indexes. Each has 12-15 indexes. The index scan that is being performed on transactions is a non clustered index on the fields recordkey and company_link
May 6, 2011 at 4:00 am
RecordKey is a char(18), but you've defined it at nvarchar(18) in your temp table. This is probably preventing an index seek
May 6, 2011 at 4:26 am
HowardW (5/6/2011)
RecordKey is a char(18), but you've defined it at nvarchar(18) in your temp table. This is probably preventing an index seek
Magic! Thank you very much. You identified the problem quickly. The query that I wrote above was the worst part of a stored proc. Earlier my stored procedure was taking 25-30 seconds to complete but now it takes around 7 seconds. This is pure genius. 🙂
Can you please explain what made you think that it could be a problem. How to look into such loopholes when DTA and DMV cannot suggest anything.
May 6, 2011 at 4:36 am
SARGability is the first thing to look for when you think you have a suitable index that isn't used. Once you can see there are no functions etc. being used that would prevent index use (like ISNULL() etc) then one of the most common reasons is implicit conversion between datatypes (unicode/non-unicode being one of the more common ones).
Sometimes you'd see a CONVERT IMPLICIT in the predicate of an operation, but not always (as in this case).
Have a google of the term SARGability and you'll find some good references that point out the major pitfalls to look out for.
May 6, 2011 at 4:59 am
chandan_jha18 (5/6/2011)
In the table variable @T the row count is 4834
That's well over what I'd put into a table variable due to the lack of statistics and its effect on the plan. Try converting that to a temp table.
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
May 6, 2011 at 6:03 am
HowardW (5/6/2011)
SARGability is the first thing to look for when you think you have a suitable index that isn't used. Once you can see there are no functions etc. being used that would prevent index use (like ISNULL() etc) then one of the most common reasons is implicit conversion between datatypes (unicode/non-unicode being one of the more common ones).Sometimes you'd see a CONVERT IMPLICIT in the predicate of an operation, but not always (as in this case).
Have a google of the term SARGability and you'll find some good references that point out the major pitfalls to look out for.
You are right. I had seen 'convert implicit' once in execution plan where Gail asked me to change the data type. Here it was not shown (not sure why). But it was indeed the cause. I will remember it always. Thanks. There are so many things that DTA or DMV will not suggest regarding indexes.
May 6, 2011 at 6:05 am
GilaMonster (5/6/2011)
chandan_jha18 (5/6/2011)
In the table variable @T the row count is 4834That's well over what I'd put into a table variable due to the lack of statistics and its effect on the plan. Try converting that to a temp table.
thanks Gail. I thought 5000 rows is a small number to be stored in a table variable. I will try to change it to temp table and see how it works.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply