Self-Join Timeout With Large DataSets

Question

0.00/5 (No votes)

See more:

I'm using MySQL 5.1 and I'm trying to take the difference between the last two values of every row with matching identifiers. Example:

SQL

CREATE TABLE tbl1(id INT AUTO_INCREMENT PRIMARY KEY,team VARCHAR(10) INDEX,score INT);

DataSet
id	team	score
1	xyz	10
2	abc	15
3	abc	22
4	rst	98
5	xyz	67
6	xyz	48
7	rst	92

Query:

SQL

SELECT
    x.id,
    x.team,
    x.score - y.score score_diff
FROM
    (SELECT a.*, COUNT(*) rank
     FROM tbl1 a
     LEFT JOIN tbl1 b
     ON b.team = a.team
     AND b.id >= a.id
     GROUP BY id
     ORDER BY id DESC) x
JOIN
    (SELECT a.*, COUNT(*) rank
     FROM tbl1 a
     LEFT JOIN tbl1 b
     ON b.team = a.team
     AND b.id >= a.id
     GROUP BY id
     ORDER BY id DESC)y
ON
    y.team = x.team
    AND y.rank = x.rank + 1
GROUP BY
    symbol
ORDER BY
    id DESC;

This code runs just fine with a small dataSet but when I use with a dataSet containing thousands of rows it dies! Can anyone show me the correct way or a more efficient way to write this query?

ANY help/advice would be GREATLY appreciated!

Thanks,

-Donald

Posted 14-Dec-10 8:40am

d.allen101

Updated 14-Dec-10 9:47am

v9

Add a Solution

Comments

AspDotNetDev 14-Dec-10 15:04pm

What do you mean by "dies"? Does the query take too long to run? Does it take up so many resources (memory/CPU) that it fails and the result is not returned? Do you get an error message of some sort?

d.allen101 14-Dec-10 15:07pm

well basically it runs for well over 5 mins then it times out. I forget the error message that's generated by MySQL but its something to the extent of the server timing out...

AspDotNetDev 14-Dec-10 15:12pm

Can you use a temporary table, a cursor, and multiple queries? If so, that could speed this up significantly. If not, then we'll have to look into other solutions.

d.allen101 14-Dec-10 15:16pm

I'm not sure if I can use temp table, cursor and multiple queries...I'm just a novice at sql and not familiar with using cursors and breaking down queries into smaller queries...

d.allen101 14-Dec-10 15:21pm

sorry but my sql skill-set is limited to simple joins, elementary functions and stored procedures.

AspDotNetDev 14-Dec-10 15:41pm

Update your question with which indexes you are using and sample output you are expecting given the sample input you've already provided. That will help me understand what you are trying to accomplish better (e.g., which two are the "last two values of every row with matching identifiers" and which identifiers should match... I assume team, but I could be wrong).

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Answer 1 · 2010-12-14T09:24:00

Solution 1

You may be able to speed up the query immensely by implementing indexing on your table appropriately. Look that this.

A simple google search on indexing also turns up lots of references for this. Cheers.

Posted 14-Dec-10 9:24am

fjdiewornncalwe

Updated 14-Dec-10 9:25am

v2

Comments

d.allen101 14-Dec-10 15:26pm

Oh sorry, I forgot to include that in my create table illustration but I do have team indexed.

AspDotNetDev 14-Dec-10 15:44pm

It would be helpful if you included exactly the indexes you are using. For example, from what you commented, I am unsure if team has its own index, or if it's part of a composite index.

AspDotNetDev · Answer 2 · 2010-12-14T10:06:00

Without profiling and such, here is the easy route.

Create a stored procedure to do all the work. It will contain more than one statement.

Create a temporary table (see here) to hold your results. Call this, say, "resultsTable".

Create another temporary table to hold some temporary results. Call this, say "tempTable".

Create a cursor (see here) that selects each record in the order you want to loop through them (e.g., sort by team).

Use the cursor to loop through each record. Insert each record into tempTable. Store the previous team in a variable and when you encounter a new team (or the end of the records), check tempTable for the "last two" values and insert the result into resultsTable. Clear the tempTable.

Once you have looped through each record, you are done.

Some of that can be optimized, but like I said it's the easy route, so it may be easiest for you to just do this.