SQL query optimization
- by nvtthang
I have a problem with my SQL query that take time to get all records from database. Any body help me. Below is a sample of database:
order(order_id, order_nm)
customer(customer_id, customer_nm)
orderDetail(orderDetail_id, order_id, orderDate, customer_id, Comment)
I want to get latest customer and order detail information.
Here is may solution:
I've created a function that GetLatestOrderByCustomer(CusID) to get lastest Customer information.
CREATE FUNCTION [dbo].[GetLatestOrderByCustomer]
(
@cus_id int
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @ResultVar varchar(255)
SELECT @ResultVar = tmp.comment
FROM
(
SELECT TOP 1 orderDate, comment
FROM orderDetail
WHERE orderDetail.customer_id = @cust_id
) tmp
-- Return the result of the function
RETURN @ResultVar
END
Below is my SQL query
SELECT
customer.customer_id
, customer.customer_nm
, dbo.GetLatestOrderByCustomer(customer.customer_id)
FROM Customer
LEFT JOIN orderDetail
ON orderDetail.customer_id = customer.customer_id
It's take time to run the function. Could anybody suggest me any solutions to make it better?
Thanks in advance.
