programmerist 1983
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?
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]
"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
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:
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 )
If you (almost) always query but CustomerId, then, if you can, change the clustering on the StokView table to:
( CustomerId, ExpiryDate )