November 27, 2015 at 1:12 am
Hi Friends,
I have one query which is taking huge time to execute.
below is a query used in source component.
tbl_documents table is having 700 rows and tbl_documentdetails table is having 1.6 millions+ rows.
I have created index on tbl_documentdetails table on invoiceid,orderid,billid column as per sequence used in join condition.
SELECT
a.*
,b.value1
,b.value2
FROM
tbl_documents AS a
LEFT OUTER JOIN
dbo.tbl_documentdetails b WITH(NOLOCK)
ON
a.invoiceid=b.invoiceid
AND
a.orderId=b.orderid
AND
a.billid=b.billid
WHERE
EXISTS
(
SELECT
c.invoice
FROM
DocumentCodeList c
WHERE
a.invoiceid = c.invoiceid
AND
b.srnumber BETWEEN 1 AND 100
)
I would really appreciate if anyone can crack the performance issue.
Thank You
November 27, 2015 at 10:34 am
Re1 (11/27/2015)
tbl_documents table is having 700 rows and tbl_documentdetails table is having 1.6 millions+ rows.
I have created index on tbl_documentdetails table on invoiceid,orderid,billid column as per sequence used in join condition.
If you (almost) always use invoiceid to do lookups on tbl_documentdetails -- and from this code it seems you probably do -- then try clustering tbl_documentdetails on ( invoiceid, orderid, billid ). That is, that table's clustered index should be on those columns, not just a nonclustered index.
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".
November 27, 2015 at 11:31 am
I like what you did on the document details table.
Did you also create a nonclustered index on DocumentCodeList(invoiceid, srnumber)?
Of course, the obligatory question is if you understand the dangers of using NOLOCK.
The other obvious question is about the three tables involved. Please tell me that they have clustered indexes on them.
From there, take a look at the actual execution plan and find your bottlenecks. If you don't see it, please post the DDL for the three tables and the actual execution plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply