Hi Stephen, Nice work.
Regards Leigh http://www.salenz.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Stephen Bertram Sent: Tuesday, May 25, 2004 8:08 AM To: NZ Borland Developers Group - Delphi List Subject: RE: [DUG] Dealing with large amounts of data - comparing Hi Steve We've come up against this several times with large datasets (>20M rows) and have found the simplest and fastest method by far is to transfer one set of data to a temporary table, delete on a join to the other set and you are left with the difference. In MSSQL: SELECT DISTINCT RefOfInterest INTO #tmp FROM MyTable WHERE Date BETWEEN @LowDate1 AND @HiDate1 DELETE FROM a FROM #tmp a, MyTable b WHERE a.RefOfInterest = b.RefOfInterest AND Date BETWEEN @LowDate2 AND @HiDate2 SELECT b.* FROM FROM #tmp a, MyTable b WHERE a.RefOfInterest = b.RefOfInterest AND Date BETWEEN @LowDate1 AND @HiDate1 DROP TABLE #tmp This reduced our "not exists" query from 2 days to 3 seconds. HTH Stephen -----Original Message----- From: Steve Aish [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 May 2004 2:31 p.m. To: [EMAIL PROTECTED] Subject: [DUG] Dealing with large amounts of data - comparing Hi y'all, I have a database that records approx 500,000 records per week in one table. I want to compare week 1 and week 2 for example to see which of a certain field are in week 1 but not in week 2. I am using Interbase. I thought of running a query (until my pc ran out of hard drive space with the temp file size). I thought the easiest (although probably not the best) way would be to fill in a stringlist (I am only worried about 1 field) with week 1 and then fill another stringlist with week 2 and then compare them. I just open a query with one field in it and iterate through the query and List.Add(Query1.FieldByName('Field').asString); This runs out of memory after 200,000 records or so. It is not the string list running out of memory - it is the Query1... ??? Confuses me. Any ideas? _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi _______________________________________________ Delphi mailing list [EMAIL PROTECTED] http://ns3.123.co.nz/mailman/listinfo/delphi
