Sql-server – Any way of optimizing joins between 2 databases with different collations

collationsql servert-sql

I'm creating a query for a report where we must do a cross database join. Unfortunately the fields we have to join on have two separate collations – "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS". The join is very slow between these and is really impacting the speed of the report – is there any ways of optimizing this? Is changing the collation to match wise and/or difficult?

The join portion of the query is below

INNER JOIN EDE.dbo.DeliveryDetail D
              ON D.DeliveryNoteNo COLLATE database_default = Consignments.CustomerReference COLLATE database_default

Best Answer

If you can add columns to tables, you can try persisted computed columns, and index those. For example:

ALTER TABLE dbo.DeliveryDetail ADD DeliveryNoteNo_Collated COLLATE whatever PERSISTED;

CREATE INDEX IX_DeliveryDateNo_Collated ON dbo.DeliveryDetail(DeliveryNoteNo_Collated);

You're basically pre-baking converted versions of your fields once on insert (and kept updated for you automatically), and indexing those.

There are two really big drawbacks. First, these are new columns in your table: they'll take up more space, the indexes will take up more space, and if your apps are doing inserts without explicitly listing their column names, their inserts will fail.

Second, if the app's connection options aren't set correctly, your delete/update/insert operations can actually fail. NUMERIC_ROUNDABORT has to be set OFF, and ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must all be set to ON. You'll definitely want to test for this in development first, and for bonus points, check sys.dm_exec_sessions in production to check everybody's session settings.

For more info:

Related Solutions

Sql-server – Join To a @Table Variable is running ineficiently

[Copying from my answer on SQLPerformance.com.]

Some very brief initial suggestions from discussions elsewhere:

Try creating @xmlTemp as a #temp table with a clustered index on (StartDate, EndDate) instead of a table variable. This may provide SQL Server with more accurate stats information (though questionably useful if the table only has one row).
If @xmlTemp has only one row always, use two variables instead of a table in the first place.
Try adding the (RECOMPILE) option to the statement, especially if you convert to variables instead of the #temp table (parameter sniffing).
Try using OPTION (MAXDOP 1) - parallelism is definitely in use, and at the lower end the threads seem partially imbalanced. I wonder if parallelism is helping or hurting here - can't hurt to test duration with and without.
You may need to perform more rigorous stats updates. A lot of these estimates are way, way off.
Remove the DISTINCTs. For this set of columns I find it hard to believe this is eliminating any duplicates, but the optimizer has to work as if there are dupes to remove.
Consider using Table-Valued Parameters (TVPs) instead of shredding XML for the different companies / stores.

T-sql – Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Dynamic SQL is another option. I would try both this method and @a1ex07's method and see which runs faster for you.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25) 
                                 ,@---Name  varchar(25) 
                                 .........
                                 ......

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * ' + CHAR(13) + 
        'FROM [Ticket]  ' + CHAR(13) + 
        'INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID  ' + CHAR(13) + 
        'INNER JOIN dbo.[Status] ON [Ticket].Stat .... ' + CHAR(13) + 
        '..... ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        'WHERE 1=1 '

IF @Category IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [TicketType].Name LIKE @Category '

IF @ReportName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Ticket].Name LIKE @ReportName  '

IF @Status IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Status].StatusName LIKE @Status '

IF @PhaseName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Phase].PhaseName LIKE @PhaseName  '

IF @Name1 IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND (BA.FullName = @Name1 OR AD.FullName = @Name1) '

EXEC sp_executesql @sql, N'@Category varchar(25), @ReportName varchar(25), @Status varchar(25), @PhaseName varchar(25), @Name1 varchar(25)',
                @Category, @ReportName, @Status, @PhaseName, @Name1

Best Answer

Related Solutions

Sql-server – Join To a @Table Variable is running ineficiently

T-sql – Return table based on 1, 2, 3, 4, 5 or 0 selected search options

Related Question