Mark L. Breen
subs1847 at solution-providers.ie
Mon Nov 24 10:31:22 CST 2003
Hello Martin, I have done a few apps with very large numbers of records. One thing that I would suggest you look at is using temp tables, I have found that often times, one or two tables have the large numbers of records while the query consists of seven or eight tables. If you can make a temp table and insert some of the query into that, and then do the join to the temp table with the remainder of the query, you can get a 10 or 100 fold reduction in processing time. To be honest, for 50k records, three seconds like a long time, I know I do not know the data, but I have a query that I was working on recently with 3.5 million records and it executes in two seconds or so. What I have just said should not be the case, they query optimiser should do that job for us, but I have found it to work. Secondly, I have regular (daily or even twice daily) maintenance routines that rebuild indexes and perform minor repairs. Without these the database can get slow. Interestingly, I recently restored (well created on the fly) a 4.5 million record database to a SQL 7 server machine. The restore went OK until I tried to use another database on the server. It would not work properly from that point on. I then ran the maintenance routines on the new database and once it had ran, every thing else was OK. To summarise this, it seems that the databases are not totally isolated from each other. Even if they are isolated from a data security point of view, it appears that they share some of the server resources (well of course they do) and those resources can overlap in some way. You mentioned that you cannot filter, so I am presuming that means that you cannot list all the A-G records and then have a --more-- option. But if you have to load all records into a standard form, can you at least only load five thousand and when the user clicks 'move next' check to see if they are in the last 20% of the current qty of the recordset and if they are the load another five thousand, starting from where the user currently is. I realise that this is a complicated way of tackling it, but if you have some idea of the typical method of working, you might modify this idea slightly to incorporate the logic. Alan Cooper in his book ' The inmates are running the asylum' mentions that the bank machines take 5 - 8 seconds to some back and say that you have 301.45 in your account. He suggest that it should immediately say you have 'Around 300' and give you the choice to check exactly if you wish to wait 8 seconds for a details check. Additionally, could you retrieve the 50 k records and then only show one or two fields, but have a 'View details' that would they use the PK to retrieve the other fields from related tables. I think that mentioning these things to you is like teaching my granny how to suck eggs, but either way, I am interested in how you solve it as I have often consider the things above myself. Best Regards Mark L. Breen Ireland ----- Original Message ----- From: "Martin Reid" <mwp.reid at qub.ac.uk> To: <dba-sqlserver at databaseadvisors.com> Sent: Monday, November 24, 2003 8:51 AM Subject: Re: [dba-SQLServer]Large recordsets > Sorry > > 50 thousand records > > Using an unbound Access app and I need to bring all 50000 at one go to the > form. I cannot change this as all records have to be available. > > Takes 3 seconds to open and populate the form using a number of stored > procedures and recordsets. I was wondering if there was a faster way to do > this. > > > Martin > > > ----- Original Message ----- > From: "Erwin Craps" <Erwin.Craps at ithelps.be> > To: <dba-sqlserver at databaseadvisors.com> > Sent: Sunday, November 23, 2003 8:31 PM > Subject: RE: [dba-SQLServer]Large recordsets > > > > Martin, > > > > Your question is not very clear to me. > > What is it that you want to do? > > I don't see what the prob is with "only" 50.000 records. > > > > Erwin > > > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin > > Reid > > Sent: Sunday, November 23, 2003 9:03 PM > > To: dba-sqlserver at databaseadvisors.com > > Subject: [dba-SQLServer]Large recordsets > > > > I have to return perhaps in access of 50,000 records for use in Access. > > Linked to SQL Server 2000 > > > > What would be the fastest approach to take. > > > > Comments appreciated. > > > > Yeah I know the obvious, filter them first. But for the moment I am not > > allowed. to. > > > > > > Martin > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >