I have the following stored procedure on MySQL. It's running perfectly the MySQL on my laptop but when I try to create it on my host server I am getting:
You have an error in your SQL syntax. check the manual that corresponds to yout mySQL server version for the right syntax to use near 'param_limit; END;
SELECT event_log.event_log_date_time, even' at line 5
this is the stored procedure:
IF (param_limit ISNOTNULL) THENBEGINSELECT event_log.event_log_date_time, event_log.event_log_title FROM event_log WHERE event_log.event_log_member_id = param_member_id ORDERBY event_log.event_log_date_time DESC LIMIT param_limit;
ELSEBEGINSELECT event_log.event_log_date_time, event_log.event_log_title FROM event_log WHERE event_log.event_log_member_id = param_member_id ORDERBY event_log.event_log_date_time DESC;
but how can i do this ,i mean use of sql profiler for compare performance between two query?
It's usually not very helpful to try and find "small" differences like those; there's also some intelligence in the server, and something called an optimizer. If your queries are slow, find out which ones are the mayor culprits. You can simply time them, by whichever means you want. Then, optimize those.
Also periodically check your server for the usual stuff that a DBA would check; are your indexes still good? Does the Wizard from the management studio agree with that? (I believe there's an index-wizard in Sql2008+) Do the procedures fetch unnecessary data (think "select *" when "select cola, bolb" would do), do any of the tables need partitioning? How about caching stuff?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
Thanks for the Advice - I agree but would have to re-write just about all the code in the app.
The app was initially never intended to have multiple locations/warehouses - this requirement came later.... FRUSTRATION!!! So I have a stock_master table with all the product codes, and then a stock_qty table, which has all the stock codes for all the locations. So the only unique identifier I have is the stock_qty.WH + stock_qty.Product. Not sure if it would help if I added some sort of ID to this table, because the history table and sales order tables cant really share the same ID. Not sure if there is a way to add table columns with the concatenation of the location + product and use this, and if this could be done by some sort of trigger or something in the database as opposed to re-writing all the code in the app.
I think I'm still stuck with getting these ugly queries to work.
Oh sh*t - sorry to hear that. You might try creating views with calculated unique fields, I know it comes down to the same thing but it may be easier to work with the key fields rather than the concats.
I would seriously look at a rewrite, you will spend more time pissing about trying to get a badly designed DB to work than you will on a rewrite. This I know, having done it a number of times and walked away from a contract where they refused to do the rewrite.
SQL Server will allow you to add an identity field to an existing table, getting the foreign key in place is a bitch. If you can add the keys you can progressively rewrite the DB using those instead of a complete break.
Never underestimate the power of human stupidity
Thanks Mycroft - I think I am going to go with your suggestion - re-write. Do you have any pointers you could offer to handle the same stock code in various locations? Should I use an Identity field, or use Location+StockCode as the PK? My starting point should be the stock_master table, which at this stage only has unique stock items. So I could write some code to duplicate all these items across all the warehouses, and let SSE assign an ID value? I could then do away with the stock_qty table, since all this has is a location, stock_code and qty, so I could just add qty to the stock master table. Then for BatchHistory, StockHistory, SalesOrders, PurchaseOrders, WarehouseTransfer tables, I guess I could also write code to lookup the ID in the stock master and populate an ID field
At the moment the app is using an MS Access DB which I am busy migrating to SSE because there are all sorts of problems with data not getting written to the database which I would assume could be due to
a) Poor coding
b) Concurrency problems with MS Access
c) Possibly a bad network
The company sells some pharmaceutical products which require batch traceability so I cant lose their data...
So yeah, sure as hell paying my school fees on this one!!! And as for MS Access, lets just say we are not very good friends at the moment!
modified 6-Apr-13 7:17am.
Last Visit: 31-Dec-99 18:00 Last Update: 24-Jun-18 7:02