Associate
Any sql sears out there who can speed up the following simple query:
SELECT b.EFFECTIVE_DATE, b.ISIN, b.NOMINEE, b.POLICY_ID, b.UNITS
FROM[dbo].[PC_tbl_PR_EXP_PC_FundData] b
WHERE b.Nominee = '********' AND b.ISIN = '*******' AND UNITS < 0 AND
b.EFFECTIVE_DATE BETWEEN '*******' AND '********' Order by POLICY_ID Asc
Note we do not have DBA rights so cannot add indexes onto these these columns without jumping through too many hoops. The actual SQL query is sent by ADO via VBA and the results are then added to a worksheet. Everything in asterisks are dependent on the input added on another worksheet in Excel. They are all varchars though.
The performance hit is on the query it is taking up to 2 minutes per item which while not the end of the world is rather painful! Only a little bit of overhead is added by ADO.
SELECT b.EFFECTIVE_DATE, b.ISIN, b.NOMINEE, b.POLICY_ID, b.UNITS
FROM[dbo].[PC_tbl_PR_EXP_PC_FundData] b
WHERE b.Nominee = '********' AND b.ISIN = '*******' AND UNITS < 0 AND
b.EFFECTIVE_DATE BETWEEN '*******' AND '********' Order by POLICY_ID Asc
Note we do not have DBA rights so cannot add indexes onto these these columns without jumping through too many hoops. The actual SQL query is sent by ADO via VBA and the results are then added to a worksheet. Everything in asterisks are dependent on the input added on another worksheet in Excel. They are all varchars though.
The performance hit is on the query it is taking up to 2 minutes per item which while not the end of the world is rather painful! Only a little bit of overhead is added by ADO.