Speed Up SQL Query

[dazzerd]

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.

 

[tom_e]

To be honest that's such a basic query that there's no real way to make it any faster, your lack of indexes is going to be causing you the biggest performance hit.

 

[dazzerd]

Yeh did think as much!

 

[skyripper]

If that table is growing in data, then your performance is only going to get worse as the SQL has to full table scan thru to find the matching rows. How much worse depends entirely on the growth rate.

 

[planty]

How big is your dataset?

Apart from indexes, one thing that would speed it up a bit is using INTs in the WHERE clause rather than VARCHARs. Are you able to map the ISIN and Nominee values to INTs somehow?

EDIT: that would require you to convert the existing data for those columns to INTs (which might take a while)

 

[dazzerd]

It is a pControl table (which was not designed very well). It grows every day unfortunatley, we run an archive process on it to cut it down but it helps little. Thanks for the help all but we will need to add some indexes on as I suspected.