# Prices and contracts query

The query below is quite slow (taking about half a second). Any obvious things I could improve?

The PRICES table has about 21 million rows and 30 columns while CONTRACTS has 130 k rows and 10 columns.

SELECT
CONTRACTS.EXT_ID,
PRICES.REFERENCE_DATE,
PRICES.PRICE_DATE,
CONTRACTS.PERIOD,
PRICES.TIME_TO_MATURITY,
PRICES.CLOSE_PRICE,
PRICES.ORIGINAL_PRICE_ID
FROM
PRICES INNER JOIN CONTRACTS ON PRICES.CONTRACT_ID = CONTRACTS.ID
WHERE
(((CONTRACTS.EXT_ID)=@CONTRACTID)
AND ((PRICES.REFERENCE_DATE)=@REFDATE)
AND ((PRICES.STATUS_TYPE) LIKE 'CAL%'));

• What database — is it Microsoft sql-server? What indexes exist on your tables? – 200_success Oct 27 '15 at 16:35
• @200_success. Tricky bit. It's not my db and I don't have documentation. That's why I'm looking for 'obvious' things. PRICES.REFERENCE_DATE and CONTRACTS.EXT_ID would be natural indexes. – BuckTurgidson Oct 27 '15 at 16:41
• I know it's an oracle db. (jdbc:oracle:thin:) – BuckTurgidson Oct 27 '15 at 17:02
• @BuckTurgidson just a guess but maybe the condition (PRICES.STATUS_TYPE) LIKE 'CAL%' can be sped up by replacing the LIKE with a string function call this way left(PRICES.STATUS_TYPE, 3) = 'CAL' – user2023861 Oct 27 '15 at 17:36
• @BuckTurgidson also why not keep a copy of these tables in a database that you do control? Refresh your database copy whenever you deem appropriate. This way you can put in indexes anyway you want. You can also add columns. For instance, I'd recommend adding a bit column called IsCal to your copy that is 1 if STATUS_TYPE starts with "CAL" and 0 otherwise. That way your query won't have any string operations in it. – user2023861 Oct 27 '15 at 17:41