Click here to Skip to main content
15,887,135 members
Search within:


Home / Discussions / Database
   

Database

 
FirstPrevNext
GeneralRe: view in which each column contains data from a different table Pin
Member 118403639-May-16 4:37
Member 118403639-May-16 4:37 
GeneralRe: view in which each column contains data from a different table Pin
Eddy Vluggen9-May-16 4:52
professionalEddy Vluggen9-May-16 4:52 
GeneralRe: view in which each column contains data from a different table Pin
Mycroft Holmes9-May-16 14:11
professionalMycroft Holmes9-May-16 14:11 
GeneralRe: view in which each column contains data from a different table Pin
CHill608-May-16 13:31
mveCHill608-May-16 13:31 
QuestionQuery Execution Time Management. Pin
Nasir Abbas5-May-16 7:27
Nasir Abbas5-May-16 7:27 
AnswerRe: Query Execution Time Management. Pin
Richard Deeming5-May-16 7:34
mveRichard Deeming5-May-16 7:34 
GeneralRe: Query Execution Time Management. Pin
Nasir Abbas5-May-16 19:41
Nasir Abbas5-May-16 19:41 
GeneralRe: Query Execution Time Management. Pin
Richard Deeming6-May-16 0:53
mveRichard Deeming6-May-16 0:53 
I'd be inclined to make both of those in-line UDFs, rather than multi-statement UDFs:
SQL
DROP FUNCTION f_PendingDCDetails;
GO
CREATE FUNCTION f_PendingDCDetails 
(
    @SID Varchar(32), 
    @iFID varchar(32), 
    @iTID varchar(32), 
    @CID varchar(32), 
    @EnDate varchar(11), 
    @iWR tinyint, 
    @iLoc varchar(8), 
    @iScrVNo varchar(80), 
    @iVtp varchar(20), 
    @iSubType varchar(8), 
    @iFinancialYear varchar(8), 
    @iMnth varChar(8), 
    @ivYear varchar(8), 
    @iLocation varchar(8), 
    @iForLocation varchar(8), 
    @iAorB varchar(1), 
    @iVNo integer, 
    @iSrNo integer, 
    @ForWhichForm varchar(2)
)
Returns Table
As
Return
(
    Select 
        Max(T.QueryNo) QueryNo, 
        Max(T.ScrVoucher_No) ScrVoucher_No, 
        Max(T.Vtp) Vtp, 
        Max(T.SubType) SubType, 
        Max(T.FinancialYear) FinancialYear, 
        Max(T.Mnth) Mnth, 
        Max(T.vYear) vYear, 
        Max(T.Location) Location, 
        Max(T.ForLocation) ForLocation, 
        Max(T.AorB) AorB, 
        Max(T.vno) vno, 
        Max(T.srno) srno, 
        Max(T.dated) dated, 
        Max(T.Item_ID) Item_ID, 
        Sum(T.Delivered) Delivered, 
        Sum(T.Returned) Returned, 
        Sum(T.Invoiced) Invoiced, 
        Sum(T.Rate) Rate, 
        Max(T.Remarks) Remarks, 
        Max(T.Project_ID) Project_ID, 
        Max(T.Client_ID) Client_ID, 
        Max(T.Emp_ID) Emp_ID, 
        Max(T.DeliveryChallanNo) DeliveryChallanNo, 
        Sum(T.GDNDelivered) GDNDelivered, 
        Max(T.EngineNo) EngineNo, 
        Max(T.FrameNo) FrameNo, 
        Max(T.VRegNo) VRegNo, 
        Max(T.ClaimType) ClaimType, 
        Max(T.PrincipalCompany_ID) PrincipalCompany_ID, 
        Sum(T.STPercent) STPercent, 
        Sum(T.EDPercent) EDPercent, 
        Sum(T.OTPercent) OTPercent, 
        Sum(T.DiscP) DiscP, 
        Sum(T.DiscA) DiscA, 
        Sum(T.GrossRate) GrossRate, 
        Sum(T.MainDiscP) MainDiscP, 
        Max(T.Party_ID) Party_ID, 
        Max(T.ClientPONo) ClientPONo, 
        Max(T.ClientPODate) ClientPODate, 
        Max(T.PaymentTerms) PaymentTerms, 
        Max(T.Job_ID) Job_ID, 
        Max(T.CostCenter_ID) CostCenter_ID, 
        Max(T.BatchNo) BatchNo, 
        Max(T.ExpiryDate) ExpiryDate, 
        Max(T.Transporter_ID) Transporter_ID, 
        Sum(T.BQO) BQO, 
        Sum(T.BQD) BQD, 
        Max(T.Gift_ID) Gift_ID, 
        Sum(T.GQO) GQO, 
        Sum(T.GQD) GQD, 
        Max(T.BiltyNo) BiltyNo, 
        Max(T.BiltyDate) BiltyDate, 
        Max(T.OldSoftNo) OldSoftNo 
    From 
        (
            ...
        ) T
    Group By 
        T.VTP, 
        T.SubType, 
        T.FinancialYear, 
        T.Mnth, 
        T.vYear, 
        T.Location, 
        T.ForLocation, 
        T.AorB, 
        T.VNo, 
        T.SrNo, 
        T.Item_ID
    Having 
    (
        @iWR = 4 
    Or 
        (@iWR = 0 And Sum(T.Delivered) > Sum(T.Invoiced - T.Returned))
    Or 
        (@iWR = 1 And Aum(T.Delivered) > Sum(T.GDNDelivered)) 
    Or 
        (@iWR = 2 And Sum(T.Delivered) < Sum(T.Invoiced - T.Returned)) 
    Or 
        (@iWR = 3 And Sum(T.Delivered) < Sum(T.GDNDelivered))
    )
);

SQL
DROP FUNCTION f_PendingDC;
GO
CREATE FUNCTION f_PendingDC 
(
    @SID varchar(32), 
    @iFID varchar(32), 
    @iTID varchar(32), 
    @CID varchar(32), 
    @EnDate varchar(11), 
    @iWR tinyint, 
    @iLoc varchar(8), 
    @iScrVNo varchar(80), 
    @iVtp varchar(20), 
    @iSubType varchar(8), 
    @iFinancialYear varchar(8), 
    @iMnth varchar(8), 
    @ivYear varchar(8), 
    @iLocation varchar(8), 
    @iForLocation varchar(8), 
    @iAorB varchar(1), 
    @iVNo integer, 
    @iSrNo integer, 
    @ForWhichForm varchar(2)
)
Returns Table
As
Return
(
    Select 
        T.ScrVoucher_No ScrVoucher_NoWithSrNo, 
        T.Vtp VTP, 
        T.SubType SubType, 
        T.FinancialYear FinancialYear, 
        T.Mnth Mnth, 
        T.vYear vYear, 
        T.Location Location, 
        T.ForLocation ForLocation, 
        T.AorB AorB, 
        T.VNo VNo, 
        T.srno SrNo, 
        T.dated Dated, 
        T.Client_ID Client_ID, 
        C.Title Client_Title, 
        T.Item_ID Item_ID, 
        IsNull(I.Title, D.Title) Item_Title, 
        T.Delivered Delivered, 
        T.Returned Returned, 
        T.Invoiced Invoiced, 
        I.Unit Unit, 
        T.Rate Rate, 
        Left(T.Remarks, 1000) Remarks, 
        T.Emp_ID Emp_ID, 
        ED.Title Emp_Title, 
        T.DeliveryChallanNo DeliveryChallanNo,
        T.GDNDelivered GDNDelivered, 
        @iWR iWR, 
        T.EngineNo EngineNo,
        T.FrameNo FrameNo, 
        T.DeliveryChallanNo ScrVoucher_No, 
        Case When I.ID Is Null Then 'Services' Else 'Parts' End ServiceOrPart, 
        T.VRegNo VRegNo,
        T.ClaimType ClaimType, 
        CP.ID PrincipalCompany_ID, 
        CP.Title PrincipalCompany_Title,
        T.STPercent STPercent, 
        T.EDPercent EDPercent, 
        T.OTPercent OTPercent, 
        T.DiscP DiscP,
        T.DiscA DiscA, 
        T.GrossRate GrossRate, 
        T.MainDiscP MainDiscP,
        A.ID Party_ID, 
        A.Title Party_Title, 
        T.ClientPONo ClientPONo, 
        T.ClientPODate ClientPODate,
        V.ID1 Main_VTP, 
        T.GrossRate SaleRate, 
        T.PaymentTerms PaymentTerms, 
        CA.Terms PaymentTermsCoa, 
        T.Job_ID Job_ID, 
        DJ.Title Job_Title, 
        T.CostCenter_ID CostCenter_ID, 
        T.Project_ID Project_ID,
        CC.Title CostCenter_Title, 
        DP.Title Project_Title, 
        T.BatchNo BatchNo, 
        T.ExpiryDate ExpiryDate,
        TT.ID Transporter_ID, 
        TT.Title Transporter_Title, 
        Case When MR.VTP Is Null Then I.PackSize Else MR.PackSize End QtyPerCTN,
        T.BQO BQO, 
        T.BQD BQD, 
        T.Gift_ID Gift_ID, 
        G.Title Gift_Title, 
        T.GQO GQO, 
        T.GQD GQD,
        T.BiltyNo BiltyNo, 
        T.BiltyDate BiltyDate, 
        T.OldSoftNo OldSoftNo
    From
        f_PendingDCDetails(@SID, @iFID, @iTID, @CID, @EnDate, @iWR, @iLoc, @iScrVNo, @iVtp, @iSubType, @iFinancialYear, @iMnth, @ivYear, @iLocation, @iForLocation, @iAorB, @iVNo, @iSrNo, @ForWhichForm) T
        Left Join Coa31 I on I.id = T.Item_ID
        Left Join Coa31 G on G.id = T.Gift_ID
        Left Join Coa32 C On T.Client_ID = C.ID
        Left Join EmployeeDetails ED On T.Emp_ID = ED.ID
        Left Join DefJobs D On T.Item_ID = D.ID
        Left Join Coa32 CP On T.PrincipalCompany_ID = CP.ID
        Left Join AnalysisCodes A On T.Party_ID = A.ID
        Left Join Vtp2 V On T.VTP = V.ID2
        Left Join Coa321 CA on CA.ID = IsNull(CP.ID, C.ID)
        Left Join DefJobs DJ On DJ.ID = T.Job_ID
        Left Join CostCenters CC On CC.ID = T.CostCenter_ID
        Left Join DefProjects DP On DP.ID = T.Project_ID
        Left Join Coa32 TT On T.Transporter_ID = TT.ID
        Left Join ManufacturingOrder_Det M On T.BatchNo = M.BatchNo
        Left Join MiscItemRecepie MR On IsNull(M.RefVTP, '') = MR.VTP 
            And IsNull(M.RefSubType, '') = MR.SubType  
            And IsNull(M.RefFinancialYear, '') = MR.FinancialYear
            And IsNull(M.RefMnth, '') = MR.Mnth
            And IsNull(M.RefvYear, '') = MR.vYear
            And IsNull(M.RefLocation, '') = MR.Location
            And IsNull(M.RefForLocation, '') = MR.ForLocation
            And IsNull(M.RefAorB, '') = MR.AorB
            And IsNull(M.RefVNo, '') = MR.VNo
);

If it's still slow, use the Index Tuning Wizard[^] to see if it thinks there are any missing indexes.

Also, bear in mind that SQL 2000 is no longer supported by Microsoft. The "Extended Lifecycle Support" ended in April 2013. If possible, you should consider upgrading to a supported version.

NB: You won't be able to upgrade directly to 2012 or higher, since you can't restore a 2000 database on a 2012 instance. Instead, you'll have to go via 2008 R2: Migration SQL Server 2000 to SQL Server 2012[^]


"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
GeneralRe: Query Execution Time Management. Pin
Nasir Abbas8-May-16 23:32
Nasir Abbas8-May-16 23:32 
Questionaddition having null values in between Pin
Member 118403633-May-16 8:22
Member 118403633-May-16 8:22 
AnswerRe: addition having null values in between Pin
Tomas Takac3-May-16 8:59
Tomas Takac3-May-16 8:59 
GeneralRe: addition having null values in between Pin
Member 118403633-May-16 9:18
Member 118403633-May-16 9:18 
GeneralRe: addition having null values in between Pin
Tomas Takac3-May-16 9:51
Tomas Takac3-May-16 9:51 
GeneralRe: addition having null values in between Pin
Member 118403633-May-16 15:03
Member 118403633-May-16 15:03 
AnswerRe: addition having null values in between Pin
ZurdoDev3-May-16 9:07
professionalZurdoDev3-May-16 9:07 
GeneralRe: addition having null values in between Pin
Member 118403633-May-16 9:32
Member 118403633-May-16 9:32 
AnswerRe: addition having null values in between Pin
ZurdoDev3-May-16 9:34
professionalZurdoDev3-May-16 9:34 
GeneralRe: addition having null values in between Pin
Member 118403633-May-16 9:42
Member 118403633-May-16 9:42 
GeneralRe: addition having null values in between Pin
ZurdoDev3-May-16 9:45
professionalZurdoDev3-May-16 9:45 
QuestionAlternatives to IBM DB2/Oracle? Pin
Member 1249383629-Apr-16 10:36
Member 1249383629-Apr-16 10:36 
AnswerRe: Alternatives to IBM DB2/Oracle? Pin
rick_lively30-Apr-16 10:40
rick_lively30-Apr-16 10:40 
NewsOmniDB - An Open Source Web Tool For Database Management and Conversion Pin
William Ivanski29-Apr-16 5:11
professionalWilliam Ivanski29-Apr-16 5:11 
GeneralRe: OmniDB - An Open Source Web Tool For Database Management and Conversion Pin
Eddy Vluggen29-Apr-16 6:36
professionalEddy Vluggen29-Apr-16 6:36 
GeneralRe: OmniDB - An Open Source Web Tool For Database Management and Conversion Pin
William Ivanski29-Apr-16 6:52
professionalWilliam Ivanski29-Apr-16 6:52 
GeneralRe: OmniDB - An Open Source Web Tool For Database Management and Conversion Pin
Eddy Vluggen29-Apr-16 7:21
professionalEddy Vluggen29-Apr-16 7:21 
Last Visit: 31-Dec-99 18:00     Last Update: 27-Apr-24 7:48Refreshᐊ Prev1...103104105106107108109110111112 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise
Privacy
Cookies
Terms of Use
Last Updated 23 Apr 2024
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1