Multiple joins to the same temp table

Question

Multiple joins to the same temp table

richardmgreen1

SSChampion

Points: 10351
More actions
March 7, 2014 at 7:26 am

#304972

Hi all
I'm looking for some advice on speeding up a query that seems a bit long winded.
I've managed to get it run in less than 20 minutes by loading the most used data into a temp table rather than sub-queries. I'm now looking to see if I can speed it up any further.
This is the code to generate the temp table:-
create table #Diag_Codes (
AbstractID varchar(44)
,EpisodeNumber int
,OrderOfDiagnosisInEpisode bigint
,RowUpdateDateTime datetime
,DiagnosisIcd varchar(30)
)
CREATE INDEX IDX_Abstract ON #Diag_Codes(AbstractID,EpisodeNumber)
I've indexed it which has helped a bit more.
I've then got up to 50 of the following joins:-
left outer join #Diag_Codes PRIMDIAG
on ***.AbstractID = PRIMDIAG.AbstractID COLLATE SQL_Latin1_General_CP1_CS_AS
and ***.ServiceSeqID = PRIMDIAG.EpisodeNumber
and PRIMDIAG.OrderOfDiagnosisInEpisode = 1
If i take out all the references to #Diag_Code and run the rest of the query it flies along quite happily and completed 800,000 records in a few minutes
I've also got a similar set-up for #Proc_Codes with the same number of joins.
Am I right in thinking it might be quicker to run UPDATE statements for these instead of joins or is there another way to speed this up?
Any help gratefully received.
::edit::
Forgot to mention, I'm also getting this error:-
"Msg 701, Level 17, State 123, Line 3
There is insufficient system memory to run this query."
even though the server has 16GB RAM, of which something like 15GB is set aside for SQL.
Any ideas on that one?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Does field [AbstractID] need to be datatype "varchar(44)"? This is quite a large field to JOIN upon...

And do you really need 50(!) JOINs of that same structure? Perhaps this single query will perform better if you split it into multiple queries and put intermediate results into other temp-tables.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

richardmgreen1 SSChampion Points: 10351 More actions · Answer 2

The field AbstractID is that type and size in the base table I'm selecting the data from.

I need to update 50 codes from the temp tables and I'm trying to find out the best way to do it (either 50 joins, 50 update statements or something completely different).

::edit::

Just done some more digging and it would appear I could change AbstractID into a bigint without too many issues.

The only downside to that would be I would have to do it on the fly.

I'm using a database snapshot where AbstractID has been set up like that on the principal server (I haven't got a clue why).

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

richardmgreen1 (3/7/2014)
...I need to update 50 codes from the temp tables and I'm trying to find out the best way to do it (either 50 joins, 50 update statements or something completely different).

I don't know your entire situation, but maybe this suggestion could help:

I expect the temp-tables that hold the updated codes are (much) smaller compared to the table that needs to be updated. Therefor put all codes that hold the updated values into a few (or one) temp-tables, grouped on the AbstractID/EpisodeNumber field. Add appropriate indexes to these temp tables and join them to the original table in a single UPDATE statement. Perhaps multiple updates could perform better, but you need to test it yourself.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

richardmgreen1 SSChampion Points: 10351 More actions · Answer 4

I've put all the codes into temp tables with appropriate indexes already.

I was just wondering if multiple joins were the way to go or if I could do the updates in a single statement considering I need to update different fields depending on the OrderOfDiagnosisInEpisode value (whihc is numeric).

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 5

You could try updating your index to be like this

CREATE INDEX IDX_Abstract ON #Diag_Codes(AbstractID,EpisodeNumber,OrderOfDiagnosisInEpisode)

INCLUDE (rowupdatedatetime,DiagnosisIcd)

That way it will be completely covered and won't have to do key lookups.