ReneS
Posts: 225
|
| Posted: 10/09/2006, 10:53 AM |
|
Hi,
Just getting back in there...
Making a report based on a view in sql server 2000. A simple one, 4 fields, no sorting, no calculations etc. Page loads in 30+ seconds.
Made a grid with same view, same fields etc. Page loads in 2 seconds.
Big difference for almost the same output...
Can anyone tell me why? Because I tried the reportbuilder with a 1.7 million records sqlserver2000 and output has yet to come (since yesterday)
Hope you can help,
Thanks,
Rene S
|
 |
 |
Rene S
|
| Posted: 10/09/2006, 12:14 PM |
|
Hi,
Investigated a little more... It also does not work (time out) on just one table (so not a query).
(no calculations, no sorting)
Grid works fine......
Rene S
(ASP/XPPRO/IIS6/MSSQLSERVER2000)
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/09/2006, 2:28 PM |
|
Rene
I have no solution, just a few questions:
The Grid that loads so much faster, how many rows on a page?
Have you tried the report on the DB using straight SQL, of course performing the same query as originaly used to build the view?
Motivation for my Q's, a Grid will traditionally show a certain number of rows, your report has to report all 1.7 mio rows, which would take more time. The Grid will load the (LIMIT) number or rows and await next/previous page command.
The report on the DB using straight SQL, with proper index usage would probably perform better than on a View (BTW statis View or dynamic), View could be dependant on DB Memory Storage Pages.
How fast is a SQL SELECT 'field' from Table, on the total 1.7 rows in CLI?
Walter
Hope to get answers for myself and (sneaky) stimulate the thoughts process at your end.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Rene S
|
| Posted: 10/10/2006, 2:07 AM |
|
Hi Walter,
Just tested a grid with 500 rows on a page, no sorting and just the "ID" field (from a table, not a view) as output: 4 seconds.
A report with only that "ID" field (from a table, not a view), no sorting, 40 lines per page: time out..
Also checked windows logfiles: SuperSocket info: (SpnRegister) : Error 1355. (SQL server) after I tried to view the report. When viewing the grid there is no such message. Could not find anything yet about that on the microsoft site.
So probably my question is also: does the reportbuilder "call" on sql server differently then the gridbuilder?
Rene S
|
|
|
|
Rene S
|
| Posted: 10/10/2006, 2:12 AM |
|
Found something from Microsoft, not a real problem:
When SQL Server starts on a computer that is running Microsoft SQL Server 2000 or Microsoft SQL Server 2005, the SQL Server program always attempts to register the virtual server in the Active Directory. The following event may be logged in the event log:
SuperSocket info: (SpnRegister): Error 8344 SuperSocket Info: (SPNRegister) : Error 1355 SuperSocket info: SpnUnRegister() : Error 8344.
NoteError 1355 is equal to ERROR_NO_SUCH_DOMAIN. Error 8344 is equal to insufficient permissions to perform the registration operation. This is shown as a warning for the SPNRegister function and as an error for the SpnUnRegister function.
This message is not an error message. This text is only a warning that SQL Server cannot register a service principal name (SPN). This indicates that the security mechanism that will be used is Microsoft Windows NT Challenge\Response (NTLM) authentication instead of Kerberos authentication.
These messages should only be considered a problem if your SQL Server installation requires Kerberos authentication. Otherwise, these messages can be ignored safely.
So that cannot be it.....
High Regards,
Rene S
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/10/2006, 3:16 AM |
|
Rene
I would advice you to report this to support.
I am experiencing a rather similar situation on MySQL (XAMPP)
But I am still trying to find out what is making this happen.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Rene S
|
| Posted: 10/10/2006, 4:10 AM |
|
Hi Walter,
Ok, but I'm not a pro, so I hope they keep it a little "simple" for me.
I'll report it tonight, see what happens, and I'll post it here.
Rene S
|
|
|
|
Rene S
|
| Posted: 10/11/2006, 8:48 AM |
|
Hi,
The answer I got from support was: " Hello,
Actually the report retrieves all possible records in a recorsdet without using TOP/LIMIT. It is necessary for valid information grouping and different reports calculations like summing, calculating average values, etc. It is behavior by design.
The solution is to restrict the recordset using WHERE parameters"
So that is why it is taking so long. However, if I do this without grouping, calculations, sorting, just "plain" data, it still takes too long......
Rene S
P.S. In Crystal reports the same action takes about 14 seconds.....
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/11/2006, 12:04 PM |
|
Based on that response I'm guessing that you will need to narrow down your putput regardless whether you're using groupings or not. You shouldn't expect the lack of groupings or sorting to work differently since the answer was that reports retrieve all records.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Rene S
|
| Posted: 10/11/2006, 12:28 PM |
|
"You shouldn't expect the lack of groupings or sorting to work differently since the answer was that reports retrieve all records."
You are right, however it should not take more than 30 seconds to retrieve all records. I hoped that since it takes Crystal reports about 14 seconds, CCS would take about the same time... Well just have to find the best way to restrict the number of records.
Rene S
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/11/2006, 12:38 PM |
|
I suspect that Crystal doesn't have grid component and is using different approach based on situation, thus without groups possibly its performance is similar to CCS grids. (?)
BTW, do you need to use reporting component without groups, or could you use grid instead? Just curious.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Rene S
|
| Posted: 10/11/2006, 1:25 PM |
|
The reporting without groups was to test to see if I could get any data dispayed with the reportwriter, ofcourse I would like the reports with grouping, calculating and all. Now just make sure that the data returned is limited.
Thanks,
Rene S
|
|
|
|
Wkempees
|
| Posted: 10/11/2006, 3:25 PM |
|
Rene,
Unfortunately I have to agree with Peterr and Support, we are trying to
report all rows, which does/could indeed take to long.
Narrowing the result set is an obvious approach.
A report with a Search, would have to use the initially empty-result-set
approach to even give a user the option to filter data into the report.
Walter
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/11/2006, 4:24 PM |
|
Well, I suspect that not everyone is "trying to report all rows" and theoretically a report could retrieve only partial data per page. However, I suspect that this is either difficult to automate or not possible with most reports, because for example if groups are used then the database itself may need to retrieve all records to find all available groups and sort them. And I'm guessing that the report feature assumes that this is always OK, without trying to determine if groupings are used, or if the LIMIT feature could be used in some cases.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Lk
|
| Posted: 10/18/2006, 2:42 AM |
|
finaly i can find this isue in this forum, i think css must fix this problem because i also find this problem and what i'am asking is with millon of data and i make a query to get the data from query analizer ms sql only 2 seccond , and after i made the same query in report builder it can't display ? but after i done something it display but longger than query about 25 second.
but not all report i can't fix i still find whats wrong. note : it only happen with big data only.
i am not using group and with complex calculation and make report like cube but manual with query and 3 level subquery.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/18/2006, 4:14 AM |
|
Lk,
Finding issues on this forum is best done using the "Search" button on the top menu of the forum.
It uses text search on multiple words.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/18/2006, 5:17 PM |
|
Ok, so I still owe you all the solution to the report problem.
First:
The underlying table (as far as needed for this topic) is 6003506 rows.
There is only a primary key on 'id'.
The fields used are id, lastname, postal_code and city.
All is run on one machine, both CCS and XAMPP.
The purpose is to create a search + report, search on lastname, postal_code and city all in the 'containing' LIKE % % version.
This is not a real live situation it was done as a test on Rene S's topic.
In a nuthell generating the search/report using the builder results in a report that will generate a blank page for as long as it needs to select all 6mio rows, then it will display the Search form as well as a paginated first page of the report.
It is a natural reaction to expect the page opening with the Search, await input and then run the report, however things work a bit different.
As stated earlier the comparison with the grid loading faster is explained by the grid implementing LIMIT function as opposed to the report that cannot do this probably because of the grouping totalling and other possible calculations.
Now how to get the report to do an acceptable job.
The Search and Report resulting from the build needs to be 'limited' in it's actions.
After building, open the reports datasource in VQB (pressing [...]).
The Where clause generated
lastname like %{s_lastname}% OR
postal_code like %{s_postal_code}% OR
city like %{s_city}%
is what we need,
to limit the report when (initialy) no search parameters are entered we set the
"is NULL" on each of the individual lines in the Where clause.
Now the report is "limited" and will display as fast as it can, enabeling us to enter Search parms.
with the search parms entered things are swift, a search all blank is however not allowed and not recommended.
The things I missed in this excercise and might have to be posted to the Wishes forum:
A "building report" message, displaying straight up, possibly even displaying progress.
In case a Search form is present, initially display the Search without running the report instantly without the need of altering the DataSource settings.
Timing on this setup will follow.
Final remark, no database optimizing done, just a straight test.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
DomIns
Posts: 3
|
| Posted: 11/07/2006, 6:26 AM |
|
Hi,
I have a MySQL database with approx. 29,000 records (rows).
I created a simple report using the report builder without any grouping or calculations. I also have included only the Search functionality in the report.
When I run the report, it timeouts and does not display any records. I notice the following message in the apache log: "PHP Fatal error: Maximum execution time of 60 seconds exceeded in C:\\wwwroot\\test.com\\test\\Common.php on line 1147
I increased the maximum_execution_time from 30 to 300 and it still timed-out.
Why can't the report display all 29,000 records.
Thanks,
Dominic
|
|
|
|
WKempees
|
| Posted: 11/07/2006, 7:12 AM |
|
Because your timeout period is not set high enough.
http://php.snippetdb.com/view.php?ID=79
http://nl2.php.net/set_time_limit
"DomIns" <DomIns@forum.codecharge> schreef in bericht
news:24550978671ec3@news.codecharge.com...
> Hi,
>
> I have a MySQL database with approx. 29,000 records (rows).
>
> I created a simple report using the report builder without any grouping or
> calculations. I also have included only the Search functionality in the
> report.
>
> When I run the report, it timeouts and does not display any records. I
> notice
> the following message in the apache log: "PHP Fatal error: Maximum
> execution
> time of 60 seconds exceeded in C:\\wwwroot\\test.com\\test\\Common.php on
> line
> 1147
>
> I increased the maximum_execution_time from 30 to 300 and it still
> timed-out.
>
> Why can't the report display all 29,000 records.
>
> Thanks,
>
> Dominic
>
>
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
