Mark A Matte
markamatte at hotmail.com
Mon Nov 24 14:44:45 CST 2003
Hello All, I also have used temp tables for some large returns...it was especially faster...when I created the table initially...and deleted/appended instead of recreating the table each time. This also gives the flexibilty of viewing the data in a few different ways quickly...instead of having to requery the db just to sort or something. Have a good day... Mark >From: "Mark L. Breen" <subs1847 at solution-providers.ie> >Reply-To: dba-sqlserver at databaseadvisors.com >To: <dba-sqlserver at databaseadvisors.com> >Subject: Re: [dba-SQLServer]Large recordsets >Date: Mon, 24 Nov 2003 16:31:22 -0000 > >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 > > > > > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Share holiday photos without swamping your Inbox. Get MSN Extra Storage now! http://join.msn.com/?PAGE=features/es