"Harry Rossignol" <harrywr2@comcast.net> writes:
> This statement via SQLexec executes in the blink of an eye
> DECLARE XNKPE BINARY CURSOR for SELECT wtsnzblob FROM "NZRECS" WHERE
> wtsevent = '05002' AND wtspaymeth = '$' AND wtspayno = '' AND ( wtstrxtm
>> '19691231 160000' OR (wtstrxtm = '19691231 160000' AND wtscpu > '0')
> OR (wtstrxtm = '19691231 160000' AND wtscpu = '0' AND wtsuser > '0'))
> ORDER BY wtstrxtm,wtscpu,wtsuser LIMIT 64
> This statement via SQLExecParams can be measured in 10's of seconds, and
> takes an amount of time that is linear to the size of the database, even
> though the number of records that equal parameter 1, wtsevent is static.
> DECLARE XNKPE BINARY CURSOR for SELECT wtsnzblob FROM "NZRECS" WHERE
> wtsevent = $1 AND wtspaymeth = $2 AND wtspayno = $3 AND ( wtstrxtm > $4
> OR (wtstrxtm = $4 AND wtscpu > $5) OR (wtstrxtm = $4 AND wtscpu = $5 AND
> wtsuser > $6)) ORDER BY wtstrxtm,wtscpu,wtsuser LIMIT 64
Could we see the EXPLAIN ANALYZE for both cases? Also the table schema
(particularly the available indexes).
(Note: easiest way to get EXPLAIN ANALYZE by hand for a parameterized
query is to PREPARE it and then issue EXPLAIN ANALYZE EXECUTE.)
regards, tom lane