1
\$\begingroup\$

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%'));
\$\endgroup\$
  • \$\begingroup\$ What database — is it Microsoft sql-server? What indexes exist on your tables? \$\endgroup\$ – 200_success Oct 27 '15 at 16:35
  • \$\begingroup\$ @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. \$\endgroup\$ – BuckTurgidson Oct 27 '15 at 16:41
  • \$\begingroup\$ I know it's an oracle db. (jdbc:oracle:thin:) \$\endgroup\$ – BuckTurgidson Oct 27 '15 at 17:02
  • \$\begingroup\$ @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' \$\endgroup\$ – user2023861 Oct 27 '15 at 17:36
  • \$\begingroup\$ @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. \$\endgroup\$ – user2023861 Oct 27 '15 at 17:41
1
\$\begingroup\$

if you less care about the "Dirty read" then you can use the "(nolock)" after the table name. And you already have the indexing.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.