Given your additional info, this suggests that once you have the query plan, take a close look at the indexes on the B table. See if there are any that have both the BOEKCODE as well as the other selected field. I'm guessing not, and by selecting that field from the B table, you have to traverse all the records rather than hit an index that just contains the other selected field, but I wouldn't want to offer any guarantee on that. The query plan will likely make things clear as to what's happening when you select it from the B table instead of the R table. You may be able to anticipate the plan's answer just by running the following query:
SELECT BOEKCODE, BOEKOMSCHRIJVING, COUNT(*)
FROM B
GROUP BY BOEKCODE, BOEKOMSCHRIJVING
HAVING COUNT(*) > 1
If that query returns any rows, you might want to substitute the following into your query in place of the B table:
SELECT DISTINCT BOEKCODE, BOEKOMSCHRIJVING
FROM B
peter 67432 (7/10/2014)
sgmunson (7/10/2014)
Have you looked at the query's execution plan? This would likely reveal the reason. Also, is there more than one record in the table with alias B for a given BOEKCODE value ? In that case you may be traversing a lot of records that aren't needed, as including the B table in the result set requires retreiving all the records from that table for that BOEKCODE value. As you are using GROUP BY, you don't need the B table data if you aren't aggregating anything from it, so using the R table's version is fine.Hi Sgmunson,
Thanks for looking into this, I will look at the execution plan to see what is happening.
From the 'B' table, I need the 'BOEKOMSCHRIJVING', that's why I've added the 'B' table.
For the extra records, Every PA has (at least) one PR, each PR contains 1 R, and each R has one B
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)