July 4, 2012 at 4:02 am
This particular qeury is taking a long time to respond there are 26 indexes in the table
SELECT CASE
WHEN TPJRCSHDRW1.CASH_DRAWER_ID = 504 THEN 1
WHEN TPTRJO1.OTHER_CASH_DRAWER_ID = 504 THEN 2
END TRADE_TYPE,
TPTR1.TRAN_NAME,
TPCADR1.CASH_DRAWER_NAME,
TPCADR1.CASH_DRAWER_NAME OTHER_CASH_DRAWER_NAME,
TPTRJO1.TRANSACTION_ID,
TPDECO1.DENOMINATION_COUNT_ID,
TPDECO1.DENOMINATION_CATEGORY,
TPDECO1.DENOMINATION_VALUE,
TPDECO1.DENOMINATION_COUNT,
TPDECO1.DENOMINATION_TYPE,
TPDECO1.DENOMINATION_NAME,
TPTRJO1.TRANSACTION_ID,
TPTRJO1.TOTAL_VALUE,
TPTRJO1.TOTAL_VALUE_CCODE,
TPTRJO1.JOURNAL_ENTRY_ID,
TPJRCSHDRW1.CASH_DRAWER_ID,
TPTRJO1.OTHER_CASH_DRAWER_ID
FROM TRANSACTION_JOURNAL TPTRJO1
INNER JOIN JOURNAL_CASH_DRAWER TPJRCSHDRW1 ON TPTRJO1.JOURNAL_ENTRY_ID = TPJRCSHDRW1.JOURNAL_ENTRY_ID
INNER JOIN TRANSACTIONS TPTR1 ON TPTRJO1.TRANSACTION_ID = TPTR1.TRANSACTION_ID
INNER JOIN CASH_DRAWER TPCADR1 ON TPCADR1.CASH_DRAWER_ID = TPJRCSHDRW1.CASH_DRAWER_ID
INNER JOIN CASH_DRAWER TPCADR2 ON TPCADR2.CASH_DRAWER_ID = TPTRJO1.OTHER_CASH_DRAWER_ID
LEFT OUTER JOIN DENOMINATION_COUNT TPDECO1 ON TPDECO1.JOURNAL_ENTRY_ID = TPJRCSHDRW1.JOURNAL_ENTRY_ID
WHERE ( ( TPJRCSHDRW1.CASH_DRAWER_ID = 504 )
OR ( TPTRJO1.OTHER_CASH_DRAWER_ID = 504 ) )
AND ( TPTRJO1.TRANSACTION_ID IN ( 13027, 13026, 1362, 605, 604, 590, 589, 563, 562 ) )
AND ( TPTRJO1.STATUS NOT IN ( 'V', 'I', 'R' ) )
AND ( ( TPTRJO1.REVERSED_BY )IS NULL )
AND ( TPJRCSHDRW1.CASH_DRAWER_DATE >= '2012 - 06 - 28' )
AND ( TPTRJO1.TOTAL_VALUE <> 0 )
AND ( ( TPTRJO1.OFFSET_ID )IS NULL)
Here is the execution plan for the query
|--Compute Scalar(DEFINE:([Expr1018]=CASE WHEN [tlrgn].[tp].[JOURNAL_CASH_DRAWER].[CASH_DRAWER_ID] as [TPJRCSHDRW1].[CASH_DRAWER_ID]=(504.) THEN (1) ELSE CASE WHEN [tlrgn].[tp].[TRANSACTION_JOURNAL].[OTHER_CASH_DRAWER_ID] as [TPTRJO1].[OTHER_CASH_DRAWER_ID]=(504.) THEN (2) ELSE NULL END END))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1017] IS NULL), OUTER REFERENCES:([Bmk1015]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([TPJRCSHDRW1].[JOURNAL_ENTRY_ID]) OPTIMIZED)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1009]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([TPJRCSHDRW1].[CASH_DRAWER_ID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TPTRJO1].[JOURNAL_ENTRY_ID], [TPTRJO1].[OTHER_CASH_DRAWER_ID], [Expr1026]) OPTIMIZED WITH UNORDERED PREFETCH)
| | | | |--Merge Join(Inner Join, MERGE:([TPTR1].[TRANSACTION_ID])=([TPTRJO1].[TRANSACTION_ID]), RESIDUAL:([tlrgn].[tp].[TRANSACTION_JOURNAL].[TRANSACTION_ID] as [TPTRJO1].[TRANSACTION_ID]=[tlrgn].[tp].[TRANSACTIONS].[TRANSACTION_ID] as [TPTR1].[TRANSACTION_ID]))
| | | | | |--Sort(ORDER BY:([TPTR1].[TRANSACTION_ID] ASC))
| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([TPTR1].[TRANSACTION_ID]))
| | | | | | |--Table Scan(OBJECT:([tlrgn].[tp].[TRANSACTIONS] AS [TPTR1]), WHERE:([tlrgn].[tp].[TRANSACTIONS].[TRANSACTION_ID] as [TPTR1].[TRANSACTION_ID]>=(562.) AND [tlrgn].[tp].[TRANSACTIONS].[TRANSACTION_ID] as [TPTR1].[TRANSACTION_ID]<=(13027.) AND PROBE([Bitmap1025],[tlrgn].[tp].[TRANSACTIONS].[TRANSACTION_ID] as [TPTR1].[TRANSACTION_ID])))
| | | | | |--Sort(ORDER BY:([TPTRJO1].[TRANSACTION_ID] ASC))
| | | | | |--Bitmap(HASH:([TPTRJO1].[TRANSACTION_ID]), DEFINE:([Bitmap1025]))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([TPTRJO1].[TRANSACTION_ID]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [TPTRJO1].[JOURNAL_ENTRY_ID], [Expr1024]) OPTIMIZED WITH UNORDERED PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([TPCADR2].[CASH_DRAWER_ID], [Expr1023]) WITH UNORDERED PREFETCH)
| | | | | | |--Index Scan(OBJECT:([tlrgn].[tp].[CASH_DRAWER].[PKC_CASH_DRAWER] AS [TPCADR2]))
| | | | | | |--Index Seek(OBJECT:([tlrgn].[tp].[TRANSACTION_JOURNAL].[I26_TRANSACTION_JOURNAL] AS [TPTRJO1]), SEEK:([TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(562.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(563.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(589.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(590.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(604.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(605.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(1362.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(13026.) OR [TPTRJO1].[OTHER_CASH_DRAWER_ID]=[tlrgn].[tp].[CASH_DRAWER].[CASH_DRAWER_ID] as [TPCADR2].[CASH_DRAWER_ID] AND [TPTRJO1].[TRANSACTION_ID]=(13027.)), WHERE:([tlrgn].[tp].[TRANSACTION_JOURNAL].[STATUS] as [TPTRJO1].[STATUS]<>N'I' AND [tlrgn].[tp].[TRANSACTION_JOURNAL].[STATUS] as [TPTRJO1].[STATUS]<>N'R' AND [tlrgn].[tp].[TRANSACTION_JOURNAL].[STATUS] as [TPTRJO1].[STATUS]<>N'V') ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([tlrgn].[tp].[TRANSACTION_JOURNAL].[I100_TRANSACTION_JOURNAL] AS [TPTRJO1]), SEEK:([TPTRJO1].[JOURNAL_ENTRY_ID]=[tlrgn].[tp].[TRANSACTION_JOURNAL].[JOURNAL_ENTRY_ID] as [TPTRJO1].[JOURNAL_ENTRY_ID] AND [Uniq1002]=[Uniq1002]), WHERE:([tlrgn].[tp].[TRANSACTION_JOURNAL].[REVERSED_BY] as [TPTRJO1].[REVERSED_BY] IS NULL AND [tlrgn].[tp].[TRANSACTION_JOURNAL].[OFFSET_ID] as [TPTRJO1].[OFFSET_ID] IS NULL AND [tlrgn].[tp].[TRANSACTION_JOURNAL].[TOTAL_VALUE] as [TPTRJO1].[TOTAL_VALUE]<>(0.000000)) LOOKUP ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([tlrgn].[tp].[JOURNAL_CASH_DRAWER].[PKC_JOURNAL_CASH_D] AS [TPJRCSHDRW1]), SEEK:([TPJRCSHDRW1].[JOURNAL_ENTRY_ID]=[tlrgn].[tp].[TRANSACTION_JOURNAL].[JOURNAL_ENTRY_ID] as [TPTRJO1].[JOURNAL_ENTRY_ID]), WHERE:([tlrgn].[tp].[JOURNAL_CASH_DRAWER].[CASH_DRAWER_DATE] as [TPJRCSHDRW1].[CASH_DRAWER_DATE]>='2012-06-28 00:00:00.000' AND ([tlrgn].[tp].[JOURNAL_CASH_DRAWER].[CASH_DRAWER_ID] as [TPJRCSHDRW1].[CASH_DRAWER_ID]=(504.) OR [tlrgn].[tp].[TRANSACTION_JOURNAL].[OTHER_CASH_DRAWER_ID] as [TPTRJO1].[OTHER_CASH_DRAWER_ID]=(504.))) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([tlrgn].[tp].[CASH_DRAWER].[PKC_CASH_DRAWER] AS [TPCADR1]), SEEK:([TPCADR1].[CASH_DRAWER_ID]=[tlrgn].[tp].[JOURNAL_CASH_DRAWER].[CASH_DRAWER_ID] as [TPJRCSHDRW1].[CASH_DRAWER_ID]) ORDERED FORWARD)
| | |--RID Lookup(OBJECT:([tlrgn].[tp].[CASH_DRAWER] AS [TPCADR1]), SEEK:([Bmk1009]=[Bmk1009]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([tlrgn].[tp].[DENOMINATION_COUNT].[PKC_DENOMINATION_COUNT] AS [TPDECO1]), SEEK:([TPDECO1].[JOURNAL_ENTRY_ID]=[tlrgn].[tp].[JOURNAL_CASH_DRAWER].[JOURNAL_ENTRY_ID] as [TPJRCSHDRW1].[JOURNAL_ENTRY_ID]) ORDERED FORWARD)
|--RID Lookup(OBJECT:([tlrgn].[tp].[DENOMINATION_COUNT] AS [TPDECO1]), SEEK:([Bmk1015]=[Bmk1015]) LOOKUP ORDERED FORWARD)
Can any one suggest what is are to be improved?
July 4, 2012 at 4:06 am
Can you please post the ddl of tables and the graphical sqlplan?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 4:08 am
To add on to Gullimeel, please follow the link in my signature on how to post performance problems.
July 4, 2012 at 4:09 am
Anthony,
I need to add one of these link in my signature.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 4:21 am
I have attached the graphical execution plan
Here is the ddl
create table TP.TRANSACTION_JOURNAL (
JOURNAL_ENTRY_ID numeric(31) not null,
PARENT_ENTRY numeric(31) null,
CASH_DRAWER_ID numeric(31) null,
CORRECTION_ENTRY_ID numeric(31) null,
OTHER_CASH_DRAWER_ID numeric(31) null,
BANK_ID numeric(10) null,
OTHER_LOCATION_CODE_ID numeric(10) null,
TRANSACTION_ID numeric(31) not null,
LOCATION_CODE_ID numeric(10) null,
CASH_DRAWER_DATE datetime null,
REGION_ID numeric(10) null,
USER_ID numeric(10) not null,
CASH_BOX_DATE datetime not null,
COMPLETED datetime null,
STATUS nvarchar(2) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_VALUE numeric(31,6) null,
TRAN_START datetime null,
TRAN_END datetime null,
SESSION_ID nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
DRAWER_SEQ numeric(10) null,
SOURCE_ERROR numeric(31) null,
SOURCE_MESSAGE nvarchar(254) collate SQL_Latin1_General_CP1_CS_AS null,
BATCH_NBR nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
CASH_IN_AMT numeric(31,6) null,
CASH_OUT_AMT numeric(31,6) null,
REVERSED_DATE datetime null,
IS_FORWARD nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
TRAN_STAT nvarchar(2) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_TYPE nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
MULTI_SESSION_NBR nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
LOCKED numeric(10) null,
IS_MAINTENANCE nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
MISC_FLAG nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
SYSTEM_DATE datetime null,
WORKSTATION_ID numeric(10) null,
ACCT_NBR_FROM nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_NBR_TO nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
COMMENT_TEXT nvarchar(512) collate SQL_Latin1_General_CP1_CS_AS null,
HAS_CONSIGNMENT nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
IS_AUDIT nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
DESCRIPTION nvarchar(512) collate SQL_Latin1_General_CP1_CS_AS null,
ADDR_CITY nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
ADDR_LINE_1 nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ADDR_PHONE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ADDR_POSTAL_CODE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ADDR_STATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ADJ_FEE_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
AMOUNT_DIFF nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ANAL_INDICATOR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
APPROVED_BY_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
APPROVED_BY_TITLE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
AUDIT_USER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
AUTH_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
BAIT_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
BOND_INT_EARNED nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
BOND_INT_TOT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CONSIGN_CALC_FEE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CONSIGN_PAY_TO nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CONSIGN_REMITTER nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CONTACT_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
COST_CENTER nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
COUNTRY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
COWNR_BENF_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
COWNR_BENF_OPTION nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CURRENCY_RATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CUSTOMER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CUSTOMER_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CUSTOMER_TIME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVER_ADDR_CITY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVER_ADDR_LINE_1 nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVER_ADDR_STATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVER_ADDR_ZIP nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DELIVERY_METHOD nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
DRAWN_ON nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ENDING_CASH nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
EQUIVALENCY_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
EXPIRE_DATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
FED_REG_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
FEE_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
FEE_DESC nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
FOOD_COUPON_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
FOREIGN_COUNTRY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
GL_ACCOUNT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
GL_ACCT_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
GL_ACTION nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
GL_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
GL_DEBIT_ACCT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
HLD_EXPIR_DAYS nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ID_ISSUED_BY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ID_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ID_OTHER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ID_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
INDICATOR_RECEIVED nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
INDICATOR_WAIVE_FEE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
INFORMATION nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
INSTRUMENT_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
IS_CUSTOMER nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_ADDRESS nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_CITY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_POSTAL_CODE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_STATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_TIN nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
MICR_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
MISC_TOT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
MUTILATED_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NAME_FIRST nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NAME_LAST nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NAME_MIDDLE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NATIONALITY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NBR_ITEMS nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NET_ADJUSTMENT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NOT_ON_US_CHECK_ACTION nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NOTE_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
NOTE_TEXT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ORDER_DATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ADDR_CITY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ADDR_LINE_1 nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ADDR_POSTAL_CODE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ADDR_STATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_BIRTH_DATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_CASH_DRAWER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_COUNTRY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ID_ISSUED_BY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ID_NBR nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ID_OTHER_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_ID_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_NOTE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_TIN nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OVERRIDE_PASSWORD nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
OVERRIDE_USER_ID nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PAY_TO nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PAYMENT_METHOD nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PAYMENT_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PICK_UP_DATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PREPARED_BY_NAME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
PRINT_DATE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
REASON nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
REMITTER nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
REPORT_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
SEQUENCE_NBR_FROM numeric(10) null,
SYS_BEG_CASH nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
SYS_END_CASH nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TIN nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CHECK_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CASH_IN_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CASH_OUT_AMT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_VALUE_FROM nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TP_DIFFERENCE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TRANS_TIME nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TRANS_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
UTIL_COMPANY nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
WAIVE_REASON nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOCATION_DATE datetime null,
CUSTOMER_ACCT nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
CUSTOMER_SESSION_NBR numeric(10) null,
CUSTOMER_TYPE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
BAG_NUMBER nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
STORE_NUMBER nvarchar(32) collate SQL_Latin1_General_CP1_CS_AS null,
REVERSED_BY numeric(10) null,
REVERSED_ENTRY numeric(31) null,
LOOSE_COIN_TOTAL nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
LOOSE_BILL_TOTAL nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ROLL_TOTAL nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
STRAP_TOTAL nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_VALUE_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
CASH_IN_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
CASH_OUT_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
FROM_ACCT_TYPE nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
TO_ACCT_TYPE nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
ADJ_FEE_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
AMOUNT_DIFF_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
BAIT_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
BOND_INT_TOT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
CONSIGN_CALC_FEE_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
ENDING_CASH_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
FEE_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
FOOD_COUPON_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
GL_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
LOOSE_BILL_TOTAL_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
LOOSE_COIN_TOTAL_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
MISC_TOT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
MUTILATED_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
OTHER_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
ROLL_TOTAL_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
STRAP_TOTAL_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
SYS_BEG_CASH_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
SYS_END_CASH_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CASH_IN_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CASH_OUT_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_CHECK_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_VALUE_FROM_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
WORKSTATION_TYPE nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
GL_TICKET_SERIAL_NBR nvarchar(16) collate SQL_Latin1_General_CP1_CS_AS null,
AGENT_ID numeric(8) null,
IS_NEW_ACCT nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
PASSBOOK_BALANCE numeric(31,6) null,
PASSBOOK_LINE_NUMBER numeric(4) null,
TRANSACTOR_USER_ID numeric(10) null,
PASSBOOK_BALANCE_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_NBR_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_NBR_FROM_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
ACCT_NBR_TO_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
CNVT_TOTAL_VALUE numeric(31,6) null,
VEN_FEE_AMT numeric(31,6) null,
BUY_RATE numeric(31,9) null,
SELL_RATE numeric(31,9) null,
DEPOSIT_AMT numeric(31,6) null,
TOTAL_FGN_AMT numeric(31,6) null,
CNVT_TOTAL_VALUE_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
VEN_FEE_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
DEPOSIT_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
TOTAL_FGN_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
INDICATOR_VENDOR_FEE nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
ADJ_VEN_FEE_AMT numeric(31,6) null,
ADJ_VEN_FEE_AMT_CCODE nvarchar(3) collate SQL_Latin1_General_CP1_CS_AS null,
IS_HOST_ACTIVE nvarchar(1) collate SQL_Latin1_General_CP1_CS_AS null,
VENDOR_WAIVE_REASON nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
IMAGE_DOCUMENT_DEPOSIT_ID numeric(4) null,
IMAGE_DOCUMENT_SCANNED nvarchar(2) collate SQL_Latin1_General_CP1_CS_AS null,
REVERSAL_CORRECTION_IND nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
IMAGE_DOCUMENT_TRANSACTION_NBR nvarchar(120) collate SQL_Latin1_General_CP1_CS_AS null,
DEF_COMPLETED_BY numeric(10) null,
TOT_FEE_AMT numeric(31,6) null,
TOT_FEE_AMT_CCODE nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
MI_AMT numeric(31,6) null,
MI_AMT_CCODE nvarchar(5) collate SQL_Latin1_General_CP1_CS_AS null,
OFFSET_ID numeric(31) null,
JOB_ID nvarchar(40) collate SQL_Latin1_General_CP1_CS_AS null,
MEMO nvarchar(255) collate SQL_Latin1_General_CP1_CS_AS null,
PAY_TO_2 nvarchar(64) collate SQL_Latin1_General_CP1_CS_AS null,
constraint PKC_TRANSACTION_JOURNAL primary key nonclustered (JOURNAL_ENTRY_ID)
on EJ_TRANS_JOURNAL_NDX,
constraint FK2_TRANSACTION_JOURNAL foreign key (REGION_ID, BANK_ID)
references TP.REGION (REGION_ID, BANK_ID),
constraint FK8_TRANSACTION_JOURNAL foreign key (REVERSED_BY)
references TP.USERS (USER_ID),
constraint FK5_TRANSACTION_JOURNAL foreign key (CASH_DRAWER_ID, CASH_DRAWER_DATE)
references TP.CASH_DRAWER_INSTANCE (CASH_DRAWER_ID, CASH_DRAWER_DATE),
constraint FK11_TRANSACTION_JOURNAL foreign key (BANK_ID)
references TP.BANK (BANK_ID),
constraint FK13_TRANSACTION_JOURNAL foreign key (CORRECTION_ENTRY_ID)
references TP.TRANSACTION_JOURNAL (JOURNAL_ENTRY_ID),
constraint FK1_TRANSACTION_JOURNAL foreign key (TRANSACTION_ID)
references TP.TRANSACTIONS (TRANSACTION_ID),
constraint FK7_TRANSACTION_JOURNAL foreign key (PARENT_ENTRY)
references TP.TRANSACTION_JOURNAL (JOURNAL_ENTRY_ID),
constraint FK9_TRANSACTION_JOURNAL foreign key (REVERSED_ENTRY)
references TP.TRANSACTION_JOURNAL (JOURNAL_ENTRY_ID),
constraint FK6_TRANSACTION_JOURNAL foreign key (OTHER_LOCATION_CODE_ID)
references TP.LOCATION_CODES (LOCATION_CODE_ID),
constraint FK15_TRANSACTION_JOURNAL foreign key (OFFSET_ID)
references TP.TRANSACTION_JOURNAL (JOURNAL_ENTRY_ID),
constraint FK4_TRANSACTION_JOURNAL foreign key (LOCATION_CODE_ID)
references TP.LOCATION_CODES (LOCATION_CODE_ID),
constraint FK3_TRANSACTION_JOURNAL foreign key (USER_ID, CASH_BOX_DATE)
references TP.CASH_BOX (USER_ID, CASH_BOX_DATE),
constraint FK10_TRANSACTION_JOURNAL foreign key (CASH_DRAWER_ID)
references TP.CASH_DRAWER (CASH_DRAWER_ID),
constraint FK12_TRANSACTION_JOURNAL foreign key (OTHER_CASH_DRAWER_ID)
references TP.CASH_DRAWER (CASH_DRAWER_ID),
constraint FK14_TRANSACTION_JOURNAL foreign key (DEF_COMPLETED_BY)
references TP.USERS (USER_ID)
)
on EJ_TRANS_JOURNAL_DAT
go
--Here is the index details for the table
/*==============================================================*/
/* Index: FK01_TRANSACTION_JOURNAL */
/*==============================================================*/
create index FK01_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
REGION_ID ASC,
BANK_ID ASC
)
go
/*==============================================================*/
/* Index: FK03_TRANSACTION_JOURNAL */
/*==============================================================*/
create index FK03_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
REVERSED_BY ASC
)
go
/*==============================================================*/
/* Index: FK04_TRANSACTION_JOURNAL */
/*==============================================================*/
create index FK04_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
CASH_DRAWER_ID ASC,
CASH_DRAWER_DATE ASC
)
go
/*==============================================================*/
/* Index: FK05_TRANSACTION_JOURNAL */
/*==============================================================*/
create index FK05_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
BANK_ID ASC
)
go
/*==============================================================*/
/* Index: FK06_TRANSACTION_JOURNAL */
/*==============================================================*/
create index FK06_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
CORRECTION_ENTRY_ID ASC
)
go
/*==============================================================*/
/* Index: I01_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I01_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
TRANSACTION_ID ASC
)
go
/*==============================================================*/
/* Index: I03_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I03_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
DEF_COMPLETED_BY ASC,
USER_ID ASC,
CASH_BOX_DATE ASC
)
go
/*==============================================================*/
/* Index: I05_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I05_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
PARENT_ENTRY ASC
)
go
/*==============================================================*/
/* Index: I07_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I07_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
REVERSED_ENTRY ASC
)
go
/*==============================================================*/
/* Index: I11_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I11_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
OTHER_LOCATION_CODE_ID ASC
)
go
/*==============================================================*/
/* Index: I13_TRN_JOURNAL */
/*==============================================================*/
create index I13_TRN_JOURNAL on TP.TRANSACTION_JOURNAL (
SESSION_ID ASC
)
go
/*==============================================================*/
/* Index: I21_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I21_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
LOCATION_CODE_ID ASC,
CASH_BOX_DATE ASC,
BATCH_NBR ASC
)
go
/*==============================================================*/
/* Index: I25_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I25_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
LOCATION_CODE_ID ASC,
CASH_BOX_DATE ASC,
JOURNAL_ENTRY_ID ASC
)
go
/*==============================================================*/
/* Index: I26_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I26_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
OTHER_CASH_DRAWER_ID ASC,
TRANSACTION_ID ASC,
STATUS ASC
)
go
/*==============================================================*/
/* Index: I27_TRANSACTION_JOURNAL */
/*==============================================================*/
create index I27_TRANSACTION_JOURNAL on TP.TRANSACTION_JOURNAL (
OFFSET_ID ASC
)
go
July 4, 2012 at 4:30 am
From what I can see, all your tables are heaps.
Is there a reason you dont have CLUSTERED indexes on any of these tables?
July 4, 2012 at 4:33 am
I am not sure why you have so many column defined as nvarchar(64) which could be defined either as float,datetime, int,and normal varchar.
This is the second instance on this forum I am seeing such a database design and it looks like that it is from some vendor or something.
And you do not seem to have any clustered index at all (it is a heap table),just curios and 26 indexes it certainly takes lots of time to insert the data ino this table.Table Could have fragmentation, forwrded rows and deleted rows. Check the index physical stats..
select * from sys.dm_db_index_physical_stats(db_id(),object_id('tablename'),null,null,'DETAILED')
Alos, how many rows query return in the end?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 4:47 am
Can you post the rest of the tables in the query along with test data for each of them
July 4, 2012 at 4:47 am
Actually we are not sure on which col we will place the clustered index, I have created a clustered index for the primary key , as per the performance engineering team it degraded the performance.
Could you please suggest on which col the cluster index can be created
July 4, 2012 at 4:50 am
Usually make your primary key the clustered index as that is the unique identifier for the row.
It will degrade performance as when you create a clustered index on a heap which has non clustered indexes, all the non clustered indexes have to be rebuilt to match the new clustered index pointers and not the heap pointers. But after the initial creation and rebuild you should have better performance in most cases.
July 4, 2012 at 4:51 am
I can not see the graphical plan but i could see the xml in text pad. It is using parallelism and in number of cases only one thread is bringing most of the data Other 3 threads are under used. Maybe check your estimates again.
At once place the estimated rows are 611 but it is bring around 180K rows..Check the statistics once again and if these are updated or not.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 4:58 am
First of all thank you for the information..
sys.dm_db_index_physical_stats table returns 2026 no of columns and the reason we have nvarchar data type is we need to support characters other than english.
July 4, 2012 at 5:04 am
Sorry, think you got confused with what Gullimeel was asking.
Please detail the output of the sys.dm_db_index_physical_stats query
Also from your original select statement, how many rows did it return?
Also from my perspective, please post the DDL for these tables
JOURNAL_CASH_DRAWER
TRANSACTIONS
CASH_DRAWER
DENOMINATION_COUNT
Also provide sample data for all tables within the query so we can create a true mock environment.
July 4, 2012 at 5:04 am
Thank you GulliMill,
There are 2016 rows in this view
July 4, 2012 at 5:23 am
I have updated my statistics one hour before...
I have attached the graphical execution plan, Are you opening it in Management Studio or not
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply