SQL Server Performance Forum – Threads Archive
How would you handle this?I have a very amatuer question to ask the experts here…I have 6 tables in my database
ord_OrderEntry, ord_LineItem, ord_IGUnit, ord_Glass, ord_Spacer, ord_Muntins ord_OrderEntry contains master record information about an order. ord_OrderNum is the field for our order Number the rest of the tables have detail information about the line items for our order.
TableName FieldName1 FieldName2
============ ============ ============
ord_LineItem ord_OrderNum ord_LineNum – 1 detail record, will always have a record
ord_IGUnit ord_OrderNum ord_LineNum – 1 detail record, will always have a record
ord_Glass OrderNumber LineNumber – up to 3 detail records, minimum of 1 record
ord_Spacer spcr_OrderNum spcr_LineNum – 1 detail record could have 0 records for order
ord_Muntins ord_OrderNum ord_LineNum – 1 detail record could have 0 records for order Presently we do not have any table relationships or indexes built. This might sound like a loaded question but how would you define these relationships if you were to work on this database? Do you think that would improve performance? I am not a DBA, I’m a VB developer first and just learning the ropes of SQL Server 2K. Most of my queries lie within source code with some stored procedures setup for the large data. Here is an example of a SQL statement we have in code: SELECT OE.ord_DueDate, OE.Cust_Num, IG.ord_IGOverall, IG.ord_InvItem AS GasInvID, IG.ord_PercentArgon, IG.ord_PercentKrypton, IG.ord_GasFillTime, LI.ord_OrderNum, LI.ord_LineNum, LI.ord_Quantity, LI.ord_SashId, LI.BeginCart, LI.BeginSlot, LI.EndCart, LI.EndSlot, LI.ord_UnitDesc, LI.ord_Custpo, LI.ord_CustData, LI.ord_SqFt, LI.ShipBeginCart, LI.ShipBeginSlot, LI.ShipEndCart, LI.ShipEndSlot, LI.ord_Comment1,
LI.ord_Comment2, LI.ord_Comment3, LI.CustRefNum, LI.ord_PrintOneLabel, LI.ord_LabelPrint, GL.Width, GL.Height, GL.IGType, GL.ShapeLibID, GL.LitePosition, GL.OrderOut AS GlassOrderOut, GL.ApplyShape, GL.UseShapeLib, GL.UseShapeFile, GL.ShapeFile, GL.ShapeCode, GL.ShapeBase, GL.ShapeLeft, GL.ShapeRight, GL.ShapeTop,
GL.ShapeS1, GL.ShapeS2, GL.CustSuppliedGlass, GL.StockSize, SP.spcr_InvItem, SP.spcr_Length, SP.spcr_Overall, SP.SpacerID, MN.ord_MuntinConfig, MN.ord_MuntinType,
MN.ord_Horizontal, MN.ord_Vertical, MN.ord_WidthOffset, MN.ord_HeightOffset, MN.ord_DiamondPattern, MN.ord_OrderedOut AS MuntinOrderOut, MN.ord_BlindsType, MN.ord_BlindsColor, MN.ord_VGPattern, MN.ord_VGFile, MN.ord_VGGlassSurfaceNum, MN.ord_InternalsType, MN.ord_CustomMuntin, MN.ord_CustSupplied AS MuntinCustSupplied, CST.Cust_Name, CST.Cust_Route, CST.Cust_DontPrintLabels, CST.Cust_PrintOneLabel, GD.Dimension, GD.NominalValue, GPC.GlassType, GPC.ColorProcess, GPC.BossCode, SpacerMat.SpacerType, SpacerMat.SpacerColor, SpacerMat.SpacerDesc, MuntinMat.MuntinMtlType, MuntinMat.MuntinMtlStyle, MuntinMat.MuntinDesc, MuntinC.MuntinMtlColor, MuntinC.Description, MuntinC.Flat5625, MuntinC.Flat610,
MuntinC.Flat8125, MuntinC.CT709, MuntinC.CT711, MuntinC.CT980, LI.BatchName, SP.spcr_Thickness, MN.ord_Size AS MuntinSize, MuntinC.SB875, GPC.BossBridgeCode, GL.ShapeFlipBit
FROM dbo.tblMtlMuntinColor MuntinC FULL OUTER JOIN dbo.tblMtlGlassProcessColor GPC FULL OUTER JOIN dbo.tblMtlGlassDimension GD FULL OUTER JOIN dbo.ord_Glass GL FULL OUTER JOIN
dbo.ord_LineItem LI FULL OUTER JOIN dbo.tblCustomer CST FULL OUTER JOIN dbo.ord_OrderEntry OE ON CST.Cust_Num = OE.Cust_Num FULL OUTER JOIN dbo.ord_IGUnit IG ON OE.ord_OrderNum = IG.ord_OrderNum ON LI.ord_OrderNum = IG.ord_OrderNum AND LI.ord_LineNum = IG.ord_LineNum ON GL.OrderNumber = LI.ord_OrderNum AND GL.LineNumber = LI.ord_LineNum ON GD.ID = GL.GlassDimensionID ON GPC.ID = GL.GlassColorID FULL OUTER JOIN dbo.ord_Spacer SP ON LI.ord_OrderNum = SP.spcr_OrderNum AND LI.ord_LineNum = SP.spcr_LineNum FULL OUTER JOIN dbo.ord_Muntins MN ON LI.ord_LineNum = MN.ord_LineNum AND LI.ord_OrderNum = MN.ord_OrderNum FULL OUTER JOIN dbo.tblMtlMuntin MuntinMat ON MN.MuntinMatID = MuntinMat.ID ON MuntinC.ID = MN.MuntinColorID FULL OUTER JOIN dbo.tblMtlSpacer SpacerMat ON SP.SpacerID = SpacerMat.ID
WHERE (LI.BatchName = ‘" & strbatchName & "’)
ORDER BY OE.Cust_Num, LI.ord_OrderNum, LI.ord_LineNum, GL.LitePosition Would setting up relationships help fix that mess? Let me know if i need to give you more information.
I think it is very difficult to build relations without knowing the actual need of software. I would suggest you to read book online; or refer sample databases Northwind & Pubs in SQL Server. See the design view of each table in Northwind database, which is similar to your requirement.
I think you should have indexes and joins for the better performance You can run the Index tunning wizard with this query and get some tips on the nessesary indexes.
Dear Mr. Mike,
i am very much agreed with Mr. Surendra kalekar, its very true that without knowing the actual need , but it is guranteed that Relation Ship / data integrity and Constraints like (Primary key,foreign key,unique…) leed your db / query performance to faster.
or please give the more description. thanx. hsGoswami