SQL Server Performance Forum – Threads Archive
Problem with query large recordset (ASP)
Hi,I’m having problem with query large recordset from MSSQL2000. The situation is we have tables generated by Peoplesoft and we need a few reports that count number of employee and their salary from user’s specified range of time (from date- to date) and organization level. After we call the SQL statement through ASP page, it takes ages to see the result and sometimes the page just disconnected. Please advise on how to manipulate large recordset or is there any way to increase query performance. (Does anyone work with Pepplesoft man power report before?) Thank you ka.
I have not worked with peoples soft man power report before.
But to increase the performance you need to tell us the table format.
after analysing table structure we can suggest some techniques.
Do you can the control over the table structures. if so you can use index tunning wizard to get some recomendations.
quote:Originally posted by monaliza_bk
Hi,
I’m having problem with query large recordset from MSSQL2000. The situation is we have tables generated by Peoplesoft and we need a few reports that count number of employee and their salary from user’s specified range of time (from date- to date) and organization level. After we call the SQL statement through ASP page, it takes ages to see the result and sometimes the page just disconnected. Please advise on how to manipulate large recordset or is there any way to increase query performance. (Does anyone work with Pepplesoft man power report before?) Thank you ka.
I’m having problem with query large recordset from MSSQL2000. The situation is we have tables generated by Peoplesoft and we need a few reports that count number of employee and their salary from user’s specified range of time (from date- to date) and organization level. After we call the SQL statement through ASP page, it takes ages to see the result and sometimes the page just disconnected. Please advise on how to manipulate large recordset or is there any way to increase query performance. (Does anyone work with Pepplesoft man power report before?) Thank you ka.
How about indexes on the tables that are involved in these queries and also check the query execution plan for further assessment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
About the table structures, since they were created either internally by Peoplesoft or by our Provider (by customization). However, the provider provided us a View (Manpower View – we called) that has following sample fields: DEPTID -> Define organization unit (10 digits)
EMPID -> Define an employee
ACTION -> Define situation of work such as terminate, transfer
ACTION_DT -> Define date for specified action
ACTION_REASON -> Define reason for this action
PL_GROUP -> Define Personal Level (Group)
PL -> Define Personal Level
EDUCATION_LVL -> Define Education Level
and other more fields. 1 Employee can have many rows in this view since he/she can have many action counting from start of work records. Sadly, this view works (when calling – result pane showed) only on Test server which has about 50% of amount of data of Prodution server. Out provider claimed that they could not have this view tested on Production Server because of security issue (I do not know why they have authority over my company’s security) But it turned out that somehow, we could not get them to work on this view over Production Server. The view composed of 10 tables and 2 more views. My provider also told me that they had already provided indexing over necessary fields (I’m not sure whether to belive them or not). And to change the view or make change (such as give index), we do not have permission to do so as well. (Must ask provider to do). Come to this point, I have created a few assumptions with lots of doubt in them. Perhaps you could spare me of wasting time from doing something that would not help cease the situation. Assumption1: I found that View makes query performance slower. Should I, instead of use the view (created by our Provider), use the SQL Statement to make relationship between necessary tables and plus our user’s filter at the same time? Assumption2: I do not know much about indexing service but after read through this website, it seems to help query performance. Which fields do I need to have them indexed? Assumption3: Does MSSQL Client Tool (ActiveX or DLL) that we can bundle in a COM works with query faster than script query such as ASP through ODBC? If so – I can create ActiveX component and bundle the driver with the package to let user download before retrieving the report. Assumption4: Our report has 2 axis
Y – Organization Level (Unit1, Unit2, Unit3)
X – Employee Type (Manager, Operation, …)
The method that previous programmer used was making query (count (*) to get number of person and sum(salary) to get summary of salary of all person) for each Unit1 and Employee Type (as 1 query). And make the same query on every Organization Level and Employee Type. Then write the result to another TMP table on separate server. Personally, I don’t think this is right because you have to make too many queries, perhaps more than necessary and plus have to involve disk work. (The process just get cut off in the middle of something) I still don’t have other idea yet to improve this method. Thanks a lot for your previous comments
]]>