For anyone than suspects what may be the issue, this is the transcript of my conversation with Duane thus far:
From terence
OK, I created a 2 column table, col 1 is a PK, col 2 is Name, table exists in the SQL server.914 rows of data.
I did not use pass through, I went directly at the table.
In my MS access fe I linked to it, used the report builder to create a report using both columns, I did no alteration, no sort, no page break nothing.
ran the report and results back in 25 to 30 seconds wall clock time.
Is this what I should expect?
Terence
From Duane
Please include all significant information from previous posts in your replies. It's difficult to remember the previous messages and I would rather not have to visit the group web page.
The table looks fairly standard. How quickly can you scroll through all records when you view the SQL Server table in datasheet or view the pass-through in datasheet?
Duane Hookom, MVP
MS Access
From terence
Duane I did just now change the PK from float to Integer, this had no effect.
From terence
Duane, I just uploaded a file to the groups FILES, named if for_duane, it's a PNG of the table description.
terence
From terence
Duane , thanx for responding.
I am Not sorting, not using page breaks, the report comes out as 1 long page.
The table does not have an index , but it does have a PK defined as:
float, is this a possible problem, I can well define it as a integer.
As soo as I find out how to send an attachment via this forum I can send you a screen shot of the table design.
terence
any thoughts ?
FYI, this issue is prevalent through out everything I am doing, I am glad you gave me a baseline of what my expectation should be.
I Really need to resolve this.
Terence
From terence
FYI more info.
I also created an MS access database on out shared drive, I put the data table in there. Nothing else.
I then used the same report and linked it to this BE and received sub second responce time:
In conclusion:
Same report run 3 different ways.
When run in Ms Access with an embedded table, Sub Second response time for the report.
When run in Ms Access with a Linked table which is in another Access DB (NON SQL Server) on a shared drive, Sub Second response time for the report.
When run in Ms Access with a Linked table which is on the SQL Server, response time greater than 90 seconds.
Terence
From Duane
Do you have indexes on the table? Are you sorting the report? Are you using Page of Pages in the report?
Anything over seconds (especially with a pass-through) is too long.
Duane Hookom, MVP
MS Access MVP
When replying please include important content from previous messages.
From terence
FYI, also made a pass-through query to see if it would have any effect, again NO logic, grabs all fields, just wanted to see if my attempt to shift the processing to the SERVER would make a difference, the effect was about the same as the run times I depicted in my post.
From terence
Here is the scoop - I have been writing using ms access for many years. I have just started to use a SQL server as BE.
I have a Table about 900 rows. When I have it on the SQL server, and I use access automated Report designer, it generates a report.
This report has NO logic, no query, It juts grabs the table and reports it out.
When run using the table linked to the SQL server. I ran the report 5 times this way, the report returned in wall clock time as follows:
run1 87 seconds
run2 98 seconds
run3 99 seconds
run4 125 seconds
run 5 118 seconds
when I bring the table local (Not linked, therefore it is local to my access db. it runs in sub second.
Whats happening ?
terence
__._,_.___
Posted by: buffalome000@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar