Link to home
Start Free TrialLog in
Avatar of programmerist 1983
programmerist 1983Flag for Türkiye

asked on

How can i make query faster by writing T-SQL?

Hi;

i have been working below query but it is working really slow. Returnind data from Sql takes 1:16 seconds. How can i make it faster than now?

SELECT
 di.Batch,
CONVERT(datetime, s.[ExpiryDate]) ExpiryDate
,s.[CustomerFullName]    
,s.[CustomerID]
,s.[MaterialName]
,s.[MaterialNumber]
,s.DeliveryDate
,s.SapContractNumber
,s.SapDeliveryNumber
,datediff(day, getdate(),s.ExpiryDate) DateDiff
FROM [Sade].[dbo].[StokView] s
LEFT JOIN dbo.DeliveryItems di ON di.SapContractNumber= s.SapContractNumber
WHERE PartType != 'ReAgent' 
AND s.[ExpiryDate] >= @date1
AND s.[ExpiryDate] <= @date2
AND s.CustomerId  = @customer
order by s.[ExpiryDate]

Open in new window

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

"Proper indexing is again the best solution to reduce response times. The correct index however depends on which of the three common join algorithms is used for the query." brb
Avatar of Vitor Montalvão
Second Mike's suggestion for creating the proper indexes but for that you'll need to provide us the Query Execution Plan.
Do you need the ORDER BY clause? Usually is where SQL engine takes longer so if you can order the data in your application it would help to boost the query performance. Check if the following improves the performance:
SELECT
 di.Batch,
CONVERT(datetime, s.[ExpiryDate]) ExpiryDate
,s.[CustomerFullName]    
,s.[CustomerID]
,s.[MaterialName]
,s.[MaterialNumber]
,s.DeliveryDate
,s.SapContractNumber
,s.SapDeliveryNumber
,datediff(day, getdate(),s.ExpiryDate) DateDiff
FROM [Sade].[dbo].[StokView] s
LEFT JOIN dbo.DeliveryItems di ON di.SapContractNumber= s.SapContractNumber
WHERE NOT (PartType = 'ReAgent')
AND s.[ExpiryDate] >= @date1
AND s.[ExpiryDate] <= @date2
AND s.CustomerId  = @customer

Open in new window

Hi Vitor,

I was trying to change the original code in a manner to apply WHERE clause first so that when JOIN clause starts working, fewer rows need to be joined. I wonder if you have some suggestions for doing this (perhaps using CTE or Derived Table)?

Thanks,

Mike
Hi Mike,

SQL Server is smart enough to perform the necessary changes for JOIN to WHERE when needed. Also, a CTE doesn't looks to me a good solution for this case. IMO, this is mostly about indexes but need to be confirmed with the Query Execution Plan.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How is the [StokView] table clustered?

If you (almost) always query but CustomerId, then, if you can, change the clustering on the StokView table to:
( CustomerId, ExpiryDate )