Hello,

I need help fine-tuning a query.
The tables, existing indexes, default indexes, and query itself include below,
along with the Query Plan (the current I/O Cost is 753680).
A restriction is that I cannot change the table design.
I can force indexes, and add indexes.
Having tested with all of the existing indexes, I've found that
the default ones are the most efficient.
They question is: what would be the best index to add and enforce?
Please note the table sizes. They are very large (hundreds of millions).
The query typically takes 70+ seconds to run as is.

Thanks very much, chassegs

Table: Accounts..Accounts (Size 766K+)
----------------------------------------
AccountId (int)
State (tinyint)
AccountClassInd (char(1))

Table: ProductSynonyms (Size 322M+)
-------------------------------------
ProductId (int)
ProductSynonymType (tinyint)
ProductSynonym (varchar(32))

Table: ForexItems (Size 104M+)
--------------------------------
ValueDate (smalldatetime)
ProductTypeId (tinyint)
AccountId (int)
ProductId (int)
PSCode (char1)
SpecialRecordId varchar(31)
ProductId (int)
ExternalItemId
StructureId (int)
ExternalItemType (tinyint),
CurrencyCode1 (char(3))
CurrencyAmount1 (money)
CurrencyCode2 (char(3))
CurrencyAmount2 (money)
ExchangeRate (tinyint)
ProductDate (smalldatetime)
ValueDate (smalldatetime)

SELECT PSCode, SpecialRecordId, t1.ProductId, t2.ProductSynonym, t3.AccountId, t3.AccountName, StructureId, ExternalItemType,
CurrencyCode1, CurrencyAmount1 = convert( float, CurrencyAmount1 ),
CurrencyCode2, CurrencyAmount2 = convert( float, CurrencyAmount2 ),
ExchangeRate, ProductDate, ValueDate
FROM ForexItems t1, ProductSynonyms t2, Accounts..Accounts t3
WHERE t1.ValueDate >= '2011-03-15 12:00:00:00 AM'
AND t1.State = 0
AND t1.ProductTypeId != 4
AND t1.AccountId IN (201, 10505, 74443, 6870, 142745, 95959)
AND t1.ProductId = t2.ProductId
AND t2.ProductSynonymType = 3
AND t1.AccountId = t3.AccountId
AND t3.State = 0
AND t3.AccountClassInd != 'I'
AND t2.ProductSynonym NOT LIKE 'TD%' `

sp_helpindex Accounts..Accounts

index_name index_description index_keys
-------------------- --------------------------------------- -------------------
AccountsIndex1 clustered, unique located on default AccountId, Version
AccountsIndex2 nonclustered located on default AccountId, State
AccountsIndex4 nonclustered located on default AccountName, State

sp_helpindex ProductSynonyms

index_name index_description index_keys
--------------------- --------------------------------------- -----------------------------
ProductSynonymsIndex2 nonclustered located on default ProductSynonym, ProductSynonymType
ProductSynonymsIndex3 nonclustered, unique located on default ProductId, ProductSynonymType
ProductSynonymsIndex1 clustered, unique located on default ProductSynonymId

sp_helpindex ForexItems

index_name index_description index_keys
----------------- ------------------------------------ --------------------------------------------------
ProductIdIndex2 nonclustered located on default ValueDate, CompanyId, CurrencyCode, Version, State
ProductIdIndex3 nonclustered located on default ProductDate, CompanyId, CurrencyCode, Version, State
ForexItemsIndex4 nonclustered located on default CompanyId, RowCreation, ValueDate, State
ForexItemsIndex5 nonclustered located on default RowCreation, State
ForexItemIndex6 nonclustered located on default AccountId
ProductIdIndex1 clustered, unique located on default ProductId, Version, State

The default indexes being used (on the 3 tables respectively):

index_name index_description index_keys
------------------ --------------------------------------- --------------------------------------------------
AccountsIndex1 clustered, unique located on default AccountId, Version
ProductSynonymsIndex3 nonclustered, unique located on default ProductId, ProductSynonymType
ProductIdIndex2 nonclustered located on default ValueDate, CompanyId, CurrencyCode, Version, State

QUERY PLAN:

set showplan on
set noexec on

Command was executed successfully

Warnings: --->
W (1):
W (2): QUERY PLAN FOR STATEMENT 1 (at line 1).
W (3): Executed in parallel by coordinating process and 15 worker processes.
W (4):
W (5):
W (6): STEP 1
W (7): The type of query is SELECT.
W (8): Executed in parallel by coordinating process and 15 worker processes.
W (9):
W (10): FROM TABLE
W (11): ForexItems
W (12): t1
W (13): Nested iteration.
W (14): Index : ProdIdIndex2
W (15): Forward scan.
W (16): Positioning by key.
W (17): Keys are:
W (18): ValueDate ASC
W (19): Executed in parallel with a 15-way hash scan.
W (20): Using I/O Size 16 Kbytes for index leaf pages.
W (21): With LRU Buffer Replacement Strategy for index leaf pages.
W (22): Using I/O Size 16 Kbytes for data pages.
W (23): With LRU Buffer Replacement Strategy for data pages.
W (24):
W (25): FROM TABLE
W (26): ProdSynonyms
W (27): t2
W (28): Nested iteration.
W (29): Index : ProdSynonymsIndex3
W (30): Forward scan.
W (31): Positioning by key.
W (32): Keys are:
W (33): ProdId ASC
W (34): ProdSynonymType ASC
W (35): Using I/O Size 2 Kbytes for index leaf pages.
W (36): With LRU Buffer Replacement Strategy for index leaf pages.
W (37): Using I/O Size 2 Kbytes for data pages.
W (38): With LRU Buffer Replacement Strategy for data pages.
W (39):
W (40): FROM TABLE
W (41): Accounts..Accounts
W (42): t3
W (43): Nested iteration.
W (44): Using Clustered Index.
W (45): Index : AccountsIndex1
W (46): Forward scan.
W (47): Positioning by key.
W (48): Keys are:
W (49): AccountId ASC
W (50): Using I/O Size 2 Kbytes for data pages.
W (51): With LRU Buffer Replacement Strategy for data pages.
W (52):
W (53): Parallel network buffer merge.
W (54):
W (55): Total estimated I/O cost for statement 1 (at line 1): 753680.
W (56):
<---
[Executed: 3/17/11 11:10:56 AM EDT] [Execution: 0ms]

It's difficult withoiut having the data infront of you but try this:

Hey thanks, mike_bike_kite!
That reduces the I/O cost by roughly 75%!
You rock!

chassegs

Just noticed you are using float for money values. It is not accurate and can produce unexpected results.

Please read this article.

Using the float Datatype Technote: Mobile Enterprise, Database Management - Sybase Inc

The Query show that ur are using " not like ", " != " , " IN " function. These functions takes more lead time to find the extract data in the page.

Temp tables are the best solution.