Re: Perl DBI (sybase) rapidly delete large number of records

It sounds like the bottelneck is communication cost, not perl precisely, so I don't think there's any way around it. If the subselect solves the problem, I'd say you have the solution. Using perl, select the values into a temporary table and then (using perl), use the subselect?

If that doesn't sound right, I may not understand the problem.

-Paul

Comment on Re: Perl DBI (sybase) rapidly delete large number of records

Replies are listed 'Best First'.
Re^2: Perl DBI (sybase) rapidly delete large number of records by simm42 (Novice) on May 07, 2007 at 10:23 UTC
The processing to find the right ids to delete is fairly complex and is a lot quicker to do in perl rather than in the database, so I have to pull the data from the db, process it which generates the list of ids to delete - its getting that list back into the database that I end up having to shell out for - would prefer to be able to do that in perl but I just cant get close to the performance of bcp	[reply]
Re^3: Perl DBI (sybase) rapidly delete large number of records by jettero (Monsignor) on May 07, 2007 at 10:48 UTC
There's about a million variations of this problem. It would help if you could submit some code and/or data that demonstrated how it is that you're sending the temporary data to the database and which (may) illuminate possible solutions. Presently, it feels like there is no solution; but that seems to be false since you have some way to "shell out" to produce the temp table. UPDATE: No, no, you miss my point entirely. If you can find a way to "shell out" and do what you want, then there's almost certainly a way to do it in perl. I still don't think I know enough about the problem or what you're doing when you "shell out" to be able to provide more information. -Paul	[reply]
Re^4: Perl DBI (sybase) rapidly delete large number of records by simm42 (Novice) on May 07, 2007 at 11:59 UTC
The shell call is to a program called bcp - a sybase utility which copies data from a file (with specified field widths or delimiters) into a table. I think you are right though that there is probably no solution in perl, bar possibly the one below of doing the delete in blocks, I suspect the bcp method is going to prove faster though, given it is an optimised utility.... just a shame its closed source - might have been nice to build a module incorporating it...	[reply]