Help me in improving the SQL query,Please

• abendigeri

  SSC-Addicted

  Points: 433

  More actions

  July 7, 2012 at 1:09 pm

  #392689

  Hi all,

  I have a report which takes almost 6-7 hours to display the records.

  We have the proper indexes and even tuning advisory did not advise any index nor stats.

  Any suggestions would really mean alot to me.

  Table initial consists of 600000 records.

  DECLARE @strBE NVARCHAR(4)

  SET @strBE ='1000'

  SELECT TT.TN, TT.PID,

  TT.IT,

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113),

  TT.WH,

  CASE WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source AND X.TN=TT.TN GROUP BY X.TN, X.WH,X.PID,X.Source) THEN

  case when (SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND

  SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113) AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0) is not null

  then (SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0) else 0 end -

  case when (SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0) is not null

  then (SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0) else 0 end

  ELSE

  0

  END

  AS 'OPENING',

  -- ,

  --0,

  --0

  -- (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND CONVERT(NVARCHAR,I.TD,103)=CONVERT(NVARCHAR,TT.TD,103) AND TT.IU=1) AS 'RECIEVED',

  -- (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND CONVERT(NVARCHAR,I.TD,103)=CONVERT(NVARCHAR,TT.TD,103) AND TT.IU=2) AS 'ISSUED',

  CASE WHEN (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113) AND I.TN=TT.TN

  AND I.IU=1)IS NULL THEN 0 ELSE

  (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND I.TN=TT.TN

  AND I.IU=1) END AS 'RECIEVED',

  CASE WHEN (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  -- I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113) AND I.TN=TT.TN

  AND I.IU=2) IS NULL THEN 0

  ELSE

  (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND I.TN=TT.TN

  AND I.IU=2) END AS 'ISSUED',

  (CASE WHEN (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113) AND I.TN = TT.TN

  AND I.IU=1) IS NULL THEN 0 ELSE

  (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND I.TN = TT.TN

  AND I.IU=1) END -

  CASE WHEN (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND I.TN = TT.TN

  AND I.IU=2) IS NULL THEN 0 ELSE

  (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  --I.TD=TT.TD

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)AND I.TN = TT.TN

  AND I.IU=2) END) AS 'CLOSING',

  CASE WHEN TT.LINK>1 AND TT.LINK<5

  THEN

  CASE WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source GROUP BY X.WH,X.PID,X.Source) THEN

  case when (SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND

  SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0) is not null

  then (SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0) else 0 end -

  case when (SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0) is not null

  then (SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)<

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0) else 0 end

  ELSE

  0

  END

  ELSE

  0 END

  AS 'OPENING ELEMENT',

  CASE WHEN TT.LINK>1 AND TT.LINK<5

  THEN

  CASE WHEN (SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1)IS NULL THEN 0 ELSE

  (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1) END

  ELSE

  0 END

  AS 'RECIEVED ELEMENT',

  CASE WHEN TT.LINK>1 AND TT.LINK<5

  THEN

  CASE WHEN (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2) IS NULL THEN 0

  ELSE

  (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2) END

  ELSE

  0 END

  AS 'ISSUED ELEMENT',

  CASE WHEN TT.LINK>1 AND TT.LINK<5

  THEN

  CASE WHEN (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1) IS NULL THEN 0 ELSE

  (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1) END -

  CASE WHEN (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2) IS NULL THEN 0 ELSE

  (SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH AND

  convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)=

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2) END

  ELSE

  0 END

  AS 'CLOSING ELEMENT',

  TT.LINK,

  TT.Source,

  TT.Customer ,

  TT.TransactionType,

  TT.TransactionGroupNumber,

  TT.INVENTORYUPDATE ,

  TT.CURRENCY,

  TT.PRICE

  --TT.DOCUMENTNUMBER

  FROM INITIAL TT

  group BY TT.TN,TT.PID,TT.IT,TT.TDDATE,TT.WH,TT.LINK,TT.Source,TT.Customer, TT.TransactionType,TT.TransactionGroupNumber,TT.INVENTORYUPDATE ,TT.CURRENCY,

  TT.PRICE--,TT.DOCUMENTNUMBER

  Thanks.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 7, 2012 at 1:32 pm

  #1510015

  Hi...a suggestion

  provide the DDL (create table statement) and sample data (insert data) for table 'INITIAL' ...lets say 100 rows

  also the definition of 'View_STK_WITH_ARC'

  and based on the sample data you provide, what you expect your results to be.

  perhaps, someone will then jump in

  kind regards

  ps...do you have an execution plan you can provide?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• abendigeri

  SSC-Addicted

  Points: 433

  More actions

  July 7, 2012 at 2:24 pm

  #1510022

  Thanks for your inputs J Livingston SQL

  Please find the attachments for Execution plan and sample output of INITIAL table.

  Initial Table

  CREATE TABLE [dbo].[INITIAL](

  [TN] [numeric](15, 0) NULL,

  [WH] [nvarchar](4) NULL,

  [TDDATE] [datetime] NULL,

  [IU] [numeric](1, 0) NULL,

  [IQTY] [numeric](20, 5) NULL,

  [IT] [nvarchar](4) NULL,

  [PID] [numeric](15, 0) NULL,

  [numeric](1, 0) NULL,

  [Source] [bit] NULL,

  [Customer] [nvarchar](50) NULL,

  [TransactionType] [nvarchar](50) NULL,

  [TransactionGroupNumber] [nvarchar](15) NULL,

  [INVENTORYUPDATE] [numeric](1, 0) NULL,

  [CURRENCY] [nvarchar](3) NULL,

  [PRICE] [numeric](15, 5) NULL

  ) ON [PRIMARY]

  GO

  Definition for View_STK_WITH_ARC

  SELECT BUSINESSENTITY, TRANSACTIONNUMBER, TRANSACTIONDATE, STOCKTRANSACTIONTEMPLATE, REASONCODE, STOCKAMENDMENTREASON,

  TWINTRANSACTIONNUMBER, TRANSACTIONGROUPNUMBER, OCCUPATIONNUMBER, DOCUMENTNUMBER, DOCUMENTDATE, INVENTORYUPDATE,

  WAREHOUSE, ITEMTYPE, PRODUCTID, QUALITYLEVEL, QUALITYREASON, PRODUCTKEY1, PRODUCTKEY2, PRODUCTKEY3, PRODUCTKEY4,

  PRODUCTKEY5, PRODUCTKEY6, PRODUCTKEY7, PRODUCTKEY8, PRODUCTKEY9, PRODUCTKEY10, NUMBEROFDAYSEXPECTED, EXPECTEDDATE,

  TRANSACTIONSTATUS, STOCKTRANSACTIONERROR, CURRENCY, REGISTEREDUNITPRICE, DEFINITIVEUNITPRICE, LOT, SUPPLIERLOT, CONTAINER,

  CONTAINERITEMTYPE, CONTAINERPRODUCTID, ELEMENT, SUPPLIERELEMENT, LOCATION, PACKAGINGORELEMENTTYPE, WAREHOUSERECORDID,

  WAREHOUSEINVENTORYRECORDID, TRANSACTIONUMQUANTITY, TRANSACTIONUM, INVENTORYUMQUANTITY, INVENTORYUM,

  ADDITIONALUMQUANTITY, ADDITIONALUM, WIDTH, BONUSQUANTITY, BONUSUM, BONUSREASON, NUMBEROFELEMENTS, LENDTO, ORDERCODE,

  ORDERLINE, GRNNO, GRNDATE, COMMENTS, INSPECTIONNUMBER, ZEROBALANCEATTHISTRANSACTION, SAFETYLEVELATTHISTRANSACTION,

  ORDERPOINTATTHISTRANSACTION, CREATIONDATETIME, CREATIONUSER, LASTUPDATEDATETIME, LASTUPDATEUSER, GUID, BUSINESSPARTNER,

  SALEORDERCODE, MADEFORORDER, IMPORTFROMFOREIGNSYSTEM, STOCKVALUE, CONTAINERTYPE, WORKORDERNUMBER,

  WORKORDERLINENUMBER, IMPORT

  FROM dbo.TBL_STOCKTRANSACTION

  UNION

  SELECT BUSINESSENTITY, TRANSACTIONNUMBER, TRANSACTIONDATE, STOCKTRANSACTIONTEMPLATE, REASONCODE, STOCKAMENDMENTREASON,

  TWINTRANSACTIONNUMBER, TRANSACTIONGROUPNUMBER, OCCUPATIONNUMBER, DOCUMENTNUMBER, DOCUMENTDATE, INVENTORYUPDATE,

  WAREHOUSE, ITEMTYPE, PRODUCTID, QUALITYLEVEL, QUALITYREASON, PRODUCTKEY1, PRODUCTKEY2, PRODUCTKEY3, PRODUCTKEY4,

  PRODUCTKEY5, PRODUCTKEY6, PRODUCTKEY7, PRODUCTKEY8, PRODUCTKEY9, PRODUCTKEY10, NUMBEROFDAYSEXPECTED, EXPECTEDDATE,

  TRANSACTIONSTATUS, STOCKTRANSACTIONERROR, CURRENCY, REGISTEREDUNITPRICE, DEFINITIVEUNITPRICE, LOT, SUPPLIERLOT, CONTAINER,

  CONTAINERITEMTYPE, CONTAINERPRODUCTID, ELEMENT, SUPPLIERELEMENT, LOCATION, PACKAGINGORELEMENTTYPE, WAREHOUSERECORDID,

  WAREHOUSEINVENTORYRECORDID, TRANSACTIONUMQUANTITY, TRANSACTIONUM, INVENTORYUMQUANTITY, INVENTORYUM,

  ADDITIONALUMQUANTITY, ADDITIONALUM, WIDTH, BONUSQUANTITY, BONUSUM, BONUSREASON, NUMBEROFELEMENTS, LENDTO, ORDERCODE,

  ORDERLINE, GRNNO, GRNDATE, COMMENTS, INSPECTIONNUMBER, ZEROBALANCEATTHISTRANSACTION, SAFETYLEVELATTHISTRANSACTION,

  ORDERPOINTATTHISTRANSACTION, CREATIONDATETIME, CREATIONUSER, LASTUPDATEDATETIME, LASTUPDATEUSER, GUID, BUSINESSPARTNER,

  SALEORDERCODE, MADEFORORDER, IMPORTFROMFOREIGNSYSTEM, STOCKVALUE, CONTAINERTYPE, WORKORDERNUMBER,

  WORKORDERLINENUMBER, IMPORT

  FROM dbo.TBL_STOCKTRANSACTION_ARCHIVE

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 7, 2012 at 3:07 pm

  #1510028

  I got part way through reformatting this code so you can actually read it and gave up. Here's the part I reformatted.

  DECLARE @strBE NVARCHAR(4)

  SET @strBE ='1000'

  SELECT TT.TN, TT.PID,

  TT.IT,

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113),

  TT.WH,

  CASE WHEN TT.TDDATE=(SELECT MIN(X.TDDATE)

  FROM INITIAL X

  WHERE X.PID=TT.PID

  AND X.WH=TT.WH

  AND X.Source=TT.Source

  AND X.TN=TT.TN

  GROUP BY X.TN, X.WH,X.PID,X.Source)

  THEN

  case when (SELECT SUM(INVENTORYUMQUANTITY)

  FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY = @strBE

  AND SS.PRODUCTID = TT.PID

  AND SS.WAREHOUSE = TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)

  < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER < TT.TN

  AND SS.INVENTORYUPDATE = 1

  AND SS.TRANSACTIONSTATUS=0) IS NOT NULL

  then (SELECT SUM(INVENTORYUMQUANTITY)

  FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)

  < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER < TT.TN

  AND SS.INVENTORYUPDATE = 1

  AND SS.TRANSACTIONSTATUS=0)

  else 0 end

  - --<<<< This is NOT a mistake... there's a MINUS sign here

  case when (SELECT SUM(INVENTORYUMQUANTITY)

  FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY = @strBE

  AND SS.PRODUCTID = TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)

  < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER < TT.TN

  AND SS.INVENTORYUPDATE = 2

  AND SS.TRANSACTIONSTATUS=0) IS NOT NULL

  then (SELECT SUM(INVENTORYUMQUANTITY)

  FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)

  < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 2

  AND SS.TRANSACTIONSTATUS=0)

  else 0 end

  ELSE 0

  END AS 'OPENING',

  CASE WHEN (SELECT SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID

  AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)

  = convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=1)IS NULL

  THEN 0

  ELSE (SELECT SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID

  AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)

  = convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=1)

  END AS 'RECIEVED',

  CASE WHEN (SELECT SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID

  AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)

  = convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=2) IS NULL

  THEN 0

  ELSE (SELECT SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID

  AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)

  = convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=2)

  END AS 'ISSUED',

  My immediate thought is that the code violates just about every major performance best practice that I know of.

  1. Triangular Aggregate Correlated Subqueries (these are real server killers).

  2. Decisions based on Aggregates on a per row basis.

  3. Non-Sargable WHERE clauses.

  4. Multiple calls to the same view which causes the view to be totally re-executed. Considering the code we've seen, I'm actually afraid to see what's in the view. 😛

  5. Use of an outer GROUP BY to eliminate duplicates instead of preventing the dupes to begin with.

  6. Trying to do something complex in an "All-in-one" query instead of using "Divide'n'Conquer" methods.

  My first logical thought was to resolve the aggregates required from the view but then I realized that the Triangular Join Aggregation was there and it was trying to do some sort of running total. A running total in anything less that SQL Server 2012 is either going to need RBAR (pretty slow but a lot faster than the Triangular Joins) or a Quirky Update to build them.

  Since I really know nothing of the data or the requirements driving this piece of code, the only recommendation I can really make is to start over. Just throw this code away and never look back at it. Resolve the aagregations of the view and store them in a temp table. Then, do the same with the INITIAL table and do the same. To get the final output, join those two temp tables which will be substantially smaller than the view or the INITIAL table thanks to the aggregations.

  {EDIT} I see that a couple of things were posted while I was building this response. I'm glad to see that the view is nothing more than an "Archive" view that puts the current data together with the archived data.

  Still, that changes nothing of what I recommended. It only belays my fears about what the view looked like.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 7, 2012 at 3:25 pm

  #1510032

  Can there ever be any rows that are in both TBL_STOCKTRANSACTION and dbo.TBL_STOCKTRANSACTION_ARCHIVE?

  If not, that should be a union all, not union.

  Probbaly won't help much given teh rest, but...

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  July 8, 2012 at 5:12 am

  #1510077

  Jeff's nailed all the reasons for rewriting this query from scratch, and I agree, chuck it away and start again. However, sometimes you can gain from having a baseline in the form of an existing query, however poor, against which to test a new query - providing the old query generates correct results. This one does not.

  Rolling up the shirtsleeves, let's get rid of the CASE statements which mimic the ISNULL function:

  DECLARE @strBE NVARCHAR(4)

  SET @strBE ='1000'

  SELECT

  TT.TN,

  TT.PID,

  TT.IT,

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113),

  TT.WH,

  'OPENING' = CASE

  WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source AND X.TN=TT.TN GROUP BY X.TN, X.WH,X.PID,X.Source) THEN

  ISNULL(SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113) < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0,0)

  - -- minus

  ISNULL(SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113) < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0,0)

  ELSE 0 END,

  'RECIEVED' = ISNULL(SELECT SUM(I.IQTY) FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=1,0),

  'ISSUED' = ISNULL(SELECT SUM(I.IQTY) FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=2,0),

  'CLOSING' = ISNULL(SELECT SUM(I.IQTY) FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN = TT.TN

  AND I.IU=1,0)

  - -- minus

  ISNULL(SELECT SUM(I.IQTY) FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN = TT.TN

  AND I.IU=2,0),

  'OPENING ELEMENT' =

  CASE WHEN TT.LINK>1 AND TT.LINK<5 THEN

  CASE

  WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source GROUP BY X.WH,X.PID,X.Source) THEN

  ISNULL(SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)< convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0,0)

  - -- minus

  ISNULL(SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE AND SS.PRODUCTID=TT.PID AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)< convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0,0)

  ELSE 0 END

  ELSE 0 END,

  'RECIEVED ELEMENT' =

  CASE WHEN TT.LINK>1 AND TT.LINK<5 THEN

  ISNULL(SELECT SUM(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1,0)

  ELSE 0 END,

  'ISSUED ELEMENT' =

  CASE WHEN TT.LINK>1 AND TT.LINK<5 THEN

  ISNULL(SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2,0)

  ELSE 0 END,

  'CLOSING ELEMENT' =

  CASE WHEN TT.LINK>1 AND TT.LINK<5 THEN

  ISNULL(SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1,0)

  - -- minus

  ISNULL(SELECT COUNT(I.IQTY) FROM INITIAL I WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2,0)

  ELSE 0 END,

  TT.LINK,

  TT.Source,

  TT.Customer ,

  TT.TransactionType,

  TT.TransactionGroupNumber,

  TT.INVENTORYUPDATE ,

  TT.CURRENCY,

  TT.PRICE

  --TT.DOCUMENTNUMBER

  FROM INITIAL TT

  GROUP BY

  TT.TN,TT.PID,TT.IT,TT.TDDATE,TT.WH,TT.LINK,

  TT.Source,TT.Customer, TT.TransactionType,

  TT.TransactionGroupNumber,TT.INVENTORYUPDATE ,TT.CURRENCY,

  TT.PRICE--,TT.DOCUMENTNUMBER

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  July 8, 2012 at 5:36 am

  #1510079

  Next, put repeated correlated subqueries into the FROM list as OUTER APPLY. What's left is fairly straightforward except for the remaining correlated subqueries in the output;

  DECLARE @strBE NVARCHAR(4)

  SET @strBE ='1000'

  SELECT

  TT.TN,

  TT.PID,

  TT.IT,

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113),

  TT.WH,

  [OPENING] =

  CASE WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source AND X.TN=TT.TN GROUP BY X.TN, X.WH,X.PID,X.Source) THEN

  ISNULL(SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113) < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0,0)

  - -- minus

  ISNULL(SELECT SUM(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113) < convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.TRANSACTIONNUMBER<TT.TN

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0,0)

  ELSE 0 END,

  RECIEVED = ISNULL(x1.RECIEVED,0),

  ISSUED = ISNULL(x2.ISSUED,0),

  CLOSING = ISNULL(x1.RECIEVED,0) - ISNULL(x2.ISSUED,0),

  [OPENING ELEMENT] =

  CASE WHEN TT.LINK>1 AND TT.LINK<5 THEN

  CASE WHEN TT.TDDATE=(SELECT MIN(X.TDDATE) FROM INITIAL X WHERE X.PID=TT.PID AND X.WH=TT.WH AND X.Source=TT.Source GROUP BY X.WH,X.PID,X.Source) THEN

  ISNULL(SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)< convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 1 AND SS.TRANSACTIONSTATUS=0,0)

  - -- minus

  ISNULL(SELECT COUNT(INVENTORYUMQUANTITY) FROM View_STK_WITH_ARC SS

  WHERE SS.BUSINESSENTITY=@strBE

  AND SS.PRODUCTID=TT.PID

  AND SS.WAREHOUSE=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,SS.TRANSACTIONDATE,113),113)< convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND SS.INVENTORYUPDATE = 2 AND SS.TRANSACTIONSTATUS=0,0)

  ELSE 0 END

  ELSE 0 END,

  [RECIEVED ELEMENT] = ISNULL(y1.[RECIEVED ELEMENT],0),

  [ISSUED ELEMENT] = ISNULL(y2.[ISSUED ELEMENT],0),

  [CLOSING ELEMENT] = ISNULL(y1.[RECIEVED ELEMENT],0) - ISNULL(y2.[ISSUED ELEMENT],0),

  TT.LINK,

  TT.Source,

  TT.Customer ,

  TT.TransactionType,

  TT.TransactionGroupNumber,

  TT.INVENTORYUPDATE ,

  TT.CURRENCY,

  TT.PRICE

  --TT.DOCUMENTNUMBER

  FROM INITIAL TT

  OUTER APPLY (

  SELECT 'RECIEVED' = SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=1,

  ) x1

  OUTER APPLY (

  SELECT 'ISSUED' = SUM(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.TN=TT.TN

  AND I.IU=2,

  ) x2

  OUTER APPLY (

  --SELECT 'RECIEVED ELEMENT' = SUM(I.IQTY) -- incorrect

  SELECT 'RECIEVED ELEMENT' = COUNT(I.IQTY) -- correct

  FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=1

  AND TT.LINK>1 AND TT.LINK<5

  ) y1

  OUTER APPLY (

  SELECT 'ISSUED ELEMENT' = COUNT(I.IQTY)

  FROM INITIAL I

  WHERE I.PID=TT.PID AND I.WH=TT.WH

  AND convert(datetime,CONVERT(NVARCHAR,I.TDDATE,113),113)= convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  AND I.IU=2

  AND TT.LINK>1 AND TT.LINK<5

  ) y2

  GROUP BY

  TT.TN,TT.PID,TT.IT,TT.TDDATE,TT.WH,TT.LINK,

  TT.Source,TT.Customer, TT.TransactionType,

  TT.TransactionGroupNumber,TT.INVENTORYUPDATE ,TT.CURRENCY,

  TT.PRICE--,TT.DOCUMENTNUMBER

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 8, 2012 at 9:59 am

  #1510097

  ...small sidebar

  why

  CONVERT( datetime , CONVERT( nvarchar , TT.TDDATE , 113 ) , 113 )

  TDATE is a datetime...as below

  CREATE TABLE [dbo].[INITIAL](

  [TN] [numeric](15, 0) NULL,

  [WH] [nvarchar](4) NULL,

  [TDDATE] [datetime] NULL,

  for example:

  select CONVERT( datetime , CONVERT( nvarchar , GETDATE() , 113 ) , 113 )

  select GETDATE()

  just curious ??....

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• robert_verell

  Say Hey Kid

  Points: 668

  More actions

  July 8, 2012 at 10:32 am

  #1510102

  I think there could be some better practices surrounding the table definition as well. Why have Numeric(1,0) NULL columns? Can you not have a value that specifies that the column has no value and set it as the default if it is not entered in an insert, and then set the column to NOT NULL? This table is screaming for integrity.

  Rant over on that, but what kind of indexes are setup on this table?

  Initial Table

  CREATE TABLE [dbo].[INITIAL](

  [TN] [numeric](15, 0) NULL,

  [WH] [nvarchar](4) NULL,

  [TDDATE] [datetime] NULL,

  [IU] [numeric](1, 0) NULL,

  [IQTY] [numeric](20, 5) NULL,

  [IT] [nvarchar](4) NULL,

  [PID] [numeric](15, 0) NULL,

  [numeric](1, 0) NULL,

  [Source] [bit] NULL,

  [Customer] [nvarchar](50) NULL,

  [TransactionType] [nvarchar](50) NULL,

  [TransactionGroupNumber] [nvarchar](15) NULL,

  [INVENTORYUPDATE] [numeric](1, 0) NULL,

  [CURRENCY] [nvarchar](3) NULL,

  [PRICE] [numeric](15, 5) NULL

  ) ON [PRIMARY]

  GO

• v s mapari

  Newbie

  Points: 7

  More actions

  July 9, 2012 at 4:14 am

  #1510235

  convert(datetime,CONVERT(NVARCHAR,TT.TDDATE,113),113)

  SS.TRANSACTIONDATE

  Most of the places used that date conversion so did you able to create extra column that have this converted value and use that column directly with using function.

  add a column using current column value as default value by applying convert function.

• CarlaAbanes

  Right there with Babe

  Points: 782

  More actions

  July 9, 2012 at 4:34 am

  #1510241

  Trying to do something complex in an "All-in-one" query instead of using "Divide'n'Conquer" methods.

  yup always do your stored procedures in chunks..its the best rule to go ..

  Cheers! 🙂
  [/url]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 9, 2012 at 5:27 am

  #1510268

  J Livingston SQL (7/8/2012)

  ---

  ...small sidebar

  why

  CONVERT( datetime , CONVERT( nvarchar , TT.TDDATE , 113 ) , 113 )

  TDATE is a datetime...as below

  CREATE TABLE [dbo].[INITIAL](

  [TN] [numeric](15, 0) NULL,

  [WH] [nvarchar](4) NULL,

  [TDDATE] [datetime] NULL,

  for example:

  select CONVERT( datetime , CONVERT( nvarchar , GETDATE() , 113 ) , 113 )

  select GETDATE()

  just curious ??....

  I'd guess it's it's an erroneous attempt at stripping time from the date, in which case it should be

  SELECT convert(datetime,CONVERT(VARCHAR(12),GETDATE(),113),113).

  But you already knew that Mr Seagull.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• abendigeri

  SSC-Addicted

  Points: 433

  More actions

  July 9, 2012 at 5:51 am

  #1510279

  Thanks all for you valuable inputs which helped very much and i really appreciate your time and knowledge.

  Right now we are rewritting the whole SQL and trying to denormalize the DB.

  I will keep you posted when completed.

  Thanks again.

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  July 9, 2012 at 8:34 am

  #1510368

  abendigeri (7/9/2012)

  ---

  Right now we are ...{snip}... trying to [font="Arial Black"]denormalize [/font]the DB.

  Gosh. I certainly hope that ISN'T true. A non-normailzed database will cause you more grief than you can ever imagine. Rather, you should ensure the database is properly normalized and learn more about how to use T-SQL to make your reports much faster and easier to maintain.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Andy Hyslop

  SSCrazy Eights

  Points: 9198

  More actions

  July 9, 2012 at 8:43 am

  #1510372

  Gosh. I certainly hope that ISN'T true. A non-normailzed database will cause you more grief than you can ever imagine. Rather, you should ensure the database is properly normalized and learn more about how to use T-SQL to make your reports much faster and easier to maintain.

  I'd say that depends Jeff, the Kimball Dimensional model for data warehousing is de-normailzed to a degree although there are as always exceptions to the rule and debates around this! 😉

  If this query is hitting a data warehouse then it is quite possible that it could improve things, if its a transactional system the yes that's a bad idea!

  ==========================================================================================================================
  A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe