February 17, 2012 at 12:03 am
Purpose : I need to insert data of specific file of specific distributor in
Table InvoiceTransaction From Table RawStockTransaction
For doing so I want to select those rows from Table RawStockTransaction which does not already exists in InvoiceTransactions
My query is as follows
Select Distinct DistributorId,FileId,DistInvoiceNo,InvoiceTypeId,InvoiceDate,CustomerId
From (
SELECT Files.DistributorId,RawST.FileId,RawST.DistInvoiceNo,RawST.InvoiceTypeId,RawST.InvoiceDate
, DistCus.CustomerId
FROM RawStockTransaction AS RawST INNER JOIN
Files ON Files.FileId = RawST.FileId And Files.FileId=@FileId INNER JOIN
DistCustomers DistCus on DistCus.DistCustomerCode=RawST.DistCustomerCode And DistCus.DistributorId=@DistributorId
Left outer join
(Select DistInvoiceNo,ST.InvoiceTypeId ,DistCustomers.DistCustomerCode
From InvoiceTransactions ST Inner join
DistCustomers on ST.CustomerId=DistCustomers.CustomerId And DistCustomers.DistributorId=@DistributorId
Where ST.DistributorId=@DistributorId ) as ec
on ec.DistInvoiceNo=RawSt.DistInvoiceNo and ec.InvoiceTypeId=RawSt.InvoiceTypeId and ec.DistCustomerCode=RawSt.DistCustomerCode
Where ec.DistInvoiceNo is null and ec.InvoiceTypeId is null and ec.DistCustomerCode is null
) as n
order by InvoiceTypeId, DistInvoiceNo
Problem # 1: When run this query from Query designer it executes and return results within seconds but if i simple write stored procedure or function it takes 18 seconds to return results to me. It is not clear to me why it takes too much time to run it from stored procedure or function
Problem #2: If i remove left outer join used to get data that does not already exists then query is fine. How can i do so because my need is to get that data which does not already exists
I have created index but that does not increase its performance
My table can has large amunt of data as there can be millions of rows in RawStockTransaction and InvoiceTransaction. currently it takes 18 seconds for only 100 thousands rows
February 17, 2012 at 2:21 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply