Inefficient SQL query

Question

0.00/5 (No votes)

See more:

I have 3 tables that I've joined and for some reason my query is VERY slow, takes 30secs - 1min to execute.

I have no experience at writing SQL other than writing simple SELECT statements or calling a storedProc that a DB developer wrote.

//trdopt rows > 100,000
//instropt rows > 100,000
//exchopt rows = 10;

SELECT trddata.date, instr.sym, instr.under, instr.exp, exch.name, trddata.prc
FROM   trdopt trddata
JOIN   instropt instr
ON     trddata.optid = instr.id
JOIN   exchopt exch
ON     trddata.exchcode = exch.id
WHERE DATE_FORMAT(trddata.date, '%Y-%m-%d %T') = '2013-04-02';

Can someone please help me out and show/explain what the problem is and the correct way to implement this query.

Thanks in advance,
-DA

Posted 4-Apr-13 14:37pm

d.allen101

Updated 4-Apr-13 14:39pm

v2

Add a Solution

1 solution

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)

Mycroft Holmes · Answer 1 · 2013-04-04T16:14:00

Solution 1

It is likely to be a lack of indexes, this is always the first place to check.

Turn on the Actual Execution plan (icon in SSMS toolbar) and run the query. When it is completed you will have a tab with the execution plan used. You MAY have a nice little green section recommending additional indexing, copy/paste this to a new query window and create the indexes. Rerun your query.

Query optimisation is an ART not a science, it takes experience, your best bet would be to start reading up on how to use the Execution plan.

Posted 4-Apr-13 16:14pm

Mycroft Holmes

Comments

d.allen101 4-Apr-13 22:19pm

Thanks for the advice but I don't know what "Acutal Execution" is and also the DB was set up by my manager so I can't ALTER any tables. By the way it's an MySql DB if that makes a difference. Basically what's going on is I'm a front-end dev and I'm querying the DB to render historial stock data to be analyzed

Mycroft Holmes 4-Apr-13 22:31pm

Sorry I assume SQL Server. If you have no access to the database other than query then you are screwed, go back to your manager and make him optimise the query.

I have never used MySQL but after the indexing I would look into the where clause which seems to be converting a date to a string and then applying the filter I think it should be convert the string to a date and filter on the date data type.

d.allen101 4-Apr-13 22:35pm

He told me that MySQL indexes foreign keys by default. So you don't see anything wrong with my query? It can't be...Even if I put a LIMIT on the number of rows to be returned e.g. (LIMIT 1000) it still takes at least 30secs. Something is not right here.

Mycroft Holmes 4-Apr-13 22:46pm

I don't have any MySQL experience so can't refute the indexing issue, 30sec is not unreasonable on a large database. Try changing the where clause.

d.allen101 5-Apr-13 7:35am

Hey Mycroft Holmes, THANK YOU! I removed that DATE_FORMAT() and it helped sooooo much!!!! it didn't resolve my issue but it DEFINITELY helped, so at least I'm now making progress! Thanks again. If you have any other suggestions plz let me know!

d.allen101 4-Apr-13 22:50pm

ok, you're referring to that DATE_FORMAT()...right? so that string conversion is causing me performance issues? that's what you'r talking about right, the advice you first gave me