Results 1 to 11 of 11

Thread: Optimize Query.

  1. #1
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19

    Unanswered: Optimize Query.

    Hi All,

    please look into below query.



    SELECT TOP 101 Cert_WarehouseDetails.CertID, Cert_WarehouseDetails.UnderwriterID,
    Cert_WarehouseDetails.WarehouseEntriesID, Cert_WarehouseDetails.DivisionWarehouseLocID,
    Cert_WarehouseDetails.TypeID, Cert_WarehouseDetails.ReportingType, Cert_WarehouseDetails.CertWHID,
    Cert_WarehouseDetails.ClientPolicyDivisionID, Cert_WarehouseDetails.RecordNO, Cert_WarehouseDetails.InsuredValueX,
    Cert_WarehouseDetails.PremiumTotalX, Cert_WarehouseDetails.StatusX, Cert_WarehouseDetails.StatusID,
    Cert_WarehouseDetails.AuthorizeDateX, Cert_WarehouseDetails.CodeX, Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.BillofLadingDateX,
    DOGX, Cert_WarehouseDetails.ConveyanceNameX, Cert_WarehouseDetails.LogonNameX , Cert_WarehouseDetails.ClientNameX,
    (CASE WHEN UnderwriterID = 0 THEN ' ' ELSE tblClientUsers.FirstName + ' ' + tblClientUsers.LastName END) AS OwnedBy,
    (CASE WHEN UnderwriterID = 0 THEN CAST(' ' AS NVARCHAR) ELSE CAST(TakeOwnershipDate as nvarchar) END) AS OwnedDate

    FROM
    ( SELECT tblCertificates.[CertID] AS CertID, tblCertificates.[UnderwriterID] AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, NULL AS WarehouseEntriesID, NULL AS DivisionWarehouseLocID , tblCertificates.CertID AS CertWHID, tblCertificates.ClientPolicyDivisionsID AS ClientPolicyDivisionID, tblCertificates.CertificateNo AS RecordNO, tblCertificates.TotalInsured AS InsuredValueX, (CASE WHEN tblCertificates.Status=101 or tblCertificates.Status=104 or tblCertificates.DivReferral=1 THEN 0 ELSE PremiumTotal-tblCertificates.Discount END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblCertificates.Status < '105' and tblCertificates.Status <> '103' THEN null ELSE AuthorizeDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblCertificates.IssuedDate AS IssuedDateX, tblCertificates.BillofLadingDate AS BillofLadingDateX, tblCertificates.DescriptionofGoods AS DOGX, tblCertificates.ConveyanceName AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblCertificates.TakeOwnershipDate AS TakeOwnershipDate, tblCertificates.ClientID AS ClientID, tblCertificates.Producer AS BrokerID, tblCertificates.SBU AS SBU, tblCertificates.AssociationID AS AssociationID, tblCertificates.AssuredName AS AssuredName, tblCertificates.UserID AS UserID, tblCertificates.Demoflag AS Demoflag FROM tblCertificates, tblReportingType,tblcurrency,tblClientUsers,tblCli ent,tblStatus WHERE tblCertificates.reportType = tblReportingType.TypeID AND tblCertificates.PremiumCurrencyType = tblCurrency.ID AND tblclientUsers.UserID = tblCertificates.UserID AND tblcertificates.ClientId = tblclient.ClientID AND tblStatus.StatusID = tblCertificates.Status
    UNION
    SELECT NULL AS CertID, NULL AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, tblWarehouseEntries.[WarehouseEntriesID] AS WarehouseEntriesID, tblWarehouseEntries.[DivisionWarehouseLocID] AS DivisionWarehouseLocID , tblWarehouseEntries.WarehouseEntriesID AS CertWHID, tblWarehouseEntries.ClientPolicyDivisionID AS ClientPolicyDivisionID, tblWarehouseEntries.WarehouseEntriesID AS RecordNO, (CASE WHEN ValueCurrencyType=0 THEN 0 ELSE UnitValue END) AS InsuredValueX, (CASE WHEN tblWarehouseEntries.StatusID=101 THEN 0 ELSE PremiumSum END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblWarehouseEntries.StatusID < '105' THEN null ELSE ApprovalDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblWarehouseEntries.IssuedDate AS IssuedDateX, tblWarehouseEntries.PeriodEndDate AS BillofLadingDateX, LocName AS DOGX, '' AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblWarehouseEntries.TakeOwnershipDate AS TakeOwnershipDate, tblWarehouseEntries.ClientID AS ClientID, tblWarehouseEntries.BrokerID AS BrokerID, tblWarehouseEntries.SBU AS SBU, tblWarehouseEntries.AssociationID AS AssociationID, tblWarehouseEntries.AssuredName AS AssuredName, tblWarehouseEntries.UserID AS UserID, tblWarehouseEntries.Demoflag AS Demoflag FROM tblWarehouseLoc, tblWarehouseEntries, tblReportingType,tblCurrency ,tblClientUsers,tblClient, tblDivisionWarehouseLoc,tblStatus WHERE tblWarehouseEntries.reportTypeID = tblReportingType.TypeID and tblWarehouseLoc.WarehouseLocationID = tblDivisionWarehouseLoc.WarehouseLocID and tblDivisionWarehouseLoc.DivisionWarehouseLocID = tblWarehouseEntries.DivisionWarehouseLocID and tblWarehouseEntries.PremiumCurrencyType = tblCurrency.ID and tblWarehouseEntries.UserID = tblClientUsers.UserID and tblClient.ClientID = tblWarehouseEntries.ClientID AND tblStatus.StatusID = tblWarehouseEntries.StatusID

    ) AS Cert_WarehouseDetails LEFT JOIN tblClientUsers ON Cert_WarehouseDetails.UnderwriterID = tblClientUsers.UserID

    WHERE 1 = 1 AND Cert_WarehouseDetails.Demoflag = 0 and
    (convert(datetime,convert(nvarchar,Cert_WarehouseD etails.IssuedDateX,101)) >= '1/1/2003') and
    (Cert_WarehouseDetails.IssuedDateX is not null and
    convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')
    ORDER BY Cert_WarehouseDetails.RecordNO



    In above query,as
    1. Union (INNER) query returns me 200000 records.

    2. If I run above query it takes 18 sec because no index on Cert_WarehouseDetails.RecordNO and i'm ordering on that.

    3. When I avoid to 'ORDER BY Cert_WarehouseDetails.RecordNO' then query takes 2 sec.

    4. In this case Do I have to use 'nvarchar'
    convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')

    5. Why do we have to use 'nvarchar' can you explain to me in above statement..


    Can you provide me alternate solution so I can make it fast.

    Please reply to me asap. Thanks in advance.

    Regards,
    M. Jain
    M. Jain

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

    Also, to avoid conversion, assuming that time portion also exists in this field, just use:

    ...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.

  3. #3
    Join Date
    Apr 2004
    Location
    OH
    Posts
    4
    Originally posted by rdjabarov
    Try to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

    Also, to avoid conversion, assuming that time portion also exists in this field, just use:

    ...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.
    What data type is Cert_WarehouseDetails.IssuedDateX?

    Why are you converting it from a datetime to a nvarchar then back to a datetime?

  4. #4
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19

    Query Optimization

    Originally posted by rdjabarov
    Try to create a clustered index on Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.RecordNO.

    Also, to avoid conversion, assuming that time portion also exists in this field, just use:

    ...Cert_WarehouseDetails.IssuedDateX >= '1/1/2003' and Cert_WarehouseDetails.IssuedDateX < '2/1/2003' and Cert_WarehouseDetails.IssuedDateX is not null.
    How do I create Index & where because 'Cert_WarehouseDetails' in sot a table.
    M. Jain

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What is it? A view/function?

  6. #6
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19
    Originally posted by o_morehart
    What data type is Cert_WarehouseDetails.IssuedDateX?

    Why are you converting it from a datetime to a nvarchar then back to a datetime?
    1. Datetime Datatype.
    2. Because I want to compare date with the style of 'mm/dd/yyyy' format.
    M. Jain

  7. #7
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19
    Originally posted by rdjabarov
    What is it? A view/function?
    In view/ function I can't use order by.
    I can't create index if query has Union.
    M. Jain

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by gan_us2003
    1. Datetime Datatype.
    2. Because I want to compare date with the style of 'mm/dd/yyyy' format.
    1. When you compare a datetime field to 'mm/dd/yyyy' the constant gets implicitly converted to datetime data type with the value yyyy-mm-dd 00:00:00.000
    2. You do not need to compare "with the style." Style will come later

  9. #9
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19
    Originally posted by rdjabarov
    1. When you compare a datetime field to 'mm/dd/yyyy' the constant gets implicitly converted to datetime data type with the value yyyy-mm-dd 00:00:00.000
    2. You do not need to compare "with the style." Style will come later
    Thanks .
    Do you have any Idea where I can create index on RecordNo.
    M. Jain

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...On the table where it's coming from...

  11. #11
    Join Date
    Nov 2003
    Location
    NJ USA
    Posts
    19
    Originally posted by rdjabarov
    ...On the table where it's coming from...
    please Can you look at above query.
    That is inherited table on that I can't create index.
    is anyone know about that.
    M. Jain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •