Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

...for Sybase the optimizer will use a generic weighting when it doesn't know the SARGS...

Well, yes, and that's exactly the problem. Generic weighting simply tells you that you have a highly-unselective index, and, thus, the index will never be used.

This is actually a common case with columns like "status" on task-tracking or ticket-tracking systems. If you've got a database with a million tasks, and nearly all of them are "COMPLETED", but the ones that you are typically interested in are "OPEN" (a small minority of the total population), then, bang, it's the exact issue I showed. You RDBMS will say "I don't know what value they want... but I know that the average value occupies half of the table. Accessing half of a table by index is a bad idea, so I'll full-table-scan."

And, yeah, we are getting away from perl somewhat, but the original post was really about database performance tuning, so I don't feel so bad answering in kind. Also, there are a lot of "grey areas" around perl that folks on this site deal with a lot: CGI, HTML, shell/command-line, file-system, and databases. If perl is commonly used as a glue between these pieces... the line starts to blur a little bit with respect to what is fair game for discussion.

------------
:Wq
Not an editor command: Wq

Comment on Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

Replies are listed 'Best First'.

Re: Re: Re: Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE
by mpeppler (Vicar) on Nov 15, 2003 at 01:12 UTC

This is actually a common case with columns like "status" on task-tracking or ticket-tracking systems.

select count(*), event_status from text_events group by event_status;
             event_status
 ----------- ------------
          26            0
     2352103            2
       81921            3
[download]

I first ran a SQL snippet like this:

declare @status int
select @status = 0
select * from text_events where status = @status
[download]

Then I tried a short DBI script, using placeholders:

my $sth = $dbh->prepare("select * from payment_db..text_events where e
+vent_status = ?");
$sth->execute(1);
fetch_all($sth);
$sth->execute(0);
fetch_all($sth);
[download]

Michael

[reply]
[d/l]
[select]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks