Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Angry Unanswered: Cursors in Access DB

    Can any one help me on this

    I have a Access Database on which complex Reports are being generated thru and Application written in VB6, the problem is Speed, right now I am using views and Crystal Reports for the same but the speed is too slow,
    althoug I have a way of speeding it up, but for that I think I'll need something like Cursors, which we use-to use in SQL Server.


    If any one has a solution or Suggesstions please send.


    Best Regards


    Ammar Sulaimani

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - cursors are typically a way that you bring SQL Server to a crawl - they virtually never result in a performance gain (there is the odd exception). The VBA equivalent of a cursor is a recordset.

    What is the data stored in (Access, SQL Server, Oracle)? What are you using to generate reports (Access, Crystal etc)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To speed up reports on access tables, you need to look at how you have things set up. Some things to look at:

    1. Are the tables indexed? Indexing fields you sort/group on will tremendously help with speed.
    2. How many tables are linked in the query the report is based off of? (and how much data are you dealing with?) For example, having 10 linked tables in the query will produce a slow query/report (especially if any of the tables are joined by 2 fields (clustered join).) What I've done in the past to speed up reports where I had multiple, multiple joined tables was break it down. It actually speed things up to run some queries which reduced the recordsets returned by creating temp tables and then link to those temp tables with other tables. I made a procedure which ran when the user requested the specific report filtering specific data. Although it still took a few seconds going through the procedure of creating the temp tables, the overall time was reduced verses having all the tables linked into 1 query.
    3. Also, although this is arguable, I would store some data reduntantly (yes I broke the rules of normalization). But having a "grouping" type field of data in the main table helped with queries so I wouldn't have to link in the relational table for the queries. I was dealing with several million records and linking an additional relational table in a query meant slower queries. I thus not only stored the ID number of the relational table in the main table but also stored the text field with it (I kept the relational table for design purposes of data entry). This though is a design decision where you have to weigh how fast you want your data returned verses staying with the rules of normalization. Obviously having 1 big flat table does not follow the rules of normalization, but it does produce faster results with queries (and flexibility) when you need to sort and group on multiple fields as you pay a penalty for each relational table you have linked into a query. Weigh this option carefully though with your design.
    4. If you're using Access tables, seriously consider upsizing to SQL Server. I won't go into all the benefits but it does make your life soo much easier.

    I wouldn't rely on cursors being the answer to speeding up your reports. I think you'll find that you're most likely opening a new can of worms and the results won't necessarily satisfy your want for speeding up reports. I would instead think about how you can design your tables/fields to minimize the number of tables you need to link into a query by what fields are mostly grouped and sorted on.

    Rambling on...

    Even though it's also arguable, but are you doing a lot of calculations to produce the query results? Storing calculated fields in a table is argued by many as a no-no but if you are totalling millions of records and summing, averaging, etc... on all those records, this can be a time killer for reports on massive records. I had to calculate total kilowatts saved on energy savings for appliances. I had a lot faster reports by storing the total kilowatts saved with the appliance record verses storing only the aspects of the equation and doing the calculation to produce total kilowatts saved when the reports ran. Storing the summed calculation verses cursors to me is a no-brainer but I don't want to start a debate on it (perhaps when I'm healthier).

    SQL Views are great and I utilized them a lot. Although I'm not a fan of Crystal Reports, I can't help to wonder though if you might achieve some faster results with Access reports. From the little experience I have with Crystal Reports, I've often wondered if there isn't some overhead with Crystal Reports which you wouldn't necessarily have with Access reports. There wasn't a single thing I wasn't able to accomplish with Access reports that I could with Crystal Reports but I'm guessing some advanced Crystal Report designers would argue that point.

    Sorry about the long reply here but I spent a lot of time figuring out ways to speed up my queries and reports. I even went through the trouble of rebuilding all my indexes (I used SQL Server). Indexing, restructuring the way data was stored, and cutting out time-consuming calculations was ultimately the best way I found to speed up any reports (regardless if they were Crystal or Access) but there are also so many other contributing factors such as network speed connection, memory, etc.. which all play into the equation of which I'm guessing you've already looked into. Seriously though, consider cursors as one of your last resorts if even considering it at all (just from my perspective.)
    Last edited by pkstormy; 03-04-08 at 13:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2008
    Posts
    4

    Cursors in Access DB

    Quote Originally Posted by pootle flump
    Well - cursors are typically a way that you bring SQL Server to a crawl - they virtually never result in a performance gain (there is the odd exception). The VBA equivalent of a cursor is a recordset.

    What is the data stored in (Access, SQL Server, Oracle)? What are you using to generate reports (Access, Crystal etc)?
    I am using Access, Crystal Reports ro generate reports, but I have found out a way to speed up the reports by processing only the required data but i have to use temporayr tables, which creates issue of multiusers not more that one user can use the report correctly, suggest what do I do.

  5. #5
    Join Date
    Mar 2008
    Posts
    4
    Quote Originally Posted by pkstormy
    To speed up reports on access tables, you need to look at how you have things set up. Some things to look at:

    1. Are the tables indexed? Indexing fields you sort/group on will tremendously help with speed.
    2. How many tables are linked in the query the report is based off of? (and how much data are you dealing with?) For example, having 10 linked tables in the query will produce a slow query/report (especially if any of the tables are joined by 2 fields (clustered join).) What I've done in the past to speed up reports where I had multiple, multiple joined tables was break it down. It actually speed things up to run some queries which reduced the recordsets returned by creating temp tables and then link to those temp tables with other tables. I made a procedure which ran when the user requested the specific report filtering specific data. Although it still took a few seconds going through the procedure of creating the temp tables, the overall time was reduced verses having all the tables linked into 1 query.
    3. Also, although this is arguable, I would store some data reduntantly (yes I broke the rules of normalization). But having a "grouping" type field of data in the main table helped with queries so I wouldn't have to link in the relational table for the queries. I was dealing with several million records and linking an additional relational table in a query meant slower queries. I thus not only stored the ID number of the relational table in the main table but also stored the text field with it (I kept the relational table for design purposes of data entry). This though is a design decision where you have to weigh how fast you want your data returned verses staying with the rules of normalization. Obviously having 1 big flat table does not follow the rules of normalization, but it does produce faster results with queries (and flexibility) when you need to sort and group on multiple fields as you pay a penalty for each relational table you have linked into a query. Weigh this option carefully though with your design.
    4. If you're using Access tables, seriously consider upsizing to SQL Server. I won't go into all the benefits but it does make your life soo much easier.

    I wouldn't rely on cursors being the answer to speeding up your reports. I think you'll find that you're most likely opening a new can of worms and the results won't necessarily satisfy your want for speeding up reports. I would instead think about how you can design your tables/fields to minimize the number of tables you need to link into a query by what fields are mostly grouped and sorted on.

    Rambling on...

    Even though it's also arguable, but are you doing a lot of calculations to produce the query results? Storing calculated fields in a table is argued by many as a no-no but if you are totalling millions of records and summing, averaging, etc... on all those records, this can be a time killer for reports on massive records. I had to calculate total kilowatts saved on energy savings for appliances. I had a lot faster reports by storing the total kilowatts saved with the appliance record verses storing only the aspects of the equation and doing the calculation to produce total kilowatts saved when the reports ran. Storing the summed calculation verses cursors to me is a no-brainer but I don't want to start a debate on it (perhaps when I'm healthier).

    SQL Views are great and I utilized them a lot. Although I'm not a fan of Crystal Reports, I can't help to wonder though if you might achieve some faster results with Access reports. From the little experience I have with Crystal Reports, I've often wondered if there isn't some overhead with Crystal Reports which you wouldn't necessarily have with Access reports. There wasn't a single thing I wasn't able to accomplish with Access reports that I could with Crystal Reports but I'm guessing some advanced Crystal Report designers would argue that point.

    Sorry about the long reply here but I spent a lot of time figuring out ways to speed up my queries and reports. I even went through the trouble of rebuilding all my indexes (I used SQL Server). Indexing, restructuring the way data was stored, and cutting out time-consuming calculations was ultimately the best way I found to speed up any reports (regardless if they were Crystal or Access) but there are also so many other contributing factors such as network speed connection, memory, etc.. which all play into the equation of which I'm guessing you've already looked into. Seriously though, consider cursors as one of your last resorts if even considering it at all (just from my perspective.)
    Thank you for such a quick reply,

    The whole scenario is this:-

    I have an Access Database and I am using view in the crystal reports to generate the Reports, But the processing was very slow as when I use views it first picks up all the data and then proccess it and then the data use to get filtered in crystal reports, a lot of waste of time, So I made temporary tables and dumped the filtered data in those talbles and then passed it to Crystal Reports, but by doing this have another problem, that now only one user is able to genrate the correct reports as, their is a single table which multiple user would be using so, you can understand the rest, and thats why I need either may be a cursor or some other way. I hope you've got my point

    Thanking you
    Regards
    Ammar Sulaimani

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ammar.sulaimani
    I am using Access, Crystal Reports ro generate reports, but I have found out a way to speed up the reports by processing only the required data but i have to use temporayr tables, which creates issue of multiusers not more that one user can use the report correctly, suggest what do I do.

    deploy the application as a front end (containing the UI, forms reports etc...) and back end to store data.... (if you are using JET)

    deploy a front end on the users remote workstation.. each user has their own front end, so the local tables should will be local to that PC. it does mean you have to do a bit more work to make sure the front end is kept up to date. but there are ways round that.. look in the code bank or do a google

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •