Re: Perl or SQL ?

Here's my take on this. Given that the sysaudits tables can be very large I would use a perl script to generate the view, and then use bcp to extract the data.

My reasoning is that bcp will pull data out from Sybase much faster than DBD::Sybase can (much less overhead), so assuming that the views cover a fairly large subset of the sysaudits tables you could potentially gain quite a lot of time.

The view is only needed so that you can use bcp (bcp will not accept any WHERE qualifiers as you know, but others here might not), and generating it in the perl script on the fly and dropping it once the request is done seems perfectly reasonable. You would probably want to create the view in the tempdb database to avoid any unnecessary database polution.

Michael

Comment on Re: Perl or SQL ?

Replies are listed 'Best First'.
Re^2: Perl or SQL ? by Anonymous Monk on Dec 21, 2005 at 10:06 UTC
As you know my original thought was to go down the create view/bcp data out route as you have suggested also. The problem is that I need to run a regex over the extracted data and I figure it's easier to do this via perl than inside sql given the complexities of what I need to do. Here is the test code I ended up with (some of which you may recognise ). This is taking the pure select route rather than the create view/bcp route. However I have a problem I getting the regex to work. This is a snapshot of the dataset 92 1 24 Dec 20 2005 2:14PM 1 14 2 sa tempdb select * from sysuser +s 92 1 24 Dec 20 2005 2:14PM 1 14 2 sa tempdb select * from master. +.sysprocesses 92 1 24 Dec 20 2005 2:14PM 1 14 2 sa tempdb select * from sysuser +s 92 1 24 Dec 20 2005 2:14PM 1 14 2 sa tempdb select * from master. +.sysprocesses 92 1 25 Dec 20 2005 2:14PM 1 14 2 sa tempdb use tempdb 92 1 25 Dec 20 2005 2:14PM 1 14 2 sa tempdb select * from sysuser +s 92 1 26 Dec 20 2005 2:14PM 1 14 2 sa tempdb use tempdb 92 1 26 Dec 20 2005 2:14PM 1 14 2 sa tempdb select @@version 92 1 26 Dec 20 2005 2:14PM 1 14 2 sa tempdb COMMIT TRAN 92 1 26 Dec 20 2005 2:14PM 1 14 2 sa tempdb create table #ttt (fo +o varchar(20), bar int) 92 1 26 Dec 20 2005 2:14PM 1 14 2 sa tempdb insert #ttt values('a + string', 1) 92 1 26 Dec 20 2005 2:14PM 1 14 2 0001fb520002 sa tempdb insert #t +tt values('another string', 2) 92 1 26 Dec 20 2005 2:14PM 1 14 2 0001fb520002 sa tempdb insert #t +tt values('foodiboo', 3) 92 1 26 Dec 20 2005 2:14PM 1 14 2 0001fb520002 sa tempdb ROLLBACK TRAN Here's the code my $lastrunt = "Dec 20 2005 9:00AM"; my @sqlcmd; $dbh->do("use sybsecurity"); $sth=$dbh->prepare("select * from sysobjects where name like 'sysaudit +s_%'"); $sth->execute; while ($data = $sth->fetch) { push @sqlcmd,"select extrainfo from @$data where suid in (select + a.suid from master..syslogins a, master..s ysloginroles b where b.srid=0 and a.suid=b.suid and a.suid not in (1,8 +))) and event=92 and eventtime > '$lastrunt'"; push @sqlcmd," union"; } pop @sqlcmd; $sth=$dbh->prepare("@sqlcmd"); $sth->execute; while ($data = $sth->fetch) { # print "@$data\n"; if($data =~ /create/) { print "@$data\n"; } } [download] I was trying to only pick out the create statements but I don't get anything. If I uncomment the print then the above dataset is returned. I'm trying to get to grips with Perl but can't figure out what is wrong with my regex statement ?	[reply] [d/l]
Re^3: Perl or SQL ? by mpeppler (Vicar) on Dec 22, 2005 at 08:38 UTC
$sth->fetch returns an array reference, and $sth->fetchrow returns a list. Each item in the array or list is a column in the SELECT statement. So: `$sth = $dbh->prepare("select a, b, c from foo"); $sth->execute; while($row = $sth->fetch) { # $row->[0] is column 'a' # $row->[1] is column 'b', # etc... }` [download] and `while(($one, $two, $three) = $sth->fetchrow_array) { # $one, $two and $three are columns a, b and c }` [download] Michael	[reply] [d/l] [select]
Re^3: Perl or SQL ? by Anonymous Monk on Dec 21, 2005 at 10:29 UTC
I've just figured it out `$sth=$dbh->prepare("@sqlcmd"); $sth->execute; while (($data) = $sth->fetchrow) { if($data =~ /create/) { print "$data\n"; } }` [download] I've simplified the regex I really need to use to demonstrate what I was attempting to do. I agree that for larger datasets the create view/bcp out is the way to go. Performance testing has revealed this to be the case. Thanks for all the responses, this is a top class site	[reply] [d/l]
Re^4: Perl or SQL ? by Anonymous Monk on Dec 21, 2005 at 10:44 UTC
Err I need to eat humble pie. The above code only works if I change the select to retrieve one column from sysaudits not all columns. Please disregard my previous post. I still have the same problem	[reply]


Don't ask to ask, just ask
	PerlMonks