?
Solved

Trying to improve performance on a simple query

Posted on 2008-11-06
7
Medium Priority
?
272 Views
Last Modified: 2013-12-13
I have a simple SQL Query running out of a PHP app I inherited to a MySQL server.  There are 2 tables involved, and we are inserting into 1 table the values which are missing.  This query takes over 20 seconds to finish.  The table DataHistory has only 110000 rows in it, and DataNew has only 5-600 rows.  Both CodeType and Bonus are indexed.  What else can I do to improve the performance of this query? (we are occassionally experiencing timeout issues, and the low number of rows should give a much quicker response, shouldn't it?)

Thanks,

Geoff
insert into
		DataNew (
		CollectorCode,
		CollectComm)
	SELECT DISTINCT
		DataHistory.CollectorCode, 0
	from
		DataHistory
	left join
		DataNew
	on
		DataNew.CollectorCode=DataHistory.CollectorCode
	where
		DataHistory.CodeType = $DataCodeType and
		DataHistory.Bonus = 0 and
		DataNew.CollectorCode is null

Open in new window

0
Comment
Question by:GeoffSutton
  • 4
  • 2
7 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 2000 total points
ID: 22896275
Are the join columns indexed?

I suspect selecting from only DataHistory will be fast:

      SELECT count (DISTINCT DataHistory.CollectorCode)
      from   DataHistory
      where  DataHistory.CodeType = $DataCodeType and
                   DataHistory.Bonus = 0

If so, then does adding DataNew slow it down?  If DataNew.CollectorCode isn't indexed it may be doing a full table scan of DataNew.
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 22896384
Create index on:

DataHistory.CollectorCode
DataHistory.CodeType
DataHistory.Bonus = 0
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22896390
I don't believe they are.  Datanew is a junk table used as a "Scratch pad" and only holds 500-600 rows.  Would it make that big a difference?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Author Comment

by:GeoffSutton
ID: 22896694
rpkhare -> All those indexes are already in place.
Milleniumaire-> Created new index on DataNew.CollectorCode.  Testing it now.
0
 
LVL 10

Author Closing Comment

by:GeoffSutton
ID: 31513949
I created the index on DataNew. CollectorCode and that dropped my query time from 25 or so seconds to somewhat less than 1 second.  I wouldn't have thought it necessary for such a low amount of rows.  Thank you so much for the suggestion.

Geoff
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 22896800
It may/may not improve the performance, you would have to try it.

Alternatively, you could try re-writing the query to use a corrolated subquery rather than outer joining or to use the minus clause if this is available in MySQL.
e.g.

insert into DataNew (CollectorCode,
             CollectComm)
      SELECT DISTINCT DataHistory.CollectorCode, 0
      from DataHistory
      where not exists (select null
                                             from DataNew
                                             where DataNew.CollectorCode=DataHistory.CollectorCode)
      and     DataHistory.CodeType = $DataCodeType
                and     DataHistory.Bonus = 0


insert into DataNew (CollectorCode,
             CollectComm)
               SELECT DISTINCT DataHistory.CollectorCode, 0
      from DataHistory
      where DataHistory.CodeType = $DataCodeType
                and     DataHistory.Bonus = 0
               minus
               SELECT CollectorCode,
                             CollectComm
                from DataNew      

0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22896805
Indexing the other side of the join made a huge difference.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question