Solved

finding query from the MYSQL SP

Posted on 2013-01-24
10
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 78

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 78

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 34

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
 
LVL 78

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 34

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 78

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 34

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I post more than 1 item to php backend 24 60
Change Wording in Wordpress Plugin 4 39
Echo values after a query in php 5 51
Google Recaptcha 13 53
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

739 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