Large loop of MySQL Queries

[Rohithzr]

ok here is a situation where i have to run (worst possible case for now) 5000 MySql Insert Into Queries for different customers in payments table.

now what is the best way and what info that i give might be helpful? I can tell you I cant escape from this. and i also have to first check that if there exists an entry in db for that date and customer (for checking sake as it is a cross platform web app)

any help appreciated.

[__]

i have to run (worst possible case for now) 5000 MySql Insert Into Queries

How much data are we talking about? How is it being submitted to your script?

I would probably write the query to a file and then run it directly in mysql. Is your database on a different server?

i also have to first check that if there exists an entry in db for that date and customer

You shouldn’t —the database should be able to check for you. If it can’t then you have a badly designed DB and should change it. That’s the whole purpose of primary/unique keys.

Large loop of MySQL Queries

Where is looping involved in this situation? You haven’t described anything that would require looping. (Unless you are dead set on doing every query individually.)

Can you explain your problem in more detail? It would probably be helpful to see your DB schema* and the relevant PHP code.

* you can use show create table `table_name_goes_here`

[__]

i also have to first check that if there exists an entry in db for that date and customer

Also, side note, but customers are not allowed to make more than one payment per day?

Even if this is the case, I would worry about checking the amount of the payment too, just in case someone tried to make two payments (I imagine they’d be pretty pissed if you only credited one of them).

[Rohithzr]

How much data are we talking about? How is it being submitted to your script?
Where is looping involved in this situation?

Not much of the data, I will be displaying a table of customers with due payment in the current month and through a checkbox the user will decide to either deactivate the user (with a due) or just due him out. hence a loop.
example query:

INSERT INTO  <code>newdcn</code>.<code>payment</code> (<code>pay_id</code> ,<code>cust_id</code> ,<code>op_id</code> ,<code>pay_amount</code> ,<code>pay_date</code> ,<code>pay_for</code> ,<code>pay_lineman</code> ,<code>pay_status</code>)
VALUES ('1',  '18',  '1',  '111',  '2014-05-14 00:00:00',  '2014-05-08',  '2',  '0');

checking the query!

well what i need to do is check whether a particular customer has done the payment in a particular month of a particular year for a max of a particular amount.

[Rohithzr]

I would probably write the query to a file and then run it directly in mysql. Is your database on a different server?

i have purchased a plesk v1 vps from bigrock and database is there only “localhost”, i have never written a query to a file before executing. will need help with that if that comes out to be a situation.

[Rohithzr]

db_schema
Your text to link here…

i thought this way would be better at least i can know if it is a good db schema

[__]

check whether a particular customer has done the payment in a particular month of a particular year for a max of a particular amount.

Are you checking to see if they’ve met a minimum payment?

Does this check comes when you are building the table to display (not when you process the form submission)?

vps …database is there only “localhost”

That actually simplifies things.

i have never written a query to a file before executing. will need help with that if that comes out to be a situation.

Just a text file with a query in it.
You’d run it (for example) via the command line:

mysql -u username-goes-here -p < name-of-file.sql

…then enter your password. You could write a shell script that automates this, so you don’t have to do it interactively.

The reason I suggested writing it to a file was the potential size: so php doesn’t run out of memory.

I will be displaying a table of customers …

If you’re doing that, it might be better to give each record its own form, and submit each via AJAX. That way, you could keep your scripts simple, don’t have to worry about memory or execution time, and, if your user gets almost to the end of the form and then someone trips on the power cord, they won’t have to start over from scratch.

i thought this way would be better at least i can know if it is a good db schema

uggh, I hate those diagrams… : )
Honestly, I find sql statements much easier to read.

It looks like you’re using foreign keys, which is good. Answer my question above (about what, specifically, you’re trying to figure out), and then we can think harder about this.

[Rohithzr]

Are you checking to see if they’ve met a minimum payment?

yes
Edit: Side note: it is possible for customers to do payment in two parts in a single month

Does this check …the form submission)?

it does, but i am worried about something like: one of the billing machine executes the payment after i display the data in the table. :( i’ll die there

If ….. and submit each via AJAX.

there will be a button to do “Deactivate All” and “Due All” [it is more likely that one of these will be pressed especially the later.]

uggh, I hate those diagrams… : )

i too never used them before really ….. but i thought it would be a good way to show the whole db structure and not just the payment table

[__]

it is possible for customers to do payment in two parts in a single month

That’s fine; you just need to SUM all of the payments for that customer+month. Something like (pseudocode):

select sum( pay_amount ) total_amount
from payments
where cust_id = ?
  and month( pay_for ) = ?
  and year( pay_for ) = ?;

i am worried about something like: one of the billing machine executes the payment after i display the data in the table. :( i’ll die there

under your current design, there are only two options:

• literally stop accepting payments during the time that payments are being tallied
• check the sum again when the record is updated, and throw an error if another payment has been made

Neither option is particularly attractive. If this is to be used across multiple platforms, it would be best to move the logic out of the application, and into the database: use stored procedures.

However, let’s take it one step further: is there a reason the totals need to be reviewed by an actual person? could they not be tallied and decided automatically?

Or, could the user decide the specific action to take (“deactivate” or “due”) for each user, and then allow the query to determine which users to update based on their total payments?

(Sorry for not being able to offer anything specific so far. There’s a lot of higher level figuring-out going on, here.)

there will be a button to do “Deactivate All” and “Due All” [it is more likely that one of these will be pressed especially the later.]

While I understand the user’s desire for such a feature, I strongly question the appropriateness in this situation. It’s just a shortcut for making mistakes, in a situation where mistakes should be avoided at all costs.

[Rohithzr]

(pseudocode):

ya that is what i’ll be doing, my point being that i cant use IF NOT EXIST :( would have been much easier

literally stop accepting payments during the time that payments are being tallied

not an option under current circumstances

check the sum again …. made

thats a good idea, and yes in case of a change i can force the customer to reload the table.

use stored procedures.

in the worst case scenario, i will be executing 90000 queries, thats not good. is it?

could they not be tallied and decided automatically?

totals are talied automatically, which of my line led you to think that a human will tally the total

could the user decide the specific … their total payments? and While I understand the user’s desire for such a feature,

mostly it would be like lets due them all and we’ll see who will we deactivate later.

side note: this software is for managing the records of our customer’s, customers and the purpose is “TV Cable Service” now you can understand what kind of population and load am i talking about

[__]

in the worst case scenario, i will be executing 90000 queries, thats not good. is it?

Well, you have to deal with the volume no matter what. I wouldn’t shove all 90k into a single transaction, no; but you will have to have the memory/ processing cycles/ storage/ bandwidth one way or the other.

Dealing with databases at scale is an art unto itself. Optimization and normalization, at every level, becomes much more important: often more important than the specific amount of data you’re moving with each transaction/ query.

Stored procedures are advantageous because, that way, you have a single point where all DB interactions are handled. You don’t have to worry about different platforms, or future code, doing things differently. Likewise, you don’t have to worry about atomicy (e.g., updating records based on outdated results) because the DB handles everything itself, internally.

It will also simplify your application code, and speed things up, because you don’t have to write/prepare/execute queries anymore. It becomes as straightforward as providing a list of arguments to a function call.

could they not be tallied and decided automatically?

totals are talied automatically, which of my line led you to think that a human will tally the total

I meant the decision part. How do your human employees make this decision? Payment history, amount past due, specific service plan…? If you can define the factors that should lead to each course of action —and with that many customers, I would hope you could— it could be much more efficient to automate it and provide a mechanism for human override (when needed).

mostly it would be like lets due them all and we’ll see who will we deactivate later.

…which would lend itself very well to an automated process like I described above.

this software is for managing the records of our customer’s, customers

Sorry, not sure if that is a typo or not: “customers” or “customer’s customers”? Not that it’s too terribly important to the issue at hand. Yes, it does give me an idea of scale.

Are you the company’s database administrator? staff web developer? contract web guy? I don’t mean to be blunt, but it sounds like you are quickly coming to the point where you will need a knowledgeable and experienced full-time DBA.

[Rohithzr]

I meant the decision part. How do your human employees make this decision?

well there is a “pack_id” column in customers table which determines the max amount. and yes it would be automatic with a human override

typo?

nope it was not a typo. our customers aka operators and then customers aka human population in large.

Are you the company’s ….

i am nearly everything this company has as it my own :) :D there is another person takes care of all the management and marketing and some times helps me in coding.

[Rohithzr]

Stored Procedure

so a humongous query executing at 2 am in the morning.

[Rohithzr]

I will need help in stored procedure.

[__]

i am nearly everything this company has as it my own :)

It’s your decision, then. :)

If you, as the owner, have a lot of other responsibilities, I would sincerely suggest you consider hiring a DBA.

I will need help in stored procedure.

Have you ever used them before? do you understand the concept?

Basically, you are making a function. You will have parameters (input/output, like arguments), and then the body of your function which actually does stuff. The body might have logic in it, or be nothing more than a typical query. If you don’t know anything about them, here’s a basic tutorial (scroll down; the numbered items at the top are advertisements).

[Author]

Posts