November 27, 2015 at 1:26 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 1:45 am
Is there a reason you've abandoned this thread - http://www.sqlservercentral.com/Forums/Topic1740359-364-1.aspx, where people are trying to help you?
November 29, 2015 at 11:17 pm
If you could post the execution plan, that would be a big help.
You do realize that NOLOCK could lead to missing or duplicate rows.
Nothing jumps out of the query as you have it written here that is glaringly an issue. My only concern is the BETWEEN statement. 100 possible values is quite a lot and could lead to scans if the statistics suggest that the this leads to less than highly selective sets of data. But that's just a vague guess. I can tell you a lot more with an execution plan. Preferably the actual execution plan, but estimated is OK.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply