How to make my query run faster

Transact-SQL
1

my select query joining tables containing lakhs of records is slow.how to make it faster.i have tried non clustered index.But it is still slow.

2

Have you tried pushing the turbo button?

No seriously, you need to at least show the query in question for us to have any chance of trying to help.

2 Likes
3

this is my query:

SELECT h.InvoiceID,InvoiceNumber, InvoiceDate,InvoiceType,TransportClass,h.Total,SubmittedTotal,TotalLineItems,
h.LongDescription as Invoice_Longdesc,CurrencyCode,DiscountTotal,CurrentOwner_DeptName,c.PartnerType as Company_partnertype,CompanyCode ,CompanyCodeType,CompanyName,
s.PartnerType,s.SiteCode,s.SiteName,s.SiteCodeType,AddressType,AddressLine,
City,StateProvince,StateProvinceCode,Country,CountryCode,PostalZipCode,
P_role,P_FName,P_LName,P_Email,p.SiteCode as Person_sitecode,p.SiteCodeType as person_sitecodetype,p.SiteName as person_sitename,p.DepartmentName as person_deptname,
ActionType,[Status],p.LongDescription as person_longdesc,TransactionDateTime,TransactionIdentifier,p.PhoneNumber,
--sp.PartnerType as site_partnertype,sp.SiteCode as site_sitecode,sp.PhoneNumber as site_phoneno,sp.PhoneNumberType,
A_role,A_FName,A_LName,A_Email,ds.SiteCode as docSource_sitecode ,ds.SiteCodeType as docSource_SiteCodeType,ds.SiteName as docSource_SiteName,ds.PhoneNumber as docSource_PhoneNumber ,
d.SiteCode as dest_sitecode,d.SiteCodeType as dest_sitecodetype,d.SiteName as dest_sitename,d.DepartmentName as dest_fdeptname ,i.LineItemNumber, ProductServiceCode,codeIndicator,ProductServiceDescription,Quantity,Units,UnitPrice,
i.LongDescription,ServiceStartDate,ServiceDate,Tax_Total,Tax_RateOrPercentage,i.Tax_TaxType as InvDetail_TaxType,Discount_Total,Discount_RateOrPercentage,
SubTotal,PreTaxTotal,i.Total as InvDetail_Total,Categorization_Scheme,Categorization_CategoryCode,DocumentCrossReference_DocNo,DocumentCrossReference_DocType,
LineItemChargeClass
,RateOrPercentage,ic.Total as InvCoding_Total,AFENumber,CostCenterNumber,PurchaseOrderNumber,WorkOrderNumber,ic.Tax_TaxType,
MajorAccount_AccNo,MajorAccount_Description,MajorAccount_AccType,MinorAccount_AccNo,MinorAccount_Description
FROM dbo.InvoiceHeader h inner JOIN
dbo.DocAction_Destination d on h.invoiceid=d.invoiceid
inner join dbo.DocAction_Person p on p.invoiceid=h.invoiceid
inner join dbo.DocAction_Source ds on ds.invoiceid=h.invoiceid
inner join dbo.InvoiceDetail I on h.invoiceid=i.invoiceid
inner join dbo.InvoiceDetail_Coding ic on i.invoiceid=ic.invoiceid and i.lineitemnumber=ic.lineitemnumber
inner join dbo.Partner_Company c on h.invoiceid=c.invoiceid
inner join dbo.Partner_Site s on s.invoiceid=h.invoiceid
--inner join dbo.Partner_Site_PhoneNo sp on sp.invoiceid=h.invoiceid and s.partnertype=sp.partnertype

4

Looks like your're querying every single rows - no filtering?

5

i need to display whole data

6

Verify you have appropriate indexes on the join columns to support the joins - other than that, there isn't anything else you can do to improve the performance.

I have to wonder who is going to view lakhs (millions?) of invoices - and how they could even begin to process through that much data.

1 Like
7

lakh
läk
noun INDIAN
plural noun: lakhs
a hundred thousand.
"they fixed the price at five lakhs of rupees"

8

Slow running query can be caused by a bad physical structure of the database or the issue in the configuration of SQL Server. You can make it faster by looking out the reason why are you experiencing this? See here:
http://www.sqlmvp.org/are-you-experiencing-slow-running-queries-in-sql-server/