Your skill will accomplishwhat the force of many cannot PerlMonks

Re^3: For a better efficiency and speed question!

by pvaldes (Chaplain)
 on Oct 04, 2011 at 15:49 UTC ( #929583=note: print w/replies, xml ) Need Help??

... two times, your sql is malformed

```=code
my \$sql = \$dbh->exec_select("SELECT acc,name,date FROM mytable WHERE m
+ame<>''

and same problem after the my \$control_num line

Replies are listed 'Best First'.
Re^4: For a better efficiency and speed question!
by pvaldes (Chaplain) on Oct 04, 2011 at 16:08 UTC
AND ((date + 125 DAYS) < CURRENT_DATE)

The same as this but more easy to calculate

AND ((date + 1 YEAR) < (CURRENT_DATE + 240 DAYS) )
Re^4: For a better efficiency and speed question!
by Anonymous Monk on Oct 04, 2011 at 17:17 UTC
What is wrong with this code?
```=code
my \$sql = \$dbh->exec_select("
SELECT acc, name,date
FROM mytable
WHERE name <> ''
AND ((date + 1 YEAR) >= (CURRENT_DATE))
AND ((date + 1 YEAR) < (CURRENT_DATE + 240 DAYS))
AND controlnum IN ('12345','8877788','23334455','223445988','222339000
+','2222333','223475699','22339668','2223999','009999','11220000','777
+66','88776'
,'9988888','11223756','22388577','9998888','998888','77665555','999988
+8','88877777','666555555','88777766666','112223333','9998877','998887
+7'
,'888777666666','00988777','887777654')
and date between '2010-11-11' and '2011-11-11' order by date desc");
=cut
If you show where you see the error it would help! Thanks

Nothing wrong really, but you ask for efficiency and speed, and anything counts...

You use: AND ((date + 1 YEAR) < (CURRENT_DATE + 240 DAYS))

Let's see the algoritm. For each record you sum 365 to date and remember this number, then you sum 240 to current date and save this to a new var somewhere in the memory, then you compare both numbers

I'm suggesting this instead: AND ((date + 125 DAYS) < CURRENT_DATE)

Now for each record you sum 125 to date and compare this with current date. You are creating a new var, not two.

Thus you need less memory and is also a little less demanding operation for the CPU. Nothing to care for when you have a few sums but in this context could suppose one advantage

And now for the sql question, if not done yet, you should index or reindex your table (the controlnum field is a good candidate to be in the index) and probably use some views in order to check only a part of your table, and simplify the work to perl.

Create A New User
Node Status?
node history
Node Type: note [id://929583]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2018-02-25 10:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
When it is dark outside I am happiest to see ...

Results (312 votes). Check out past polls.

Notices?