Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread SELECT Queries causing locking
Mon, Sep 29 2008 5:54 PMPermanent Link

Michael
DBISAM v4.25 Build 6.  

We have reports that take from 1 to 5 minutes to run.  The report runs a single SQL Script
that contains about 4 actual SQL selects.  The first three are populating memory tables
and the last select is combining all the results.

They have 4 users all setup in Client-Server mode and the database on a dedicated dual
processor server with 4gb of ram.  Everything works very fast until someone starts one of
these reports.  As soon as someone starts a report, all other users are blocked from
making any edits until the report is finished.  That means they are sitting there with an
hourglass for 1 to 5 minutes.  As soon as the report is finished, all waiting transactions
go through just fine.

The SQL Script is about 99% optimized according to the "Generate Plan" results in DBSYS.
It is a complicated SQL with a lot of tables and I'm not worried about the 1 to 5 minutes
the report is taking.  I'm just worried about the other users being locked from making
edits.  The customer is about to bail and purchase another program.

Does anyone know how to improve this or what I might be doing wrong?

--
Thanks,
Michael B
Tue, Sep 30 2008 5:56 AMPermanent Link

"Eduardo [HPro]"
Michael

Could you post the SQL statement here ? If so, we can help you to find some
other option to perform the same task.

Eduardo

Tue, Sep 30 2008 8:27 AMPermanent Link

Michael
Eduardo,

It isn't one specific SQL statement.  The entire custom reporting engine lets our users
select different query parameters, report settings, etc.  Based on the users input we
generate the needed SQL and present a report to the user through our report writer (ACE by
SCTAssociates).  There are thousands of combinations for these reports so posting one
isn't going to help much.  

They are just select statements, no updating.  They range from a single SQL SELECT to
multiple SQL SELECTs submitted to the server in one query as a script.  They take anywhere
from 1 to 5 minutes to run but are almost all 100% optimized according to the "Generate
Plan".  They just take a while because they are going through a couple hundred thousand
records with a lot of joins.  

Is it normal for all edit/update actions to be blocked while doing a select query?  That
doesn't make a ton of sense and I've not seen that with the other database systems I've
used (SQL Server, Firebird, SqlAnywhere).

--
Michael


"Eduardo [HPro]" <contato@hpro.com.br> wrote:

Michael

Could you post the SQL statement here ? If so, we can help you to find some
other option to perform the same task.

Eduardo
Tue, Sep 30 2008 8:42 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Michael,

Are the queries being executed under a transaction ?

--
Fernando Dias
[Team Elevate]

Tue, Sep 30 2008 12:46 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Michael,

<< They are just select statements, no updating.  They range from a single
SQL SELECT to multiple SQL SELECTs submitted to the server in one query as a
script.  They take anywhere from 1 to 5 minutes to run but are almost all
100% optimized according to the "Generate Plan".  They just take a while
because they are going through a couple hundred thousand records with a lot
of joins.

Is it normal for all edit/update actions to be blocked while doing a select
query?  That doesn't make a ton of sense and I've not seen that with the
other database systems I've used (SQL Server, Firebird, SqlAnywhere). >>

No, DBISAM does not block writers when executing a query, as long as the
query is optimized.  If you're seeing readers blocking writers, then you've
got an issue with a query executing an entire table scan, or close to it,
which will cause a read lock to be held on the table for most of the
duration of the scan.

As Eduardo has pointed out, there's no way we can help you further without
seeing at least an execution plan of a query that is having this issue.
Generalities don't really help since they don't give us any specific
information that we can comment on.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Sep 30 2008 4:44 PMPermanent Link

Michael
Here is the plan.  Almost near the bottom is one UN-OPTIMIZED scan selection on
 trans_type = 0 OR NOT ta_linkto_trans IS NULL

There is an index on trans_type and ta_linkto_trans is from a table that is connected
through an outer join.  Would this be the cause and if so, how can that statement be
optimized?

There are other SQL statements that look like they could be eliminated completely but
remember that the entire SQL is generated from a set of components that is taking into
consideration multiple user inputs and those components are used for all reports.


================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

SELECT

    b1.bp_internal_id ,

    b1.bp_linkto_pa ,

    b1.bp_type
INTO

    MEMORY\bp_filter
FROM

    billing_parties b1

    INNER JOIN billing_parties b2_pa ON b1.bp_linkto_pa = b2_pa.bp_linkto_pa
WHERE

    (b2_pa.bp_level = 0)

   AND ((b1.bp_type = 0) OR (b1.bp_type = 1) OR ((b1.bp_type = 2) AND
(b1.bp_level in (2))))

   AND (b2_pa.bp_linkto_th IN (84))

   AND (b2_pa.bp_type = 0)
GROUP BY

    b1.bp_internal_id

Tables Involved
---------------

billing_parties (b1) table opened shared, has 18794 rows
billing_parties (b2_pa) table opened shared, has 18794 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

bp_internal_id

Result set will be ordered by the temporary index created for the grouping

WHERE Clause Execution
----------------------

The expression:

b2_pa.bp_level = 0

is OPTIMIZED, covers 11940 rows or index keys, costs 108654 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

The expression:

b1.bp_type = 0 OR b1.bp_type = 1 OR b1.bp_type = 2 AND b1.bp_level in (2)

is PARTIALLY-OPTIMIZED, covers 9755 rows or index keys, costs 278992 bytes, and
will be applied to the billing_parties table (b1) before any joins

The expression:

b2_pa.bp_linkto_th IN (84)

is OPTIMIZED, covers 96 rows or index keys, costs 6614 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

The expression:

b2_pa.bp_type = 0

is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

Join Ordering
-------------

The driver table is the billing_parties table (b1)


The billing_parties table (b1) is joined to the billing_parties table (b2_pa)
with the INNER JOIN expression:


b1.bp_linkto_pa = b2_pa.bp_linkto_pa

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

Optimized Join Ordering
-----------------------

The driver table is the billing_parties table (b2_pa)


The billing_parties table (b2_pa) is joined to the billing_parties table (b1)
with the INNER JOIN expression:


b2_pa.bp_linkto_pa = b1.bp_linkto_pa

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

b2_pa.bp_linkto_pa = b1.bp_linkto_pa

is OPTIMIZED

================================================================================
>>>>> 38 rows affected in 1.732 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

CREATE INDEX i_bp_filter ON MEMORY\bp_filter (bp_internal_id)

================================================================================
>>>>> 0 rows affected in 0 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

SELECT

    b1.bp_internal_id ,

    b1.bp_linkto_pa ,

    b1.bp_type
INTO

    MEMORY\pa_filter
FROM

    billing_parties b1

    INNER JOIN billing_parties b2_pa ON b1.bp_linkto_pa = b2_pa.bp_linkto_pa
WHERE

    (b2_pa.bp_level = 0)

   AND ((b1.bp_type = 0) OR (b1.bp_type = 1) OR ((b1.bp_type = 2) AND
(b1.bp_level in (2))))

   AND (b2_pa.bp_linkto_th IN (84))

   AND (b2_pa.bp_type = 0)
GROUP BY

    b1.bp_linkto_pa

Tables Involved
---------------

billing_parties (b1) table opened shared, has 18794 rows
billing_parties (b2_pa) table opened shared, has 18794 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

bp_linkto_pa

Result set will be ordered by the temporary index created for the grouping

WHERE Clause Execution
----------------------

The expression:

b2_pa.bp_level = 0

is OPTIMIZED, covers 11940 rows or index keys, costs 108654 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

The expression:

b1.bp_type = 0 OR b1.bp_type = 1 OR b1.bp_type = 2 AND b1.bp_level in (2)

is PARTIALLY-OPTIMIZED, covers 9755 rows or index keys, costs 278992 bytes, and
will be applied to the billing_parties table (b1) before any joins

The expression:

b2_pa.bp_linkto_th IN (84)

is OPTIMIZED, covers 96 rows or index keys, costs 6614 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

The expression:

b2_pa.bp_type = 0

is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be
applied to the billing_parties table (b2_pa) before any joins

Join Ordering
-------------

The driver table is the billing_parties table (b1)


The billing_parties table (b1) is joined to the billing_parties table (b2_pa)
with the INNER JOIN expression:


b1.bp_linkto_pa = b2_pa.bp_linkto_pa

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

Optimized Join Ordering
-----------------------

The driver table is the billing_parties table (b2_pa)


The billing_parties table (b2_pa) is joined to the billing_parties table (b1)
with the INNER JOIN expression:


b2_pa.bp_linkto_pa = b1.bp_linkto_pa

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

b2_pa.bp_linkto_pa = b1.bp_linkto_pa

is OPTIMIZED

================================================================================
>>>>> 19 rows affected in 0.078 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

CREATE INDEX i_pa_filter ON MEMORY\pa_filter (bp_linkto_pa)

================================================================================
>>>>> 0 rows affected in 0.016 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

SELECT

    trans_internal_id ,

    trans_linkto_bp ,

    trans_linkto_pa
INTO

    MEMORY\trans_filter
FROM

    transactions
WHERE

    (trans_type <> 0)

   AND (trans_type <> 2)

   AND (trans_type <> 3)

   AND (trans_type <> 4)

   AND (trans_date >= '2008-01-01')

   AND (trans_date < '2008-05-01')

Tables Involved
---------------

transactions (transactions) table opened shared, has 185019 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

trans_type <> 0 AND trans_type <> 2 AND trans_type <> 3 AND trans_type <> 4 AND
trans_date >= '2008-01-01' AND trans_date < '2008-05-01'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 0 rows or index keys,
costs 0 bytes, and will be applied to the transactions table (transactions)
before any joins

================================================================================
>>>>> 11152 rows affected in 9.392 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

CREATE INDEX i_trans_filter ON MEMORY\trans_filter (trans_internal_id)

================================================================================
>>>>> 0 rows affected in 0.047 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

SELECT

    bp.bp_last_name + ', ' + bp.bp_first_name + IF
(LENGTH(bp.bp_middle_initial) > 0 THEN ' ' + bp.bp_middle_initial ELSE '') AS
bp_name ,

    trans_internal_id ,

    trans_type ,

    pa.bp_last_name + ', ' + pa.bp_first_name + IF
(LENGTH(pa.bp_middle_initial) > 0 THEN ' ' + pa.bp_middle_initial ELSE '') AS
pa_name ,

    trans_date ,

    trans_writeoff ,

    trans_sess_charge ,

    trans_amount ,

    trans_amount - trans_sess_charge + trans_writeoff AS trans_tax ,

    trans_expected_amount ,

    trans_pay_method ,

    trans_pay_check_num ,

    sess_diag_1 ,

    sess_diag_2 ,

    sess_diag_3 ,

    sess_diag_4 ,

    sess_diag_codeptr1 ,

    sess_diag_codeptr2 ,

    sess_diag_codeptr3 ,

    sess_diag_codeptr4 ,

    sess_units ,

    ta_amount ,

    IF (trans_type = 0 THEN ser_name  ELSE IF (trans_type = 1 THEN 'Payment'
ELSE IF (trans_type = 2 THEN 'Adjustment' ELSE IF (trans_type = 3 THEN
'Startup Balance' ELSE IF (trans_type = 4 THEN 'Interest' ELSE 'Unknown')))))
AS trans_desc ,

    ta_linkto_trans ,

    trans_linkto_th ,

    trans_linkto_pr ,

    trans_linkto_acc ,

    pa.bp_internal_id AS pa_bp_internal_id ,

    pa.bp_linkto_pa ,

    pa.bp_last_name AS pa_last_name ,

    pa.bp_first_name AS pa_first_name ,

    pa.bp_middle_initial AS pa_middle_initial ,

    bp.bp_internal_id ,

    bp.bp_type ,

    bp.bp_level ,

    IFNULL (th_first_name THEN ' Transactions with no Therapist selected' ELSE
th_last_name + ', ' + th_first_name + IF (LENGTH(th_middle_initial) > 0 THEN
' ' + th_middle_initial ELSE '') + IF (LENGTH(th_degree) > 0 THEN ' ' +
th_degree ELSE '')) AS group_name ,

    trans_linkto_th AS group_id
FROM

    transactions

    INNER JOIN MEMORY\trans_filter ON trans_internal_id =
trans_filter.trans_internal_id

    INNER JOIN billing_parties pa ON trans_linkto_pa = pa.bp_linkto_pa

    INNER JOIN MEMORY\pa_filter ON pa.bp_linkto_pa = pa_filter.bp_linkto_pa

    LEFT OUTER JOIN session ON trans_internal_id = sess_linkto_trans

    LEFT OUTER JOIN service_library ON trans_linkto_ser = ser_internal_id

    LEFT OUTER JOIN transaction_amount ON trans_internal_id = ta_linkto_trans

    INNER JOIN billing_parties bp ON ta_linkto_bp = bp.bp_internal_id

    INNER JOIN MEMORY\bp_filter ON ta_linkto_bp = bp_filter.bp_internal_id

    LEFT OUTER JOIN therapist_facesheet ON trans_linkto_th = th_internal_id
WHERE

    (pa.bp_type = 0)

   AND (trans_date >= '2008-01-01')

   AND (trans_date < '2008-05-01')

   AND ((trans_type = 0 OR (NOT ta_linkto_trans IS NULL)))
ORDER BY

    group_name ,

    group_id ,

    trans_date ,

    pa.bp_last_name ,

    pa.bp_first_name ,

    pa.bp_middle_initial ,

    pa.bp_internal_id ,

    trans_internal_id ,

    bp.bp_type ,

    bp.bp_level NOCASE

Tables Involved
---------------

transactions (transactions) table opened shared, has 185019 rows
trans_filter (trans_filter) table opened shared, has 11152 rows
billing_parties (pa) table opened shared, has 18794 rows
pa_filter (pa_filter) table opened shared, has 19 rows
session (session) table opened shared, has 107435 rows
service_library (service_library) table opened shared, has 115 rows
transaction_amount (transaction_amount) table opened shared, has 244330 rows
billing_parties (bp) table opened shared, has 18794 rows
bp_filter (bp_filter) table opened shared, has 38 rows
therapist_facesheet (therapist_facesheet) table opened shared, has 133 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-insensitive
temporary index:

group_name ASC
group_id ASC
trans_date ASC
pa_last_name ASC
pa_first_name ASC
pa_middle_initial ASC
pa_bp_internal_id ASC
trans_internal_id ASC
bp_type ASC
bp_level ASC

WHERE Clause Execution
----------------------

The expression:

pa.bp_type = 0

is OPTIMIZED, covers 7901 rows or index keys, costs 225968 bytes, and will be
applied to the billing_parties table (pa) before any joins

The expression:

trans_date >= '2008-01-01'

is OPTIMIZED, covers 52759 rows or index keys, costs 823040 bytes, and will be
applied to the transactions table (transactions) before any joins

The expression:

trans_date < '2008-05-01'

is OPTIMIZED, covers 161604 rows or index keys, costs 2521022 bytes, and will
be applied to the transactions table (transactions) before any joins

Join Ordering
-------------

The driver table is the transactions table (transactions)


The transactions table (transactions) is joined to the trans_filter table
(trans_filter) with the INNER JOIN expression:


trans_internal_id = trans_filter.trans_internal_id


The transactions table (transactions) is joined to the billing_parties table
(pa) with the INNER JOIN expression:


trans_linkto_pa = pa.bp_linkto_pa


The transactions table (transactions) is joined to the session table (session)
with the LEFT OUTER JOIN expression:


trans_internal_id = sess_linkto_trans


The transactions table (transactions) is joined to the service_library table
(service_library) with the LEFT OUTER JOIN expression:


trans_linkto_ser = ser_internal_id


The transactions table (transactions) is joined to the transaction_amount table
(transaction_amount) with the LEFT OUTER JOIN expression:


trans_internal_id = ta_linkto_trans


The transactions table (transactions) is joined to the therapist_facesheet
table (therapist_facesheet) with the LEFT OUTER JOIN expression:


trans_linkto_th = th_internal_id


The billing_parties table (pa) is joined to the pa_filter table (pa_filter)
with the INNER JOIN expression:


pa.bp_linkto_pa = pa_filter.bp_linkto_pa


The transaction_amount table (transaction_amount) is joined to the
billing_parties table (bp) with the INNER JOIN expression:


ta_linkto_bp = bp.bp_internal_id


The transaction_amount table (transaction_amount) is joined to the bp_filter
table (bp_filter) with the INNER JOIN expression:


ta_linkto_bp = bp_filter.bp_internal_id

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

Optimized Join Ordering
-----------------------

The driver table is the pa_filter table (pa_filter)


The pa_filter table (pa_filter) is joined to the billing_parties table (pa)
with the INNER JOIN expression:


pa_filter.bp_linkto_pa = pa.bp_linkto_pa


The billing_parties table (pa) is joined to the transactions table
(transactions) with the INNER JOIN expression:


pa.bp_linkto_pa = trans_linkto_pa


The transactions table (transactions) is joined to the trans_filter table
(trans_filter) with the INNER JOIN expression:


trans_internal_id = trans_filter.trans_internal_id


The transactions table (transactions) is joined to the session table (session)
with the LEFT OUTER JOIN expression:


trans_internal_id = sess_linkto_trans


The transactions table (transactions) is joined to the service_library table
(service_library) with the LEFT OUTER JOIN expression:


trans_linkto_ser = ser_internal_id


The transactions table (transactions) is joined to the transaction_amount table
(transaction_amount) with the LEFT OUTER JOIN expression:


trans_internal_id = ta_linkto_trans


The transactions table (transactions) is joined to the therapist_facesheet
table (therapist_facesheet) with the LEFT OUTER JOIN expression:


trans_linkto_th = th_internal_id


The transaction_amount table (transaction_amount) is joined to the
billing_parties table (bp) with the INNER JOIN expression:


ta_linkto_bp = bp.bp_internal_id


The transaction_amount table (transaction_amount) is joined to the bp_filter
table (bp_filter) with the INNER JOIN expression:


ta_linkto_bp = bp_filter.bp_internal_id

Scan Expression Execution
-------------------------

The expression:

trans_type = 0 OR NOT ta_linkto_trans IS NULL

is UN-OPTIMIZED and will be applied to each candidate row in the result set as
the result set is generated

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

pa_filter.bp_linkto_pa = pa.bp_linkto_pa

is OPTIMIZED

The expression:

pa.bp_linkto_pa = trans_linkto_pa

is OPTIMIZED

The expression:

trans_internal_id = trans_filter.trans_internal_id

is OPTIMIZED

The expression:

trans_internal_id = sess_linkto_trans

is OPTIMIZED

The expression:

trans_linkto_ser = ser_internal_id

is OPTIMIZED

The expression:

trans_internal_id = ta_linkto_trans

is OPTIMIZED

The expression:

ta_linkto_bp = bp.bp_internal_id

is OPTIMIZED

The expression:

ta_linkto_bp = bp_filter.bp_internal_id

is OPTIMIZED

The expression:

trans_linkto_th = th_internal_id

is OPTIMIZED

================================================================================
>>>>> 1 rows affected in 4.914 seconds
================================================================================
Wed, Oct 1 2008 12:39 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Michael,

<< Here is the plan.  Almost near the bottom is one UN-OPTIMIZED scan
selection on trans_type = 0 OR NOT ta_linkto_trans IS NULL

There is an index on trans_type and ta_linkto_trans is from a table that is
connected through an outer join.  Would this be the cause and if so, how can
that statement be optimized? >>

That statement won't be an issue since it won't cause a read lock to be held
on the source table.  The one statement that strikes me as odd, however, is
this one:

trans_type <> 0 AND trans_type <> 2 AND trans_type <> 3 AND trans_type <> 4
AND
trans_date >= '2008-01-01' AND trans_date < '2008-05-01'

11152 rows affected in 9.392 seconds

It indicates that the I/O cost is 0 rows, yet 11152 rows are generated in
the result set.  Could you possibly email me the transactions table ?  I'm
going to have to evaluate this one to see:

1) Why the I/O cost projection is wrong, and
2) Why it is taking 9 seconds to generate 11,000+ rows.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image