SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow Query when perform INNER JOIN Between a very large partioned table and a non partitioned table


Slow Query when perform INNER JOIN Between a very large partioned table and a non partitioned table

Author
Message
Karim Diouf
Karim Diouf
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 80
I have a very large - partitioned - table with about 50 millions records. Partition column is PeriodDate (format: YYYYMM)
I need to perform an Inner Join between that partitioned table and a - non partitioned table - (about 3 millions rows)
Partitioned Data is not sorted (Data could be sorted, if correct index is created, but within every single partition of the partitioned table. Not globally!)
It seems that, in that case, the only join type available is the HASH JOIN.....
My problem is that the query is very slow when using the HASH JOIN.
Do you have a better option? Please help
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99928 Visits: 20890
karim.diouf - Friday, February 9, 2018 3:25 PM
I have a very large - partitioned - table with about 50 millions records. Partition column is PeriodDate (format: YYYYMM)
I need to perform an Inner Join between that partitioned table and a - non partitioned table - (about 3 millions rows)
Partitioned Data is not sorted (Data could be sorted, if correct index is created, but within every single partition of the partitioned table. Not globally!)
It seems that, in that case, the only join type available is the HASH JOIN.....
My problem is that the query is very slow when using the HASH JOIN.
Do you have a better option? Please help

Too little information, please post the actual execution plan, the query and the full DDL for the tables in question.
Cool

TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: General Forum Members
Points: 81859 Visits: 8942
karim.diouf - Friday, February 9, 2018 3:25 PM
I have a very large - partitioned - table with about 50 millions records. Partition column is PeriodDate (format: YYYYMM)
I need to perform an Inner Join between that partitioned table and a - non partitioned table - (about 3 millions rows)
Partitioned Data is not sorted (Data could be sorted, if correct index is created, but within every single partition of the partitioned table. Not globally!)
It seems that, in that case, the only join type available is the HASH JOIN.....
My problem is that the query is very slow when using the HASH JOIN.
Do you have a better option? Please help

1) Are you doing an INNER JOIN with direct equality of matching datatypes?

2) 50M records is SMALL these days. I have a several client databases on my laptop with numerous tables with up to 10X that number of rows.

3) Partitioning does NOT exist to make your queries run faster!! It exists for large-scale data handling.

4) What is the FULL create table statements for both tables (with indexing)?

5) What predicate are you using in your query? If none, then it is almost certain that you will be hitting ALL data. And without appropriate SORT orders to allow a FAR more efficient MERGE JOIN you will be stuck with the HASH as the fastest way to join the data. Try forcing a loop join and see how bad that is!

6) What is the data size of both tables? And now what is the total RAM on your server? If 1 is way bigger than 2, and you are not maxed out on RAM on your edition of SQL Server then that becomes your HIGHEST PRIORITY TASK!!! It is so important that I HAVE TURNED DOWN POTENTIAL CLIENT WORK BECAUSE OF INSUFFICIENT RAM ON SERVERS (and also for other totally unacceptable configurations). Memory runs at microsecond timescale and it shoves data into CPUs that do 3-5 BILLION operations per second on your data.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search