Solved

finding query from the MYSQL SP

Posted on 2013-01-24
10
327 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 77

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 77

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

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 77

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

919 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

16 Experts available now in Live!

Get 1:1 Help Now