want to create new records from an existing record 2

[joeclueless2]

Hello,

First off, I am rusty as the tin man.

What I would like to do is add new records to a new table. These new records will be almost identical to existing records in another table. Each existing record that would be duplicated will hold all the values of the original, the challenge is to create one new record for each interval of 2 in an existing range that is represented by a [low] field and a [high] field in the original record. The record id would also be replaced with a new id.

The example only shows the concept, but there are many more fields in the data.

example:

In record: 100 198 Main St

Out records: 100 Main St
102 Main St
104 Main St
106 Main St
108 Main St
110 Main St
112 Main St
114 Main St
etc...
194 Main St
196 Main St
198 Main St

I appreciate any help

Thanks,

Joe

 

[joeclueless2]

Hello,

I was able to get option 2 to work, and it seems to have processed the records properly

One thing is that the process is pretty slow. I did modify the "I" query to include longer integers as shown:

SELECT I1.Num*10000+I2.Num*1000+I3.Num*100+I4.Num*10+I5.Num AS N
FROM Integers AS I1, Integers AS I2, Integers AS I3, Integers AS I4, Integers AS I5;

This seems to add quite a bit of time to the process.

Would any indexing or other steps help to speed things up? Right now I am testing it to process 12 records and it takes about 80 secs to run, with about 4550 records being output. I intend to run 80,000+ records that would result in millions of output records.

At any rate, it works for now!!

Thanks!!!

-Joe

 

[Golom]

Indexing is probably not going to help. The time is being burned by the cross-join. You may be able to help things out by running this

Select MAX(End_Hse-Beg_Hse) + 1 As MaxSpan From myTable

That should tell you the maximum number of values you need to provide.

Take that value and plug it into

Select N Into NT
From I
Where N <= MaxSpan

Now use NT in place of I in your query.

Your "I" query generates 100,000 records. If you need only (for example) 5,000 then you are producing (95,000 * the number of records in your input table) that are not required and are filtered out by the WHERE clause.

I did a small test

I generated 5,960 records in 14 seconds using the full 100,000 version of the query. I ran the first query (above) and it said that I needed only 3,399 values. I then ran the second query specifying MaxSpan as that value. After changing my source from the query "I" to the table "NT" it generated the same 5,960 records in about a second.

 

[joeclueless2]

Thanks!

I took the part:

Select N Into NT
From I
Where N <= MaxSpan

and named it query "NT". After running:

Select MAX(End_Hse-Beg_Hse) + 1 As MaxSpan From myTable

to determine the maximum span of numbers, I put that number into the "NT" query in place of "MaxSpan" (BTW it was 800 for the test set).

Then I only replaced "I" with "NT" in the FROM clause. (Is that right?)

I ran it and it took about 8-10 secs.

Thank you AGAIN!!!!

-Joe

 

[Golom]

Then I only replaced "I" with "NT" in the FROM clause. ([red]Is that right?[/red])

Not exactly. You need to replace "I" with "NT" everywhere

SELECT M.OBJECTID_1, M.SIDE, 
       IIF((SP(BEG_HSE,SIDE)+[red]NT[/red].N) MOD 2 = 0,"E","O") AS NSIDE, 
      (SP(BEG_HSE,SIDE)+[red]NT[/red].N) AS HSE

FROM myTable AS M, [red]NT[/red]

WHERE SP(BEG_HSE,SIDE)+[red]NT[/red].N<= M.End_Hse
   AND ( (SP(BEG_HSE,SIDE)+[red]NT[/red].N+ IIF(SIDE="O",1,0)) Mod IIF(M.SIDE="B" ,1,2) = 0
             OR SP(BEG_HSE,SIDE)+[red]NT[/red].N=  M.End_Hse)

ORDER BY 1, 4;

You created some of this problem for yourself by adding

[blue]I1.Num*10000+I2.Num*1000+I3.Num ...[/blue]

to your query. Just

[blue]I1.Num*100+I2.Num*10+I3.Num[/blue]

would give you 1,000 (i.e 0-999) numbers which was more than you needed. By using the NT table route you get exactly the number of records that you need, thus reducing the impact of the cross join as much as possible.

Another trick that may reduce it further might be something like this.

If you had a record

[pre]
OBJECTID_1 SIDE BEG_HSE END_HSE
3625 O 1 1000[/pre]

That would require 1,000 numbers. If instead you used

[pre]
OBJECTID_1 SIDE BEG_HSE END_HSE
3625 O 1 500
3625 O 501 1000[/pre]

You get the same output but you need only 500 numbers. I can visualize an approach that would automatically split records like that but that's probably overkill at this point.

 

[joeclueless2]

Golom,

Thanks again for all the help!!

-Joe

 

[Golom]

You're welcome.

Since I have no life, I spent part of the weekend looking at this problem and came to a couple of conclusions.

- You can get a similar speed improvement to the NT table that I proposed just by using a query. For example

SELECT I3.Num*1000 + I2.Num*100 + I1.Num*10 + I0.Num*1 AS N
FROM Integers AS I3, Integers AS I2, Integers AS I1, Integers AS I0
WHERE [I3].[Num]*1000 + [I2].[Num]*100 + 
      [I1].[Num]*10 + [I0].[Num]*1 <= [red]SomeSpanValue[/red]

Where [red]SomeSpanValue[/red] is the maximum number of numeric values that you need.
Using this approach I was able to generate 61,913 records in 5.250 seconds.

- Splitting the records to reduce the span has a negligible effect on speed. The reduction in the number of numeric values is offset by an increase in the number of records in your source table.

Good luck with your project. Drop by again if you have more questions.