No such thing as a small change | |
PerlMonks |
Re: oracle temp table does not existby roboticus (Chancellor) |
on Dec 01, 2021 at 16:37 UTC ( [id://11139297]=note: print w/replies, xml ) | Need Help?? |
I don't have a database server installed handy, so I can't really help directly with your question. But with the example code you listed, I just wanted to offer a couple suggestions: You might consider using placeholders in your prepared statements, as that is well-tested and can help you avoid injection attacks. Something like this:
Next, you ought to leverage the database and let it do more of the work. The database is optimized for large sets of data. The cost of serializing the result of the select and shipping it to your computer, then having your computer deserialize the data handle it and then turn it into a sequence of SQL statements, serializing each one and shipping it back to the database server (who would have to deserialize each of the statements and then process them and perform the inserts) costs *vastly* more time, CPU and network bandwidth than simply telling the database server to insert the rows into your database. Something like this:
Finally, when working with SQL, I suggest you structure your statements slightly differently. Using the longer join syntax lets you tell the database how to connect the tables together, freeing the WHERE clause to specify which records to select. That helps future programmers more easily see what the statement is doing:
The more complex the statement, the easier it is to express the structure and make the SQL easier to comprehend. Here's a purely artificial example with multiple joins against tables in a single statement:
Yes, it's an overly-contrived example, but it easily lets you separate "standard" table joining from the important selection criteria. When you stuff it all in a WHERE clause, it's difficult to sort it all out in your head. There's *no* difference at all to the SQL server, but it's a world of difference to the people who have to figure out what the statement says with a minimum of effort. ...roboticus When your only tool is a hammer, all problems look like your thumb.
In Section
Seekers of Perl Wisdom
|
|