SQL Server and performance..?

[Magiaus]

Let's say we are joing 12-14 tables. First we simplify that down to 5 table variables. One of the 5 new tables is a table of Unit of Measure Conversions. Some times you have to convert from one UOM to another UOM to get to the correct UOM for display. Of course there are times where you have to convert through several to get to the UOM you need.

The question is is it better to try to do the conversion in the database a.k.a. that server, or on the web server in codebehind. The field is being displayed on a report and each field is set in the codebehind already from a DataReader. Note, I've already by passed the 3-tier object layer because of the grouping I needed to do.

I'm working with two types of contracts, the contracts are for grain/corn/...
The diffrent types of grain on the different types of contracts have to be converted to most likely a paramater based type of measurement conversion.

The reason I just skipped over the object layer is because there are two type of contract objects based on the third base contract class. If I had done an ICompare for the grouping and the sorting;some type of bubble sort. That would work, but then the objects would .Sort(): by commodity type, by contract sub type, by contract type, by year, by month, by 1/2 month sorting. They even say they want that grouping and sorting to happen in my codebehind, not in the stored procedure. It's ok to do it, but sorting in the code is the best practice.

[Magiaus]

ALTER PROCEDURE dbo.proc_ContractsFetchForOpenContractsReports
(@status_id int)
AS
    --Date variables
    DECLARE @Now datetime
    DECLARE @Day int
    DECLARE @Month int
    DECLARE @Year int
    
    SET @Now = CURRENT_TIMESTAMP
    SET @Day = DAY(@Now)
    SET @Month = MONTH(@Now)
    SET @Year = YEAR(@Now)
    
    --DECLARE @status_id int
    --SET @status_id = 9

    --Table variables used to cache sort and join data
    DECLARE @Contracts TABLE
    (
        ID int IDENTITY,
        ContractID int,
        ContractNumber varchar(100),
        ContractType varchar(100),
        ContractSubType varchar(100),
        ContractDate datetime,
        CustomerContractNbr varchar(100),
        ContractQty decimal,
        ContractBasis decimal,
        ContractMarketZone varchar(100),
        ContractOptionMonthCode varchar(100),
        ContractVehicleType varchar(100),
        ContractGoverningGrade varchar(100),
        ContractTypeGroup varchar(100)
    )    
    
    --Get base contract data
    INSERT INTO @Contracts (ContractID, ContractNumber, ContractType, ContractSubType, ContractDate, CustomerContractNbr, ContractQty, ContractBasis, ContractMarketZone, ContractOptionMonthCode, ContractVehicleType, ContractGoverningGrade, ContractTypeGroup)
    SELECT 
        tblContract.ContractID, 
        tblContract.ContractNbr, 
        type = CASE 
            WHEN tblContractType.isExport = 0 THEN 'Domestic'    --false
            WHEN tblContractType.isExport = 1 THEN 'Export'        --true
        END,
        subType = CASE
            WHEN tblContractType.isPurchase = 1 AND tblContractType.isSale = 0 THEN 'Purchase'
            WHEN tblContractType.isPurchase = 0 AND tblContractType.isSale = 1 THEN 'Sale'
            ELSE 'Unknown'
        END,
        tblContract.ContractDate,
        tblContractCustomer.CustomerContractNbr,
        tblContractCustomer.ContractQty,
        tblContract.BasisAmt,
        tblMarketZone.MarketZoneDesc,
        tblOptionMonth.ExchangeMonthCode,
        tblVehicleType.VehicleTypeCode,
        tblGoverningGrade.GoverningGradeDesc,
        typeGroup = CASE
            WHEN tblContractType.isExport = 0 AND tblContractType.isPurchase = 0 AND tblContractType.isSale = 1 THEN 'A'
            WHEN tblContractType.isExport = 1 AND tblContractType.isPurchase = 0 AND tblContractType.isSale = 1 THEN 'B'
            WHEN tblContractType.isExport = 0 AND tblContractType.isPurchase = 1 AND tblContractType.isSale = 0 THEN 'C'
            WHEN tblContractType.isExport = 1 AND tblContractType.isPurchase = 1 AND tblContractType.isSale = 0 THEN 'D'
        END
    FROM     tblContract
    INNER JOIN tblContractType ON tblContractType.ContractTypeID = tblContract.ContractTypeID
    INNER JOIN tblStatus ON tblContract.ContractStatusID = tblStatus.StatusID
    INNER JOIN tblContractCustomer ON  tblContract.ContractID = tblContractCustomer.ContractID
    INNER JOIN tblVehicleType ON tblContract.VehicleTypeID = tblVehicleType.VehicleTypeID
    INNER JOIN tblGoverningGrade ON tblContract.GoverningGradeID = tblGoverningGrade.GoverningGradeID
    LEFT JOIN tblMarketZone ON  tblContract.MarketZoneID = tblMarketZone.MarketZoneID
    LEFT JOIN tblOptionMonth ON tblContract.ContractOptionMonthID = tblOptionMonth.OptionMonthID
    WHERE tblStatus.StatusID = @status_id
    ORDER BY type, subType
    
    DECLARE @Shipping TABLE
    (
        ID int IDENTITY,
        ContractID int,
        StartDate datetime,
        EndDate datetime,
        Period varchar(1000),
        DayNbr int,
        MonthNbr int,
        YearNbr int
    )
    
    --Get Contract shipping information
    INSERT INTO @Shipping (ContractID, StartDate, EndDate, Period,DayNbr, MonthNbr, YearNbr)
    SELECT DISTINCT
        ConTBL.ContractID,
        tblContractShippingPeriod.ShippingPeriodStartDate,
        tblContractShippingPeriod.ShippingPeriodEndDate,
        period = CASE
            WHEN YEAR(tblContractShippingPeriod.ShippingPeriodEndDate) < @Year THEN 'Prior to'
            WHEN MONTH(tblContractShippingPeriod.ShippingPeriodEndDate) < @Month AND YEAR(tblContractShippingPeriod.ShippingPeriodEndDate) <= @Year THEN 'Prior to'
            WHEN DAY(tblContractShippingPeriod.ShippingPeriodEndDate) <= 15 THEN 'First Half of'
            WHEN DAY(tblContractShippingPeriod.ShippingPeriodEndDate) >= 16 THEN 'Last Half of'
            ELSE 'Unknown'
        END,
        DAY(tblContractShippingPeriod.ShippingPeriodEndDate) AS DayNbr,
        MONTH(tblContractShippingPeriod.ShippingPeriodEndDate)AS MonthNbr,
        YEAR(tblContractShippingPeriod.ShippingPeriodEndDate) AS YearNbr
    FROM tblContractShippingPeriod, @Contracts AS ConTBL
    WHERE 
        tblContractShippingPeriod.ContractID = ConTBL.ContractID
    AND 
        tblContractShippingPeriod.ContractShippingPeriodID =   (SELECT     MAX(tblContractShippingPeriod.ContractShippingPeriodID) 
                                    FROM     tblContractShippingPeriod 
                                    WHERE     tblContractShippingPeriod.ContractID = ConTBL.ContractID)
    ORDER BY tblContractShippingPeriod.ShippingPeriodEndDate
    
    DECLARE @GradeCommodity TABLE
    (
        ID int IDENTITY,
        ContractID int,
        GradeCode varchar(50),
        GradeDescription varchar(3000),
        CommodityCode varchar(50),
        CommodityDescription varchar(3000)
    )
    
    INSERT INTO @GradeCommodity (ContractID, GradeCode, GradeDescription, CommodityCode, CommodityDescription)
    SELECT
        ConTBL.ContractID,
        tblGrade.GradeCode,
        tblGrade.GradeDesc,
        tblCommodity.CommodityCode,
        tblCommodity.CommodityDesc
    FROM tblCommodity, tblGrade, @Contracts As ConTBL, tblContract
    WHERE
        ConTBL.ContractID = tblContract.ContractID
    AND
        tblCommodity.CommodityID = tblContract.CommodityID
    AND
        tblGrade.CommodityID = tblCommodity.CommodityID
    AND
        tblGrade.GradeID = tblContract.GradeID
    ORDER BY tblGrade.GradeCode, tblCommodity.CommodityCode
    
    --End table variables init
    
    --OUTPUT QUERY
    SELECT                            --DataReader/Recordset Index
        ConTBL.ContractID,                -- 0
        ConTBL.ContractNumber,                -- 1
        ConTBL.ContractType,                -- 2
        ConTBL.ContractSubType,                -- 3
        ConTBL.ContractDate,                -- 4
        ConTBL.CustomerContractNbr,            -- 5
        ConTBL.ContractQty,                -- 6
        ConTBL.ContractBasis,                -- 7
        ConTBL.ContractGoverningGrade,            -- 8
        ConTBL.ContractMarketZone,            -- 9
        ConTBL.ContractOptionMonthCode,            -- 10
        ConTBL.ContractVehicleType,            -- 11
        ShipTBL.StartDate,                -- 12
        ShipTBL.EndDate,                -- 13
        ShipTBL.Period,                    -- 14
        ShipTBL.DayNbr,                    -- 15                
        ShipTBL.MonthNbr,                -- 16
        ShipTBL.YearNbr,                -- 17
        GC_TBL.GradeCode,                -- 18
        GC_TBL.GradeDescription,            -- 19
        GC_TBL.CommodityCode,                -- 20
        GC_TBL.CommodityDescription,            -- 21
        ConTBL.ContractTypeGroup            -- 22

    FROM @Contracts AS ConTBL, @Shipping AS ShipTBL, @GradeCommodity AS GC_TBL
    WHERE
        ConTBL.ContractID = ShipTBL.ContractID
    AND
        ConTBL.ContractID = GC_TBL.ContractID
    --GROUP BY 
    --    GC_TBL.CommodityCode,                
    --    GC_TBL.CommodityDescription,            
    --    ShipTBL.YearNbr,                    
    --    ConTBL.ContractType,                
    --    ConTBL.ContractSubType,                
    --    ShipTBL.MonthNbr,                    
    --    ShipTBL.Period,                        
    --    ShipTBL.DayNbr,                        
    --    ShipTBL.EndDate,                    
    --    ShipTBL.StartDate,                    
    --    ConTBL.ContractNumber,                
    --    ConTBL.CustomerContractNbr,            
    --    ConTBL.ContractGoverningGrade,        
    --    ConTBL.ContractDate,                
    --    ConTBL.ContractMarketZone,            
    --    GC_TBL.GradeCode,                    
    --    GC_TBL.GradeDescription,            
    --    ConTBL.ContractBasis,                
    --    ConTBL.ContractQty,                    
    --    ConTBL.ContractVehicleType,                
    --    ConTBL.ContractOptionMonthCode,        
    --    ConTBL.ContractID                 
    ORDER BY GC_TBL.CommodityCode, ShipTBL.YearNbr, ConTBL.ContractTypeGroup, ShipTBL.MonthNbr, ShipTBL.Period
    
    RETURN 

[szlamany]

12 to 14 joins - especially to low record count tables - is not a problem.

I'm not at work right now - but I could RDP in and use SQL to check your sproc out...

But I do have some observations.

Have you tried to JOIN with the ON clause:

You have:

FROM tblCommodity, tblGrade, @Contracts As ConTBL, tblContract 
    WHERE 
        ConTBL.ContractID = tblContract.ContractID 
    AND 
        tblCommodity.CommodityID = tblContract.CommodityID 
    AND 
        tblGrade.CommodityID = tblCommodity.CommodityID 
    AND 
        tblGrade.GradeID = tblContract.GradeID

Where that could also be said as:

FROM tblCommodity 
Join tblGrade on tblGrade.CommodityID = tblCommodity.CommodityID
Join @Contracts ConTbl On ConTBL.ContractID = tblContract.ContractID
Join tblContract On ConTBL.ContractID = tblContract.ContractID

Actually after typing that I'm not sure I see the clear JOIN's in this operation.

But at any rate - you should only have one table in the WHERE clause - that table choice should be carefully made.

Each JOIN/ON clause should also be carefully made to minimize table scans/index scans for relationships.

Also, I'm making a big assumption that this is MS SQL Server 2000 - if so, you should be testing this in QUERY ANALYZER and looking at the execution plan to see where the I/O and processing bottlenecks are.

[Magiaus]

I didn't use the JOIN Clause because I'm from the old school and at times it's less trouble to do it the old way unless you need a left or right join to deal with NULL. The shipping JOIN has been changed to do this because it has some NULL crap.

I am using SQL Server. The final SELECT that puts the data out has no need to handle null values since it is joining tables I've filled and none of them will come back with completely any empty records.

I haven't analized the performance as of yet. Mostly because I don't have permissions for it in the db which is pretty weird....

At any rate the question isn't about you likeing my old style of joining that requires less typing. It's do you think the calculation the 3 fields I'm not getting yet in that SQL should be done in the database or on the web server.

I've already heard plenty of whinning about my joins. The fact is they work and they should be at least 99% as fast as using INNER JOIN tbl ON tbl.id = tb2.tblid.....

The report at this time is 200 pages. About 30 records per page so 6000 records and growing.

So should I be looping/ using a recursive function in SQL Server or should I be doing it on the web server?

I haven't even mentioned the applicationj of target factors yet.... that when the grain weight more or less because it is wet or dry or has bugs in it or.....

[szlamany]

By putting a single table in the FROM clause you are intentionally directing SQL to use that table as a start - hopefully the where clause will have index related conditions - making this "primary" table work be as efficient as possible.

But using clear and direct JOIN/ON clauses you are also intentionally directing SQL on how a join to another table was designed by you to work. Also these ON relationships should be in indexed columns - obviously.

I am in no way trying to force you to do it any particular way - that choice is always up to you.

You are questioning whether the "speed" of the server will be sufficient to do business logic there - right? I would say that it should be with properly created queries. UOM tables should have very few rows (I'm guessing) - making the cost of touching them in a query low.

[Magiaus]

Side note. I was talking to one of my co-workers about this and she asked in what ancient time I learned SQL and on what system. I told her it was about 10 years ago from a book that was about 6-7 years old....

[szlamany]

Can't you get the cheap MS SQL Server developers edition and get a local copy of the DB to play with? We have copies of SQL running on laptops and workstations all over my office...

It's always a huge benefit to see the cost associated with execution in query analyzer...

[Magiaus]

Currently 8 rows. And, I am thinking along the same lines as you are. It's my co-workers that are saying it is bad to ORDER BY in SQL, it is bad to GROUP BY in SQL, it is bad to do anything but dump data into the .Net code and then mess with it there.....

I already have the function for the UOM in the works.... in SQL

[szlamany]

We are mainframe programmers here from the 1980's.

4 years ago we started converting our clients to MS SQL server.

We developed a very lightweight client tool that calls STORED PROCEDURES in SQL for everything.

This tool doesn't know if it's maintaining a student registration record or a health claim processing record. It's nearly 100% business logic blind.

With that said we are huge proponents of all business logic in the SPROC - that means GROUP BY, ORDER BY - CASE/WHEN's - everything.

We do fully customized applications for anywhere from 50 to 1000 user systems. It's so nice to change a SPROC and compile it into the DATABASE and have the effect immediately available to clients - without even leaving the application!

We even format all our dates with CONVERT(varchar(10),datetime,101) in the SPROC - so that the VB side can deal with simple strings.

Good luck with you co-worker debates!

[Magiaus]

Can't you get the cheap MS SQL Server developers edition and get a local copy of the DB to play with? We have copies of SQL running on laptops and workstations all over my office...

It's always a huge benefit to see the cost associated with execution in query analyzer...

Well, the deal there is that we push and change the db every Friday and it is considered a waste of time to maintain my local copy. Especially if say I have data I want to keep. Because the push overwrite the good data in test with the !@#$% data from dev..... not my way of doing things it's just how it's being done....


One more thing if you can explain a little better about how putting multi tables in the whewre clause is less efficiant than using Joins you may change my way of thinking. I know all about indexes btw, but currenly this db is a mess the relation may are may not be set same for index....

[szlamany]

One more thing if you can explain a little better about how putting multi tables in the whewre clause is less efficiant than using Joins you may change my way of thinking. I know all about indexes btw, but currenly this db is a mess the relation may are may not be set same for index....

From a quick GOOGLE I can see articles pointing out that if you are fully aware of how the engine is going to handle the possible ambiguities of a FROM with multiple tables then use it - otherwise use the JOIN/ON clauses...

My copy of Inside SQL Server 2000 (by Kalen Delaney) is in my office - very much worth reading if you have time... It has some info on this.

I've helped colleagues with slow query issues in the past and discovered that the FROM/TABLES join method sometimes creates many more records in the working recordset that are collapsed later.

This is all vague and can only really be tried on your own data and tables in QA - seeing that execution plan makes all the difference.

Here's an example of how big our JOINS can get.

FROM Claim_T CL
LEFT JOIN Claim_T CL2 on CL2.ClaimEntry=CL.AssocClaimEntry
LEFT JOIN WelCheck_T WC on WC.CheckNum=CL.CheckNum
LEFT JOIN Master_T MT on MT.MasId=CL.MasId
LEFT JOIN Provider_T PR on PR.ProvId=WC.ProvId
LEFT JOIN Provider_T PR2 on PR2.ProvId=CL.ProvId
LEFT JOIN Master_T MT2 on MT2.MemberSSN=MT.MemberSSN and MT2.Affil=1
LEFT JOIN Addr_T AD on AD.MasId=MT2.MasId and AD.AddrTag = MT2.ResAddrTag
LEFT JOIN BeneType_T BT on BT.BeneType=CL.BeneType
LEFT JOIN ClaimType_T CT on CT.ClaimType=Cl.ClaimType
LEFT JOIN LetterType_T LT1 on LT1.LetterType=CL.LetterType1
LEFT JOIN LetterType_T LT2 on LT2.LetterType=CL.LetterType2
LEFT JOIN LetterType_T LT3 on LT3.LetterType=CL.LetterType3
LEFT JOIN Master_T MT3 on MT3.MasId=IsNull(WC.BenefId,0)
LEFT JOIN Master_T MT4 on MT4.MasId=WC.PayeeID
LEFT JOIN Addr_T AD2 on AD2.MasId=MT2.MasId and AD2.AddrTag = IsNull(MT4.ResAddrTag,0)
WHERE IsNull(PR.SingleChk,'N')<>'Y' AND
CL.AdjDate>=@Adjudication_Date and CL.AdjDate<=@Adjudication_Date and 
...

CLAIM_T table has 3 million rows. ADJDATE is an index. We wanted to allow nothing to come in the way of the fact that we are attaching just one days adjudicated claims (1000 or so).

We always use LEFT JOIN because we have legacy data in our tables that might not always follow full referential integrity rules. It's become a habit because of that fact.

[Magiaus]

I think I may try to find myself a copy of that book. I know the way the data is supposed to fit and my from/where joins are strict enough that they are working the way an INNER JOIN would. Now the fact about the LEFT JOIN is something I will have to think about. This system is going to take over for the legacy system. An AS-400 I think. PACKARD COBOL. HAHAHAHA in fact they all freaked out because I'm 27 and I know COBOL. I asked for the COBOL source for the legacy report because I was tring to figure out the grouping from just a report print out.... and it wasn't what I would call clear cut...

I most likely will update the query to use more modern Joins. This one is just the first draft....

thx for the info