Removing HASH Match / Inner Join

Hi,

I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained in indexes. But it does an index scan and a massive hashmatch. Why is this? both tables have columns in the index ordered the same, all datatypes of the indexes are ints. The code looks like this.

SELECT TblActivities.*
FROM TblActivities
INNER JOIN TblBookingsCraig
ON
TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion

Does anyone know why it won't perform an index seek?

Cheers
CYou should have the indexes on the fields you want to join, do you have them like that?

TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion|||Hi,

Thanks for the reply. Yes I do have the indexes identically setup on both tables. Could it be ignoring the indexes because of the size difference of the two tables? One has 1millish and the other 2.5mill ish?

Cheers
C|||Did you check the values of these columns? if they contain duplicate values or highly identical values for number of records; say for 100 records at least the indexes have same values. In this case, the index scan would be the decision that SQL optimizer might take!

Removing blank space caused by wide table

Hi all,

Report:

-For instance 2 small tables (eg. width 10cm = 3 inch?)

-And one wide table (eg. width 30cm = 10 inch?)

All separated by "insert pagebreak after table".

Problem:

When rendered, the pages with the small tables on have a lot of white blank space at the right of the table. This is probably caused by the big table on page 3.

This report is distributed by email in Excell format. So on sheet 1 and 2 there are a lot of white cells on the right of the tables. When trying to print, they just want to use the "landscape" option and the "fit to page" option. Because of the empty white cells, the fit to page option reduces the first 2 tables to a very small table which covers only 50 % of the page width. The other 50 % is reserved for the empty cells.

Off course, I know that deleting the empty cells offers a solutions, but it would be a lot more handier if there were no empty cells in the first place.

Anybody with a solution?

I had a similar problem with one report where one table's data was pushing the rows down and creating white space to the right and then displaying a second table's data. What I did is to put the tables into rectangles and this solved the problem. I don't know if this will help you or now, but I hope it might.|||

Hi guyinkalamazoo3,

Thx for the suggestion. Tried that but didn't work either.

Now, I see that I can't really reproduce the error. I thought that if I could scroll with the scrollbar into a lot of blank space (caused by the wide table on the next page) that this was also exported to excell. Now apparently, this is not the problem as with the testreports I created, no blank cells were imported in excell.

Any other solutions?

P.S. Can I give page-setup parameters when I want to export to excell? For instance, I would like to have the 'landscape' and 'fit to page' option to be selected ?