Solved

finding query from the MYSQL SP

Posted on 2013-01-24
10
325 Views
Last Modified: 2013-02-28
Dear all,

  right now we have conflict here that developer is arguing where to write the SQL script, right now they are all doing teh SQL script inside PHP code as someone argue that it make the script/query easiler to find in case of problem of slow.

  But if all SQL query writen in store procedure (SP) itself, then once running in the trouble, it is hard to find out the mySQL script that write inside the SP. But doing the code in SP will give faster SQL result, right?

  Please share your experience on how to get rigt of slow in perofmrance of the MySQL in Applicatoin tier and the store procedure in the MySQL tier.


 Also any tools to found out if the MYSQL is the performance bottleneck.

DBA100.
0
Comment
Question by:marrowyung
  • 4
  • 3
  • 3
10 Comments
 
LVL 76

Assisted Solution

by:arnold
arnold earned 250 total points
ID: 38817473
The difference between SP and code within PHP is the control over the flow of data.
SP you provide it a set of inputs and it provides a previously defined response.

With the code being within PHP you control the flow within the code based on the information you retrieve.
i.e. a daily report of all transactions, it might be better to have it in an SP.

The issue often deals with the complexity of the data manipulation that is required and whether it is better for performance/responsiveness to have the data crunched manipulated within the mysql server scope (i.e. some data reformating/data type variances might not be possible) or provide the response to PHP and process the data there.


phpmyadmin is a good tool that provides info on mysql usage and suggestion on improvement of performance using mysql generated/collected statistics.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38817662
Right now the problem is, we don't know where the bottleneck is and from DBA point of view( I come from MS SQL background) that we should do all data programming in SP, then the script will be compile before execution, then much better.

The data transfer between network is then smaller, then therefore faster!

so if the Query is writen in SP in MySQL, there are no way for us to find it, right? we want to know which SP they come from as the new developer might pick up job from the old developer who was left.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38817797
There is a dismissing return for trying to create an SPfor every combination and every variant of possible data extraction.

In it is difficult to assign a value to on approach versus another in the abstract.

I.e. you have a set of table where you want data based on column1, another deals with column2.
0
 
LVL 33

Accepted Solution

by:
Slick812 earned 250 total points
ID: 38819486
greetings  marrowyung, , ,  many have found some speedup (faster) data retrieval by using stored procedure for some (but not all) Table data sets, but no matter what you use "PHP code" or  "stored procedure", , in Table access speed, I have found that correct and efficient column INDEXS do more to speed things up, than in stored or not. see these MySQL pages -
http://dev.mysql.com/doc/refman/5.0/en/select-speed.html
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38824147
Slick812,

This probably the case of MySQL, but for MS SQL, it is true that using SP is faster is everything is pre compiled. network traffice is minimized , etc.

I know tunning index is good, as far as my knowledge is about.

but my question still, if I create SP for all the MySQL query, anyway to quickly find out what SP this query is coming from ? if there is no way than we might still relies on the file search in the PHP tier. Then we change nothing and allow the application to hard to tune.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Expert Comment

by:arnold
ID: 38824164
The complexity is increased if every query must be an SP on the database.  SPs reduce flexibility in adjusting modifying criteria.

SP have their place, you have to determine whether a set of querys that is relied on by multiple PHP pages to display certain info.
This deals with whether you have a similar set of queries in multiple phps versus a single SP that is referenced by those same pages.
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38824411
If you have a "Static" or unchanging data-set from a query, as has been said, SP may be efficient and best for you, as far as "Speed" goes, there are many factors that are in effect for different queries and what the relationships for Tables (join and other) are, also for high traffic MySQL server pipelines, there are data server settings and hardware configures that can make tremendous differences in data server speeds for high traffic areas.
For Me, , The way to hammer out  the speed thing is to do speed tests for some things that you do, change it to another steps or procedures, and test again. There are some who specialize in Data Server set up and config for speed, that may can help, but again it may have many factors to deal with. Sorry if this seems non-helpful, but maybe a general "one size fits all" answer here will not be so easy. But in general I do believe that the stored procedures are faster in MySQL. But bad or inefficient queries are still crap even in SP.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38879020
Slick812,

" also for high traffic MySQL server pipelines, there are data server settings and hardware configures that can make tremendous differences in data server speeds for high traffic areas."

Sorry we are in CNY and in holiday, please provide no guide on customizing the MySQL server size.

someone told that the we can move data from one MySQL data volume to another to offload the data load speed from one partitition, is that right? someone told me before that it can't do it !

for exampl,e if later on we buy a faster hard disk, we can move part of the LARGE data to that volume only, is that right? how to do it ?
0
 
LVL 76

Expert Comment

by:arnold
ID: 38879042
/var/lib/mysql/highperformancedatabase can be a partition on this faster drive.

Though speed up benefits from larger availability of memory. In a comparison access to data stored in memory  is significantly faster than access on the hard drive no matter how fast.
0
 
LVL 33

Expert Comment

by:Slick812
ID: 38880931
you ask some about - " if later on we buy a faster hard disk, we can move part of the LARGE data to that volume",  I would say that it is possible, but you seem to think there are "One Size Fits All" ways (how to do it) for these sorts of things, and I do not think that is so, you would likely need to know the EXACT details of database and hard drive setup, and work out a solution for that system. This is not what I do (database optimization), but I have had to get someone with experience to do that for us, OR tell us what to do. Sorry for not having example, but that's just what I think.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now