ryoka012
Starting Member
20 Posts |
Posted - 2013-08-04 : 23:05:50
|
Hi guys,I have a stored procedure that have a union all that generate a report, as later days it seems working fine but after a week it's taking a longer time to generate a my user is complaining about the time it's to generate their report..below is my actual query..my question is how can i optimize my query below..Thanks a bunch.Declare @DateFrom as datetime ,@DateTo as datetime ,@Country as intSet @DateFrom = '2011-03-01 00:00:00'Set @DateTo = '2011-03-31 23:59:59'Set @Country = 65; -- Replace with CountryIDWith Broadcast as (select C.BookingNumber ,B.BLNo ,A.Barcode --,replace(replace(A.Message,' ','. '),' ','. ') [Message] ,dbo.fnRemoveInvalidChar(A.Message,' ') [Message] ,A.CreateBy ,convert(varchar(19),A.CreateDate,21) CreateDate ,(case isnull(B.UpdateMode,'0') when '0' THEN 'Auto' when '1' THEN 'Manual' when '2' then '' END) as Mode ,C.BookingOffice ,C.CID ,C.SourceCity ,C.DestinationCity ,C.LoadPort ,C.DischargePort ,C.MotherSVVD ,C.Shipperfrom broadcastmessage Ainner join bookinglinkinformation B on A.Barcode = B.Barcodeinner join BookingInformation C on B.HID = C.IDWhere (A.CreateDate > @DateFrom) and (A.Barcode <> '') and (C.CID = @Country)Union Allselect B.BookingNumber ,C.BLNo ,C.Barcode --,replace(replace(A.Message,' ','. '),' ','. ') [Message] ,dbo.fnRemoveInvalidChar(A.Message,' ') [Message] ,A.CreateBy ,convert(varchar(19),A.CreateDate,21) CreateDate ,(case isnull(c.UpdateMode,'0') when '0' THEN 'Auto' when '1' THEN 'Manual' when '2' then '' END) as Mode ,B.BookingOffice ,B.CID ,B.SourceCity ,B.DestinationCity ,B.LoadPort ,B.DischargePort ,B.MotherSVVD ,B.Shipperfrom broadcastmessage Ainner join BookingInformation B on A.BookingNo = B.BookingNumberinner join BookingLinkInformation C on B.ID = C.HIDWhere (A.CreateDate > @DateFrom) and (A.Barcode = '') and (A.BookingNo <> '') and (B.CID = @Country))Select A.Barcode as Barcode --,cast(B.BookingNumber as bigint) as BookingNumber ,B.BookingNumber as Bookingnumber --,cast(A.BLNo as bigint) as BLNo ,A.BLNo as BLNo ,convert(varchar(19),A.CreateDate,21) as ReceivedDate ,D.Firstname + ' ' + D.Lastname as Fullname ,convert(varchar(19),C.CreateDate,21) CreateDate ,(case isnull(A.UpdateMode,'0') when '0' THEN 'Auto' when '1' THEN 'Manual' when '2' then '' END) as Mode --,replace(replace(C.Message,' ','. '),' ','. ') [Message] ,dbo.fnRemoveInvalidChar(C.Message,' ') [Message] ,B.BookingOffice ,E.Description as Country ,C.SourceCity ,C.DestinationCity ,C.LoadPort ,C.DischargePort ,C.MotherSVVD ,C.Shipperfrom BookingLinkInformation Ainner join BookingInformation B on A.HID = B.IDinner join Broadcast C on A.Barcode = C.Barcodeinner join Users D on C.CreateBy = D.Usernameinner join Country E on B.CID = E.IDwhere (A.CreateDate Between @DateFrom and @DateTo) and (B.bookingstatus <> 'cancelled' and B.bookingstatus <> 'terminated') and (B.Flag not between '2' and '3') and (B.CID = @Country)order by blno |
|