plautzer Posted October 22, 2009 Author Share Posted October 22, 2009 the question is rather if stored procedures are really help performing better... Ive read some articles where they say otherwise i.e.: http://www.karlkatzke.com/stored-procedures-vs-sql-calls/ So I am not sure to get into that at all. Did u have experience regarding peformance with stored procs? Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-941919 Share on other sites More sharing options...
Mchl Posted October 22, 2009 Share Posted October 22, 2009 If your stored procedure is going to do exactly same thing that your query does, you will not see any benefit (performance wise). However, if by using stored procedures, you could limit somehow number of rows you have to compare, then by all means you should try it. Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-941926 Share on other sites More sharing options...
kickstart Posted October 22, 2009 Share Posted October 22, 2009 Hi I would be tempted to have the stored procedure do the suggested SQL for a single id on any insert or update, but storing the details on a separate table. When you need your report it is just a simple extract from that separate table, which should be pretty fast. Overall this would be less efficient (as you are doing the calculations when not required), but it will spread that slightly greater load over a far greater time. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-941992 Share on other sites More sharing options...
plautzer Posted October 22, 2009 Author Share Posted October 22, 2009 Keith, I have some questions for ur last statement... I would be tempted to have the stored procedure do the suggested SQL for a single id on any insert or update, but storing the details on a separate table. What do u exactly mean by id and details? Are saying to separate the id and the rest of the parameter in seperate tables? Or do u mean by details the results (sum, type1, type2...) of the query which I would store in second table? When you need your report it is just a simple extract from that separate table, which should be pretty fast. Overall this would be less efficient (as you are doing the calculations when not required), but it will spread that slightly greater load over a far greater time. How does it spread the load? Thx, Plautzer Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-942007 Share on other sites More sharing options...
kickstart Posted October 22, 2009 Share Posted October 22, 2009 Hi Idea is that ANY update will also update the subset of rows on the 2nd table. The 2nd table just stores data used for your stats (basically duplicating some data). If this would work it would mean that rather than processing (say) 1m rows at once when you want the stats you instead process those million rows over hours / days / weeks as they are updated (quite possibly meaning several million reads and updates, but spread over quite a hours / days / weeks). However having just looked at what you need again I am not sure how easy this would be. Appears that almost any row being updated would affect every row. Anything which is doing fairly complex checks on millions of records is going to take time to run. What is the realistic number of rows on the table? I cannot see a more efficient way to give the stats you seem to want than we have already discussed. However, exactly what are the stats giving details of. You appear for each id to be getting a count of each of the Types of every different id. Not sure that is logical so I might have missed something. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-942057 Share on other sites More sharing options...
plautzer Posted October 22, 2009 Author Share Posted October 22, 2009 Hi, I think we are drifting a lil apart That’s my Process right now: I am adding 250 000 records to table A (in a loop for example). Every time when a new record is added to the table A, I want to seek thru the table A and count the records that somehow match the criteria of the added (new) record. (After that I want to store the result in a table B for example) Just to be clear… there are no later updates on the records in the table. But I want to repeat this process a couple of times with different records. Therefore I need the script to be fast. Otherwise it really takes ages So under that background I don’t understand how duplicating some parameters of Table A in another table would increase the performance. Greetz and thx Plautzer Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-942106 Share on other sites More sharing options...
kickstart Posted October 22, 2009 Share Posted October 22, 2009 Hi Ah, right. My misunderstanding. I thought you were extracting stats for a regular report. Are you not going to have issues that by the time you have added 250k records the similar details for the first records you added will be out of date? Or will you get the details of the similar records once all the records are inserted. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-942147 Share on other sites More sharing options...
plautzer Posted October 22, 2009 Author Share Posted October 22, 2009 yea, the first records will be most likely of the date range if I reach 100k... the data range is shifting as the records are added. And once the process is thru and deleting the data. Quote Link to comment https://forums.phpfreaks.com/topic/178228-optimizing-a-query-taking-upto-01s/page/2/#findComment-942159 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.