Joining one small table ( 1million rows) with huge table (50 million rows)

The performance sucks big time. It is taking about 50-60 minutes to complete the query.

Even after giving optimizer hints to use proper indexes, it is taking that long. It is MSSQL server database and the join is between a temp table with one million rows and a regular table with 50 million rows.

Given a multi CPU system with multiple disks, how fast this type of join should complete?
Any tips would highly be appreciated.
Best regards

 

THATS A LOT OF DATA!

If it is a temp table, I suggest a regular back up then delete the old data out of the table leaving only the newest data so it's not such a resource hog,

I'm not sure if that helps your situation. I am simply responding based on the limited information you posted.

 

The data in the temp table is only million rows and that is something i can loose.

 

some things to ask yourself? You aren't querying against fulltext indexes right? try and find the bottlenecks, at most i'd say it should run 1-2 minutes. maybe show your query and run the mssql indexing helper tool again. Make sure you are querying correclty like if you are doing sub-sub selects, do your checks in them rather than waiting till your joining on the outside

for example if you are waiting till the outside query to check all inner sub-queries if "status = 1" or something, put that code in the inner queries. Also try and use a SP or UDF...

 

Make sure that you are using the right indexes as it looks like in your case it does not happens...

You need to SELECT FROM the big table LEFT JOIN the smaller table using the indexed fields in the WHERE condition

Also need an index on the field used to JOIN

 

Have you tried looking at the Execution Plan?

If you don't know where to find it, open your query in Query Analyser (Or in a query window in SQL Management Studio if your using SQL 2005), and hit CTRL-L (Or Query->Display Estimated Execution Plan).

Then run the query including the actual execution plan (Query->Include Actual Execution Plan) although that may only be available in Management Studio (haven't use the SQL 2000 tools in ages).

Compare the two, and if possible post them here but the very basic rule of thumb is the further left the bigger numbers are the "faster" the query should run.

Also what indexes do you have available? If its taking that long I'm guessing its doing some kind of table scan, or the index just isnt reducing the search enough.

Jen

 

what are you joining on?

identity Id numbers will join WAY faster than text/string fields.

are you using like %% commands?